Skip to content
Tutorials Center
My learning

Topics

Lesson 11 of 79 ยท Data Models and ER Modelling

Weak Entities, Specialization and Aggregation

Go beyond basic ER diagrams: weak entities, identifying relationships, ISA hierarchies with disjoint and total constraints, ternary relationships and aggregation.

The basic entity-relationship model gives you entities, attributes and binary relationships. That is enough for many small designs, but real requirements quickly run into things the basics cannot express. A hotel room has no meaning without its hotel. A manager is an employee who has extra attributes. Three parties (a supplier, a part and a project) take part in one single fact. A relationship itself needs to take part in another relationship.

This lesson covers the standard extensions that handle these cases: weak entities, specialization and generalization (the ISA hierarchy), ternary relationships and aggregation. The diagrams here are read-only pictures of the design; the live examples show how the same ideas look as real tables, which you will formalize in Mapping ER Diagrams to Tables.

Four extensions to the ER model

Weak entity Cannot be identified without its owner.
ISA hierarchy Subtypes inherit from a supertype.
Ternary relationship One fact that involves three entities.
Aggregation Treat a relationship as an entity.

Each one fixes a modelling problem the basic model cannot express.

1 / 2

Weak entities

A strong entity has its own key: a Student has a student number that identifies it anywhere in the database. A weak entity does not. It only has a partial key (also called a discriminator) that is unique among the weak entities belonging to the same owner.

Think of rooms in a hotel. Room "101" exists in many hotels, so the number alone identifies nothing. The pair (hotel, room number) does. The hotel is the identifying (owner) entity, the relationship between them is the identifying relationship, and Room is the weak entity.

