ER to Relational Model Conversion

1. Need for ER to Relational Conversion

ER diagrams are conceptual, whereas databases are implemented using relational tables. Conversion bridges this gap.

• ER model → Design level
• Relational model → Implementation level
• Ensures correct table creation

2. Basic Conversion Rules

• Entity → Table
• Attribute → Column
• Primary Key → Primary Key
• Relationship → Foreign Key / New Table

3. Conversion of Strong Entity

Each strong entity is converted into a separate table with its attributes.

ER Entity:
[ STUDENT ]
RollNo, Name, Marks

Relational Table:
STUDENT (RollNo PK, Name, Marks)

4. Conversion of Weak Entity

Weak entity table includes its partial key and the primary key of the strong entity.

EMPLOYEE (Emp_ID PK, Name)
DEPENDENT (Dep_Name, Age, Emp_ID)

Primary Key → (Emp_ID, Dep_Name)

5. Conversion of 1:1 Relationship

Add the primary key of one entity as a foreign key to the other entity.

PERSON (PID PK, Name)
PASSPORT (PassportNo PK, IssueDate, PID FK)

6. Conversion of 1:M Relationship

Add the primary key of the one-side entity as a foreign key in the many-side entity.

COURSE (CourseID PK, CourseName)
STUDENT (RollNo PK, Name, CourseID FK)

7. Conversion of M:N Relationship

Create a new table containing the primary keys of both participating entities.

STUDENT (RollNo PK)
COURSE (CourseID PK)

ENROLL (
    RollNo FK,
    CourseID FK,
    PRIMARY KEY (RollNo, CourseID)
)

8. Conversion of Composite Attribute

Composite attributes are broken into simple attributes.

Address → Street, City, Pin

STUDENT (RollNo, Name, Street, City, Pin)

9. Conversion of Multivalued Attribute

Create a separate table for multivalued attributes.

STUDENT (RollNo PK, Name)
PHONE (RollNo FK, PhoneNo)

PRIMARY KEY (RollNo, PhoneNo)

10. Summary of Conversion Rules

ER Component           Relational Mapping
--------------------   ------------------------------
Strong Entity          Table
Weak Entity            Table with FK + Partial Key
1:1 Relationship       FK in one table
1:M Relationship       FK in many-side table
M:N Relationship       New table
Composite Attribute    Split into attributes
Multivalued Attribute  Separate table

Practice Questions

1. Why ER to relational conversion is required?
2. Explain conversion of weak entity.
3. How is M:N relationship converted?
4. Explain conversion of multivalued attribute.
5. Write conversion rules for ER model.

Practice Task