Database Design Lecture 7 part 2 Mapping ERD

Database Design Lecture 7 part 2 Mapping ERD to Tables Database Design Slide 1

Conversion to Tables q To implement the database in relational DBMS, ERD must be translated to tables – different strategies exist, resulting different tables. – general rules are ØThe tables should accurately represent the information modeled in the ERD. Øchoose strategies that result in less redundancy, and fewer null values. Database Design Slide 2

Conversion of Entities q A (strong) entity set reduces to a table with the same attributes and PK. – If composite attributes exist, only their component simple attributes are needed. – Derived attributes are usually omitted. – Usually NOT including multi_valued attributes either – every multi-valued attribute is put in a separate table. Database Design Slide 3

Conversion of Multi_valued Attributes q A multivalued attribute M of an entity E is represented by a separate table EM – Table EM has attributes corresponding to the primary key EPK of E and an attribute corresponding to multivalued attribute M. – E. g. Multivalued attribute dependent-names of employee is represented by a table employee-dependent-names(employee-id, dname) – Each value of the multivalued attribute maps to a separate row of the table EM – The PK of table EM may be (EPK, M) or M. – EPK is a FK of EM. Database Design Slide 4

Conversion of Weak Entities q A weak entity set becomes a table that includes the primary key of the owner entity. The primary key consists of the PK of the owner entity, possibly together with some attributes of the weak entity. Example: Employee 1 has M Dependents(employee. No, dependent. Name, …) Database Design Slide 5

Conversion of Relationships q Let R be the relationship set involving multiple entities. Let A 1, . . . , Am be the primary key attributes of the participating entities. Let B 1, . . . , Bn be the attributes of the relationship set. Then the relationship R can be represented by the relation R(A 1, . . . , Am , B 1, . . . , Bn ) q This is a general rule for converting relationships. The primary key should be chosen from A 1, . . . , Am according to situation. A 1, . . . , Am are foreign keys. . For binary 1: M or 1: 1 relationships, the conversions can be (better) done as shown later. q q q Database Design Slide 6

Conversion of Binary Relationships q q Many-to-many binary relationship set – create a table with columns for the PKs of the two participating entity sets, and any descriptive attributes of the relationship set. The PK consists of the PKs of the two entities. The PKs of the two entities also serve as foreign keys referencing the entities. One-to-many binary relationship sets – Instead of using a separate table for the relationship, just modify the tables for the two entities: Ø add the PK of the one side to the many side. It also serves as a FK of the many side. Øattributes of the relationship also go to the many-side. – if optional on the many-side, can result in null values. Database Design Slide 7

Conversion of Binary Relationships q One-to-one relationship sets – mandatory participation on both sides Øadd the PK attributes of one side, and attributes of the relationship, to the other side. – mandatory on one side Øadd the PK attributes of the optional side, and attributes of the relationship, to the mandatory side. – Optional on both sides Ø choose one side and add its PK, and attributes of the relationship, to the other side. ØMay have null values. Maybe using a separate relation for the relationship is better. Database Design Slide 8

1: 1 Unary Relationships q Follow rules for 1: 1 binary relationship. – mandatory participation on both sides: single relation with two copies of the primary key (one needs to be renamed), plus attributes of the relationship. – mandatory participation on only one side: option to create a single relation with two copies of the primary key, or create a new relation to represent the relationship. The new relation would only have two attributes (if the relationship does not have attributes itself and the PK of the entity has one attribute), both are copies of the primary key. – optional participation on both sides, again create a new relation as described above. Database Design Slide 9

Example 1—M: N relationship M STUDENT N enrol date st. No st. Name SUBJECT s. Code s. Name Student(st. No, st. Name) Subject(scode, s. Name) Enrol(st. No, scode, date) FKs: st. NO reference Student(st. No) scode reference Subject(s. Code) Database Design Slide 10

Example 2 – 1: M relationship Staff 1 teach M Subject s. Code staf. No semester s. Name staf. Name Staff(staf. No, staf. Name) Subject(s. Code, s. Name, staf. No, semester) Database Design Slide 11

Example 3 – 1: 1 relationship Staff staf. No 1 Has 1 Office o. No location staf. Name Staff(staf. No, staf. Name) Staff. Office(staf. No, o. No, location) Database Design Slide 12

Example 4 - 1: 1 relationship Staff 1 Has staf. No 1 Office o. No location staf. Name Staff. Office(staf. No, staf. Name) Office(o. No, location, staf. No ) Database Design Slide 13

Example 5 1 Staff 1 Has staf. No Office o. No location staf. Name Staff(staf. No, staf. Name) Office(o. No, location, staff. No) Database Design Slide 14

Example 6 Staff 1 staf. No staf. Name Has 1 Next-of-Kin kin. Name kintelephone Staff(Staf. No, staf. Name) Staff. NOK(staf. No, kin. Name, kintelephone) Database Design Slide 15

Example 7 - Unary relationship Staff staf. No 1 M Supervise staf. Name Staff(staf. No, staf. Name, supervisorstaf. No) Database Design Slide 16