Aggregation in DBMS

Design steps for aggregation 

• Define entities and their attributes
• Add a relationship between these entities
• Define another entity so that the relationship can be established between the existing relationship and this entity

Why aggregation

• Aggregation is a process of compiling information on an object thereby abstracting higher-level object
• In SQL we need to find the sum of salaries of all the employees working in an organization or to find the highest-paid employee from all branches of the organization etc.

When using data in the form of numerical values, the following operations can be used to perform DBMS aggregation:

• Average (AVG): This function provides the mean or average of the data values.
• Sum: This provides a total value after the data values have been added.
• Count: This provides the number of records.
• Maximum (Max): This function provides the maximum value of a given set of data.
• Minimum (Min): This provides the minimum value of a given set of data.
• Standard deviation (std dev): This provides the dispersion or variation of the sets of data. Let’s take a simple example of a database of student marks. If the standard deviation is high, it means the average is obtained by lower number of students than usual, and the lowest and highest marks are higher.

Characteristics of Aggregation

Aggregation is used when the DBMS has the following characteristics.

• Many trivial entities: A DBMS may consist of many entities that are not significant enough to provide meaningful information. In such a case, the trivial entities can be combined into one complex entity through aggregation. For example, many trivial entities called rooms can be combined to form a single entity called hotel.
• One trivial entity: Aggregation is also needed if a DBMS has a single trivial entity that should be used for multiple operations. In this case, the trivial entity is used to form relationships with other entities. This may lead to many aggregation entities depending on the operations required. For example, an employee in an organization may be given an insurance policy that covers his dependants. The entity dependants is a trivial entity because it cannot exist without the entity employee.
• Inapplicable entity-model relationship: The entity-model relationship cannot be applied to certain entities within the system. These specific entities can be combined with other entities to allow the application of the entity-model relationship in the entire system. This ensures that all the entities in the system are utilized. For example, the entity-model relationship for students can only be applied if students enroll in a class. The entity grade can only be formed if the relationship enroll exists.