Aggregation in DBMS

Introduction

Aggregation in DBMS(Database Management System) is a process of combining two or more entities that cannot be used alone, to form a more meaningful entity. When a single entity alone doesn't make sense on its own in a relationship then the aggregation process is done and the relationship of two entities acts as one entity after the aggregation has been done. Let's consider a classic example to understand the aggregation in DBMS.

Example: Consider the above structure and let's take an example of an employee and his manager, and the employee working on a project which is also managed by his manager. In a real scenario, you know that a manager not only manages the employee under them but he has to manage the work undertaken by the employee and the project as well. As discussed in the above example, if the "Manager" makes a relationship - "manages" with either "Employee" or "Project" entity alone then it will not make any sense because a manager has to manage both the employee as well as the project, similarly, it can be said that if a single entity cannot derive results alone then it can be aggregated with another entity to form a meaningful relationship. So in this case, a new relationship is formed between the two entities, "Employee" and "Project", the relationship between the two entities acts as one entity. In the given example the relationship "Builds" between the two entities - "Employee" and "Project" acts as one entity that has the relationship "Manages" with the manager.

Why Use Aggregation in DBMS

Aggregation in DBMS is applied when the DBMS has all or some of the below conditions:

• At times, the database might contain some entities which are less important, such as two or more entities that can be used to create a relationship with one another. This aggregation process helps to create more meaningful entities which can derive great results.
• When the ER (Entity - Relationship model) is not able to represent the relationship between any of the present entities in the system then that particular entity can be used to create a new relationship with the other entity. The resultant entity can be used to create a relationship in the Entity-Relationship model.
• When the DBMS has no complex entities but only trivial entities which are not upgradable in the database management system, then it is not possible for future upgrades. In this situation, two or more trivial entities can be used to create a new relationship between these entities and the resultant can be formed as a new complex entity.

Process Flow

Consider an ER model given below. In this model F and G are the entities that can be considered trivial or which alone do not make any sense and are not significant enough to provide meaningful information. Hence, the below entities F and G are connected to form a new relationship, that is, R01.

Now that the R01 is created, it has to have a link with the other entities in the system in a sequence to be useful for other operations in the database management system. That is when the aggregation is applied on R01, to create the relationship, R02. Now, the R02 relationship needs a new entity to keep its functionality in a usable state.

After the aggregation operation on the ER model, a new entity C is formed which is more usable and many results can be taken out from the new entity. Without the aggregation process, the trivial entities may become inoperative. Therefore the above aggregation process helps at ensuring that all the entities are utilized within the system.

Example of Aggregation in DBMS

Let us take the above example of Employee and Manager to understand the aggregation method on the ER model of a database management system. The above entity relationship diagram consists of, the Employee, a project, and a manager. The above ER diagram also has two relationship boxes - they are "Builds" and "Manages". Consider a real scenario where an employee in the company works / builds on a given project. Now, there can be many employees in the company with the various projects and to revert back to the project or to get a reference of a project and its builder, we have to manually search through the files. But that's not how real life works. This brings up the need for an upper body that takes care of the project as well as the employee that is a new entity - "Manager". The manager manages the employee and the project and the data related to the project with the employee is saved by the manager according to their preference. This is called aggregation. In the above figure, the Employee and the Project are connected to the "builds" operation. This builds operation is connected to the "manages" operation that represents the aggregation function. The "manages" operation is then connected to the new entity called as Manager entity. By using the aggregation, this ER diagram appears to be more meaningful.

Importance of Aggregation in DBMS

As discussed above, the aggregation on the ER model is one of the most crucial concepts in the database management system, and the importance of the aggregation in DBMS is as below:

• When one or more entities do not make sense in the DBMS (Database Management System) and are used alone, this will not produce any exceptional results, and also it'll occupy the space in the database. But, the same entity can be aggregated to form a relationship with the other trivial entity and a meaningful entity is formed which means that all the entities are utilized to their full potential.
• Aggregation is applied so that the idle entity or the entity which has not been used to obtain the desired results can work in relationship with any other such trivial entities in the database management system.
• The entities that are at the same level and are redundant when used alone, a relationship is created between such entities during the aggregation process. The resultant entity after the aggregation process moves to a higher level i.e. more meaningful information can be fetched from the overall newly formed entity which can perform in a better way.

Read More:

• Entity in DBMS.
• ER diagram in DBMS.
• What is ER model in DBMS?.
• Types of Relationship in DBMS.