In this article, I will be sharing step by step method to map an ER Model to Relational Diagram.
In Database Management Systems, ER stands for Entity-Relationship. ER modelling help to figure out the set of entities, attributes of each entitiy, and the relationship that is shared between entities. In other words it helps us to explain the logical structure of databases.
This image shows the ER-Diagram for a Company Database.
Now, let’s dive in to convert this ER-Diagram to Relational Schema…
Step 1:
- Figure out all the regular/strong entity from the diagram and then create a corresponding relation(table) that includes all the simple attributes.
- Choose one of the attributes as a primary key. If composite, the simple attributes together form the primary key.
- For the given ER-Diagram we have Employee, Department and Project as strong/regular entity, as they are enclosed in single rectangle.
- So, we create respective relations that is depicted in the figure below.
Step 2:
- Figure out the weak entity types from the diagram and create a corresponding relation(table) that includes all its simple attributes.
- Add as foreign key all of the primary key attributes in the entity corresponding to the owner entity.
- The primary key is a combination of all the primary key attributes from the owner and the primary key of the weak entity.
- For the given ER-Diagram we have Dependent as a weak entity, as it is enclosed in a double rectangle that is indicative of an entity being weak.
- The Dependent relation(table) is created that is shown in the figure below.
Step 3:
- Now we need to figure out the entities from ER diagram for which there exists a 1-to-1 relationship.
- The entities for which there exists a 1-to-1 relationship, choose one relation(table) as S, the other as T.
Better if S has total participation (reduces the number of NULL values). - Then we need to add to S all the simple attributes of the relationship if there exists any.
- After that, we add as a foreign key in S the primary key attributes of T.
- For the given ER-Diagram there exists a 1-to-1 relationship between Employee and Department entity.
- Here Department has total participation therefore consider it as relation S and Employee as relation T.
- The 1-to-1 mapping between Employee and Department is depicted in the figure below.
Step 4:
- Now we need to figure out the entities from ER diagram for which there exists a 1-to-N relationship.
- The entities for which there exists a 1-to-N relationship, choose a relation as S as the type at N-side of relationship and other as T.
- Then we add as a foreign key to S all of the primary key attributes of T.
- In the given ER diagram there are two 1-to-N relationships that exists between Employee-Department and Employee-Dependent entity.
- The 1-to-N mapping between Employee-Department and Employee-Dependent is depicted in the figure below.
Step 5:
- Now we need to figure out the entities from ER diagram for which there exists an M-to-N relationship.
- Create a new relation(table) S.
- The primary keys of relations(tables) between which M-to-N relationship exists, are added to the new relation S created, that acts as a foreign key.
- Then we,add any simple attributes of the M-to-N relationship to S.
- For the given ER-Diagram there exists M-to-N relationship between Employee and Project entity.
- The new table Works_On is created for mapping the relationship between Employee and Project relation(table).
Step 6:
- Now identify the relations(tables) that contain multi-valued attributes.
- Then we need to create a new relation S
- In the new relation S we add as foreign keys the primary keys of the corresponding relation.
- Then we add the multi-valued attribute to S; the combination of all attributes in S forms the primary key.
- For the given ER-Diagram there exists a multi-valued attribute (Locations) in Department relation(table).
- So, we create a new relation called Dept_Locations. To this new relation we add the primary key of Department Table that is D_Number and the multi-valued attribute Locations.
Hurray, we have successfully mapped the ER-Diagram of the Company Database to its Relational Schema. Just follow these 6 steps to successfully map any ER-Diagram to its Relational Schema.
