ER Schema to Relational DB Schema

• Procedures to create a relational DB schema from an Entity-Relationship (ER) schema are presented
• Uses a seven-step algorithm to convert the basic ER model

ER Model Conversion

• Converts entity types (strong and weak)
• Converts binary relationships (with various constraints)
• Converts n-ary relationships
• Converts attributes (simple, composite, and multivalued) into relations

Step 1: Mapping Regular Entity Types

• For each regular (strong) entity type E in the ER schema, create a relation R
• Relation R includes all the simple attributes of E
• Only the simple component attributes of a composite attribute are included
• The key attribute of E is set as the primary key for R
• If the chosen key of E is a composite, then the set of simple attributes that form the composite key set the primary key for R
• The relations EMPLOYEE, DEPARTMENT, and PROJECT( and there attributes) correspond to the regular entity types EMPLOYEE, DEPARTMENT, and PROJECT
• The Ssn, Dnumber, and Pnumber are primary keys for the relations EMPLOYEE, DEPARTMENT, and PROJECT, respectively

Step 2: Mapping Weak Entity Types

• For each weak entity type W in the ER schema with an owner entity type E, create a relation R
• Relation R includes all simple attributes (or simple components of composite attributes) of W as attributes of R
• Also, include as foreign key attributes of R, the primary key attribute(s) of the relation(s) that correspond to the owner
• The relation DEPENDENT corresponds to the weak entity type DEPENDENT
• The primary key Ssn of the EMPLOYEE relation corresponds to the owner entity type as a foreign key attribute of DEPENDENT, and is renamed Essn
• The primary key of the DEPENDENT relation is the combination {Essn, Dependent_name (renamed from Name)}

Step 3: Mapping Binary 1:1 Relationship Types

• For each binary 1:1 relationship type R in the ER schema, identify the relations S and T that correspond to the entity types participating in R
• Choose one of the relations (S say) and include as a foreign key in S the primary key of T
• Choose the entity type with total participation in R to serve in the role of S
• The 1:1 relationship type MANAGES is mapped by choosing the participating entity type DEPARTMENT to serve in the role of S since its pariticipation is total
• The primary key of the EMPLOYEE relation is included as a foreign key in the DEPARTMENT

Step 4: Mapping Binary 1:N Relationship Types

• For each regular binary 1:N relationship type R, identify the relation S that represents the participating entity type at the N-side of the relationship type
• Include as foreign key in S the primary key of the relation T that represents the other entity type participating in R
• The 1:N relationship types WORKS_FOR, CONTROLS, and SUPERVISION are mapped
• For WORKS_FOR the primary key Dnumber of the DEPARTMENT relation is included in the EMPLOYEE relation as a foreign key and is called Dno
• For SUPERVISION the primary key of the EMPLOYEE relation is included in the EMPLOYEE relation itself as foreign key and is called Super_ssn
• The CONTROLS relationship is mapped to the foreign key attribute Dnum of PROJECT referencing the primary key Dnumber of the DEPARTMENT

Step 5: Mapping Binary M:N Relationship Types

• For each binary M:N relationship type R, create a new relation S to represent R
• Include as foreign key attributes in S the primary keys of the relations that represent the participating entity types; their combination will form the primary key of S
• Also include any simple attributes of the M:N relationship type as attributes of S
• An M:N relationship type cannot be represented by a single foreign key attribute in participating relations because of the M:N cardinality ratio, so create a separate relationship relation S
• The M:N relationship type WORKS_ON is mapped by creating the relation WORKS_ON
• The primary keys of the PROJECT and EMPLOYEE relations are included in WORKS_ON as foreign keys (renamed them as Pno and Essn)
• Also include an attribute Hours in WORKS_ON to represent the Hours attribute of the relationship type
• The primary key of the WORKS_ON relation is the combination of Essn and Pno

Step 6: Mapping Multivalued Attributes

• For each multivalued attribute A, create a new relation R
• Relation R will include an attribute corresponding to A, plus the primary key attribute K as a foreign key in R of the relation that represents the entity type or relationship type that has A as a multivalued attribute
• The relation DEPT_LOCATIONS is created
• The attribute Dlocation represents the multivalued attribute LOCATIONS of DEPARTMENT
• Dnumber represents the primary key of the DEPARTMENT relation as an attribute of DEPT_LOCATIONS
• The primary key of DEPT_LOCATIONS is the combination of Dnumber and Dlocation

Step 7: Mapping of N-ary Relationship Types

• For each n-ary relationship type R (n > 2), create a new relation S to represent R
• Include as foreign key attributes in S the primary keys of the relations that represent the participating entity types
• Also include any simple attributes of the n-ary relationship type
• The primary key of S is usually a combination of all the foreign keys that reference the relations representing the participating entity types
• The relationship type SUPPLY can be mapped to the relation SUPPLY, whose primary key is the combination of the three foreign keys {Sname, Part_no,