💻
Unit 5: Database Design
9.1 Relational Database Design Using ER-to-Relational Mapping
9.1.1 ER-to-Relational Mapping Algorithm
This section details the steps to convert an ER schema into a relational
database schema using the COMPANY database as an example.
Unit 5: Database Design 1
Steps of the ER-to-Relational Mapping Algorithm:
1. Mapping of Regular Entity Types
Create a relation R for each strong entity type E.
Include all simple attributes of E in R.
Use a key attribute of E as the primary key for R.
Example: Relations EMPLOYEE, DEPARTMENT, and PROJECT are
created from their corresponding entity types with primary keys Ssn,
Dnumber, and Pnumber respectively.
2. Mapping of Weak Entity Types
Create a relation R for each weak entity type W.
Include all simple attributes of W and the primary key of the owner
entity type as a foreign key in R.
Unit 5: Database Design 2
The primary key of R is a combination of the owner’s primary key and
the weak entity’s partial key.
Example: DEPENDENT relation includes the primary key Ssn of
EMPLOYEE as a foreign key (renamed Essn), and its primary key is
{Essn, Dependent_name}.
3. Mapping of Binary 1:1 Relationship Types
Three approaches:
1. Foreign key approach: Include the primary key of one entity type as
a foreign key in the other.
2. Merged relation approach: Merge the entities into a single relation
if both participations are total.
3. Cross-reference approach: Create a separate relation for the
relationship.
Example: The MANAGES relationship is mapped by including the
primary key of EMPLOYEE as a foreign key (Mgr_ssn) in DEPARTMENT,
and including the attribute Start_date.
4. Mapping of Binary 1:N Relationship Types
Two approaches:
1. Foreign key approach: Include the primary key of the entity on the
1-side as a foreign key in the entity on the N-side.
2. Relationship relation approach: Create a separate relation for the
relationship.
Example: WORKS_FOR, CONTROLS, and SUPERVISION relationships
are mapped using foreign keys. For WORKS_FOR, Dnumber from
DEPARTMENT is included in EMPLOYEE as Dno.
5. Mapping of Binary M:N Relationship Types
Create a new relation S to represent the M:N relationship.
Include the primary keys of the participating entity types as foreign
keys in S, forming the primary key of S.
Example: WORKS_ON relation includes primary keys of PROJECT (Pno)
and EMPLOYEE (Essn) and an attribute Hours.
6. Mapping of Multivalued Attributes
Unit 5: Database Design 3
Create a new relation R for each multivalued attribute.
Include an attribute corresponding to the multivalued attribute and the
primary key of the entity as a foreign key.
The primary key of R is the combination of the multivalued attribute and
the entity’s primary key.
Example: DEPT_LOCATIONS includes Dlocation and Dnumber as the
primary key.
7. Mapping of N-ary Relationship Types (n > 2)
Create a new relationship relation S.
Include the primary keys of the participating entity types as foreign
keys.
The primary key of S is usually a combination of all foreign keys.
Example: SUPPLY relation includes Sname, Part_no, and Proj_name as
foreign keys.
Unit 5: Database Design 4
For Chapter 14 Please go through the following ppt
Elmasri_6e_Ch15.ppt
For Chapter 15 go through the following ppt up to page 22
Elmasri_6e_Ch16.[Link]
Unit 5: Database Design 5
