Weak entities, specialisation and aggregation

Entities, attributes and relationships describe most designs, but three situations need more. A weak entity is one that cannot be identified on its own: a room numbered within its hostel is identified by its hostel together with its room number, its partial key, through an identifying relationship. Specialisation and generalisation describe entities that come in kinds with extra attributes, such as cars and trucks among vehicles, with constraints that say whether the kinds may overlap and whether every entity must belong to one. Aggregation treats a relationship as an entity, so that it can take part in another relationship, as when a manager monitors an employee’s assignment to a project. A union type, also called a category, describes “the owner is a person or a company”. The ER model with these additions is known as the extended or enhanced ER model, EER. This lesson shows when each one is the right tool, how it is drawn and what it guarantees.

Weak entities and partial keys

The hostel brief of the first lesson in this module has rooms numbered within each hostel: Kaveri has a room 101, and so has Ganga. The room number is unique only among the rooms of one hostel, so on its own it cannot identify a room. ROOM is a weak entity set, HOSTEL is its owner (or identifying entity set), and the relationship between them is the identifying relationship. A room is identified by the owner’s key, the hostel’s name, together with the room’s partial key (also called a discriminator), its room number.

Chen notation marks each part. The weak entity set has a double rectangle and the identifying relationship a double diamond. The partial key is underlined with a dashed line, the owner’s key with a solid one, and the weak side of the identifying relationship has a double line. Three rules follow from what a weak entity is:

• The identifying relationship is 1:N from the owner to the weak entity. A hostel has many rooms; a room has exactly one hostel, since a second owner would give it two identities.
• The weak entity’s participation is total. A room without a hostel would have nothing to be identified by.
• The weak entity goes when its owner goes. Its existence depends on the owner, so deleting a hostel deletes its rooms, or must be refused while rooms remain.

Here is what that means once the design becomes tables, in a preview of the mapping lesson:

-- ROOM is a weak entity: its room number is unique only within its hostel, its owner.
PRAGMA foreign_keys = ON;  -- SQLite checks foreign keys only when a connection asks for it

CREATE TABLE hostel (
  hostel_name TEXT PRIMARY KEY,
  warden      TEXT NOT NULL
);
CREATE TABLE room (
  hostel_name TEXT NOT NULL REFERENCES hostel (hostel_name) ON DELETE CASCADE,  -- the owner's key
  room_no     TEXT NOT NULL,                                                   -- the partial key
  beds        INTEGER NOT NULL CHECK (beds > 0),
  PRIMARY KEY (hostel_name, room_no)
);
INSERT INTO hostel VALUES ('Kaveri', 'R. Menon'), ('Ganga', 'S. Das');
INSERT INTO room VALUES ('Kaveri', '101', 3), ('Kaveri', '102', 2), ('Ganga', '101', 4);

-- Room 101 exists twice, once in each hostel: the partial key repeats across owners.
SELECT room_no, hostel_name, beds FROM room ORDER BY room_no, hostel_name;

-- Within one owner it may not repeat, and a room cannot exist without its hostel.
INSERT INTO room VALUES ('Kaveri', '101', 2);
INSERT INTO room VALUES ('Narmada', '001', 2);

-- The weak entities go with their owner.
DELETE FROM hostel WHERE hostel_name = 'Kaveri';
SELECT hostel_name, count(*) AS rooms FROM room GROUP BY hostel_name;

┌─────────┬─────────────┬──────┐
│ room_no │ hostel_name │ beds │
├─────────┼─────────────┼──────┤
│ 101     │ Ganga       │ 4    │
│ 101     │ Kaveri      │ 3    │
│ 102     │ Kaveri      │ 2    │
└─────────┴─────────────┴──────┘
┌─────────────┬───────┐
│ hostel_name │ rooms │
├─────────────┼───────┤
│ Ganga       │ 1     │
└─────────────┴───────┘

Runtime error near line 21: UNIQUE constraint failed: room.hostel_name, room.room_no
Runtime error near line 22: FOREIGN KEY constraint failed

The key of room is the pair (hostel_name, room_no), so room 101 can exist in both hostels but not twice in Kaveri, and a room for a hostel that does not exist is refused. Deleting Kaveri removed its two rooms with it (ON DELETE CASCADE), which is the existence dependency written as a rule. SQLite enforces foreign keys only after PRAGMA foreign_keys = ON on each connection, a setting that is easy to forget.

