Converting an E-R Model into Relational Model in DBMS

In this lesson you will learn to convert er model into relational model. 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.

Read: DBMS Basics

Read: Basic DML commands

Read: Basic DDL commands

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 – Converting ER model into Relational model

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 - er model into relational model
Entity-Relationship Mode

 

Convert to Relational Model

The next step in the database design is to convert the ER 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. This the result after converting ER model into relational model.

Relational Model of Database - er model into relational model
Relational Model of Database

 


Bibliography

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).

 

Advertisements