Aggregation

Aggregation is an abstraction mechanism in the Extended ER EER model that allows a relationship set to be treated as a higher-level entity set. This makes it possible for

Definition

Aggregation is an abstraction mechanism in the Extended ER (EER) model that allows a relationship set to be treated as a higher-level entity set. This makes it possible for the (aggregated) relationship to participate in another relationship.

Aggregation was introduced to handle situations where a relationship needs to be associated with another entity — something the basic ER model cannot represent directly.

Motivation: The Problem

Consider a university scenario:

An employee works on a project
A manager manages this assignment

Attempt without aggregation:

This is problematic because:

manages appears to come out of a relationship (works_on) — which is not allowed in standard ER
A relationship cannot directly participate in another relationship
This doesn't clearly express that the manager manages the *combination* of employee + project

Solution: Aggregation

Aggregation treats the works_on relationship (along with its participating entities) as a single abstract entity:

Now manages is a relationship between MANAGER and the aggregated entity (EMPLOYEE works_on PROJECT).

Another Example — Funding Agencies

Scenario: A funding agency sponsors specific employee+project combinations.

Key Properties of Aggregation

An aggregated relationship set behaves like an entity set in higher-level relationships.
The aggregated unit gets its own identifier (typically the combined PK of its components).
Aggregation is used when a relationship itself needs to be associated with something else.
Only used when simpler ER constructs are insufficient.

Relational Mapping of Aggregation

When converting aggregation to relational tables:

sql exampleWoHoTech

-- Base entities
CREATE TABLE Employee (emp_id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE Project  (proj_id INT PRIMARY KEY, title VARCHAR(100));
CREATE TABLE Manager  (mgr_id INT PRIMARY KEY, name VARCHAR(100));

-- The aggregated relationship: Employee works_on Project
CREATE TABLE Works_On (
    emp_id   INT,
    proj_id  INT,
    hours    INT,
    PRIMARY KEY (emp_id, proj_id),
    FOREIGN KEY (emp_id)  REFERENCES Employee(emp_id),
    FOREIGN KEY (proj_id) REFERENCES Project(proj_id)
);

-- The outer relationship: Manager manages (Employee, Project) pair
CREATE TABLE Manages (
    mgr_id   INT,
    emp_id   INT,
    proj_id  INT,
    since    DATE,
    PRIMARY KEY (mgr_id, emp_id, proj_id),
    FOREIGN KEY (mgr_id)           REFERENCES Manager(mgr_id),
    FOREIGN KEY (emp_id, proj_id)  REFERENCES Works_On(emp_id, proj_id)
);

Aggregation vs. Ternary Relationship

Both can model three-way associations. Aggregation is preferred when:

The inner relationship already exists independently
The outer relationship applies to the inner relationship as a whole
The semantics require treating the inner relationship as an object

Summary

Regular ER Limitation

Relationships cannot participate in other relationships.

Aggregation Solution

Wrap a relationship + its entities into an abstract higher-level entity

→ That aggregate can now participate in a new relationship

Use Case

When the "context" of a relationship needs to be associated with

another entity.

Definition

Aggregation was introduced to handle situations where a relationship needs to be associated with another entity — something the basic ER model cannot represent directly.

Motivation: The Problem

Consider a university scenario:

An employee works on a project
A manager manages this assignment

Attempt without aggregation:

This is problematic because:

manages appears to come out of a relationship (works_on) — which is not allowed in standard ER
A relationship cannot directly participate in another relationship
This doesn't clearly express that the manager manages the *combination* of employee + project

Solution: Aggregation

Aggregation treats the works_on relationship (along with its participating entities) as a single abstract entity:

Now manages is a relationship between MANAGER and the aggregated entity (EMPLOYEE works_on PROJECT).

Another Example — Funding Agencies

Scenario: A funding agency sponsors specific employee+project combinations.

Key Properties of Aggregation

An aggregated relationship set behaves like an entity set in higher-level relationships.
The aggregated unit gets its own identifier (typically the combined PK of its components).
Aggregation is used when a relationship itself needs to be associated with something else.
Only used when simpler ER constructs are insufficient.

Relational Mapping of Aggregation

When converting aggregation to relational tables:

sql exampleWoHoTech

-- Base entities
CREATE TABLE Employee (emp_id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE Project  (proj_id INT PRIMARY KEY, title VARCHAR(100));
CREATE TABLE Manager  (mgr_id INT PRIMARY KEY, name VARCHAR(100));

-- The aggregated relationship: Employee works_on Project
CREATE TABLE Works_On (
    emp_id   INT,
    proj_id  INT,
    hours    INT,
    PRIMARY KEY (emp_id, proj_id),
    FOREIGN KEY (emp_id)  REFERENCES Employee(emp_id),
    FOREIGN KEY (proj_id) REFERENCES Project(proj_id)
);

-- The outer relationship: Manager manages (Employee, Project) pair
CREATE TABLE Manages (
    mgr_id   INT,
    emp_id   INT,
    proj_id  INT,
    since    DATE,
    PRIMARY KEY (mgr_id, emp_id, proj_id),
    FOREIGN KEY (mgr_id)           REFERENCES Manager(mgr_id),
    FOREIGN KEY (emp_id, proj_id)  REFERENCES Works_On(emp_id, proj_id)
);

Aggregation vs. Ternary Relationship

Both can model three-way associations. Aggregation is preferred when:

The inner relationship already exists independently
The outer relationship applies to the inner relationship as a whole
The semantics require treating the inner relationship as an object

Summary

Regular ER Limitation

Relationships cannot participate in other relationships.

Aggregation Solution

Wrap a relationship + its entities into an abstract higher-level entity

→ That aggregate can now participate in a new relationship

Use Case

When the "context" of a relationship needs to be associated with

another entity.

Aggregation

Definition

Motivation: The Problem

Solution: Aggregation

Another Example — Funding Agencies

Key Properties of Aggregation

Relational Mapping of Aggregation

Aggregation vs. Ternary Relationship

Summary

Regular ER Limitation

Aggregation Solution

Use Case

Continue learning this concept

Aggregation

Definition

Motivation: The Problem

Solution: Aggregation

Another Example — Funding Agencies

Key Properties of Aggregation

Relational Mapping of Aggregation

Aggregation vs. Ternary Relationship

Summary

Regular ER Limitation

Aggregation Solution

Use Case

Continue learning this concept