Entity-Relationship Model Ch 3 Mr John Ortiz Lecture

Entity-Relationship Model Ch. 3 Mr John Ortiz Lecture 2 Entity-Relationship Model

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 Lecture 2 Entity-Relationship Model 2

Requirement Analysis ® Need to interact with domain experts ® Application specific ® Time consuming ® Very important ® Difficult to be thorough Lecture 2 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, … Lecture 2 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) Lecture 2 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, … Lecture 2 Entity-Relationship Model 7

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. ssn name gpa Students Can informally think of entities as records Lecture 2 Entity-Relationship Model 8

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. grade name ssn gpa Students title cno Enrolled credit Courses Not every entity participates in a relationship Lecture 2 Entity-Relationship Model 9

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) fname ssn minit name lname gpa bdate hobby Students age Lecture 2 Entity-Relationship Model 10

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. name ssn lot P# Employees supervisor subordinate Reports_To Lecture 2 name qty S# color Order Parts J# Projects Entity-Relationship Model name city Suppliers dept 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. , ssn of Students) Lecture 2 Entity-Relationship Model 12

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

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 E 1 is associated with 0 or one entity in E 2, and vice versa. ssn name Employees age 1 since name dno manages 1 room Departments Other notation exists Lecture 2 Entity-Relationship Model 14

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

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

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

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. name city Universities cname size 1 have m phone type Student_clubs FHow to refer to a weak entity? Lecture 2 Entity-Relationship Model 18

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

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

Look Ahead ® Next topic: Relational Data Model ® Read from the textbook: æChapter 7. 1 -7. 3 Lecture 2 Entity-Relationship Model 21