Updated March 27, 2023
Introduction to Aggregation in DBMS
Aggregation in DBMS (Database Management System) is a process of combining two or more entities to form a more meaningful new entity. This Aggregation process is done when the entities don’t make sense on their own without applying the aggregation process. In order to create aggregation between two entities, which cannot be used for its individual qualities, a relationship is established and the resulting product is created into a new entity. The relationship can be of any type, such as SUM, AVG, AND, OR, etc. Aggregation on tables can be performed using various tools available in the market.
Usually, an ETL tool will have the Aggregation option along with its other operations.
Reasons for Using Aggregation in DBMS
Aggregation method in DBMS is applied when the DBMS has the below conditions,
- When the DBMS has only trivial entities and no complex entities, it is not possible for future upgrade or In this situation, two or more trivial entities can be used to create a new relationship between them, and the resulting artifact can be formed as a new complex entity.
- When the DBMS has one trivial entity, which needs to be used for more than one operation, then this entity is used to create a relationship with more than one other This can result in multiple aggregation processes, and multiple new entities depending on the operations applied between the entities.
- When the Entity-Relationship model is not able to represent the relationship between any entities in its system, that particular entity can be used to create a new relationship with any other The resulting entity can be used to set up relationships in the Entity-Relationship model. In this scenario, Aggregation helps in making use of all the entities in the system, as an alternative to letting any entity to be inoperative.
These are a few sample scenarios where Aggregation will be an essential process. Practically, there can be other possible situations where Aggregation can help in making the DBMS more scalable for future upgrades and long-lasting in nature.
Process Flow
In the below ER model (Fig. 01), A and B are the Entities that can be considered to not operative on its own. Hence the entities are connected to form a new relationship, that is, R1. Now R1 is created, but it has to have a link with other entities in the system in sequence to be useful for other operations in the DBMS. That is when aggregation is applied on R1, to create the new relationship R2. R2 needs a new entity to keep its functionality in a usable state. Consequently, the new entity C is formed as a result of aggregation operation on this ER model.
Example to Implement in DBMS
Let us see an example to help in understanding the Aggregation method on the ER model of a Database Management System. The below model (Fig. 02) shows the same,
- This Entity Relationship diagram consists of three entities, the Patient, the Doctor, and Patient History. It also has two relationship boxes, they are, the Diagnosis, Filing. In real life, when the patient visits the doctor, a diagnosis will be made. The data on the patient, the doctor and the diagnosis will be stored in the database. But there will be no future use for these data if that is stored individually. Moreover, When these data are required in the future, it is going to be a tedious job and/or not practically possible to query each and every record that is required.
- This brings up the need to organize the data of the Patient, the Doctor, and the Diagnosis relating both the Patient and the Doctor entities. This is subjected to further Aggregation by applying the Filing operation, as Filing can make it easy to fetch the contents whenever the requirement arises. Now we need an entity to hold this Aggregation operation, that is, Patient History Entity.
- In the diagram, it shows the Patient and the Doctor are connected to the Diagnosis operation. Diagnosis is then connected to the Filing operation that represents the Aggregation function. Filing is then connected to the newly created entity, Patient History. By introducing the Aggregation process using the Filing operation and Patient History Entity, the ER diagram appears to be more meaningful. This can now be used for any and all the future upgrades, and it provides scalability to the Database Management System.
Significance of Aggregation in DBMS
Aggregation on the ER Model is one of the most crucial concepts in the Database Management System. Below are the important factors for Aggregation in DBMS,
- In a table where one or more entities does not make sense when used alone, the entity holds data that can be just stored and never can function on its own, it is going to occupy space in the database without any potential use for the In this situation, Aggregation can be applied so that the idle entity can work in relationship with any other entity from the system.
- When the Database Management System is used for Analysis and Reporting processes, first the Database structuring process will be When the Multidimensional structuring or the Relational Structuring process is to be applied, all the entities are expected to be functional and in-scope for any future operation, without any exception. In such cases, unused entities can be passed through the Aggregation method, in order to keep it relational with other entities in the system.
- The Aggregation method is applied to create a relationship between an entity and the other entities of the same level, and the resulting artifact is aggregated further into a higher level This helps in identifying the entity formed as a result of Aggregation in the hierarchical Entity Relationship system.
Conclusion
Aggregation of Entities in the Database Management System corresponds to a connection between two entities that are theoretically at the same hierarchical level in the ER model. The relationship created between the entities can also be represented in the ER model as ‘has-a,’ ‘is-a’ or ‘is-part-of’ relationships, which can have any type of entities from the Entity-Relationship tree. Aggregation does not characterize any alteration on the flow of navigation through the hierarchical ER model, or the relationship pattern in the ER model.
Recommended Articles
This is a guide to Aggregation in DBMS. Here we discuss the meaning, reason to use, process flow examples to implement and significance. You can also go through our other related articles to learn more –
