Aggregation in DBMS

 Aggregation:

One limitation of the E-R model is that it cannot express relationships among relationships. To illustrate the need for such a construct, consider the ternary relationship works-on in following fig., between an employee, branch, and job.

Fig: E-R diagram for ternary relationship

Now, suppose we want to record managers for tasks performed by an employee at a

Branch i.e. we want to record managers for (employee, branch, job) combinations. Let us assume that there is an entity set manager. One alternative for representing this relationship is to create a quaternary relationship manages between employee, branch, job, and manager.

 

A quaternary relationship is required in this case because binary relationship between manager and employee would not permit us to represent which (branch, job) combinations of an employee are managed by which manager. Using the basic E-R modeling constructs, we obtain the following E-R diagram:

Fig: E-R diagram with redundant relationship

But, there is redundant information in the above fig. since every employee, branch, job combination in manages is also in works-on. The best way to model a situation such as the one just described is to use aggregation.

 

Aggregation is an abstraction through which relationships are treated as higher level entities. Thus, for our example, we regard the relationship set works-on (relating the entity sets employee, branch, and job) as a higher-level entity set called works-on. Such an entity set is treated in the same manner as is any other entity set. We can then create a binary relationship ‘manages’ between works-on and manager to represent who manages what tasks as shown in following fig.

Fig: E-R diagram with aggregation.