Aggregation

The aggregation construct in ER modeling is a powerful conceptual tool, but ultimately every database design must be implemented in a relational database management system. The translation from an aggregation diagram to actual SQL tables requires careful consideration of table structures, primary keys, foreign keys, and integrity constraints.

The good news: aggregation maps cleanly to relational structures. The aggregated relationship is already a table (since M:N relationships become tables), and the outer relationship simply references this table. The challenge lies in understanding the key relationships and ensuring referential integrity is properly maintained.

In this page, we'll master the complete process of translating aggregation constructs into well-designed relational schemas, with practical SQL examples you can adapt for your own projects.

Before diving into specific mapping techniques, let's establish the fundamental principles that govern aggregation-to-relational translation.

Principle 1: Inner Relationship Becomes a Table

The inner relationship (the relationship being aggregated) maps to its own table according to standard relationship mapping rules:

• For M:N relationships → Always creates a table with composite primary key
• For 1:N relationships → May merge into the N-side entity or create separate table
• For 1:1 relationships → May merge into either entity or create separate table

Since aggregation typically involves M:N inner relationships (the most common case requiring aggregation), the inner relationship usually produces a distinct table.

Principle 2: Inner Relationship Table Is Primary Reference

The table created for the inner relationship serves as the reference point for the outer relationship. The outer relationship will have foreign key(s) pointing to this inner relationship table.

Principle 3: Outer Relationship Follows Standard Rules

The outer relationship is mapped just like any binary relationship:

• M:N outer relationship → Creates a separate table
• 1:N outer relationship → May use foreign key approach
• 1:1 outer relationship → May merge into either side

Principle 4: Composite Foreign Keys Are Common

Since the inner relationship typically has a composite primary key (combining keys from both participating entities), the outer relationship table will need composite foreign keys to reference the aggregated relationship.

Principle 5: Cascading Behavior Enforces Existence Dependency

The conceptual existence dependency (outer relationship instances depend on inner relationship instances) is implemented through:

• Foreign key constraints with appropriate ON DELETE actions
• Typically CASCADE DELETE or RESTRICT, depending on business rules

Principle 6: Participation Constraints Map to Nullability

• Total participation → NOT NULL constraints on foreign keys
• Partial participation → NULLable foreign keys (or no constraint in the outer relationship table)

Let's walk through the complete mapping process using our running example: MANAGER sponsors (EMPLOYEE works_on PROJECT).

Step 1: Map Inner Entity Sets

Create tables for EMPLOYEE and PROJECT:

CREATE TABLE Employee (
    employee_id     INT PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,
    department      VARCHAR(50),
    hire_date       DATE
);

CREATE TABLE Project (
    project_id      INT PRIMARY KEY,
    title           VARCHAR(200) NOT NULL,
    deadline        DATE,
    budget          DECIMAL(12,2)
);

Step 2: Map Inner Relationship (WORKS_ON)

Since WORKS_ON is M:N, create a relationship table:

CREATE TABLE Works_On (
    employee_id     INT NOT NULL,
    project_id      INT NOT NULL,
    start_date      DATE NOT NULL,
    hours_per_week  INT,
    role            VARCHAR(50),
    
    PRIMARY KEY (employee_id, project_id),
    
    FOREIGN KEY (employee_id) REFERENCES Employee(employee_id)
        ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES Project(project_id)
        ON DELETE CASCADE
);

The composite primary key (employee_id, project_id) uniquely identifies each work assignment—this is the aggregated entity's identity.

Step 3: Map Outer Entity Set

Create table for MANAGER:

CREATE TABLE Manager (
    manager_id      INT PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,
    budget_authority DECIMAL(12,2)
);

Step 4: Map Outer Relationship (SPONSORS)

The SPONSORS relationship connects MANAGER to the aggregated WORKS_ON. Since it's M:N (a manager can sponsor many assignments; an assignment can have many sponsors), we create a separate table:

CREATE TABLE Sponsors (
    manager_id      INT NOT NULL,
    employee_id     INT NOT NULL,
    project_id      INT NOT NULL,
    sponsorship_date DATE NOT NULL,
    allocated_budget DECIMAL(12,2),
    priority        VARCHAR(20),
    
    PRIMARY KEY (manager_id, employee_id, project_id),
    
    FOREIGN KEY (manager_id) REFERENCES Manager(manager_id)
        ON DELETE CASCADE,
    FOREIGN KEY (employee_id, project_id) 
        REFERENCES Works_On(employee_id, project_id)
        ON DELETE CASCADE
);

Critical observations:

1. The Sponsors table has a three-column primary key: (manager_id, employee_id, project_id)
2. There's a composite foreign key (employee_id, project_id) referencing Works_On
3. The foreign key references the Works_On composite primary key, not individual entity tables
4. ON DELETE CASCADE ensures that if a work assignment is deleted, its sponsorships are also deleted

Here is the complete, production-ready SQL schema for the project management aggregation scenario:

Key Schema Features:

1. Referential Integrity: Foreign keys ensure data consistency across all related tables

2. Cascading Deletes: Deleting an employee or project cascades to Works_On; deleting from Works_On cascades to Sponsors

3. Cascading Updates: If primary keys are updated (rare but possible), changes propagate correctly

4. Check Constraints: Business rules are enforced at the database level (valid dates, budgets, etc.)

5. Indexes: Performance-optimized for common query patterns involving joins

The outer relationship can have different cardinalities, each requiring a slightly different mapping approach.

Case 1: M:N Outer Relationship (Multiple sponsors per assignment, multiple assignments per sponsor)

This is our default example. The outer relationship creates its own table with a composite primary key spanning all three identifiers:

-- Primary Key: (manager_id, employee_id, project_id)
CREATE TABLE Sponsors (
    manager_id  INT NOT NULL,
    employee_id INT NOT NULL,
    project_id  INT NOT NULL,
    -- relationship attributes
    PRIMARY KEY (manager_id, employee_id, project_id),
    ...
);

Case 2: 1:N Outer Relationship (One sponsor per assignment, many assignments per sponsor)

If each work assignment can have at most one sponsor:

Option A: Separate Table

CREATE TABLE Sponsors (
    employee_id INT NOT NULL,
    project_id  INT NOT NULL,
    manager_id  INT NOT NULL,
    -- relationship attributes
    PRIMARY KEY (employee_id, project_id),  -- Sponsor per assignment
    FOREIGN KEY (employee_id, project_id) REFERENCES Works_On(...),
    FOREIGN KEY (manager_id) REFERENCES Manager(...)
);

Option B: Add columns to Works_On

ALTER TABLE Works_On ADD (
    sponsor_manager_id INT,
    sponsorship_date DATE,
    allocated_budget DECIMAL(12,2),
    FOREIGN KEY (sponsor_manager_id) REFERENCES Manager(manager_id)
);

Option B is more compact but mixes concerns. Choose based on whether sponsorship attributes are numerous and complex.

Case 3: N:1 Outer Relationship (Multiple sponsors per assignment, each sponsor manages one assignment)

If each manager sponsors exactly one work assignment:

Option A: Separate Table

CREATE TABLE Sponsors (
    manager_id  INT PRIMARY KEY,  -- Each manager appears once
    employee_id INT NOT NULL,
    project_id  INT NOT NULL,
    -- relationship attributes
    FOREIGN KEY (employee_id, project_id) REFERENCES Works_On(...)
);

Option B: Add columns to Manager

ALTER TABLE Manager ADD (
    sponsored_employee_id INT,
    sponsored_project_id INT,
    -- relationship attributes
    FOREIGN KEY (sponsored_employee_id, sponsored_project_id) 
        REFERENCES Works_On(employee_id, project_id)
);

Case 4: 1:1 Outer Relationship (One sponsor per assignment, one assignment per sponsor)

The primary key can be either manager_id alone or (employee_id, project_id). Add a unique constraint on the other:

CREATE TABLE Sponsors (
    manager_id  INT PRIMARY KEY,
    employee_id INT NOT NULL,
    project_id  INT NOT NULL,
    UNIQUE (employee_id, project_id),  -- Each assignment sponsored by at most one
    ...
);

Participation constraints determine whether participation in a relationship is mandatory or optional. These constraints must be implemented carefully in the relational schema.

Inner Relationship Participation:

