CSE 480: Database Systems

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

2. The ER diagram for COMPANY database

3. Mapping ER diagram into relational schema

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

5. Mapping ER diagram into relational schema • Map everything into 1 table DEPARTMENT EMPLOYEE 1 1 MANAGES N 1 WorksFor Too much redundancy

6. Outline • Mapping ER Constructs to Relations • 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)

7. C D R ID B ID C D E Entity type, E Relation, R ER-to-Relational Mapping Algorithm • Step 1: Mapping of Regular (Strong) Entity Types • For each strong entity type E, create a relation R • Include all the simple attributes of E as columns in R • Include component attributes of a composite attribute as columns in R • Ignore the derived attributes • 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.

8. Example PRIMARY KEY (Ssn)

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

10. ER-to-Relational Mapping Algorithm • Step 2: Mapping of Weak Entity Types • For each weak entity type W with owner entity type O, create a relation R • Include all simple attributes of W as columns in R • Include components of a composite attribute as columns in R • Include primary key attribute(s) of the the owner entity type(s) O as foreign key attributes of R • 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 P O W 1 N R Id Q S A

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

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

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

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

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

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

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

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

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

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

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

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

23. Summary (Binary M-N Relationships) For each binary M:N relationship type R Create a new relation R 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. Include simple attributes of the relationship type R as attributes of the relation R T S M N R

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

25. Example

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

27. Ternary relationship types

28. Exercise