Aggregation

An ER diagram is, at its core, a visual communication tool. It must convey complex database structures to diverse stakeholders—from business analysts who validate requirements, to database administrators who implement the schema, to developers who write queries. The notation we use directly impacts comprehension, accuracy, and collaboration.

Aggregation introduces a unique visual challenge: how do we show that a relationship participates in another relationship? Unlike entities and simple relationships, which have well-established, universally recognized symbols (rectangles for entities, diamonds for relationships), aggregation requires a notation that visually encapsulates a relationship and its participants while allowing that encapsulated unit to connect to other elements.

In this page, we'll master the notation for aggregation—examining standard conventions, variations across methodologies, and best practices for creating clear, professional ER diagrams that accurately represent aggregation constructs.

The standard notation for aggregation uses a bounding rectangle (or box) drawn around the relationship and its participating entities. This box visually encapsulates the aggregated unit, treating it as a single higher-level element.

Core Notation Elements:

1. Inner Elements: The entities and relationship being aggregated are drawn as normal (rectangles for entities, diamond for relationship, lines connecting them)

2. Bounding Box: A rectangle is drawn around the entire inner structure—encompassing both entities and the relationship diamond

3. Box Styling: The bounding box is typically drawn with:

   • A regular solid line (same weight as entity boxes) or
   • A dashed/dotted line to differentiate from entity rectangles

4. Aggregation Label: The aggregated unit may optionally receive a label (placed near or on the bounding box) indicating its conceptual name

5. Outer Connection: A line extends from the bounding box to the outer relationship diamond, which then connects to the outer entity

Visual Structure (Text Description):

┌─────────────────────────────────────────────────┐
│                                                 │
│   ┌──────────────┐       ┌──────────────┐       │
│   │   EMPLOYEE   │───────│   PROJECT    │       │
│   └──────────────┘   ◇   └──────────────┘       │
│                   WORKS_ON                      │
│                      │                          │
│                  (attributes)                   │
│                                                 │
└───────────────────────┬─────────────────────────┘
                        │  (aggregation connection)
                        │
                   ◇ SPONSORS
                        │
                        │
                ┌───────────────┐
                │    MANAGER    │
                └───────────────┘

Reading the diagram:

1. EMPLOYEE and PROJECT are connected by WORKS_ON (inner relationship)
2. The bounding box around EMPLOYEE, PROJECT, and WORKS_ON indicates aggregation
3. The aggregated unit connects to SPONSORS (outer relationship)
4. SPONSORS connects the aggregated unit to MANAGER (outer entity)
5. The complete reading: "MANAGER sponsors (EMPLOYEE works_on PROJECT)"

Creating an aggregation diagram requires a methodical approach. Here's a detailed step-by-step process:

Step 1: Draw the Inner Relationship First

Begin by drawing the inner relationship as if no aggregation exists:

• Draw the participating entity rectangles
• Draw the relationship diamond between them
• Connect entities to relationship with lines
• Add cardinality notation to the lines
• Add any relationship attributes

Example: Draw EMPLOYEE rectangle, PROJECT rectangle, WORKS_ON diamond, connecting lines with M:N cardinality, and attributes like start_date, hours_per_week.

Step 2: Verify Inner Relationship Completeness

Before adding the aggregation box, verify:

• All inner entities are positioned within anticipated box boundaries
• The inner relationship diamond is centered or positioned for visual clarity
• There's adequate space around the inner elements for the bounding box
• Attributes are positioned appropriately (inside or outside the eventual box)

Step 3: Draw the Bounding Box

Now add the aggregation rectangle:

• Draw a rectangle that completely encloses the inner entities and relationship
• Leave adequate margin between the inner elements and the box edges
• Ensure the box doesn't intersect any elements—it should cleanly contain them
• If using a specific style (dashed, double-line), apply it consistently

Step 4: Add Aggregation Label (Optional)

If the aggregated concept has a meaningful name in the domain, add it:

• Position the label above, below, or inside the bounding box
• Use a distinct format (e.g., italics, bold, or prefixed with "AGG:")
• Examples: "WORK_ASSIGNMENT", "AGG: Work Assignment", or simply leave unlabeled if context is clear

Step 5: Draw the Outer Entity

Position and draw the outer entity that will relate to the aggregation:

