CSE 480 Database Systems Lecture 6 ER to

CSE 480: Database Systems Lecture 6: ER to Relational Mapping Reference: Read Chapter 9 of the textbook 1

The ER diagram for COMPANY database 2

Mapping ER diagram into relational schema 3

Mapping ER diagram into relational schema l Map everything as separate tables Proj. Number Proj. Name Proj. Number Proj. Location Query processing becomes expensive E. g. : Find me the names of all projects located in Michigan Need to perform many join operations 4

Mapping ER diagram into relational schema l Map everything into 1 table EMPLOYEE 1 MANAGES N 1 DEPARTMENT 1 Works. For Too much redundancy 5

Outline l Mapping ER Constructs to Relations – – – – l Step 1: Mapping of Regular (Strong) Entity Types Step 2: Mapping of Weak Entity Types Step 3: Mapping of Binary 1: 1 Relation Types Step 4: Mapping of Binary 1: N Relationship Types. Step 5: Mapping of Binary M: N Relationship Types. Step 6: Mapping of Multivalued attributes. Step 7: Mapping of N-ary Relationship Types. The procedure – Avoids generating too many unnecessary tables – Avoids too much redundancy in tables – More details in Chapter 10 (normal forms) 6

ER-to-Relational Mapping Algorithm l Step 1: Mapping of Regular (Strong) Entity Types – For each strong entity type E, create a relation R u Include all the simple attributes of E as columns in R u Include component attributes of a composite attribute as columns in R u Ignore the derived attributes u Choose one of the key attributes of E as the primary key for R. – If the chosen key attribute of E is composite, the set of simple attributes that form it will together form the primary key of R. C ID D B R ID C D E Entity type, E Relation, R 7

Example PRIMARY KEY (Ssn) 8

Example PRIMARY KEY (Dnumber) UNIQUE(Dname) secondary keys (can be null) PRIMARY KEY (Pnumber) UNIQUE(Pname) 9

ER-to-Relational Mapping Algorithm l Step 2: Mapping of Weak Entity Types – For each weak entity type W with owner entity type O, create a relation R O 1 Id P W N R Q A S u Include all simple attributes of W as columns in R u Include components of a composite attribute as columns in R u Include primary key attribute(s) of the owner entity type(s) O as foreign key attributes of R u Primary key of R is the combination of the primary key(s) of the owner(s) and the partial key of the weak entity type W 10

ER-to-Relational Mapping Algorithm l Step 2: Mapping of Weak Entity Types P O W 1 Id N R Q A S O W Id A Oid P R S PRIMARY KEY (Oid, P) W(Oid) REFERENCES O(Id) ON DELETE CASCADE 11

Example Ssn EMPLOYEE 1 PRIMARY KEY(Essn, Dependent_name) DEPENDENT(Essn) REFERENCES EMPLOYEE(Ssn) ON DELETE CASCADE 12

ER-to-Relational Mapping Algorithm l Step 3: Mapping of Binary 1 -to-1 Relationship types A A A 1 1 1 R R R 1 1 1 B Cross-reference approach: 3 Tables B Foreign key approach: 2 Tables Merged relation approach: 1 Table 13

Cross-Reference Approach Start. Date EMPLOYEE 1 MANAGES 1 DEPARTMENT Cross-reference approach: MANAGES(Dnumber) REFERENCES DEPARTMENT(Dnumber) MANAGES(Mgr_ssn) REFERENCES EMPLOYEE(Ssn) 14

Foreign Key Approach Total participation Start. Date EMPLOYEE 1 MANAGES 1 DEPARTMENT Foreign key approach: DEPARTMENT(Mgr_ssn) REFERENCES EMPLOYEE(Ssn) Mgr_ssn is NOTNULL 15

Merged Relation Approach Total participation Start. Date MANAGER 1 MANAGES 1 DEPARTMENT MANAGER Merged relation approach: DEPARTMENT_W_MANAGER 16

Summary (Binary 1 -1 Relationships) Cross-reference: P S 1 1 R T Merged relation: P S 1 R Foreign key: 1 T 17

ER-to-Relational Mapping Algorithm l Step 4: Mapping of Binary 1: N Relationship Types. P S 1 R N T T_R(SID) references S(SID) 18

Example WORKS_FOR Supervisor Subordinate EMPLOYEE 1 N 1 DEPARTMENT N SUPERVISES 19

Summary (Binary 1 -N Relationships) P S N R 1 T – For each binary 1: N relationship type R u Identify the relation S that represents the participating entity type at the N-side of the relationship type. u Include as foreign key in S the primary key of the T u Include any simple attributes of R as attributes of S. 20

ER-to-Relational Mapping Algorithm l Step 5: Mapping of Binary M: N Relationship Types. P S M R N T R(SID) references S(SID) R(TID) references T(TID) Primary key(SID, TID) 21

Example Hours EMPLOYEE M N PROJECT WORKS_ON Primary key(Essn, Pno) 22

Summary (Binary M-N Relationships) S M R N T – For each binary M: N relationship type R u Create a new relation R u Include as foreign key attributes in R the primary keys of the relations that represent the participating entity types; their combination will form the primary key of R. u Include simple attributes of the relationship type R as attributes of the relation R 23

ER-to-Relational Mapping Algorithm l Step 6: Mapping of Multivalued attributes. B A K S – For each multivalued attribute A, create a new relation T. u Include attribute corresponding to A u Include primary key attribute K of relation S – as a foreign key in T u Primary key of T is the combination of A and K 24

Example 25

ER-to-Relational Mapping Algorithm l Step 7: Mapping of N-ary Relationship Types. T S R P – For each n-ary relationship type R, create a new relation R u Include primary keys of the relations participating in the relationship type as foreign key attributes in R u Include any simple attributes of the n-ary relationship type R as attributes of R 26

Ternary relationship types 27

Exercise 28