CS 430 Database Theory

1. CS 430Database Theory Winter 2005 Lecture 15: How to Convert an ER Model to Relations

2. Issue • We do Conceptual Modeling using ER Models • Our databases are relational • Look a little like ER models but not exactly • How to we convert an ER model to relations for an RDBMS • Note: Typically the resulting relations are 3NF

3. ER Model • ER Model has: • Entities, Relationships, Attributes • Relationships may have attributes • May have multi-valued and composite attributes • To convert to Relation Tables: • Need Keys for Entities • Relationships need cardinalities, e.g. 1:1, 1:n, m:n • Example ER Model: • Figure 7.1 from Text Book

4. Steps • Convert Independent (Strong, Regular) Entities • Convert Dependent (Weak) Entities • Convert Binary Relations • 1:1 • 1:n • m:n • Convert N-Ary Relationships (N>2) • Convert Multi-Valued Attributes

5. Convert Independent Entities • Create a new Relation for the Entity • Make all simple (non-multi-valued, non-composite) attributes of the Entity into attributes of the Relation • For single-valued composite attributes, use the underlying simple attributes • Choose some Key attribute(s) to be the Primary Key

6. Result So Far EMPLOYEE DEPARTMENT PROJECT

7. Convert Dependent Entities • Create a new Relation for the Entity • Make all simple attributes into attributes of the relation (same as for Independent Entities) • Add the primary key attribute(s) of the owning entity as a foreign key for the weak entity • Convert entities in order: If one weak entity owns another, convert the owner first • Add ON UPDATE and ON DELETE • The usual approach is CASCADE

8. Result So Far EMPLOYEE DEPARTMENT PROJECT DEPENDENT

9. Map 1:1 Relations • Foreign Key approach: • Include the primary key of one relation as a foreign key in the other • Include attributes of the relationship with the foreign key • Merged Relationship • Merge the two relations • Cross Reference • Add a new table with the keys from both entities (see m:n for example of this)

10. Result So Far EMPLOYEE DEPARTMENT PROJECT DEPENDENT

11. Map 1:n Relations • Let R be the “1 side”, S the “n side” • Include R’s key as a foreign key in S • Include any attributes of the relation together with the foreign key in S • Or use the Cross Reference table approach (m:n approach)

12. Result So Far EMPLOYEE DEPARTMENT PROJECT DEPENDENT

13. Map m:n Relations • Create a new Relation to represent the Relationship • Include the keys of the related entities as foreign keys in the relation • Make the key of the relation the combined keys of the participating entities • Include any simple attributes of the relationship as attributes of the relation • Include CASCADE for UPDATE and DELETE (typical)

14. Result So Far EMPLOYEE DEPARTMENT PROJECT WORKS_ON DEPENDENT

15. Map N-Ary Relationships • Treat the same as mapping m:n relationships • Create a new relation • Make the keys of all the parents, foreign keys of the relation • Make the key the combined foreign keys • Copy any simple attributes of the relationship as attributes of the relation

16. Map Multi-Valued Attributes • Create a new Relation to represent the Attribute • Include the key of the parent Relation as a foreign key for the new relation • Include the Multi-Valued Attribute as an attribute of the relation • Make the primary key the combination of the parent key and the attribute • CASCADE on UPDATE and DELETE • Note: If you have a composite multi-valued attribute, check to make sure you don’t have a weak entity • Impacts the set of attributes that make up the primary key

17. Final Result EMPLOYEE DEPARTMENT DEPT_LOCATIONS PROJECT WORKS_ON DEPENDENT