Mapping an ERD to a Relational Database

1. Mapping an ERD to a Relational Database To map an ERD to a relational database, five rules are defined to govern how tables are constructed. • Rule for entity types • Rule for relationships • Rule for attributes • Rule for generalization/specialization hierarchies (will not be discussed in this course) 5) Rule for participation constraint

2. Entities Each entity set is implemented with a separate relation. • Strong Entities Strong, or regular, entities are mapped to their own relation. The PK (Primary Key) is chosen from the set of keys available.

3. Example: Strong Entities d A c e c d e A

4. Entities Each entity set is implemented with a separate relation. • Weak Entities Weak entities are mapped to their own relation The PK of the weak entity is the combination of the PKs of entities related through identifying relationships and the discriminator (partial key) of the weak entity.

5. Example: Weak Entities d c e A B c x y B y x

6. Relationships We’ll consider binary relationships only. All relationships involve the use of foreign keys: the PK attribute of one entity set is added as an attribute to the relation representing the other entity set. a.   Binary One-To-One In general, with a one-to-one relationship, you have a choice regarding where to implement the relationship. You may choose to place a foreign key in one of the two relations, or in both.

7. Example: 1-1 d c e c is a FK in B A 1 B x y c 1 B y x

8. Example: 1-1 d c e X is a FK in A A 1 A c … X 1 B y x

9. If the participation constraint on an entity set is mandatory, we must choose the table for that entity set. d c e X is a FK in B A 1 A c … X 1 B y x

10. Relationships • Binary One-To-Many With a one-to-many relationship you must place a foreign key in the relation corresponding to the many side of the relationship.

11. Example: 1-n d c e c is a FK placed on the “many” side of the relationship A 1 B n x y c B y x

12. Example: 1-n d c e s is also involved in the table A 1 s B n x y c s B y x

13. Relationships • Binary Many-To-Many A many-to-many relationship must be implemented with a separate relation for the relationship. This new relation will have a composite primary key comprising the primary keys of the participating entity sets plus any discriminator attribute.

14. Example: m-n x and c are FKs, and together they form the PK of AB d c e A AB m x c s s n B y x

15. Attributes All attributes, with the exception of derived and composite attributes, must appear in relations. • Simple, atomic These are included in the relation created for the pertinent entity set, many-to-many relationship, or n-ary relationship.

16. Example: Atomic Attributes d A c e c d e A

17. Attributes • Multi-valued Each multi-valued attribute is implemented using a new relation. This relation will include the primary key of the original entity set. The primary key of the new relation will be the primary key of the original entity set and the multi-valued attribute. Note that in this new relation, the attribute is no longer multi-valued.

18. Example: Multi-valued Attributes d A AE c e c d c e A e is a multi-valued attribute.

19. Attributes • Composite Composite attributes are not included. However the atomic attributes comprising the composite attribute must appear in the pertinent relation.

20. Example: Composite Attributes d A e c n c d e A

21. Generalization/Specialization Hierarchies 91.2914 can ignore this section

22. c x d y A B 5. Participation constraints If a relationship is mandatory for an entity set, then if the entity set is on the “many” side of the relationship, then a specification is required to ensure a foreign key has a value, and that it cannot be null • setting the ‘required’ property for the FK in MS Access, or • NOT NULL constraint in the DDL. N 1

23. A B c d x y c The “required” property for attribute c is set “yes”.

24. Setting the required property to Yes

25. c x d y A B 5. Participation constraints Otherwise, if the entity set is on the “one” side of a relationship, then a check constraint or database trigger can be specified to ensure compliance. N 1

26. A B c d x y c A program should be produced to check that any value appearing in c-column in table A must appear at least once in c-column in table B.

27. Example The company database keeps track of a company’s employees, departments, and projects: Requirements: concerning the department: 1. company is organized into departments 2. a department has a unique name, a unique number, and a specific employee is its’ manager 3. we track the start date for the manager function 4. a department may be in several locations 5. a department controls a number of projects concerning the project: 6. a project has a unique name, a unique number, and is in a single location

28. example continued concerning the employee: 7. each employee has a name, social insurance number, address, salary, sex, and birth date 8. an employee is assigned to one department but may work on several projects which are not necessarily controlled by the same department 9. we track the number of hours per week that an employee works on each project 10. we keep track of the direct supervisor of each employee 11. we track the dependents of each employee (for insurance purposes) concerning the dependent: 12. we record each dependent’s first name, sex, birth date, and relationship to the employee

29. The entities: employee dependent department project

30. The entities: fname lname minit dependent salary address name sex relationship ssn name sex birthdate employee bdate name number location project department name number location

31. With relationships: 1 works for N department 1 1 controls manages employee 1 N supervisee supervisor N N M supervision 1 works on 1 project dependents of N partial constraint dependent total constraint

32. name location number 1 fname lname works for minit N department salary address name sex 1 number of employees startdate 1 ssn controls manages employee 1 bdate N supervisor hours N degree supervisee M supervision works on 1 project dependents of name number location N dependent relationship name sex birthdate

33. EMPLOYEE fname, minit, lname, ssn, bdate, address, sex, salary, superssn, dno DEPARTMENT Dname, dnumber, mgrssn, mgrstartdate Dnumber, dlocation PROJECT DEPT _LOCATIONS Pname, pnumber, plocation, dnum WORKS_ON Essn, pno, hours DEPENDENT Essn, dependentname, sex, bdate, relationship

34. Summary • Entities a. Each entity set is implemented with a separate relation. • Weak Entities Weak entities are mapped to their own relation The PK of the weak entity is the combination of the PKs of entities related through identifying relationships and the discriminator (partial key) of the weak entity;

35. 2. All relationships involve foreign keys. If the relationship is identifying then the primary key of the strong entity must be propagated to the relation representing the weak entity. • Binary One-To-One In general, with a one-to-one relationship, you have a choice regarding where to implement the relationship. You may choose to place a foreign key in one of the two relations, or in both. b. Binary One-To-Many With a one-to-many relationship you must place the foreign key in the relation corresponding to the many side of the relationship.

36. c. Binary Many-To-Many A many-to-many relationship must be implemented with a separate relation for the relationship. This new relation will have a composite primary key comprising the primary keys of the participating entity sets plus any discriminator attribute. d. n-ary, n>2 new relation is generated for the n-ary relationship. This new relation will have a composite primary key comprising the primary keys of the participating entity sets plus any discriminator attribute. If the cardinality related for any entity set is 1, then the primary key of that entity set is only included as a foreign key and not as part of the primary key of the new relation.

37. 3. Attributes a. Simple, atomic These are included in the relation created for the pertinent entity set, many-to-many relationship, or n-ary relationship. b. Multi-Valued A multi-valued attribute is implemented using a new relation. This relation will include the primary key of the entity set. The primary key of the new relation will be the primary key of the entity set and the multi-valued attribute. Note that in this relation, the attribute is no longer multi-valued. c. Composite Composite attributes must be replaced by their equivalent atomic attributes in the pertinent relation.

38. 4. Generalization/Specialization Hierarchies There are several options available for this case … (ignore for 91.2914) 5. Participation constraints. If a relationship is mandatory for an entity set, then if the entity set is on the “many” side of the relationship, a specification is required (a NOT NULL constraint) to ensure a foreign key has a value, and that it cannot be null. If the entity set is on the “one” side of a relationship, then that “one” side will be chosen as the primary table and the other is chosen as the referenced table. A NOT NULL constraint for the foreign key should be specified.