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.

Advertisements

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 modeling the real-world problem into 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 keep track.
  • The Company also maintains details of the dependents of each employee. Attributes of dependent include dname, dob, gender and relationship with the employee.

Model an entity-relationship diagram for the above scenario.

Advertisements

Solution – Converting ER model into Relational model

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

Entity-Relationship Mode - er model into relational model
Figure 1 – Entity-Relationship Mode

Convert to Relational Model

The next step in the database design is to convert the ER Model into the Relational Model. In the Relational Model, we will define the schema for relations and their relationships. The attributes from the entity-relationship diagram will become fields for a relationship and one of them is a 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 the 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
Figure 2 – Relational Model of Database

References

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

Ads Blocker Image Powered by Code Help Pro

Ads Blocker Detected!!!

We have detected that you are using extensions to block ads. Please support us by disabling these ads blocker.