Aggregation

In standard Entity-Relationship modeling, relationships connect entities. But what happens when we need to model a scenario where a relationship itself becomes the subject of another relationship? This is not a hypothetical edge case—it's a common pattern that arises in complex real-world domains.

Consider these scenarios:

• A Job Placement Agency tracks which consultants are placed at which clients, and which account managers are responsible for each placement
• A Research Institution monitors which scientists collaborate on which papers, and which grants fund each collaboration
• A Healthcare System records which patients undergo which treatments, and which insurance policies cover each treatment episode

In each case, there's an inner relationship (consultant-client, scientist-paper, patient-treatment) and an outer entity (account manager, grant, insurance) that relates not to the individual entities, but to their specific association.

Understanding how relationships can involve other relationships requires examining the underlying structure carefully. Let's break down the mechanics step by step.

The Two-Layer Structure:

When a relationship involves another relationship, we have a layered structure:

1. Layer 1 (Inner Relationship): A standard relationship set connecting two or more entity sets. This relationship exists independently and has its own instances, attributes, and constraints.

2. Layer 2 (Outer Relationship): A relationship that connects an entity set to the aggregated form of the inner relationship. The outer relationship treats the inner relationship instances as if they were entities.

How the connection works:

The key insight is that each instance of the inner relationship can be uniquely identified by the combination of entities that participate in it. For a binary relationship R between entity sets A and B, each relationship instance can be identified by a pair (a, b) where a ∈ A and b ∈ B.

When we aggregate R, we're essentially saying: "Treat each (a, b) pair—each instance of R—as if it were an entity." The outer relationship can then associate these 'entity-like' relationship instances with entities from another entity set.

Formal representation:

Let's formalize this structure:

• Let E₁ and E₂ be entity sets
• Let R be a relationship set between E₁ and E₂: R ⊆ E₁ × E₂
• Let E₃ be another entity set
• Let AGG(R) denote the aggregation of R (treating R as an entity set)
• Let S be the outer relationship set between E₃ and AGG(R): S ⊆ E₃ × AGG(R)

Each instance of S connects an entity from E₃ to a specific instance of R (which is a pair from E₁ × E₂).

Example formalization:

• E₁ = EMPLOYEE, E₂ = PROJECT
• R = WORKS_ON ⊆ EMPLOYEE × PROJECT
• E₃ = MANAGER
• AGG(R) = WORK_ASSIGNMENT (aggregated WORKS_ON)
• S = SPONSORS ⊆ MANAGER × WORK_ASSIGNMENT

A specific instance of SPONSORS might be: (Manager M1, (Employee E1, Project P1)), meaning Manager M1 sponsors the work assignment where Employee E1 works on Project P1.

When a relationship involves another relationship, several important semantic implications arise. Understanding these implications is crucial for correct modeling and querying.

Implication 1: Existence Dependency

The outer relationship instance can only exist if the corresponding inner relationship instance exists. If Employee E1 no longer works on Project P1 (the WORKS_ON instance is removed), then Manager M1's sponsorship of that work assignment must also be removed.

This creates a cascading dependency:

WORKS_ON(E1, P1) exists → SPONSORS(M1, (E1, P1)) can exist
WORKS_ON(E1, P1) deleted → SPONSORS(M1, (E1, P1)) must be deleted

Implication 2: Query Path Requirements

To query the outer relationship, you must traverse through the inner relationship structure. Finding "all work assignments sponsored by Manager M1" requires joining the outer relationship with the aggregated inner relationship.

Implication 3: Cardinality Interpretation

Cardinality constraints on the outer relationship apply between the outer entity and relationship instances of the inner relationship:

• 1:1 — One outer entity relates to at most one inner relationship instance, and vice versa
• 1:N — One outer entity can relate to many inner relationship instances
• M:N — Many outer entities can relate to many inner relationship instances

Implication 4: Attribute Layering

Attributes can exist at multiple levels:

• Inner entity attributes: Properties of the participating entities (e.g., employee name, project budget)
• Inner relationship attributes: Properties of the association (e.g., works_on start_date, hours_per_week)
• Outer relationship attributes: Properties of the higher-level association (e.g., sponsorship budget, approval_date)

Each layer captures different semantic information. The sponsorship budget is not the same as the employee's salary or the project's total budget—it's specifically the budget allocated to that particular work assignment by that particular manager.