• Draw as a standard entity rectangle
• Position it outside the aggregation box with adequate spacing
• Placement typically follows diagram flow (left-to-right or top-to-bottom)

Step 6: Draw the Outer Relationship

Connect the aggregation to the outer entity:

• Draw the outer relationship diamond between the aggregation box and outer entity
• Draw a line from the aggregation box (typically from the box edge, not from a specific internal element) to the relationship diamond
• Draw a line from the relationship diamond to the outer entity
• Add cardinality notation to both lines

Step 7: Add Outer Relationship Attributes

If the outer relationship has attributes:

• Attach them to the outer relationship diamond
• Use the same notation style as other relationship attributes in the diagram
• Examples: sponsorship_date, allocated_budget, priority

Step 8: Add Participation Constraints

Indicate participation constraints for the outer relationship:

• Use double lines for total participation
• Use single lines for partial participation
• Apply to both the aggregation-to-relationship and outer-entity-to-relationship connections

Correctly notating cardinality and participation constraints is crucial for aggregation diagrams. These constraints apply at multiple levels and must be clearly distinguished.

Inner Relationship Cardinality:

The inner relationship uses standard cardinality notation:

Place the cardinality notation on the lines connecting entities to the inner relationship diamond.

Outer Relationship Cardinality:

The outer relationship also requires cardinality notation. This cardinality describes:

• How many aggregated relationship instances can one outer entity relate to?
• How many outer entities can relate to one aggregated relationship instance?

The notation is placed on:

1. The line from the aggregation box to the outer relationship diamond
2. The line from the outer relationship diamond to the outer entity

Example cardinality reading:

Aggregation Box ──N── ◇ SPONSORS ──M── MANAGER

This indicates M:N cardinality: a manager can sponsor many work assignments, and a work assignment can have many manager sponsors.

Participation Constraints:

Participation is typically shown using line styles:

Applying participation to aggregation:

Total participation of aggregation in outer relationship:

• Every work assignment must be sponsored
• Draw double line from aggregation box to SPONSORS diamond

Partial participation of aggregation in outer relationship:

• Work assignments may or may not be sponsored
• Draw single line from aggregation box to SPONSORS diamond

Total participation of outer entity:

• Every manager must sponsor at least one work assignment
• Draw double line from MANAGER to SPONSORS diamond

Partial participation of outer entity:

• Managers may or may not have sponsorships
• Draw single line from MANAGER to SPONSORS diamond

Combined notation example:

┌───────────────────────────┐
│  EMPLOYEE ── WORKS_ON ── PROJECT  │ ══════ ◇ SPONSORS ────── MANAGER
└───────────────────────────┘

(Double line from aggregation = Total participation: every assignment needs a sponsor)
(Single line to MANAGER = Partial participation: not all managers have sponsorships)

Different ER modeling methodologies and tools represent aggregation with varying notation styles. Understanding these variations helps you work across different contexts and with different teams.

Chen Notation (Original ER):

The classic Chen notation (and its extensions) uses:

• Rectangles for entities
• Diamonds for relationships
• Ovals for attributes
• A bounding rectangle for aggregation

The aggregation rectangle is typically a solid line, same weight as entity rectangles. Some variations use a dashed or dotted rectangle to distinguish the aggregation box from entity rectangles.

Enhanced ER (EER) Notation:

Textbooks like Elmasri & Navathe use a solid rectangle around the aggregated elements. The rectangle may be labeled (e.g., with the aggregation name in italics above the box) or left unlabeled when the aggregation intent is clear from context.

IDEF1X Notation:

IDEF1X (Integration Definition for Information Modeling) uses different visual conventions. Aggregation-like concepts may be represented through relationship cardinality and entity types rather than explicit bounding boxes. IDEF1X focuses more on the relational mapping and less on conceptual abstraction.

Crow's Foot (IE) Notation:

Crow's Foot notation (used in tools like ER/Studio, MySQL Workbench) doesn't have a standard aggregation symbol. Workarounds include:

• Creating an explicit 'intersection entity' for the inner relationship, then relating the outer entity to it
• Using notes or annotations to indicate aggregation semantics
• Some tools provide extension mechanisms for aggregation

UML Class Diagrams (Association Classes):

UML represents a similar concept through association classes. An association class is a relationship (association) that has class-like properties and can participate in other associations.