erDiagram
    HOTEL ||--|{ ROOM : "has (identifying)"
    HOTEL {
        int hotel_id PK
        string name
    }
    ROOM {
        string room_number "partial key"
        int floor
    }

Room is weak: its full identity is the owner's key plus its own partial key.

Three rules define a weak entity:

  • It has total participation in the identifying relationship: every room belongs to some hotel.
  • The relationship is one-to-many from owner to weak entity (one hotel, many rooms).
  • Its identity is owner key + partial key. If the hotel is deleted, its rooms have no meaning left, so they usually disappear with it.

In classical ER notation, a weak entity is drawn as a double rectangle, the identifying relationship as a double diamond, and the partial key with a dashed underline. Mermaid cannot draw those shapes, so the diagram above shows the idea with labels.

In tables, the weak entity's primary key is a composite key that includes the owner's key as a foreign key. Here is a hotel schema you can run right now:

SQL A weak entity as a composite key

Press Run (or Ctrl + Enter) to see the result here.

Try adding a second (1, '101', 5) row: it is rejected, because the composite key is already taken inside hotel 1.

A weak entity is not just an entity with a foreign key. A normal Order has a foreign key to Customer but its own order number. A weak entity's identity genuinely depends on the owner. When you are unsure, ask: "can I identify this thing without saying what it belongs to?" If not, it is weak.

Specialization and generalization

Often a group of entities share most attributes, but some members have extras. Every Employee has a name and a salary, but only some are Engineers with a programming language, and only some are Managers with a budget.

  • Specialization works top-down: start with Employee and carve out subtypes.
  • Generalization works bottom-up: notice that Car and Truck share attributes and pull them into a Vehicle supertype.

Both produce the same structure, an ISA hierarchy ("is a"): an engineer is an employee. Subtypes inherit all attributes of the supertype, including its key, and the relationships it takes part in.

flowchart TD
    E["Employee<br/>emp_id, name, salary"]
    ISA(("ISA"))
    ENG["Engineer<br/>language"]
    MGR["Manager<br/>budget"]
    E --- ISA
    ISA --- ENG
    ISA --- MGR

A triangle (here a circle) labelled ISA connects the supertype to its subtypes.

Two constraints on every hierarchy

Every ISA hierarchy answers two independent questions, giving four combinations.

Question Option Meaning
Can an entity belong to more than one subtype? Disjoint At most one subtype (an employee is an engineer or a manager).
Overlapping Several allowed (a person can be both a student and an employee).
Must every supertype entity belong to a subtype? Total (full) Yes, every vehicle is a car or a truck.
Partial No, some employees are neither.

These constraints are decisions about the business, not facts you can look up. They matter later, because the mapping strategy you pick depends on them, and because the tables must enforce them (or the application must).

SQL A hierarchy as tables, with a disjoint check

Press Run (or Ctrl + Enter) to see the result here.

The kind column plus the shared key is one common way to keep subtypes disjoint. It does not stop someone inserting emp 1 into manager too; that stricter check needs a trigger or a composite foreign key, topics for Enforcing Integrity.

Ternary relationships

Most relationships connect two entities. Sometimes a single fact really involves three. Suppose a supplier supplies a part to a project: the fact "Acme supplies bolts to the bridge project" is about all three at once.

flowchart LR
    S["Supplier"] --- R{"Supplies"}
    P["Part"] --- R
    J["Project"] --- R

One diamond joined to three entities: a ternary relationship.

You cannot always replace this with three binary relationships (Supplier-Part, Part-Project, Supplier-Project). Those would record that Acme supplies bolts, that bolts are used in the bridge and that Acme works on the bridge, but not that Acme supplies bolts to the bridge. Combining binary facts can invent combinations that were never true.

SQL A ternary relationship keeps the full fact

Press Run (or Ctrl + Enter) to see the result here.

The first query returns 0; the second returns 1. The three binary tables, joined back together, claim a fact that was never recorded. That is the reason to keep the ternary relationship as one unit. (The same issue returns in multivalued dependencies and 4NF.)

The opposite mistake exists too: if a "ternary" relationship really decomposes without losing information, three binary relationships are simpler. Test it by asking whether the three binary facts can ever be true while the combined fact is false.

Aggregation

ER diagrams do not allow a relationship to take part directly in another relationship. But sometimes you need exactly that. Suppose employees work on projects (a relationship), and a monitor then reviews each such assignment. The reviewer is not related to the employee or the project alone, but to the assignment.

Aggregation solves this: you draw a box around the Employee works-on Project relationship and treat the whole box as if it were an entity. The new relationship monitors then connects Monitor to that box.

flowchart LR
    subgraph AGG["Aggregated: Works_on"]
        E["Employee"] --- W{"Works on"}
        P["Project"] --- W
    end
    M["Monitor"] --- R{"Monitors"}
    W --- R

The dashed box becomes a single unit that other relationships can connect to.

In tables, an aggregated relationship is simply the table of the lower relationship. Its key becomes the foreign key in the new relationship's table:

SQL Aggregation: monitoring an assignment

Press Run (or Ctrl + Enter) to see the result here.

Compare this with a ternary relationship. A ternary Employee-Project-Monitor says every combination of the three is a single fact. Aggregation says the assignment exists first, and monitoring is an extra fact about it: you cannot monitor an assignment that does not exist. That is the difference to ask about when choosing.

Recursive relationships

So far every relationship has connected two different entity sets. A recursive (or unary) relationship connects an entity set to itself. An employee manages other employees, a course has other courses as prerequisites, a person is a parent of other people. The same entity set plays the relationship twice, so each end needs a role name to tell the two apart: in manages, one end is the manager and the other is the report.

erDiagram
    EMPLOYEE ||--o{ EMPLOYEE : "manages (manager / report)"
    COURSE }o--o{ COURSE : "requires (course / prerequisite)"

Both relationships loop back to the same box. The first is one-to-many, the second many-to-many.

The cardinality is read in each direction, as with any binary relationship:

  • One-to-many (employee-manager). Each employee has at most one manager, and a manager has any number of reports. The top person has no manager, so participation on the "report" side is partial.
  • Many-to-many (prerequisites). A course may require several courses, and a course may be required by several others.
  • One-to-one (spouse, or "successor"). Each entity has at most one partner, and the relationship points back at the same set.

Mapping a recursive relationship

The mapping rules are the same as for any relationship; only the role names matter, because two columns now point at the same table.

  • One-to-many: add a foreign key to the table itself, named after the role (manager_id), nullable when participation is partial. No extra table is needed.
  • Many-to-many: create a junction table with two foreign keys to the same table, named by role (course_id, prereq_id).
SQL Employee-manager and course prerequisites

Press Run (or Ctrl + Enter) to see the result here.

The first query lists Ada with (nobody) because of the LEFT JOIN. The second follows the chain down the tree: Ada at depth 0, Ben and Cleo at depth 1, the other three at depth 2. The last query joins course twice under different aliases, which is the SQL version of role names.

Minimizing the diagram's tables

A direct mapping gives one table per entity set and one per relationship set. Several of those relationship tables are redundant. Minimization means merging them into neighbouring tables, as long as no information is lost. The decision depends on the structural constraints, the cardinality ratio and the participation of each side. Written as a pair (min, max) on each edge, a structural constraint like (1,1) says "exactly one" and (0,N) says "any number, possibly none".

Relationship Constraints Tables after minimizing
One-to-many The many side has (1,1) or (0,1) Foreign key in the many-side table; no relationship table
One-to-one, one side total Total side is (1,1) Foreign key in the total side, or merge the entities
One-to-one, both sides total Both (1,1) Merge both entities into one table
Many-to-many Both (0,N) or (1,N) A relationship table is unavoidable

Two cautions apply. First, merging a one-to-one pair whose sides are only partially participating creates many NULL columns, so a foreign key with a UNIQUE constraint is the better choice. Second, never merge a many-to-many relationship, because that duplicates rows and brings back the anomalies from Anomalies and Redundancy.

SQL Merging a total one-to-one relationship

Press Run (or Ctrl + Enter) to see the result here.

Three tables become one, and every join between them disappears. The price is that the two entities can no longer be created independently; that is exactly what "total on both sides" promised. If a passport could exist without a person, or a person without a passport, the merge would be wrong.

Check your understanding

Quick check ยท 6 questions

of 6 answered You scored / 6 ยท perfect!

  1. 1. What makes an entity weak?

    A weak entity has only a partial key. Its full identity is the owner's key combined with the partial key, such as (hotel, room number).
  2. 2. A university says every person in its database is a student or an employee, and some are both. How is this ISA hierarchy classified?

    Every person is at least one subtype, so it is total. Some belong to both, so it is overlapping.
  3. 3. Why can a ternary relationship Supplier-Part-Project not always be replaced by three binary relationships?

    Knowing the three pair facts does not tell you which triples hold. Rebuilding from pairs can invent triples, so the combined fact must be stored as one.
  4. 4. What does aggregation allow you to do in an ER diagram?

    Aggregation boxes a relationship and treats it as a single unit that other relationships can connect to, like monitors reviewing assignments.
  5. 5. How is a one-to-many recursive relationship such as employee-manager usually mapped?

    Each employee has at most one manager, so one column is enough. It is nullable because the top person has no manager.
  6. 6. Which relationship always needs its own table, even after minimization?

    With a many-to-many relationship, neither side can hold a single foreign key, so a junction table is unavoidable.

Summary

  • A weak entity has only a partial key; its identity is the owner's key plus the partial key, and it depends on the owner through an identifying relationship.
  • Specialization (top-down) and generalization (bottom-up) build ISA hierarchies where subtypes inherit the supertype's attributes and key.
  • Every hierarchy is disjoint or overlapping, and total or partial; these business rules drive the table mapping.
  • A ternary relationship records one fact about three entities and cannot always be split into binary ones without inventing facts.
  • Aggregation treats a relationship as an entity so another relationship can attach to it.
  • A recursive relationship connects an entity set to itself; role names tell the two ends apart, and it maps to a self-referencing foreign key (one-to-many) or a junction table (many-to-many).
  • Minimization merges relationship tables into entity tables when the structural constraints allow it; many-to-many relationships always keep their own table.

Next: Mapping ER Diagrams to Tables, where all these constructs get systematic rules.