In database design, one of the most important steps is converting an Entity-Relationship (ER) model into a Relational model. This transformation bridges the gap between conceptual data modeling and practical database implementation. Understanding this process is key for database developers, designers, and students learning about DBMS.
In this article, we will explain what ER and relational models are, why the conversion is needed, and how it is done step-by-step.
What is an ER Model?
An Entity-Relationship (ER) model is a high-level conceptual data model used to define the structure of a database. It was introduced by Peter Chen in 1976. The ER model uses simple diagrams (ER diagrams) to represent the major components of a database:
- Entities: Objects or things in the real world (e.g., Student, Teacher, Book).
- Attributes: Properties that describe entities (e.g., name, roll number).
- Relationships: Associations between entities (e.g., Student borrows Book).
ER diagrams provide a clear view of data, making it easier to design and communicate the structure of the database.
What is a Relational Model?
The Relational model is a logical data model used by most modern database systems like MySQL, Oracle, and PostgreSQL. In this model:
- Data is organized into tables (also called relations).
- Each table consists of rows (records) and columns (fields or attributes).
- Tables are connected through keys like Primary Key and Foreign Key.
The relational model is efficient, flexible, and ideal for storing and querying structured data.
Why Convert ER Model to Relational Model?
The ER model is ideal for designing, but it’s not directly usable by a database management system (DBMS). Hence, we must convert it to a relational model, which can be implemented in SQL.
Key reasons for conversion:
- To create actual database tables.
- To maintain data consistency and integrity.
- To implement real-world relationships using keys.
Steps to Convert ER to Relational Model
Here are the step-by-step rules to convert ER diagrams into relational schema:
1. Convert Entity Types to Tables
Each strong entity becomes a separate table. Its attributes become the columns of the table. The primary key of the entity becomes the primary key of the table.
Example:
Entity: Student
Attributes: Roll_No (PK), Name, Age
Relational Table:
Student(Roll_No PRIMARY KEY, Name, Age)
2. Convert Weak Entities
Weak entities do not have a primary key of their own. They depend on a strong entity. During conversion:
- Create a new table.
- Include the primary key of the related strong entity.
- Combine it with the weak entity’s partial key.
- Set the combination as the primary key.
Example:
Weak Entity: Dependent
Related Strong Entity: Employee (Emp_ID)
Relational Table:
Dependent(Emp_ID, Dependent_Name, Age)
PRIMARY KEY(Emp_ID, Dependent_Name)
FOREIGN KEY(Emp_ID) REFERENCES Employee(Emp_ID)
3. Convert Relationships
There are three types of relationships: One-to-One (1:1), One-to-Many (1:N), and Many-to-Many (M:N).
a. One-to-One (1:1)
Add the primary key of one table as a foreign key in the other. Place it in the table where it makes more logical sense (e.g., fewer NULL values).
Example:
Person(PID), Passport(PassportNo)
You can add PID to Passport table:
Passport(PassportNo PRIMARY KEY, PID FOREIGN KEY REFERENCES Person(PID))
b. One-to-Many (1:N)
Add the primary key of the “one” side as a foreign key in the “many” side.
Example:
Department(Dept_ID), Employee(Emp_ID)
Employee(Emp_ID PRIMARY KEY, Name, Dept_ID FOREIGN KEY REFERENCES Department(Dept_ID))
c. Many-to-Many (M:N)
Create a new junction table that includes primary keys from both related tables as foreign keys. These keys together form the primary key of the new table.
Example:
Student(SID), Course(CID)
Create a new table:
Student_Course(SID, CID)
PRIMARY KEY(SID, CID)
FOREIGN KEY(SID) REFERENCES Student(SID)
FOREIGN KEY(CID) REFERENCES Course(CID)
4. Convert Multivalued Attributes
Multivalued attributes require a new table:
- The new table includes the attribute and the primary key of the main entity.
- The combination becomes the primary key.
Example:
Entity: Student, Attribute: PhoneNumber (multivalued)
Student_Phone(Roll_No, PhoneNumber)
PRIMARY KEY(Roll_No, PhoneNumber)
FOREIGN KEY(Roll_No) REFERENCES Student(Roll_No)
5. Convert Composite Attributes
Break composite attributes into separate columns.
Example:
Address → Street, City, Zip
Student(Roll_No, Name, Street, City, Zip)
6. Handle Derived Attributes
Derived attributes (like Age from DateOfBirth) are usually not stored. They are computed when needed.
Example:
SELECT YEAR(CURDATE()) - YEAR(DateOfBirth) AS Age FROM Student;
Final Example: ER to Relational Model Conversion
Suppose we have an ER diagram with:
- Entity: Student(RollNo, Name, Age)
- Entity: Course(CourseID, CourseName)
- Relationship: Enrolls(Student, Course) [M:N]
Relational Schema:
Student(RollNo PRIMARY KEY, Name, Age)
Course(CourseID PRIMARY KEY, CourseName)
Enrolls(RollNo, CourseID)
PRIMARY KEY(RollNo, CourseID)
FOREIGN KEY(RollNo) REFERENCES Student(RollNo)
FOREIGN KEY(CourseID) REFERENCES Course(CourseID)
Conclusion
Converting an ER model into a relational model is a vital step in building any relational database. The ER model helps visualize the data structure, while the relational model allows implementation and querying through SQL. By following systematic conversion steps—such as creating tables from entities, handling relationships properly, and dealing with different types of attributes—you can ensure a smooth and logical transition from design to deployment.
Whether you’re a beginner or a professional, mastering this conversion process is essential for effective database design and development.