Notation:

• A dashed line connects the association (line between classes) to a class box
• The class box represents the association class
• The association class can then have associations with other classes

While not identical to ER aggregation, association classes serve a similar purpose: treating associations as first-class elements.

Object-Role Modeling (ORM):

ORM uses the concept of objectification (also called nesting). A relationship is objectified by drawing a rounded rectangle around the relationship's fact type. The objectified fact can then participate in other fact types.

Barker Notation:

Barker notation (used in Oracle Designer and other tools) represents relationships differently and doesn't have explicit aggregation notation. Similar effects may be achieved through careful entity and relationship design.

Being able to quickly and accurately read aggregation diagrams is as important as creating them. Here's a systematic approach to interpreting aggregation notation:

Step 1: Identify the Aggregation Box

Look for a bounding rectangle that encloses multiple elements. This box indicates aggregation. Note:

• What entities are inside the box?
• What relationship diamond is inside the box?
• Is the box labeled with an aggregation name?

Step 2: Understand the Inner Relationship

Analyze the structure inside the box:

• What entities participate in the inner relationship?
• What is the cardinality (1:1, 1:N, M:N)?
• What are the participation constraints?
• What attributes belong to the inner relationship?

Example reading: "EMPLOYEE works on PROJECT with M:N cardinality. The relationship has attributes start_date and hours_per_week."

Step 3: Identify the Outer Entity

Look outside the aggregation box:

• What entity connects to the aggregation box through a relationship?
• What are its attributes?
• This is the entity that relates to the aggregated relationship

Step 4: Understand the Outer Relationship

Analyze how the aggregation connects to the outer entity:

• What is the outer relationship name?
• What is the cardinality between the aggregation and outer entity?
• What are the participation constraints?
• What attributes belong to the outer relationship?

Example reading: "MANAGER sponsors the aggregated work assignment with M:N cardinality. Sponsorship has attributes allocated_budget and priority."

Step 5: Formulate the Complete Semantics

Combine your understanding into a complete semantic statement:

Example: "MANAGER sponsors (EMPLOYEE works_on PROJECT). A manager can sponsor multiple work assignments, and each work assignment can have multiple sponsors. Sponsorship includes budget allocation and priority."

Practice Exercise:

Given a diagram description:

┌──────────────────────────────────────────┐
│  STUDENT ── ENROLLS_IN ── COURSE  (M:N)  │ ── FUNDED_BY ── SCHOLARSHIP (M:N)
└──────────────────────────────────────────┘

ENROLLS_IN attributes: semester, grade
FUNDED_BY attributes: amount, award_date

Reading:

• STUDENT enrolls in COURSE (inner relationship, M:N)
• Enrollment has semester and grade
• The aggregated enrollment is funded by SCHOLARSHIP (outer relationship)
• Funding has amount and award_date
• Complete statement: "SCHOLARSHIP funds specific (STUDENT enrolls_in COURSE) enrollments with an amount and award_date."

Real-world data models sometimes require more complex aggregation structures. Understanding notation for these scenarios prepares you for advanced modeling challenges.

Scenario 1: Multiple Outer Entities

The same aggregated relationship may participate in relationships with multiple outer entities:

                                        ┌──────────────┐
                               AUDITS   │   AUDITOR    │
                              ┌─────────┴──────────────┘
┌─────────────────────────────┤
│  EMPLOYEE ── WORKS_ON ── PROJECT  │
└─────────────────────────────┤
                              └─────────┬──────────────┐
                              SPONSORS  │   MANAGER    │
                                        └──────────────┘

Both AUDITOR and MANAGER relate to the same aggregated work assignment, through different outer relationships (AUDITS and SPONSORS). Each outer relationship has its own attributes and cardinality.

Scenario 2: Aggregation Within Aggregation (Nested Aggregation)

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

┌─────────────────────────────────────────────────────────────────┐
│ ┌──────────────────────────────────┐                            │
│ │  EMPLOYEE ── WORKS_ON ── PROJECT │ ── SPONSORS ── MANAGER    │
│ └──────────────────────────────────┘                            │
└───────────────────────────────────────┬─────────────────────────┘
                                        │
                                    APPROVED_BY
                                        │
                                ┌───────────────┐
                                │    DIRECTOR   │
                                └───────────────┘

