Transforming ER Model to Relational Model

Introduction

The ER Model is used to design a database conceptually.
But relational databases store data in the form of tables.

Therefore, we convert the ER model into tables using transformation rules.
This process is called Mapping from ER Model to Relational Model.

In simple terms:

  • Entities → Tables
  • Attributes → Columns
  • Relationships → Foreign Keys or New Tables

Let us understand the rules step by step.

1. Mapping Entities

1.1 Strong Entity

Rule:

  • Create a table for each strong entity.
  • All simple attributes become columns.
  • Primary key becomes the table’s primary key.

Example:

Student (RollNo, Name, Age)

Becomes:

Student (RollNo PRIMARY KEY, Name, Age)

1.2 Weak Entity

A weak entity depends on a strong entity.

Rule:

  • Create a table for the weak entity.
  • Include:
    • All its attributes
    • Primary key of the owner entity as a foreign key
  • Primary key = Owner’s PK + Partial Key

Example:

Employee (EmpID – PK)
Dependent (DepName – Partial Key, Age)

Dependent Table:

Dependent (EmpID, DepName, Age)
Primary Key = (EmpID, DepName)

2. Mapping Attributes

2.1 Simple Attribute

Directly becomes a column.

2.2 Composite Attribute

Break into simple attributes.

Example:

Name → (FirstName, LastName)

Instead of storing Name, store:

FirstName, LastName

2.3 Multivalued Attribute

Rule:

  • Create a separate table.
  • Include:
    • Primary key of the original entity
    • Multivalued attribute
  • Primary key = Combination of both

Example:

Student (RollNo, Name)
PhoneNumber (Multivalued)

Create:

StudentPhone (RollNo, PhoneNumber)

Primary Key = (RollNo, PhoneNumber)

2.4 Derived Attribute

Derived attributes are calculated from other attributes.

Example:
Age derived from DateOfBirth.

Rule:

  • Do not store derived attributes in the table.
  • Calculate when required.

3. Mapping Relationships

3.1 One-to-One (1:1)

Rule:

  • Add the primary key of one entity as a foreign key in the other.
  • Prefer placing it in the entity with total participation.

3.2 One-to-Many (1:N)

Rule:

  • Add the primary key of “1” side as a foreign key in the “N” side.

Example:

Department (DeptID)
Employee (EmpID, DeptID)

DeptID in Employee is a foreign key.

3.3 Many-to-Many (M: N)

Rule:

  • Create a new table.
  • Include primary keys of both entities. The combined key becomes the primary key.

Example:

Enrollment (RollNo, CourseID)

Primary Key = (RollNo, CourseID)

If the relationship has attributes, include them here.

3.4 N-ary Relationship

For relationships involving more than two entities:

Rule:

  • Create a new table.
  • Include primary keys of all entities.
  • Combined key forms a primary key.

4. Generalization and Specialization

Generalization and specialization represent inheritance in the ER model.

Example:

Employee
→ Manager
→ Engineer

Employee is the supertype.
Manager and Engineer are subtypes.

When converting to a relational model, there are two common methods.

Method 1: Create Separate Tables for Supertype and Subtypes

Rule:

  • Create one table for the supertype.
  • Create separate tables for each subtype.
  • Subtype table’s primary key is:
    • Also, a foreign key referencingthe supertype.

Example:

Employee (EmpID, Name, Salary)

Manager (EmpID, Bonus)

Engineer (EmpID, Skill)

Here:

  • EmpID in Manager and Engineer is both PK and FK.

This method avoids redundancy and is commonly used.

Method 2: Create a Single Table for the Entire Hierarchy

Rule:

  • Create one table containing:
    • All attributes of the supertype
    • All attributes of subtypes
  • Add a type attribute to identify the subtype.

Example:

Employee (EmpID, Name, Salary, Bonus, Skill, Type)

Type indicates whether the employee is a Manager or an Engineer.

Note:

  • Some attributes may remain NULL depending on the subtype.

This method is simple but may cause many NULL values.

5. Aggregation

Aggregation is used when:

  • A relationship between entities is treated as a higher-level entity.
  • Another entity participates in that relationship.

Example:

Employee works_on Project
Manager monitors (Employee works_on Project)

Here, the relationship works_on is treated as an entity for the monitoring relationship.

Mapping Aggregation to a Relational Model

Rule:

  1. First, convert the inner relationship normally.
  2. Then treat that relationship table as an entity.
  3. Add a foreign key of the outer entity.

Example:

Works_On (EmpID, ProjectID)

Now for monitoring:

Monitoring (ManagerID, EmpID, ProjectID)

Primary Key = (ManagerID, EmpID, ProjectID)

Aggregation is handled by creating additional tables.

Final Summary

• Strong entity → Create table
• Weak entity → Table with owner PK + partial key
• Composite attribute → Break into simple attributes
• Multivalued attribute → Separate table
• Derived attribute → Do not store
• 1:1 relationship → Add foreign key
• 1:N relationship → Add foreign key in N side
• M:N relationship → Create new table
• N-ary relationship → Create new table
• Generalization → Two methods (separate tables or single table)
• Aggregation → Convert the relationship first, then treat it as an entity