Converting an E-R Model into Relational Model in DBMS

Previous Article
Next Article

First step in designing a database is to create an entity-relationship model. Then the entity-relationship model is converted into a relational model. The relational model is nothing but a group of tables or relations that create for your database.

This article you will learn to convert an entity-relationship model into a relational model using an example. You will understand the process of modelling the real world problem in to an entity-relationship diagram and then convert that entity-relationship diagram into a relational model.

Problem Definition

In this example problem, you will create a database for an organization with many departments. Each department has employees and employees have dependents. To create a database for the company, read the description and identify all the entities from the description of the company.

  • The company organized into departments and departments have employees working in it.
  • Attributes of Department  are dno, dname. Attributes of Employee include eno, name, dob,gender, doj, designation, basic_pay, panno, skillsSkills are multi-valued attribute.
  • The Department has a manager managing it. There are also supervisors in Department who supervises a set of employees.
  • Each Department enrolls a number of projects. Attributes of Project are pcode, pname. A project is enrolled by a department. An employee can work on any number of projects on a given day. The Date of employee work in-time and out-time has to be keep track.
  • The Company also maintains details of the dependents of each employees. Attributes of dependent include dname, dob, gender and relationship with the employee.
Model an entity-relationship diagram for the above scenario.

Solution – ER Diagram

From the real world description of the organization, we were able to identify following entities. These entities will become basis for an entity-relationship diagram or model.


Entity-Relationship Mode
Entity-Relationship Mode


Convert to Relational Model

The next step in the database design is to convert the E-R Model  into  Relational Model. In Relational Model, we will define the schema for relations and their relationships. The attributes from entity-relationship diagram will become fields for a relation and one of them is primary field or primary key. It is usually underlined in the entity-relationship diagram.

An entity in a relational model is a relation. For example, the entity Dependent is a relation in relational model with all the attributes as fields – eno, dname, dob, gender, and relationship.

Here is the Relational Model for above diagram of the company database.

Relational Model of Database
Relational Model of Database



Avi Silberschatz, Henry F. Korth, and S. Sudarshan. 27-Jan-2010. Database System Concepts. McGraw-Hill Education.

Ramakrishnan, Johannes Gehrke and Raghu. 1996. Database Management Systems. McGraw Hill Education; Third edition (1 July 2014).


Previous Article
Next Article