E-R to Relational Mapping Algorithm (Step-by-Step with Examples)
The E-R to Relational Mapping Algorithm converts an Entity-Relationship (ER) diagram into a Relational Schema (set of tables). This is essential to implement a database design in SQL.
There are six main steps in the algorithm:
Step 1: Mapping of Regular (Strong) Entity Types
- Create a relation (table) for each strong entity.
- Each attribute becomes a column.
- The primary key of the entity becomes the primary key of the relation.
Example:
ER Diagram:
Entity: Student (sid, name, age) Primary Key: sid
Relational Schema:
Student(sid PRIMARY KEY, name, age)
Step 2: Mapping of Weak Entity Types
- Create a table for the weak entity.
- Include all attributes.
- Add the primary key of the owner entity as a foreign key.
- The primary key is the combination of the weak entity’s partial key and the owner’s key.
Example:
ER Diagram:
Entity: Dependent (name, age) Weak Entity of: Employee (eid) Partial key: name
Relational Schema:
Dependent(eid, name, age,
PRIMARY KEY (eid, name),
FOREIGN KEY (eid) REFERENCES Employee(eid))
Step 3: Mapping of Binary 1:1 Relationships
- Choose one of the participating entities.
- Include the primary key of the other entity as a foreign key.
- Add any attributes of the relationship.
- Add a unique constraint on the foreign key to ensure 1:1.
Example:
ER Diagram:
Employee(eid), ParkingLot(pid) 1:1 relationship: Assigned
Relational Schema:
Employee(eid PRIMARY KEY, name, pid UNIQUE,
FOREIGN KEY (pid) REFERENCES ParkingLot(pid))
Step 4: Mapping of Binary 1:N Relationships
- Add a foreign key in the table on the N side pointing to the 1 side.
- Include attributes of the relationship if any.
Example:
ER Diagram:
Department(did), Employee(eid) 1:N relationship: Works_in
Relational Schema:
Employee(eid PRIMARY KEY, name, did,
FOREIGN KEY (did) REFERENCES Department(did))
Step 5: Mapping of Binary M:N Relationships
- Create a separate relation (table) for the relationship.
- Include primary keys of both participating entities as foreign keys.
- These combined keys become the primary key of the relationship table.
- Include relationship attributes if any.
Example:
ER Diagram:
Student(sid), Course(cid) M:N relationship: Enrolled Attributes: grade
Relational Schema:
Enrolled(sid, cid, grade,
PRIMARY KEY (sid, cid),
FOREIGN KEY (sid) REFERENCES Student(sid),
FOREIGN KEY (cid) REFERENCES Course(cid))
Step 6: Mapping of Multivalued Attributes
- Create a new relation.
- Include the primary key of the original entity and the multivalued attribute.
- The combination is the primary key.
Example:
ER Diagram:
Student(sid, name) Multivalued attribute: phone
Relational Schema:
Phone(sid, phone,
PRIMARY KEY (sid, phone),
FOREIGN KEY (sid) REFERENCES Student(sid))
Summary Table:
| Step | ER Concept | Mapping Result |
|---|---|---|
| 1 | Strong Entity | One table with primary key and attributes |
| 2 | Weak Entity | Table with owner’s key + partial key as primary key |
| 3 | 1:1 Relationship | Foreign key in one table with UNIQUE constraint |
| 4 | 1:N Relationship | Foreign key in N-side table |
| 5 | M:N Relationship | Separate table with both keys and relationship attributes |
| 6 | Multivalued Attribute | New table with entity key + multivalued attribute |