Open In App

Minimization of ER Diagrams

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

Pre-Requisite: ER Diagram

Entity-Relationship (ER) Diagram is a diagrammatic representation of data in databases, it shows how data is related to one another. In this article, we require previous knowledge of ER diagrams and how to draw ER diagrams.

Minimization of ER Diagram simply means reducing the quantity of the tables in the ER Diagram. When there are so many tables present in the ER DIagram, it decreases the readability and understandability of the ER Diagram, and it also becomes difficult for the admin also to understand these. Minimizing the ER Diagram helps in better understanding. We reduce tables depending on the cardinality.

Cardinality 

Cardinality means that what is the number of relationships between the two entity sets in any relationship model. There are four types of cardinality which are mentioned below.

  • One-to-One
  • One-to-Many
  • Many-to-One
  • Many-to-Many

Many-to-One Cardinality

For example, a student can be enrolled only in one course, but a course can be enrolled by many students.
 

Many to One Cardinalitry

Many to One Cardinality

For Student(SID, Name), SID is the primary key.  For Course(CID, C_name ), CID is the primary key.

Table Student

SID Name
1 A
2 B
3 C
4 D

Table Course

CID C_name
c1 Z
c2 Y
c3 X

Table Enroll

SID CID
1 C1
2 C1
3 C3
4 C2

Now the question is, what should be the primary key for Enroll? Should it be SID or CID or both combined into one? We can’t have CID as the primary key because a CID can have multiple SIDs. (SID, CID) can distinguish table uniquely, but it is not minimum. So SID is the primary key for the relation enrollment. 

For the above ER diagram, we considered three tables in the database

Student
Enroll
Course

But we can combine the Student and the Enroll table renamed as Student_enroll. 

Table Student_Enroll

SID Name CID
1 A C1
2 B C1
3 C C3
4 D C2

Student and enroll tables are merged now. So require a minimum of two DBMS tables for Student_enroll and Course. 

Note: In One to Many relationships we can have a minimum of two tables. 

Many to Many Cardinality

Let us consider the above example with the change that now a student can enroll in more than 1 course.

Many to Many

Table Student

SID Name
1 A
2 B
3 C
4 D

Table Course

CID C_Name
C1 Z
C2 Y
C3 X

Table Enroll

SID CID
1 C1
1 C2
2 C1
2 C2
3 C3
4 C2

Now, the same question arises. What is the primary key to Enroll relation? If we carefully analyze, the primary key for Enroll table is ( SID, CID ). 

But in this case, we can’t merge Enroll table with any of the Student and Course. If we try to merge Enroll with any one of the Student and Course it will create redundant data. 

Note: A minimum of three tables are required in the Many to Many relationships.

One-to-One Cardinality

One to One Cardinality has two possible cases where we have the case of either total participation or no participation at one end.

There are two possibilities

Total Participation at One End

For example, consider the below ER diagram. 

One to One

One to One

A1 and B1 are the primary keys of E1 and E2 respectively. In the above diagram, we have total participation at the E1 ends. Only a single table is required in this case having the primary key of E2 as its primary key. Since E2 is in partial participation, atleast one entry in E2 does not participate in relationship set, but all entries in E1 are related to an entry in E2. Therefore E2 cannot be null for any value of E1, but E1 will be null for atleast one value of E2.

Refer Case-1 at https://www.geeksforgeeks.org/mapping-from-er-model-to-relational-model

Note: Only 1 table is required.

No Participation at One End

One to One

One to One

A1 and B1 are the primary keys of E1 and E2 respectively. 

The primary key of R can be A1 or B1, but we can’t still combine all three tables into one. if we do so, some entries in the combined table may have NULL entries. So the idea of merging all three tables into one is not good. But we can merge R into E1 or E2. So a minimum of 2 tables is required.

Below are the Gate Previous Year Questions.
https://www.geeksforgeeks.org/gate-gate-cs-2008-question-82/ 
https://www.geeksforgeeks.org/gate-gate-cs-2008-question-83/  

FAQs

1. What is an ER Diagram?

Answer:

An entity Relationship Diagram is a diagram that shows the relationship between different entities and relationship between them.

2. What is a 1:1 Relationship in an ER Diagram?

Answer:

1:1 Relationship basically tells us that one row of the table is attached with one row of the another table.



Last Updated : 21 Jul, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads