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 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, skills. Skills 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.
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.
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.
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).