In this (admittedly complex) scenario:

• EMPLOYEE works on PROJECT (first inner relationship)
• MANAGER sponsors that work assignment (first aggregation + outer relationship)
• The sponsorship is approved by DIRECTOR (second aggregation + outer relationship)

This creates three levels. While structurally possible, such deep nesting is rarely needed and should be used with caution.

Scenario 3: Aggregation of Ternary Relationships

A ternary (or higher-degree) relationship can also be aggregated:

┌──────────────────────────────────────────────────────┐
│                                                      │
│   SUPPLIER ──┐                                       │
│              │                                       │
│              ◇── SUPPLIES ────── PROJECT             │
│              │                                       │
│   PART     ──┘                                       │
│                                                      │
└───────────────────────────────┬──────────────────────┘
                                │
                        CONTRACTED_THROUGH
                                │
                        ┌───────────────┐
                        │   CONTRACT    │
                        └───────────────┘

The ternary relationship SUPPLIES (among SUPPLIER, PART, and PROJECT) is aggregated, and CONTRACT relates to specific supply arrangements.

Scenario 4: Multiple Aggregations in Same Diagram

Large data models may contain multiple, independent aggregation constructs:

┌─────────────────────────────┐                    ┌─────────────────────────────┐
│  EMPLOYEE ── WORKS_ON ── PROJECT  │──SPONSORS──MANAGER    │  STUDENT ── ENROLLS_IN ── COURSE  │──FUNDED_BY──SCHOLARSHIP
└─────────────────────────────┘                    └─────────────────────────────┘

Each aggregation is independent and should be visually separated in the diagram layout. Use consistent notation across all aggregations for clarity.

Notation Guidelines for Complex Scenarios:

• Maintain consistent bounding box style throughout
• Use adequate spacing to prevent visual clutter
• Consider using color or shading to distinguish nested levels
• Label aggregations explicitly when multiple exist
• Document complex structures with accompanying text explanations

Creating professional, clear, and maintainable aggregation diagrams requires attention to detail and adherence to best practices.

Practice 1: Consistent Notation Throughout

Select a notation style and apply it consistently:

• Use the same line weights for all entity boxes
• Use consistent cardinality notation (don't mix Chen labels with Crow's Foot symbols)
• Use consistent attribute representation (ovals vs. listed format)
• Use the same bounding box style for all aggregations

Practice 2: Adequate Spacing and Margins

Visual clarity depends on proper spacing:

• Leave margin between inner elements and the bounding box
• Ensure the outer entity and relationship are clearly separated from the box
• Prevent overlapping lines and elements
• Use consistent spacing across the entire diagram

Practice 3: Meaningful Labels

Labels communicate semantics:

• Name relationships with verbs or verb phrases (WORKS_ON, SPONSORS, FUNDS)
• Name entities with nouns (EMPLOYEE, PROJECT, MANAGER)
• Consider labeling the aggregation itself if it has a domain-meaningful name
• Avoid redundant or unclear labels

Practice 4: Visual Hierarchy

The diagram layout should guide the reader's eye:

• Position the aggregation centrally when it's the focus
• Flow typically left-to-right or top-to-bottom
• Group related elements together
• The most important relationships should be visually prominent

Practice 5: Attribute Management

Attributes can clutter diagrams if not managed:

• Consider listing key attributes only in the diagram
• Use separate attribute tables or documentation for complete attribute lists
• Distinguish between entity attributes and relationship attributes
• Don't forget attributes of the outer relationship

Practice 6: Documentation and Legends

Complex diagrams benefit from supporting documentation:

• Include a notation legend explaining symbols used
• Provide text descriptions of semantic intent
• Document constraints not expressible visually
• Include version information for diagram evolution tracking

Practice 7: Review and Validation

Always validate your diagrams:

• Review with domain experts to confirm semantics
• Verify cardinality and participation constraints
• Test by tracing sample data through the structure
• Check that the diagram supports all expected queries

We've comprehensively covered the notation for representing aggregation in ER diagrams. Let's consolidate the key takeaways:

What's next:

Now that we can read and create aggregation diagrams, the next page explores use cases—the specific real-world scenarios where aggregation is the appropriate modeling choice. We'll examine domain-specific examples across multiple industries.