Convert the ER Diagram into a Table

Introduction  

Do you know?

An ER model (Entity-relationship model) describes the structure of the database using a diagram, which is known as ER Diagram(Entity-relationship Diagram). An Entity-relationship model is a blueprint (design) of a database that can be implemented as a database later.

The main components of ER model are Entity Set and Relationship Set.

Entity set: 

An entity set is a group of similar entities, and these entities can have different attributes.

Relationship set:

A relationship set is a set of relationships of the same type. A relationship is an association between various entities. 

Let us take a quick revision of ER Diagram.

What is an ER Diagram?

An ER diagram is a graphical representation of entities and relationships in the database. In Database Management System, an entity is a table or attribute of a table in the database, so relationships among tables and their attributes help the ER diagram to show the complete logical structure of the database.

Following are some geometric shapes that are used to draw ER(Entity-relationship ) Diagram:

  • Rectangle: It represents the entity sets
  • Ellipses: It represents attributes
  • Diamond: It represents relationship set
  • Lines:  They link attributes to entity set and entity sets to the relationship sets
  • Double ellipses: It represents multi-valued attributes 
  • Dashed ellipses: It represents derived attributes 
  • Double rectangle: It represents weak entity sets
  • Doble lines: These lines represent the total participation of an entity in a relationship set


Now we will learn how to convert the ER Diagram into a table.

Conversion of ER Diagram to Table 

We start with converting simple ER diagrams to tables.

Strong entity set with Simple attributes

In the case of strong entity sets, the strong entity set becomes the table, and the attributes of the entity set become the attributes of the table. The key attribute of the entity set will be the primary key of the table.

Example:

Let say we have an entity named Employee with the attributes Emp_Name, Age, Emp_ID, and Salary. When we convert the ER diagram into a table, the entity set becomes a table so that the table name will be "Employee". And the attributes of the entity set become the attributes of the Employee table.

The following table figure will make it clear to you.

Note: The underlined attributes are the primary attributes. 

The following table “Employee” is the conversion of an ER diagram.

Emp_ID

Emp_Name

Age

Salary

    

Strong entity set with composite attributes

Now we will learn to convert the ER diagram into a table for a strong entity set with composite attributes. Here, the entity set will be the table, and the simple attributes of composite attributes will become the attributes of the table. Composite attributes will be ignored during the conversion.

Example:

We have an entity set named Employee with the attributes Age, Emp_ID, and Salary. There is a composite attribute Emp_Name with its simple attributes are First_Name and Last_Name. When we convert the ER diagram to the table, the entity set becomes a table so that the table name will be "Employee". And the attributes of the entity set become the attributes of the Employee table, but the composite attribute itself will be ignored, and simple attributes of the composite attributes will be the attributes of the table.

The following figures explain this conversion clearly.

The following table “Employee” is the conversion of an ER diagram.

Emp_ID

First_Name

Last_Name

Age

Salary

     

 

Strong entity set with multi-valued attributes

The entity sets with multi-valued attributes require two tables in the relational model in the database.

We will understand this conversion using a suitable example.

Example:

Let's take the example that we have taken above, and now we added a new multi-valued attribute named Dept. Since an employee can work in multiple departments, this is a multi-valued attribute. Since it is an entity set with multi-valued attributes, there will be two tables as shown below:

Emp_ID

First_Name

Last_Name

Age

Salary

     

 

+

Emp_ID

      Dept
  

 

Relationship set to table conversion

In the conversion of relationship set to a table, the entity sets' primary attribute becomes the table's attributes, and attributes of relationship set, if any, also become the attributes of the table.

Example:

Here, we have two entity sets named Employee and Department. These entity sets are associated with a relationship set named "Works". To convert this relationship set to a table, we take the primary attributes of each entity set ( Emp_ID and Dept_ID ) and all the attributes of the relationship set and create the table.

Emp_ID

    Dept_ID            Experience
   

 

Frequently Asked Questions

  1. What do you mean by ER model?
    An Entity-relationship model is a blueprint (design) of a database that can be implemented as a database later. The main components of ER model are Entity Set and Relationship Set.
     
  2. What are the components of ER Diagram?
    There are three major components of ER Diagram that are as follows:
    Entity: It is an object or component of data.
    Attribute: It describes the property of an entity
    Relationship: It shows the relationship among entities.
     
  3. What is a Relationship set?
    A relationship set is a set of relationships of the same type. A relationship is an association between various entities.

Key Takeaways

In this blog, we learned about ER models and ER diagrams. We learn about entity and relationship sets, then some geometric shapes that are used to draw ER diagrams. And then we learn how to convert the ER diagram into a table. We learn the four cases of conversion of ER diagram to table with their suitable examples.

You can visit here to learn more about different topics related to database management systems.

Also, try CodeStudio to practice programming problems for your complete interview preparation. Ninja, don't stop here; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.

Was this article helpful ?
1 upvote

Comments

No comments yet

Be the first to share what you think