Implication 5: Referential Semantics

The outer relationship references the inner relationship as a whole. It doesn't reference the entities separately. This means:

• The manager sponsors the assignment, not the employee or the project individually
• Changes to the assignment (e.g., changing hours_per_week) don't affect the sponsorship's identity
• Changes to participating entities' primary keys would cascade through both relationships

Let's work through a comprehensive example to solidify understanding. We'll model a Project Management System where employees work on projects and managers sponsor specific work assignments.

Requirements:

1. The company has EMPLOYEES identified by employee_id, with attributes name and department
2. The company has PROJECTS identified by project_id, with attributes title and deadline
3. Employees can work on multiple projects; projects can have multiple employees (M:N)
4. The WORKS_ON relationship has attributes: start_date, hours_per_week, role
5. MANAGERS (identified by manager_id, with attributes name and budget_authority) can sponsor work assignments
6. Sponsorship includes: sponsorship_date, allocated_budget, priority
7. A manager can sponsor multiple work assignments; each work assignment can have multiple sponsors
8. Not all work assignments need sponsors (some are routine and don't require management attention)

Step-by-step model construction:

Step 1: Model the Inner Relationship (WORKS_ON)

Entity Set: EMPLOYEE
  - employee_id (PK)
  - name
  - department

Entity Set: PROJECT
  - project_id (PK)
  - title
  - deadline

Relationship Set: WORKS_ON
  - employee_id (FK to EMPLOYEE)
  - project_id (FK to PROJECT)
  - start_date
  - hours_per_week
  - role
  - Primary Key: (employee_id, project_id)
  - Cardinality: M:N
  - Participation: Partial for both (employees may not be assigned; projects may have no employees yet)

Step 2: Apply Aggregation

We aggregate the WORKS_ON relationship along with EMPLOYEE and PROJECT into an abstract entity called WORK_ASSIGNMENT.

Aggregated Entity: WORK_ASSIGNMENT
  - Represents: Instances of WORKS_ON relationship
  - Identity: (employee_id, project_id) from WORKS_ON
  - Inherits attributes: start_date, hours_per_week, role

Step 3: Model the Outer Entity (MANAGER)

Entity Set: MANAGER
  - manager_id (PK)
  - name
  - budget_authority (maximum amount manager can allocate)

Step 4: Create the Outer Relationship (SPONSORS)

Relationship Set: SPONSORS
  - manager_id (FK to MANAGER)
  - employee_id (FK to WORK_ASSIGNMENT via WORKS_ON)
  - project_id (FK to WORK_ASSIGNMENT via WORKS_ON)
  - sponsorship_date
  - allocated_budget
  - priority (e.g., 'High', 'Medium', 'Low')
  - Primary Key: (manager_id, employee_id, project_id)
  - Cardinality: M:N (between MANAGER and WORK_ASSIGNMENT)
  - Participation: 
    - Total for MANAGER in SPONSORS (every manager must sponsor at least one assignment)
    - Partial for WORK_ASSIGNMENT (not all assignments need sponsors)

Step 5: Sample Data

Observations from Sample Data:

1. Work assignment (E001, P101) is sponsored by M01 with $15,000
2. Work assignment (E001, P102) is sponsored by M02 with $8,000 (different manager for same employee's different project)
3. Work assignment (E002, P102) is sponsored by M01 with $20,000
4. Work assignment (E003, P101) has NO sponsor (partial participation in SPONSORS)
5. Manager M01 sponsors multiple work assignments (M:N cardinality)
6. The sponsorship budget is for the specific work assignment, not for the employee or project globally

Understanding how aggregation affects queries is essential for both conceptual modeling and practical implementation. Let's examine typical queries in our project management system and how they traverse the aggregation structure.

Query Type 1: Simple Entity Queries

These don't involve the aggregation and work normally:

"Find all employees in the Engineering department"

SELECT * FROM EMPLOYEE WHERE department = 'Engineering';

Query Type 2: Inner Relationship Queries

These query the inner relationship without involving aggregation:

"Find all projects that employee E001 works on"

SELECT p.* 
FROM WORKS_ON w 
JOIN PROJECT p ON w.project_id = p.project_id
WHERE w.employee_id = 'E001';

Query Type 3: Aggregation-Aware Queries

These require traversing the aggregation structure:

"Find all work assignments sponsored by Manager M01"

SELECT e.name AS employee_name, p.title AS project_title, 
       w.role, s.allocated_budget
FROM SPONSORS s
JOIN WORKS_ON w ON s.employee_id = w.employee_id 
                AND s.project_id = w.project_id
JOIN EMPLOYEE e ON w.employee_id = e.employee_id
JOIN PROJECT p ON w.project_id = p.project_id
WHERE s.manager_id = 'M01';

Query Type 4: Aggregate Across Aggregation

"Find total budget allocated by each manager across all sponsorships"

SELECT m.name, SUM(s.allocated_budget) AS total_allocated
FROM MANAGER m
JOIN SPONSORS s ON m.manager_id = s.manager_id
GROUP BY m.manager_id, m.name;

Query Type 5: Finding Unsponsored Assignments

"Find all work assignments that have no sponsor"

SELECT e.name, p.title, w.role
FROM WORKS_ON w
JOIN EMPLOYEE e ON w.employee_id = e.employee_id
JOIN PROJECT p ON w.project_id = p.project_id
LEFT JOIN SPONSORS s ON w.employee_id = s.employee_id 
                     AND w.project_id = s.project_id
WHERE s.manager_id IS NULL;

Query Type 6: Complex Aggregation Queries

"For each project, find the total sponsorship budget allocated to its work assignments"

SELECT p.project_id, p.title, 
       COALESCE(SUM(s.allocated_budget), 0) AS total_sponsorship
FROM PROJECT p
LEFT JOIN WORKS_ON w ON p.project_id = w.project_id
LEFT JOIN SPONSORS s ON w.employee_id = s.employee_id 
                     AND w.project_id = s.project_id
GROUP BY p.project_id, p.title;

Cardinality constraints in aggregation scenarios require careful analysis because they operate at multiple levels. Let's systematically examine cardinality in the context of relationships involving relationships.

Level 1: Inner Relationship Cardinality

The cardinality between the entities in the inner relationship follows standard rules:

• EMPLOYEE to PROJECT (via WORKS_ON): M:N
• An employee can work on many projects
• A project can have many employees

This determines how many WORKS_ON instances exist and thus how many aggregated WORK_ASSIGNMENT entities are available for the outer relationship.

Level 2: Outer Relationship Cardinality

The cardinality between the outer entity and the aggregated inner relationship:

• MANAGER to WORK_ASSIGNMENT (via SPONSORS): M:N
• A manager can sponsor many work assignments
• A work assignment can have many sponsors

Possible Cardinality Combinations:

Different scenarios call for different outer cardinality patterns:

Cardinality Constraints Impact:

1. Key Definition: The outer relationship's primary key depends on cardinality:
   • 1:1: Either outer entity key or aggregated relationship key can be primary
   • 1:N: Aggregated relationship key is primary key
   • M:1: Outer entity key is primary key
   • M:N: Composite of outer entity key + aggregated relationship key

Participation Constraints:

Participation constraints also apply at both levels:

Inner relationship participation:

• Must every employee work on at least one project? (Total participation of EMPLOYEE in WORKS_ON)
• Must every project have at least one employee? (Total participation of PROJECT in WORKS_ON)

Outer relationship participation:

• Must every work assignment be sponsored? (Total participation of WORK_ASSIGNMENT in SPONSORS)
• Must every manager sponsor at least one assignment? (Total participation of MANAGER in SPONSORS)

Example constraint specifications:

Inner Level:
  EMPLOYEE participates PARTIALLY in WORKS_ON (not all employees are assigned)
  PROJECT participates PARTIALLY in WORKS_ON (some projects await staffing)

Outer Level:
  WORK_ASSIGNMENT participates PARTIALLY in SPONSORS (routine assignments don't need sponsors)
  MANAGER participates TOTALLY in SPONSORS (managers are defined by having sponsorship responsibility)

These constraints must be enforced at the database level and inform integrity checking logic.

Relationship-involving-relationship scenarios appear in various patterns across different domains. Recognizing these patterns accelerates modeling and helps identify aggregation opportunities.

Pattern 1: Monitoring/Oversight Pattern

An entity monitors, audits, or oversees specific associations between other entities.

Structure: E₃ MONITORS (E₁ R E₂)

Examples:

• Auditor audits employee-transaction associations
• Supervisor oversees operator-machine assignments
• Inspector inspects supplier-delivery relationships

Pattern 2: Resource Allocation Pattern

An entity allocates resources (budget, equipment, time) to specific associations.

Structure: E₃ ALLOCATES-RESOURCE-TO (E₁ R E₂)

Examples:

• Manager allocates budget to employee-project assignments
• Fund provides grants to researcher-study collaborations
• Equipment pool assigns machines to worker-shift pairings

Pattern 3: Authorization/Approval Pattern

An entity authorizes or approves specific associations to proceed.

Structure: E₃ APPROVES (E₁ R E₂)

Examples:

• Compliance officer approves trader-instrument authorizations
• Department head approves student-course enrollments
• Legal team approves vendor-contract executions

Pattern 4: Facilitation/Mediation Pattern

An entity facilitates or mediates associations between other entities.

Structure: E₃ FACILITATES (E₁ R E₂)

Examples:

• Placement agency facilitates consultant-client matches
• Broker facilitates buyer-seller transactions
• Mediator facilitates party-dispute resolutions

Pattern 5: Documentary Pattern

An entity documents, records, or provides evidence for associations.

Structure: E₃ DOCUMENTS (E₁ R E₂)

Examples:

• Certificate documents student-course completions
• Contract documents employer-employee agreements
• License documents practitioner-practice authorizations

Variations in Structure:

Variation A: Multiple Outer Entities

More than one entity type may relate to the same aggregated relationship:

Auditor AUDITS (Employee WORKS_ON Project)
Manager SPONSORS (Employee WORKS_ON Project)
BudgetSystem TRACKS-COST-OF (Employee WORKS_ON Project)

Each creates a separate outer relationship to the same aggregated unit.

Variation B: Chained Aggregation

In rare complex scenarios, an aggregated relationship may itself be aggregated:

(Manager SPONSORS (Employee WORKS_ON Project)) → AUDITED_BY → Auditor

Here, the sponsorship relationship is itself aggregated and connected to an auditor. This creates three levels of nesting and should be used sparingly due to complexity.

Variation C: Self-Aggregation

The outer entity may be the same type as an inner entity:

SeniorEmployee MENTORS (JuniorEmployee WORKS_ON Project)

A senior employee mentors the work assignment of a junior employee on a project. The EMPLOYEE entity set participates both in WORKS_ON and as the outer entity.

Modeling relationships that involve other relationships is an advanced skill. Here are common errors and misconceptions to avoid:

Mistake 1: Conflating Aggregation with Ternary Relationships

Error: Modeling Manager-Employee-Project as a ternary SPONSORS relationship.

Problem: This incorrectly implies that all three entities are peers and that the employee-project association doesn't exist independently.

Correction: Use aggregation when the employee-project relationship (WORKS_ON) exists independently and the manager relates to that relationship.

Mistake 2: Omitting the Inner Relationship

Error: Only modeling SPONSORS(MANAGER, EMPLOYEE, PROJECT) without explicitly modeling WORKS_ON.

Problem: Loses the semantics that employees work on projects independently of sponsorship. Can't represent work assignments without sponsors.

Correction: Explicitly model WORKS_ON as a separate relationship before aggregating it.

Mistake 3: Incorrect Cardinality Specification

Error: Specifying cardinality between manager and employee or manager and project instead of between manager and work-assignment.

Problem: Misrepresents the actual constraints. The cardinality is about how many work assignments a manager can sponsor, not how many employees or projects.

Correction: Always specify cardinality relative to the aggregated relationship instances.

Mistake 4: Forgetting Existence Dependencies

Error: Not implementing cascading deletes when the inner relationship instance is removed.

Problem: Creates orphaned outer relationship instances that reference non-existent inner relationships.

Correction: Implement referential integrity with CASCADE DELETE from inner relationship to outer relationship.

Mistake 5: Attribute Misplacement

Error: Placing sponsorship_budget on the WORKS_ON relationship instead of the SPONSORS relationship.

Problem: Implies budget is a property of the work assignment itself, not of the sponsorship.

Correction: Attributes belong to the relationship where they're semantically determined. The budget is determined by the sponsorship, not the assignment.

We've thoroughly explored the mechanics and implications of relationships that involve other relationships. Let's consolidate the key takeaways:

What's next:

Now that we understand the mechanics of how relationships can involve other relationships, the next page focuses on the notation used to represent aggregation in ER diagrams. We'll examine standard notational conventions and how to read and draw aggregation constructs.