Participation of inner entities in the inner relationship follows standard rules:

• Total participation of EMPLOYEE in WORKS_ON: Every employee must work on at least one project
• Partial participation of EMPLOYEE in WORKS_ON: Employees may exist without project assignments

Total participation is typically enforced through application logic or triggers, not schema constraints (since the employee must exist before the Works_On record).

Outer Relationship Participation (Aggregation to Outer Entity):

Total participation of aggregation in outer relationship: Every work assignment must have at least one sponsor.

-- This cannot be enforced purely through FK constraints
-- Use triggers or application logic:

CREATE TRIGGER enforce_assignment_sponsor
AFTER INSERT ON Works_On
FOR EACH ROW
BEGIN
    -- Logic to ensure sponsor is assigned
    -- Or schedule a check after a grace period
END;

Partial participation of aggregation: Work assignments may or may not have sponsors. This is the default—no special constraint needed.

Outer Entity Participation:

Total participation of MANAGER in SPONSORS: Every manager must sponsor at least one work assignment.

-- Similar to inner entity total participation,
-- enforce through triggers or application logic:

CREATE TRIGGER enforce_manager_sponsorship
AFTER INSERT ON Manager
FOR EACH ROW
BEGIN
    -- Check or ensure manager has sponsorship
END;

-- Alternatively, prevent manager deletion if they have no sponsorships
-- (though this is the inverse constraint)

Partial participation of MANAGER: Managers may exist without sponsoring any assignments. No constraint needed.

Using Views to Enforce/Monitor Participation:

Views can help identify constraint violations:

-- Find work assignments without sponsors (if total participation expected)
CREATE VIEW Unsponsored_Assignments AS
SELECT w.employee_id, w.project_id, e.name AS employee_name, p.title
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;

-- Find managers without sponsorships (if total participation expected)
CREATE VIEW Inactive_Managers AS
SELECT m.manager_id, m.name
FROM Manager m
LEFT JOIN Sponsors s ON m.manager_id = s.manager_id
WHERE s.manager_id IS NULL;

Let's populate our schema with sample data and demonstrate key queries.

Key Aggregation Queries:

Query 1: All sponsorships with full details

SELECT 
    m.name AS manager_name,
    e.name AS employee_name,
    p.title AS project_title,
    w.role,
    s.allocated_budget,
    s.priority
FROM Sponsors s
JOIN Manager m ON s.manager_id = m.manager_id
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
ORDER BY m.name, p.title;

Query 2: Total budget allocated per manager

SELECT 
    m.name AS manager_name,
    COUNT(*) AS sponsorship_count,
    SUM(s.allocated_budget) AS total_allocated,
    m.budget_authority,
    (m.budget_authority - SUM(s.allocated_budget)) AS remaining_authority
FROM Manager m
LEFT JOIN Sponsors s ON m.manager_id = s.manager_id
GROUP BY m.manager_id, m.name, m.budget_authority;

Query 3: Work assignments without sponsors

SELECT 
    e.name AS employee_name,
    p.title AS project_title,
    w.role,
    w.start_date
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 4: Sponsorship summary per project

SELECT 
    p.title AS project_title,
    COUNT(DISTINCT w.employee_id) AS total_assignments,
    COUNT(DISTINCT s.employee_id || '-' || s.project_id) AS sponsored_assignments,
    COUNT(DISTINCT s.manager_id) AS sponsor_count,
    COALESCE(SUM(s.allocated_budget), 0) AS total_sponsorship_budget
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;

Query 5: Employees with sponsorships from multiple managers

SELECT 
    e.name AS employee_name,
    p.title AS project_title,
    COUNT(DISTINCT s.manager_id) AS sponsor_count,
    STRING_AGG(m.name, ', ') AS sponsors
FROM Works_On w
JOIN Employee e ON w.employee_id = e.employee_id
JOIN Project p ON w.project_id = p.project_id
JOIN Sponsors s ON w.employee_id = s.employee_id 
               AND w.project_id = s.project_id
JOIN Manager m ON s.manager_id = m.manager_id
GROUP BY e.name, p.title
HAVING COUNT(DISTINCT s.manager_id) > 1;

Some aggregation scenarios require more sophisticated mapping techniques.

