Conversion of E-R Diagram into Relational Model

Conversion of an E-R diagram into a relational model is required because E-R diagrams represent the conceptual level of the database design while the relational model represents the logical level. The relational models can be easily implemented using RDBMS like Oracle, MySQL, etc.

In general conversion of E-R diagram into a relational model involves the following:

  1. Mapping of an entity set into relation (tables) of the database.
  2. The attributes of a table include the attributes of an entity
  3. The key attribute of an entity becomes the primary key of the relation

For example,

Consider the following E-R diagram in the figure below. The E-R diagram consists of Employee as an entity set and EmpNo, EmpName, and Salary as its attributes. Here we map entity set into a relation Employee and attributes of an entity set will become the attributes inside the table. The key attribute will become the primary key of the table.

Conversion of a simple E-R diagram into a table
Conversion of a simple E-R diagram into a Table

The conversion of an E-R diagram into the relational model also depends on the type of components used in the E-R diagram. The following section describes the different cases of the E-R diagram and their conversion into their corresponding tables.

Entity set with a composite attribute

While converting an E-R diagram consisting of a composite attribute we do not include the composite attribute in the relational model. The compositions of the composite attribute will become the attributes in the relational model. For example in the figure given below, Salary is the composite attribute, and Basic, DA, and HRA are its compositions.

Conversion of an E-R diagram containing composite attribute
Conversion of an E-R diagram containing composite attribute
Entity set with multivalued attributes

If an entity contains a multivalued attribute, we split the attributes into two relations in the relational model. One with key attribute and all simple attributes and other with key attribute and all multivalued attributes. For example, in the figure given below, PhoneNo is the multivalued attribute.

E-R diagram containing multivaled attriute
E-R diagram containing multivalued attribute

If we include the PhoneNo in the table with all other attributes, then for a single-valued tuple we may have multiple entries as shown in the table below

Duplicate values with multivalued attribute
Duplicate values with multivalued attribute

However, to avoid duplicate values in the table, we split the attributes into two different relations as shown in the figure below

Conversion of multivalued attributes into relation
Conversion of multivalued attributes into relation
Translation of a relationship into a relation

Likewise, we map the entity set into the relation in a relational model, we can also map a relationship set into a relation. The attribute of such a relation includes key attributes of the participating relations. The attributes are will become a foreign key.

For example, in the figure given below, there are two entity sets Employee and Department. These entity sets are participating in a relationship works in. The relationship set is converted into relation with attributes EmpNo from Employee relation, D_id from Department relation and Since, the attribute of the relationship set itself.

Translation of a relationship into a relation
Translation of a relationship into a relation
Sample Question
  1. Consider the following tables:

Course (Course_id,Course_name)

Teacher (Teacher_id,Teacher_name)

Assigned_to (Teacher_id, Course_id)

a) How many tables will be created using the above scenario?

b) What will be the foreign key?

Other Useful Links

entity-relationship-diagrams/

Leave a Reply

Your email address will not be published. Required fields are marked *