Converting ER diagram to Relations (Tables)

To implement the database, it is necessary to use relational model. There is a simple way of mapping ER model to relational model.

1. Converting regular entities

Each entity in an ER diagram is transformed into a relation. The name given to the relation is generally the same as the entity type.

Each simple attribute of the entity type becomes an attribute of the relation.

The identifier of the entity becomes the primary key of the corresponding relation.

2. Converting composite in an ER diagram to tables

When a regular entity type has a composite attribute, only the simple component attributes of the composite attribute are included.

Example:

In this example the composite attribute is the Customer address, which consists of Street, City, State, and Zip.

3. Mapping multi valued attribute in ER diagram to table

When the regular entity contains a multi valued attribute, two new relations are created.

The first relation contains all the attributes of the entity type except the multi valued attribute.

The second relation contains attributes that form the primary key of the second relation.

 

In this example, the skill associated with the EMPLOYEE is a multi valued attribute, since an EMPLOYEE can have more than one skill as fitter, electrician, turner, etc.

4. Converting weak entities in ER diagram to table

Weak entity type does not have an independent existence and it exists only through an identifying relationship with another entity type called the owner.

For each weak entity type, create a new relation and include all of the simple attributes as attributes of the relation. Then include the primary key of the identifying relation as a foreign key attribute to this new relation.
The primary key of the new relation is the combination of the primary key of the identifying and the partial identifier of the weak entity type. In this example DEPENDENT is weak entity.

5. Converting a binary relationship to table

A relationship which involves two entities can be termed as binary relationship.

To convert a binary relationship into table, create two separate tables for two entities such that it contains a common attribute. The common attribute in the fist table is known as primary key and in the second table is known as foreign key.

Here we have two entities CUSTOMER and ORDER. The relationship between CUSTOMER and ORDER is one-to-many. For two entities CUSTOMER and ORDER, two tables namely CUSTOMER and ORDER are created as shown later. The primary key CUSTOMER ID in the CUSTOMER relation becomes the foreign key in the ORDER relation.

6. Converting associative entity to table

May-to-many relationships can be modeled as an associative entity in the ER diagram.

To convert an ER diagram which contains two associative entities create three tables for two relationships and one table for associative entity. The associative entity table contains the primary keys of two entities and if associative entity contains an identifiers they also included in third table.

Example:

Here the associative entity is ORDERLINE, which is without an identifier. That is the associative entity ORDERLINE is without any key attribute

The first step is to create three relations, one for each of the two participating entity types and the third for the associative entity. The relation formed from the associative entity is associative relation.

7. Converting unary relationship to table

Unary relationships are also called recursive relationships. The two most important cases of unary relationship are one-to-many and many-to-many

One-to-many Unary Relationship

Each employee has exactly one manager. A given employee may manage zero to many employees. The foreign key in the relation is named Manager-ID. This attribute has the same domain as the primary key Employee-ID.

 

8. Converting ternary relationship to table

A ternary relationship is an association among three attributes. To convert a ternary relationship to tables create three tables for three attributes and one table for relationship which contains all the primary keys of entities in the relation.

The primary key attributes – Patient ID, Physician ID, and Treatment Code – become foreign keys in PATIENT TREATMENT. These attributes are components of the primary key of PATIENT TREATMENT.