DBMS Notes
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...
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 — something impossible in the basic ER model.
Aggregation was introduced because the standard ER model has a strict rule: relationships can only exist between entities, never between a relationship and an entity. When real-world scenarios demand exactly that, aggregation provides an elegant solution by "encapsulating" an entire relationship (along with its participating entities) into a single abstract entity.
The Solution: Aggregation
Aggregation treats the works_on relationship (along with EMPLOYEE and PROJECT) as a single abstract entity:
The dashed box represents the aggregation. Now manages is a valid binary relationship between MANAGER and the aggregated entity. This cleanly expresses: "A manager manages a particular employee-project assignment."
Formal Definition
Given entities E1, E2, ..., En participating in relationship R, the aggregation of R creates a new abstract entity A such that:
- A represents each instance of R (i.e., each tuple in the relationship set)
- A can participate in other relationships just like any regular entity
- A inherits the primary key of R (typically the combination of participating entity keys)
Detailed Example — Research Sponsorship
Scenario: A university tracks which professors conduct research on projects, and external funding agencies sponsor specific professor-project research efforts.
Without aggregation (incorrect):
This fails because a funding agency does not sponsor a professor or a project in isolation — it sponsors the specific research effort of a professor on a project.
With aggregation (correct):
The sponsors relationship connects FUNDING_AGENCY to the aggregated "professor-researches-project" unit.
Relational Schema Mapping
When converting an aggregation to relational tables:
-- Entity tables
CREATE TABLE Professor (
prof_id INT PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(50)
);
CREATE TABLE Project (
project_id INT PRIMARY KEY,
title VARCHAR(200),
start_date DATE
);
CREATE TABLE FundingAgency (
agency_id INT PRIMARY KEY,
agency_name VARCHAR(100),
country VARCHAR(50)
);
-- Relationship table for the aggregated relationship
CREATE TABLE Researches (
prof_id INT REFERENCES Professor(prof_id),
project_id INT REFERENCES Project(project_id),
hours_per_week INT,
PRIMARY KEY (prof_id, project_id)
);
-- Relationship table connecting the aggregation to another entity
CREATE TABLE Sponsors (
prof_id INT,
project_id INT,
agency_id INT REFERENCES FundingAgency(agency_id),
grant_amount DECIMAL(12,2),
PRIMARY KEY (prof_id, project_id, agency_id),
FOREIGN KEY (prof_id, project_id) REFERENCES Researches(prof_id, project_id)
);Notice how the Sponsors table references the composite key of Researches — this captures the aggregation semantics in the relational model.
Aggregation vs. Ternary Relationship
| Aspect | Aggregation | Ternary Relationship |
|---|---|---|
| Structure | Binary relationship on an aggregated unit | Single relationship among three entities |
| Flexibility | Inner relationship can exist without outer | All three entities always needed together |
| Constraints | Different constraints on inner vs. outer | Single set of constraints |
| Semantics | "Manager manages an assignment" | "Employee, Project, Manager connected" |
| When to use | Outer relationship is optional/separate | All three are inherently coupled |
When to Use Aggregation
Use aggregation when:
- A relationship needs to participate in another relationship
- The inner relationship has independent meaning (can exist without the outer)
- You need to attach attributes or constraints to the outer relationship separately
- A ternary relationship would incorrectly force all three entities to always appear together
Common Mistakes
- Using ternary when aggregation is needed: If employee-project assignments can exist without a manager, a ternary relationship forces you to leave manager as NULL — awkward and semantically incorrect.
- Confusing aggregation with generalization: Aggregation encapsulates a relationship as an entity; generalization creates IS-A hierarchies among entities. They solve completely different problems.
- Forgetting the foreign key in schema mapping: The outer relationship table must reference the composite primary key of the inner relationship table — not individual entity keys separately.
Summary
Aggregation bridges a fundamental limitation of the ER model by allowing relationships to be treated as entities. It produces cleaner, more accurate conceptual models for scenarios where one relationship logically depends on another. In practice, it maps to a foreign key referencing a composite key in the relational schema, maintaining referential integrity across both relationship levels.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Aggregation — Database Management Systems.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this Database Management Systems (DBMS) topic.
Search Terms
dbms, database management systems (dbms), unit, aggregation, aggregation — database management systems
Related Database Management Systems (DBMS) Topics