ER to Relational Mapping

⏱ Estimated reading time: 3 min

ER to Relational Mapping is the process of converting an Entity–Relationship (ER) Diagram into a Relational Schema (tables) so that the database can be implemented in a relational DBMS (like MySQL, Oracle, SQL Server).

An ER diagram is a conceptual design, whereas a relational schema is a logical design. ER-to-relational mapping ensures that the conceptual model is correctly transformed into tables with proper keys and constraints.

Steps of ER to Relational Mapping

ER Model → Relational Model involves 7 major steps:

1️⃣ Mapping Regular (Strong) Entities

Each strong entity becomes a table.

Rule:

• Attributes → Columns

• Primary key → Primary Key of table

Example:

Entity: Student (RollNo, Name, Course)
Table:
Student(RollNo PK, Name, Course)

2️⃣ Mapping Weak Entities

A weak entity depends on a strong entity and does not have its own primary key.

Rule:

• Create a table for the weak entity

• Primary key = Partial key + Primary key of strong entity

• Add foreign key referencing strong entity

Example:

Weak Entity: Dependent(Name, Age)
Strong Entity: Employee(EmpID)
Table:
Dependent(EmpID FK, Name, Age, Primary Key(EmpID, Name))

3️⃣ Mapping 1:1 Relationships

Two methods depending on the relationship type.

Rule:

• Add the primary key of one entity as a foreign key in the other

• Prefer the entity with total participation

Example:

Person ↔ Passport
Person(PersonID PK, Name, PassportNo FK)
OR
Passport(PassportNo PK, PersonID FK)

4️⃣ Mapping 1:N Relationships

Rule:

• Add the primary key of the "one" side as a foreign key in the "many" side.

Example:

Department(DeptID PK)
Employee(EmpID PK, Name, DeptID FK)

5️⃣ Mapping M:N (Many-to-Many) Relationships

Rule:

• Create a separate table for the relationship

• Primary key = Combination of both entity keys

• Additional attributes of the relationship also added

Example:

Student —< Enroll>— Course
Enroll(StudentID FK, CourseID FK, Grade)
Primary Key(StudentID, CourseID)

6️⃣ Mapping Multivalued Attributes

Rule:

• Create a new table

• Include multivalued attribute + primary key of the entity

• Primary key = combination

Example:

Student(RollNo PK, Name)
Phones(RollNo FK, PhoneNumber, PK(RollNo, PhoneNumber))

7️⃣ Mapping Composite Attributes

Rule:

• Break into simple attributes

• Do not create separate tables

Example:

Address = (HouseNo, City, State)
Stored as:
Address_HouseNo, Address_City, Address_State

Complete Example

ER Diagram:

• Student(StudentID, Name)

• Course(CourseID, Title)

• Student —< Enroll>— Course

• Enroll has attributes: Date, Grade

Relational Mapping:

1. Student Table
   Student(StudentID PK, Name)

2. Course Table
   Course(CourseID PK, Title)

3. Enroll Table
   Enroll(StudentID FK, CourseID FK, Date, Grade,
   Primary Key(StudentID, CourseID))

Why ER to Relational Mapping is Important?

• Converts conceptual design into implementable schema

• Ensures correct primary & foreign key placement

• Eliminates redundancy

• Preserves all constraints (1:1, 1:N, M:N)

• Ensures logical consistency and integrity

Conclusion

ER to Relational Mapping is a systematic process that converts high-level ER models into relational schemas. By following rules for entities, relationships, keys, attributes, and constraints, we can design a structured and efficient relational database. This process ensures that the database remains consistent, accurate, and easy to manage when implemented in SQL-based systems.