Lecture 1: Mapping ER Diagram to Relational Database PDF

Lecture 1 Mapping ER Diagram to a Relational Database Database 2 Dr. Mohamed Saied Amer Outline Mapping entities: The strong entities. The weak entities. Mapping relationships: The binary M : N relationships. The binary 1 : 1 relationships. The binary 1 : N relationsh...

Lecture 1 Mapping ER Diagram to a Relational Database Database 2 Dr. Mohamed Saied Amer Outline Mapping entities: The strong entities. The weak entities. Mapping relationships: The binary M : N relationships. The binary 1 : 1 relationships. The binary 1 : N relationships. Recursive relationships. N-ary (higher than binary) relationships. Map generalizations/specializations. M1. Map the strong entities For strong entities — create a new table (relation)for each strong entity and make the indicated key of the strong entity the primary key of the table. If more than one candidate key is indicated on the ER diagram, choose one as the primary key. We have three types of attributes: Atomic attribute Stud_id Address Composite attribute Student Multi-valued attribute name school First_name last_name  M1. Map the strong entities (Cont.) M1.A) For entities with atomic attributes: Mapped by forming columns from the atomic attributes. M1.B) For entities with composite attributes, form columns from the elementary (atomic) parts of the composite PK attributes. addr Stud_id Stud_id First_name Last_name school addr Student name school First_name Last_name  M1. Map the strong entities (Cont.) M1.C) For multi-valued attributes, form a separate table for the multi-valued attribute. Include the primary key from the original table as foreign key and the key of the new table will be the primary key. FK Addr Stud_id First_name Last_name School_id addr Stud_id Student PK School_id School_name name school First_name last_name  M2. Map the weak entities For Weak Entities — Create a new table for each weak entity. Do the same steps in M1 i.e. (M1.A, M1.B and M1.C in previous step). To relate the weak entity to its owner, include the primary key of the owner entity in the weak table as a foreign key. The primary key of the weak table will be the partial key. First_name First_name name name Last_name Last_name 1 N:0 Employee has Dependent Birth_date Employee_id insurance  M2. Map the weak entities (cont.) PK Employee Employee_id First_name Last_name 502 Ahmed Salem 520 Ali Kamal 710 Mohamed Nagi PK FK Dependent Dfirst_name Dlast_name Birth_date Insurance Employee_id Saad Ahmed 1/7/2000 3123123 502 Emad Ahmede 5/8/2005 2323423 502 Mona Ali 2/6/2001 2342342 520 M3. Map the binary M:N relationships. For each M : N relationship, create a new table with the primary keys of each of the two entities. The key of this new table will be the concatenated keys of each of the two owner entities. Course_id Credit_hrs PK Course_id Course_name Credit_hrs Course Course_name PK M Student_id Course_id Enroll address N PK Student name Student_id Name address Student_id M4. Map the binary 1:1 relationships For binary 1:1 relationships: Include the primary key of Entity A into Entity B as the foreign key. PK Student_id Car_id Student_id Name address Model Car Car_id Model Year Student_id 1 Year has FK address 1 Student name Student_id M5. Map the binary 1:N relationships Although most binary 1:N relationships are mapped with the PK/FK method, the separate table per rule M3a can be used. PK Employee_id First_name Last_name 502 Ahmed Salem 520 Ali Kamal 710 Mohamed Nagi PK FK Dfirst_name Dlast_name Birth_date Insurance Employee_id Saad Ahmed 1/7/2000 3123123 502 Emad Ahmede 5/8/2005 2323423 502 Mona Ali 2/6/2001 2342342 520 M6. Map recursive relationships. For recursive entities, two types of mapping rules have been First_name Last_name developed: name Employee_id For 1:N recursive relationships, re-include the primary key of the table with the recursive relationship in the same table. Person Employee_id First_name Last_name Manage_id 900 Ahmed Ali Null 905 Hossam Salem 900 1 N manage 907 Alaa Gamal 900 First_name Last_name For M:N recursive relationships, create a separate table for the name Employee_id relationship. Employee_id Manage_id Person 900 Null 905 900 M N manage M7. Map n-ary relationships. For each n –ary relationship, create a new table. In the table, include all attributes of the relationship. Then include all keys of connected entities as foreign keys and make the concatenation of the foreign keys the primary key of the new table. M8. Map generalizations/specializations. For each generalization/specialization entity situation, create one table for the generalization entity and create one table for each specialization entity. Put the attributes for each entity in the corresponding table. Add the primary key of the generalization entity into the specialization entity. M8. Map generalizations/specializations Ex. Summary of Mapping constructs and constraints Mapping Example  Thanks Any Questions