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.

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.

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.

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

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

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.

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/