Not every entity that depends on another is weak. An order line could get its own identifier and still be deleted with its order; whether to model it as weak is a design decision. Choose a weak entity when the partial key is how people already name the thing (room 101 of Kaveri, line 3 of an order, the second dependant of an employee), and a strong entity with its own key when other tables must refer to it often, or when it can move to another owner.

Specialisation and generalisation

Some entity sets have kinds. Every car and every truck in a fleet is a vehicle with a registration number and an owner, but only cars have a number of seats and only trucks a payload. The design then has a superclass, VEHICLE, and subclasses, CAR and TRUCK: each car is a vehicle. A subclass inherits every attribute and relationship of its superclass and adds its own, so a car has a registration number, an owner and a number of seats.

The two words describe the direction of the design work. Specialisation goes top-down: start from VEHICLE and split it by a property that matters, such as the kind of vehicle. Generalisation goes bottom-up: start from CAR and TRUCK, notice what they share and introduce VEHICLE to hold it. Smith and Smith’s 1977 paper on database abstractions is named after this idea and the one in the next section, generalisation and aggregation, and Chen’s paper had already allowed entity sets to overlap, with every male person also a person. The finished diagram looks the same whichever way you got there.

Two independent constraints describe a specialisation:

• Disjoint or overlapping. In a disjoint specialisation an entity belongs to at most one subclass: a vehicle is a car or a truck, not both. In an overlapping one it may belong to several: a person can be a student and an employee at once. The diagram marks the circle with d or o.
• Total or partial. In a total specialisation every superclass entity belongs to some subclass: every vehicle of this fleet is a car or a truck. In a partial one some belong to none: a visitor is a person who is neither a student nor an employee. A double line from the superclass to the circle marks total.

All four combinations occur: vehicles in a fleet of cars and trucks (disjoint, total), people on a campus (overlapping, partial), employees who are salaried or paid by the hour (disjoint, total), and members of a sports club who may be players, coaches or both, or just supporters (overlapping, partial). When the subclass is decided by an attribute of the superclass, such as a vehicle type, the specialisation is attribute-defined, and the attribute’s value says where each entity belongs. UML writes the same two constraints on a generalisation set as {disjoint, complete}, {overlapping, incomplete} and so on.

A subclass can also have two superclasses. A teaching assistant is both a student and an employee, and a TEACHING ASSISTANT subclass under both inherits from each; this needs an overlapping specialisation, or the two memberships could not coexist.

Aggregation

ASSIGNED relates EMPLOYEE to PROJECT, with the hours as an attribute. Managers now monitor some of those assignments: not an employee in general, nor a project in general, but one employee’s work on one project. The plain ER model lets relationships join entity sets only, so there is nothing for MONITORS to connect to.

A ternary relationship between MANAGER, EMPLOYEE and PROJECT looks like a way out, but it says less than the requirement. Nothing would tie a monitoring to an existing assignment, so a manager could monitor work that nobody was assigned to, and the hours would have no clear home. Aggregation solves this by drawing a box around ASSIGNED and its two entity sets and treating the whole as an entity that takes part in MONITORS: the relationship becomes an object of a higher level, the abstraction that Smith and Smith’s paper calls aggregation.

In tables, aggregation means that the relationship’s key becomes the target of a foreign key:

-- Aggregation: MONITORS is about an assignment, the pair of an employee and a project, not about either alone.
PRAGMA foreign_keys = ON;

