Minimization of ER Diagrams

ER Diagram

ER Diagram stands for Entity Relationship diagram. When we draw the relationships between entities using a diagram, then it is called an entity relationship diagram. The ER Diagram is just for understanding the purpose of the database administrator. We cannot use the ER Diagram directly on the computer. ER diagrams are converted into the tabular form then they are inserted into the computer using any query language. In ER diagrams, we use attributes, entities, and the relationships between entities. We use an oval shape to represent the entity and a diamond shape to represent the relationships between entities.

Cardinality

The number of the relationship between two entity sets can have is known as cardinality.

There are four types of cardinalities in ER diagram:

• One to one
• One to many
• Many to one
• Many to many

Minimization of ER Diagrams

Minimization means reducing the number of tables from the ER diagram. We know we map the ER Diagram into the tables to save it into the system. If there are many tables, then it will be more difficult for the database administrator to handle the data and access the different tables. We can minimize these tables as much as possible to reduce the complexity of the database. Reducing the ER Diagram will depend upon the cardinality of the relationship. So we will see different cases for minimizing the er diagram:

• One to One Relationship:

When there is one to one relationship between two entities, then there can be two possibilities:

When there is Total Participation at one end:

Let's suppose we have two entities, and p1 and p2 are the attributes and primary keys of both the entities, respectively. If there is total participation at the end of entity 1, then we can combine the two tables into one with the primary key as p1.

Since entity 1 is in the total participation, which means each row of entity 1 relates to one row of entity 2. Still, it is not necessary that each entry of entity 2 will relate to rows of entity 1. So, we will choose the primary keys as p1.

When there is no Total Participation:

Let's suppose we have two entities, and p1 and p2 are the attributes and primary keys of both the entities, respectively. If there is no total participation, then we cannot combine and reduce the tables because if we combine the tables with either p1 as the primary key or p2 as the primary key, then we will miss some entries, and we will get null values in the combined table.

So at least two tables are required in one-to-one relationship if there is no total participation.

• Many to One Cardinality in ER Diagram:

We can understand it by an example. Let's suppose we have one entity as a student and another entity as a course, and the relation is enrollment. One student can enroll in one course, but one course can be enrolled by many students, so it is many to one cardinality.

We will have three tables: one is for students, one is for the course, and the last one is for enrollment. We can combine any two tables to minimize the er diagram. We can combine the student and enrollment table with the primary key as student id and other tables, of course. So, we can minimize it into two tables.

So, in many to one cardinality, we require at least two tables.

• One to Many Cardinalities:

In one to many, cardinality works the same as many to one, and we combine the tables into at least two tables to minimize the ER Diagram.

• Many to Many Cardinalities:

Let's suppose we have the same example as we used in the above cases, but now one student can enroll in more than one course, and one course can be enrolled by many students. So, it will be many to many cardinality relationships. Now, if we merge the two tables, then we have to make primary keys either to the student id or the course id, and in both cases, the combined table will have redundant data. So, we cannot merge the tables, and at least three tables are required in the many to many cardinality relationships.