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