From ER to Tables

1. From ER to Tables Mapping ER Diagrams to Tables

2. Designing the Tables First approach… • Draw a dependency diagram • Each statement is a single path through the diagram • Tables are formed by traversing the dependency diagram

3. Simpler method – Use ER Diagram Paths are hard to follow… • Create an ER Diagram using the Dependencies • Transform Entities into Tables • Transform Relationships into keys • You’re done! • No paths to follow!

4. Example ER Diagram Fname Lname Number Name N Gender 1 Locations Salary WORKS_FOR Name Address StartDate DEPARTMENT Snn EMPLOYEE 1 1 MANAGES Birthdate CONTROLS Hours N N 1 WORKS_ON 1 N PROJECT SUPERVISION DEPENDENTS_OF Location Name N Number DEPENDENT Name Gender Birthdate

5. since name dname ssn lot Employees Manages since Review Key Constraints An employee can work in many departments; a dept can have many employees. In contrast, each dept has at most one manager, according to the key constrainton Manages. did budget Departments Works_In

6. Review Participation Constraints • Does every employee work in a department? • If so, this is a participation constraint • Basically means “at least one” since since name name dname dname ssn did did budget budget lot Departments Employees Manages Works_In since

7. name cost pname age ssn lot Policy Dependents Employees Identify Weak Entities A weak entity can be identified uniquely only by considering the primary key of another (owner) entity. • Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner, many weak entities). • Weak entity set must have total participation in this identifying relationship set. Weak entities have only a “partial key” (dashed underline)

8. pname age name ssn lot Dependents Covers Employees Policies policyid cost name pname age ssn lot Dependents Employees Purchaser Beneficiary Better design Policies policyid cost Review Relationships If each policy is owned by just 1 employee: Bad design Key constraint on Policies would mean policy can only cover 1 dependent!

9. Review Relationships • Previous example illustrated a case when two binary relationships were better than one ternary relationship. • An example in the other direction: a ternary relation Contracts relates entity sets Parts, Departments and Suppliers, and has descriptive attribute qty. No combination of binary relationships is an adequate substitute.

10. Review Relationships qty S “can-supply” P, D “needs” P, and D “deals-with” S does not imply that D has agreed to buy P from S. Departments Parts Contract VS. Suppliers Parts Departments needs can-supply deals-with Suppliers

11. How do you make the Tables? Fname Lname Number Name N Gender 1 Locations Salary WORKS_FOR Name Address StartDate DEPARTMENT Snn EMPLOYEE 1 1 MANAGES Birthdate CONTROLS Hours N N 1 WORKS_ON 1 N PROJECT SUPERVISION DEPENDENTS_OF Location Name N Number DEPENDENT Name Gender Birthdate

12. Each Entity becomes a Table ENTITY TABLE PRIMARY KEY Employee Employee Employee_SSN Department Department Department_Number Project Project Project Number

13. 1:N Relationships - Create the Primary Key For each 1:N relationship – create the Primary Key from the entity on the 1 side to the entity on the N side where it will become a foreign key. EMPLOYEE WORKS_FOR DEPARTMENT N1 Department Number is moved into the Employee entity

14. Examine Weak Entities • The DEPENDENT entity cannot exist on its own and must have a parent entity (EMPLOYEE). • They are identified in a M:N relation as the ‘intersect entity’ with the primary key the composite of the primary keys of the two (or more) other ‘strong entities’

15. Weak Entities Become Tables as Well For each weak entity create a table - include the Primary Key of the owner tables The Primary Key becomes the: owner key plus the weak entity key Entity Table Primary Key Dependent Dependent Employee_SSN + Dependent_No

16. What about M:N Relationships? For each M:N relationship create a new table with the Primary Key being the the Primary Key of both entities involved in the relationship EMPLOYEE WORKS_ON PROJECT Empno Hours Projno E1 P1 3 E1 P2 4 E2 P1 5 E3 P2 3

17. Representing the M:N Relationships

18. What about Multi-valued Attributes? For each multi-valued attribute create a new relation. The Primary Key is the Primary Key of the entity plus the Multi-valued attribute. DEPARTMENT LOCATION TABLE PRIMARY KEY DEPARTMENT_LOCATIONS DEPARTMENT LOCATION D1 MELB D1 SYD D2 MELB

19. What about N-ary Relationships? For N-ary Relationships create a new entity and create the Primary Key of each entity involved in the relationship to the new entity Supplier supplies Parts from Cities TABLESPRIMARY KEY SUPPLIER_PARTS_CITIES SNo PNo City S1 NUT MELB S1 NUT SYD

20. The Finished Table Design EMPLOYEE Fname Lname Essn Bdate Address M/F Salary Superssn Dno p.k DEPARTMENT f.k Dname Dnumber Mgrssn Mgrstartdate p.k DEPT_LOCATIONS Dnumber Dlocation PROJECT WORKS_ON Pname Pnumber Plocation Dnumber f.k f.k EssnPnumber Hours DEPENDENT f.k NOT NULL NOT NULL EssnDependent_name M/F Bdate