CREATE TABLE employee (emp_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE project  (project_id INTEGER PRIMARY KEY, title TEXT NOT NULL);
CREATE TABLE manager  (manager_id INTEGER PRIMARY KEY, name TEXT NOT NULL);

-- The relationship ASSIGNED, with its own attribute.
CREATE TABLE assigned (
  emp_id     INTEGER NOT NULL REFERENCES employee (emp_id),
  project_id INTEGER NOT NULL REFERENCES project (project_id),
  hours      INTEGER NOT NULL CHECK (hours > 0),
  PRIMARY KEY (emp_id, project_id)
);
-- MONITORS treats an assignment as an entity: its foreign key points at ASSIGNED's key.
CREATE TABLE monitors (
  emp_id     INTEGER NOT NULL,
  project_id INTEGER NOT NULL,
  manager_id INTEGER NOT NULL REFERENCES manager (manager_id),
  PRIMARY KEY (emp_id, project_id),                                    -- at most one manager per assignment
  FOREIGN KEY (emp_id, project_id) REFERENCES assigned (emp_id, project_id)
);

INSERT INTO employee VALUES (1, 'Asha'), (2, 'Bilal');
INSERT INTO project  VALUES (10, 'Library app'), (20, 'Hostel portal');
INSERT INTO manager  VALUES (100, 'Kiran');
INSERT INTO assigned VALUES (1, 10, 12), (2, 10, 8), (2, 20, 20);
INSERT INTO monitors VALUES (1, 10, 100), (2, 20, 100);

-- Asha works on the library app only, so nobody can monitor her work on the hostel portal.
INSERT INTO monitors VALUES (1, 20, 100);

SELECT e.name, p.title, coalesce(m.name, '(not monitored)') AS monitored_by
FROM assigned AS a
JOIN employee AS e ON e.emp_id = a.emp_id
JOIN project  AS p ON p.project_id = a.project_id
LEFT JOIN monitors AS mo ON mo.emp_id = a.emp_id AND mo.project_id = a.project_id
LEFT JOIN manager  AS m  ON m.manager_id = mo.manager_id
ORDER BY e.name, p.title;

┌───────┬───────────────┬─────────────────┐
│ name  │     title     │  monitored_by   │
├───────┼───────────────┼─────────────────┤
│ Asha  │ Library app   │ Kiran           │
│ Bilal │ Hostel portal │ Kiran           │
│ Bilal │ Library app   │ (not monitored) │
└───────┴───────────────┴─────────────────┘

Runtime error near line 31: FOREIGN KEY constraint failed

The foreign key from monitors to assigned refused a monitoring of Asha’s work on the hostel portal, because she was never assigned to it. Monitoring is partial: Bilal’s work on the library app is assigned but nobody monitors it, and the LEFT JOIN shows it.

Union types (categories)

A vehicle’s owner can be a person or a company. Persons and companies have different keys and different attributes, and there is no sensible superclass of both in the design. A union type, or category, describes exactly this: OWNER is a subclass of the union of PERSON and COMPANY, and each owner is one person or one company. Elmasri, Weeldreyer and Hevner introduced categories as an extension of the ER model in their 1985 paper.

A category differs from generalisation in where the entities come from. In a generalisation every subclass entity is also an entity of the one superclass, and they share its key. In a category the entities come from different entity sets with different keys, and a category can be partial (not every person is an owner) or total (every person and every company is an owner, drawn with a double line). When the design becomes tables, a category usually gets a key of its own, which the person and company rows refer to.

Choosing the construct

Each construct answers a different wording in the brief:

• Weak entity. The brief numbers or names a thing within another thing. The key is the owner’s key plus the partial key, and the rule it guarantees is that no weak entity exists without its owner. The common mistake is to make the partial key alone the key.
• Specialisation. Some entities of a set have extra attributes or relationships. Every subclass shares the superclass key, and the diagram states disjoint or overlapping, total or partial. The common mistake is one subclass for every value of an attribute that nothing else depends on.
• Aggregation. Something is recorded about a relationship itself. The key is the key of the aggregated relationship, and only relationships that exist can take part. The common mistake is a ternary relationship that loses the link to the relationship it was about.
• Union type. One role can be filled by entities of different sets. The category gets a key of its own, and each of its entities is exactly one of the united entities. The common mistake is a superclass that forces unrelated entity sets to share a key.

Interview questions

What makes an entity weak, and what is its partial key? A weak entity set has no key of its own: its entities are identified only together with an owner entity, through an identifying relationship. The partial key, or discriminator, is the attribute that tells apart the weak entities of one owner, such as a room number within a hostel, so a weak entity’s full key is the owner’s key plus the partial key. The identifying relationship is 1:N from the owner, the weak entity’s participation in it is total, and deleting the owner deletes its weak entities or is refused. In tables, the weak entity’s primary key is the owner’s key and the partial key together, with a foreign key to the owner.

Key takeaways

• A weak entity is identified by its owner’s key plus its own partial key; the identifying relationship is 1:N with total participation of the weak side, and the weak entities go with their owner.
• A subclass inherits everything from its superclass; specialisation and generalisation are two ways to reach the same hierarchy.
• Disjoint or overlapping and total or partial are independent constraints; read both from the brief.
• Aggregation lets a relationship take part in another relationship; in tables it becomes a foreign key to the relationship’s key.
• A union type, or category, unites entity sets with different keys, such as persons and companies as owners.
• Choose the construct from the wording of the requirement, and state the notation you draw it in.