Entity-Relationship Model Ch. 3

1. Entity-Relationship ModelCh. 3 Mr John Ortiz

2. Overview of Database Design Steps in building a database for an application • Requirement analysis. Specify what need to be modeled in a real-world domain • Conceptual design. Specify a high level schema using a database design model • Logical design. Refine a conceptual design to a schema using the data model of a DBMS • Physical design. Define the schema using the DDL of the DBMS • Database creation. Load data into a database Entity-Relationship Model

3. Requirement Analysis • Need to interact with domain experts • Application specific • Time consuming • Very important • Difficult to be thorough Entity-Relationship Model

4. A Sample Requirement Consider a regional hospital. • There are doctors, patients, nurses, prescriptions, tests, … • Each doctor has Id, Name, Specialty, Age, Sex, … • Patients see doctors, doctors prescribe drugs to patients, … • Each patient has one primary care physician, a nurse is assigned to at most 5 patients, … Entity-Relationship Model

5. Conceptual Design • Design a high level schema based on requirements • Why do we do a conceptual design? • To communicate with domain experts (use concepts of the application domain). • It is easier to set up a good model (focus on data content not structure) • Flexible (system independent, hmm… ) • How to describe a conceptual design? • Use an ER Diagram (most popular) Entity-Relationship Model

6. Entity-Relationship Model • Proposed by Peter Chen in 1976 • Historically very popular • Graphical • Major concepts: entity type, relationship type, attribute, constraints, … Entity-Relationship Model

7. name ssn gpa Students Entity Type • Entity. A real-world object distinguishable from other objects. Each entity is described by a set of attributes (or properties). • Entity Type. A definition of a collection of entities with identical set of attributes.  Can informally think of entities as records Entity-Relationship Model

8. grade name title ssn credit gpa cno Students Courses Enrolled Relationship Type • Relationship. An association between two or more entities (usually of different types). A relationship may also have attributes. • Relationship Type. A definition of a collection of relationships with the identical set of attributes. Not every entity participates in a relationship Entity-Relationship Model

9. name fname minit lname hobby ssn gpa bdate Students age Attributes • Domain of an attribute. The set of values the attribute may take. • Many types of attributes. (simple vs composite, single-value vs multi-value, stored vs derived) Entity-Relationship Model

10. name name ssn lot qty name S# P# color city Employees Order Suppliers Parts super-visor subor-dinate Projects dept J# Reports_To Degree of Relationships • Degree. Number of entity types involved in a relationship type. (unary, binary, ternary, … ) • Role. Part played by entities of a unary relationship. Entity-Relationship Model

11. Key Constraint • Key Attribute(s). A set of attributes of an entity type whose values are unique for each entity of that type. • Key Constraint. Every (almost!) entity type must have a key attribute(s). (e.g., ssnof Students) Entity-Relationship Model

12. grade name title ssn gpa credit cno Students takes Courses Participation Constraint • ParticipationConstraint. Whether or not every entity in an entity type participates in a type of relationship. Either total or partial. Every course is taken by some students Entity-Relationship Model

13. name name since room dno ssn age Employees manages Departments 1 1 Cardinality Constraints • Cardinality Ratio. Whether or not an entity of a type may involve in a type of relationship with multiple entities of another type. • One-to-one (1-to-1). Each entity in E1 is associated with 0 or one entity in E2, and vice versa. Other notation exists Entity-Relationship Model

14. name name room gpa ssn ssn Professors Students 1 m advises Cardinality Constraints • One-to-many (1-to-m). Each entity in E1 is associated with 0 or more entities in E2, and each entity in E2 is associated with 0 or one entity in E1. The reverse is many-to-one (m-to-1). To determine for Student, fix a Professor, see how many students are advised by him. Entity-Relationship Model

15. Students Courses m n grade credit name cno gps title ssn takes Cardinality Constraints • Many-to-many (m-to-n). Each entity in E1 is associated with 0 or more entities in E2, and vice versa. This is the default if not labeled Entity-Relationship Model

16. m n Parts Suppliers order r Projects Multiway Relationships • What does it say? Pick an entity from each of the other 2 entity types, how many entities in the third entity type are associated with the pair? • What if r = 1? • What if both r =1 and n=1? Entity-Relationship Model

17. cname phone name type size city Universities Student_clubs 1 m have Weak Entity Type • Weak Entity Type. An entity type without its own key. Typically have a partial key. Must totally participate in an identifying relationship with a strong entity type. The relationship must be m-to-1 or 1-to-1. • How to refer to a weak entity? Entity-Relationship Model

18. (min, max) E R grade credit name cno gps title ssn Courses Students (1,5) (5,40) takes Extended ER Model (EER) • Structural Constraint. The min & max number of relationships in R that each entity in E must participate. Entity-Relationship Model

19. (0, 1) (0, 1) 1 1 R R E F E F (0, 1) (0, m) 1 m R R E F E F (0, m) (0, n) m n R R E F E F (1, 1) (0, m) 1 m R R E F E F Cardinality ve Structural Cardinality Structural • The labels are placed in reverse order. Entity-Relationship Model

20. Look Ahead • Next topic: Relational Data Model • Read from the textbook: • Chapter 7.1-7.3 Entity-Relationship Model