Converting ERDs to Relational Tables

1. Converting ERDs to Relational Tables Joe Meehean

2. The Players • ERDs • easy to reason about • express lots of information in limited space • easy to create from business narrative • Relational Tables • easy for DBMSs to store data in tables • use SQL to ask lots of different questions about data • Need to convert ERDs to Relational tables • using a set of rules and some intuition

3. Entity Type Rule • each entity becomes a table • primary key of entity is primary key of table • attributes become columns Student Student ID Last name First name

4. 1-M Relationship Rule • primary key of parent becomes a foreign key in table of child entity • child entity is entity near Crow’s Foot symbol • if minimum cardinality on parent side is 1 (required), foreign key cannot accept null value

5. 1-M Relationship Rule Offering Teaches Section # Room Time Faculty Faculty ID Last name First name Offering

6. M-N Relationship Rule • M-N relationship becomes it own table • primary key is combined key formed from primary keys of participating entities Offering Section # Room Time Enrolls Student Student ID Last name First name Enrolls

7. Identification Dependency Rule • add a component to the primary key of the weak entity • primary key = • primary key of weak entity (if any) • + • primary keys from independent entities

8. Identification Dependency Rule Offering Course Has Number Name Credits Section # Room Time Offering

9. Questions?

10. In Motorcycle Order Parts In Has Part# Description Quantity Order# Date $Total ID# Quantity Style In QUIZ BREAK!!! Has Distributor Supplier Employee JobTitle Supplies Customer# Name Address Supplier# Name Address Employee# Name Years Position# Name BaseSalary Years Has- JobTitle Has- Empl

11. Optional 1-M Relationships Rule • Optional relationship: minimal cardinality of 0 on parent side (1-side) • Convert using the 1-M rule • foreign key in child table (M-side) • foreign key can be NULL • can be problem for queries

12. Optional 1-M Relationships Rule Offering Teaches Section # Room Time 12 Faculty Faculty ID Last name First name Offering

13. Optional 1-M Relationships Rule • Optionally, can use Optional 1-M Relationship Rule • relationship becomes its own table • primary keys in both entities become foreign keys • primary key from child entity (M-side) becomes primary key in new table

14. Optional 1-M Relationships Rule Offering Teaches Section # Room Time 14 Faculty Offering Faculty ID Last name First name Teaches

15. Optional 1-M Relationships Rule • When to used Optional 1-M Relationship Rule • its optional • Optional rule makes more tables • more complex • more SQL operations (slower) • 1-M rule makes NULL foreign keys • can be difficult to deal with • 3rd option • replace optional relationship with required relationship and default value

16. Optional 1-M Relationships Rule Offering Teaches Section # Room Time 16 Faculty Faculty ID Last name First name Offering

17. Generalization Hierarchy Rule • Each entity in a generalization hierarchy becomes a table • Includes only attributes in entity • not its ancestors • Except it includes ancestors primary key • uses it as its own primary key • also a foreign key • Perform cascading deletes • if ancestor is deleted • so is subtype table entry

18. Generalization Hierarchy Rule Faculty College People Student Department Office Major Grad Date College ID Last name First name Faculty College People

19. 1-1 Relationship Rule • Put a foreign key in each table in the relationship • Unless one table will have many NULL foreign keys • Then drop the foreign key in the table where it will be mostly NULL

20. 1-1 Relationship Rule Chairs 20 Department Faculty Faculty Department Faculty ID Last name First name Dept. Name Funding

21. 1-1 Relationship Rule Chairs 21 Department Faculty Faculty Department Faculty ID Last name First name Dept. Name Funding

22. Self Referencing Entities • Apply same rules • 1-M rule • add a new column with primary key as foreign key • M-N rule • add a new table representing the relationship

23. Self Referencing Entities Manages Employee Employee Employee ID Last name First name

24. Self Referencing Entities Prerequisite Course Number Name Credits Prerequisites Course

25. Converting ERD Review • Every entity becomes a table • Some relationships become tables • Majority of conversion rules dictate where foreign key goes • foreign key links row in table to primary key in another table • M-N: new table with foreign keys from both entities • 1-M: foreign key in M entity • optional 1-M: more complex

26. Questions?

27. QUIZ BREAK!!! • Convert to tables Major Advisor Faculty Student Faculty ID Last name First name Student ID Last name First name

28. QUIZ BREAK!!! • Convert to tables Salary Employee Hourly Salary Contract Expires Hours Rate Employee ID Last name First name

29. QUIZ BREAK!!! • Convert to tables Building Room Building ID Name Address Room # Capacity In

30. QUIZ BREAK!!! • Convert to tables Faculty Faculty ID First Name Last Name Married