1 / 17

CS 430 Database Theory

CS 430 Database Theory. Winter 2005 Lecture 15: How to Convert an ER Model to Relations. 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

yamka
Download Presentation

CS 430 Database Theory

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related