Minimization of ER Diagrams

Last Updated : 13 Aug, 2024

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.

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.

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

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/

Enhanced ER Model

kartik

Improve

Article Tags :