Scenario 1: Multiple Outer Relationships to Same Aggregation

When multiple outer entities relate to the same aggregated relationship, each outer relationship becomes its own table:

-- Auditors audit work assignments
CREATE TABLE Audits (
    auditor_id      INT NOT NULL,
    employee_id     INT NOT NULL,
    project_id      INT NOT NULL,
    audit_date      DATE NOT NULL,
    findings        TEXT,
    
    PRIMARY KEY (auditor_id, employee_id, project_id, audit_date),
    FOREIGN KEY (auditor_id) REFERENCES Auditor(auditor_id),
    FOREIGN KEY (employee_id, project_id) 
        REFERENCES Works_On(employee_id, project_id)
);

-- Finance tracks costs for work assignments  
CREATE TABLE Cost_Tracking (
    cost_center_id  INT NOT NULL,
    employee_id     INT NOT NULL,
    project_id      INT NOT NULL,
    period          VARCHAR(7) NOT NULL,  -- YYYY-MM
    actual_cost     DECIMAL(12,2),
    
    PRIMARY KEY (cost_center_id, employee_id, project_id, period),
    FOREIGN KEY (cost_center_id) REFERENCES Cost_Center(cost_center_id),
    FOREIGN KEY (employee_id, project_id) 
        REFERENCES Works_On(employee_id, project_id)
);

Both Audits and Cost_Tracking reference the same Works_On table, representing different outer relationships to the same aggregated entity.

Scenario 2: Aggregation of Ternary Relationships

When the inner relationship is ternary, the composite key has three components:

-- Ternary: Supplier supplies Part for Project
CREATE TABLE Supplies (
    supplier_id     INT NOT NULL,
    part_id         INT NOT NULL,
    project_id      INT NOT NULL,
    unit_price      DECIMAL(10,2),
    lead_time_days  INT,
    
    PRIMARY KEY (supplier_id, part_id, project_id),
    FOREIGN KEY (supplier_id) REFERENCES Supplier(supplier_id),
    FOREIGN KEY (part_id) REFERENCES Part(part_id),
    FOREIGN KEY (project_id) REFERENCES Project(project_id)
);

-- Outer: Contract governs supply arrangements
CREATE TABLE Contract_Coverage (
    contract_id     INT NOT NULL,
    supplier_id     INT NOT NULL,
    part_id         INT NOT NULL,
    project_id      INT NOT NULL,
    terms           TEXT,
    effective_date  DATE,
    
    PRIMARY KEY (contract_id, supplier_id, part_id, project_id),
    FOREIGN KEY (contract_id) REFERENCES Contract(contract_id),
    FOREIGN KEY (supplier_id, part_id, project_id) 
        REFERENCES Supplies(supplier_id, part_id, project_id)
);

The composite foreign key now spans three columns.

Scenario 3: Self-Referential Aggregation

When the outer entity is of the same type as an inner entity:

-- Inner: Junior employee works on project
-- Outer: Senior employee mentors the junior's work

-- Works_On already exists

CREATE TABLE Mentors (
    senior_employee_id  INT NOT NULL,
    junior_employee_id  INT NOT NULL,
    project_id          INT NOT NULL,
    mentoring_focus     VARCHAR(100),
    start_date          DATE NOT NULL,
    
    PRIMARY KEY (senior_employee_id, junior_employee_id, project_id),
    FOREIGN KEY (senior_employee_id) REFERENCES Employee(employee_id),
    FOREIGN KEY (junior_employee_id, project_id) 
        REFERENCES Works_On(employee_id, project_id),
    CONSTRAINT chk_not_self CHECK (senior_employee_id != junior_employee_id)
);

The check constraint prevents self-mentoring.

We've covered the complete process of translating aggregation constructs into relational database schemas. Let's consolidate the key takeaways:

Module Complete:

You have now completed the comprehensive module on Aggregation. You understand:

• The conceptual foundation of aggregation and why it's needed
• How relationships can involve other relationships
• The notation for representing aggregation in ER diagrams
• Real-world use cases across multiple domains
• How to map aggregation to relational database schemas

With this knowledge, you can recognize scenarios requiring aggregation, model them correctly in ER diagrams, and implement them in production-quality relational databases.