1 / 16

Database Design Lecture 7 part 2 Mapping ERD to Tables

Database Design Lecture 7 part 2 Mapping ERD to Tables . Conversion to Tables. To implement the database in relational DBMS, ERD must be translated to tables different strategies exist, resulting different tables. general rules are

gzifa
Télécharger la présentation

Database Design Lecture 7 part 2 Mapping ERD to Tables

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. Database Design Lecture 7 part 2 Mapping ERD to Tables Database Design Slide 1

  2. Conversion to Tables • 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

  3. Conversion of Entities • 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

  4. Conversion of Multi_valued Attributes • 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 tableemployee-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

  5. Conversion of Weak Entities • 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 has Dependents Dependents(employeeNo, dependentName,…) 1 M Database Design Slide 5

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

  7. Conversion of Binary Relationships • 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

  8. Conversion of Binary Relationships • 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

  9. 1:1 Unary Relationships • 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

  10. sCode sName M N STUDENT enrol SUBJECT Example 1—M:N relationship date stNo stName Student(stNo, stName) Subject(scode, sName) Enrol(stNo, scode, date) FKs: stNO reference Student(stNo) scode reference Subject(sCode) Database Design Slide 10

  11. sCode sName stafNo stafName Example 2 – 1:M relationship M 1 Subject teach Staff semester Staff(stafNo,stafName) Subject(sCode, sName, stafNo, semester) Database Design Slide 11

  12. 1 1 oNo location stafNo stafName Example 3 – 1:1 relationship Staff Has Office Staff(stafNo, stafName) StaffOffice(stafNo, oNo, location) Database Design Slide 12

  13. 1 1 oNo location stafNo stafName Example 4- 1:1 relationship Staff Has Office StaffOffice(stafNo, stafName) Office(oNo, location, stafNo ) Database Design Slide 13

  14. Example 5 Staff Has Office Staff(stafNo, stafName) Office(oNo, location, staffNo) 1 1 oNo location stafNo stafName Database Design Slide 14

  15. Example 6 Staff Has Next-of-Kin 1 1 stafNo stafName kinName kintelephone Staff(StafNo, stafName) StaffNOK(stafNo, kinName, kintelephone) Database Design Slide 15

  16. Example 7- Unary relationship Staff Supervise Staff(stafNo, stafName, supervisorstafNo) 1 M stafNo stafName Database Design Slide 16

More Related