14. The Enhanced ER Model

Lecture 13 ended on an unresolved problem: SalesPersonnel and Manager are clearly special kinds of Staff, sharing every attribute Staff has plus a few of their own, but plain ER modeling has no vocabulary for "is a special case of." The Enhanced ER (EER) Model adds exactly that vocabulary — four new modeling concepts layered on top of everything from Lectures 12–13: specialization, generalization, aggregation, and categorization. None of these are exotic — every object-oriented programmer already thinks in class hierarchies — but they need precise diagram notation and precise mapping rules before a DBMS can use them, which is this lecture's job, continuing straight into Lecture 16.

In This Lecture

• Why plain ER modeling breaks down for domains with natural sub-types
• Specialization: the top-down direction of reasoning
• Generalization: the bottom-up direction of reasoning
• Superclasses, subclasses, and inheritance of attributes and relationships
• The four specialization/generalization constraints: disjoint vs. overlapping, total vs. partial — and what each of the four combinations actually means for real data
• Aggregation: treating a relationship itself as a participant in another relationship
• Composition: aggregation's stronger, ownership-implying cousin
• Categorization (union types): a subclass drawn from the union of several unrelated superclasses

Why Plain ER Isn't Enough

Plain ER modeling (Lectures 12–13) is built entirely from three ingredients: entities, relationships, and attributes, all sitting at the same conceptual "flat" level. That is genuinely sufficient for a great many domains. It breaks down specifically when a domain has natural sub-types that share a common core but each add their own extra structure — and forcing that shape into plain ER always produces one of two bad outcomes:

• Attribute overload: cram every subtype's attributes onto one entity type. Every SalesPersonnel-only attribute (salesArea, carAllowance) sits NULL on every Manager row and vice versa — a schema-level admission that the entity type is really hiding two (or more) different kinds of thing.
• Lost commonality: model SalesPersonnel and Manager as two entirely separate entity types. Now staffNo, name, position, and salary are duplicated in both, with no way to express "these are still fundamentally the same kind of thing" or to query "all staff, regardless of role" without a manual union.

The EER model's superclass/subclass machinery solves both problems at once: shared attributes and relationships live once, on the superclass, and each subclass adds only what's genuinely specific to it.

Specialization

Specialization is the top-down design process: start from one superclass already known to the model (Staff), and identify meaningful, distinct subgroupings within it based on some distinguishing characteristic (here, job role) — each subgrouping becomes a subclass.

Specialization of Staff: top-down, starting from the superclass

Staff (superclass)

• staffNo
• name
• position
• salary
• branchNo

SalesPersonnel (subclass)

• salesArea
• carAllowance

Manager (subclass)

• bonus
• mgmtLevel

The designer's reasoning runs: "I already have Staff. Within it, sales personnel and managers need extra, different attributes I have nowhere good to put — let me specialize Staff into subclasses that each carry only their own extra attributes."

Generalization

Generalization is the bottom-up mirror image: start from several already-known, separately modeled entity types that turn out to share a meaningful common core, and factor that shared core out into a new superclass.

Generalization: bottom-up, starting from the subclasses

Car

• regNo
• seats
• doors

Van

• regNo
• loadCapacityKg

Vehicle (generalized superclass)

• regNo
• make
• model
• year

Here the designer's reasoning runs the opposite direction: "I already separately modeled Car and Van, each with regNo, make, model, year duplicated across both — let me generalize the shared attributes upward into a new Vehicle superclass, leaving each subclass with only what's genuinely specific to it."

Same resulting diagram, opposite starting point

Specialization and generalization produce the identical final diagram shape — a superclass with subclasses beneath it. They differ only in which direction the designer reasoned in to get there: specialization starts from an existing superclass and splits it; generalization starts from existing separate entity types and merges their common core. Most textbooks (and this course) use "specialization/generalization" as a single combined topic for exactly this reason — the notation and constraints below apply identically regardless of which direction produced the diagram.

Superclasses, Subclasses, and Inheritance

A superclass is an entity type whose occurrences are grouped into one or more distinct, meaningful subgroupings — each subgrouping is a subclass. Every occurrence of a subclass is simultaneously an occurrence of its superclass (every Manager row is also, inescapably, a Staff row) — this is the same "is-a" relationship object-oriented programming calls inheritance, and it works the same way here:

• Attribute inheritance — a subclass automatically has every attribute its superclass has, without redeclaring them. Manager has staffNo, name, position, salary, branchNo and bonus, mgmtLevel — the first five inherited, the last two its own.
• Relationship inheritance — a subclass automatically participates in every relationship its superclass participates in. Because Staff Manages PropertyForRent is defined on Staff, both SalesPersonnel and Manager occurrences can participate in it too, with no separate relationship needing to be drawn for each subclass.
• A subclass may also have its own additional relationships that other subclasses (or the superclass in general) don't participate in — e.g., only Manager participates in Manager Approves Expenditure.

Constraints on Specialization/Generalization

Two independent yes/no questions fully describe how a superclass's subclasses relate to each other and to the whole superclass population. Every specialization in this course (and in Connolly & Begg) is one of exactly four combinations of these two constraints:

Disjoint vs. Overlapping — can one occurrence belong to more than one subclass at once?

• Disjoint (d) — an occurrence can belong to at most one subclass. A Staff member is either SalesPersonnel or Manager, never both.
• Overlapping (o) — an occurrence can belong to more than one subclass simultaneously. A Person can be both a Student and an Employee at the same time (a working student).

Total vs. Partial — must every occurrence of the superclass belong to at least one subclass?

• Total — every superclass occurrence must belong to at least one subclass; there is no such thing as a "plain" superclass occurrence belonging to none.
• Partial — some superclass occurrences may belong to no subclass at all, remaining just a plain, unspecialized instance of the superclass.

The four disjoint/overlapping × total/partial combinations

Disjoint, Total Vehicle → Car, Van, Truck. Every vehicle is exactly one of the three; none is left unclassified.

Disjoint, Partial Staff → SalesPersonnel, Manager. Neither overlaps the other, but plenty of staff hold neither role.

Overlapping, Total Person → Student, Employee. Every person in the system is at least one; some are both.

Overlapping, Partial Staff → CommitteeMember, Trainer. A staff member can hold both extra roles, one, or neither.

Worked example — Staff specialization is disjoint AND partial

Staff (superclass)

• staffNo
• name
• salary

d disjoint   partial — an ordinary assistant belongs to neither subclass below

SalesPersonnel

• salesArea
• carAllowance

Manager

• bonus

Getting disjoint/overlapping wrong is a real, silent design bug

Declaring Staff → SalesPersonnel, Manager disjoint when a real staff member is later promoted to manager while continuing to hold sales targets forces the database to represent them as one or the other, silently dropping a role that's still true in reality. Always check the constraint against the actual business rules, not against whichever combination happens to be simpler to model — Lecture 16 shows this constraint is not just documentation, it changes which mapping strategy even works.

Aggregation

Aggregation models a "has-a" / "part-of" relationship at the relationship level — it represents a situation where a relationship between two entity types must itself participate, as a whole, in a relationship with a third entity type. Plain ER can connect entities to entities, but it has no way to connect a relationship to an entity — aggregation is the EER construct that closes this gap.

Consider: Staff Manages PropertyForRent already exists as an ordinary relationship. Now suppose the agency introduces periodic reviews, where a Supervisor reviews specific management assignments — not staff members in general, and not properties in general, but the particular fact "this staff member manages this property." The thing being reviewed is the Manages relationship itself.

Aggregation: the Manages relationship, as a whole, participates in Reviews

Staff

• staffNo

Manages

1N

PropertyForRent

• propertyNo

the Manages relationship, aggregatedtreated as a single participant below

Reviews

N1

Supervisor

• supervisorNo

Without aggregation, Reviews would have to connect Supervisor directly to either Staff or PropertyForRent alone — but a review is genuinely about the pairing, not about either side individually, and a supervisor might review the same staff member's management of one property differently from another. Aggregation lets the model say exactly that.

Composition

Composition is aggregation's stronger cousin: a whole-part relationship where the part has no independent existence apart from the whole — if the whole is deleted, its parts are deleted with it, and (unlike ordinary aggregation) a part typically belongs to exactly one whole at a time, never shared across several.

Composition: a Room cannot exist independently of its Branch

Branch

• branchNo
• street
• city

Composed Of

1N

Room

• roomNo
• floorArea

	Aggregation	Composition
Existence dependency	The part can outlive the whole	The part cannot exist without the whole
Sharing	A part can belong to more than one whole	A part belongs to exactly one whole
Example	A Property advertisement can be reused across several magazine placements	A Room ceases to exist, organizationally, if its Branch is closed
Deleting the whole	Parts are typically left in place, or reassigned	Parts are deleted along with the whole (cascading)

Connolly & Begg treat aggregation as the primary EER construct; composition is the common UML-influenced refinement

Composition is not always given separate formal notation in every relational-modeling textbook — some treat it as simply "aggregation with a strong ownership constraint" rather than an entirely distinct symbol. This course draws the distinction explicitly because it changes a concrete design decision in Lecture 16: composition almost always implies ON DELETE CASCADE on the resulting foreign key, while plain aggregation does not.

Categorization (Union Types)

Every specialization/generalization so far had subclasses drawn from a single superclass. Categorization (also called a union type) is different: a subclass represents a subset of the union of two or more distinct, otherwise unrelated superclasses — an occurrence of the subclass comes from exactly one of several possible "parent" categories, and those parents don't otherwise share a common superclass of their own.

RegisteredOwner is a category — the union of three unrelated entity types

Person

• cnic
• name

Bank

• bankCode
• name

Company

• regNo
• name

U (union)a RegisteredOwner occurrence comes from exactly one of the three above

RegisteredOwner (category)

• ownerSince

A Vehicle's registered owner might be an individual Person, a leasing Bank, or a fleet Company — three genuinely unrelated entity types that share no natural common superclass the way SalesPersonnel and Manager share Staff. RegisteredOwner exists precisely to give Vehicle a single, uniform relationship to attach to ("Registered To"), no matter which of the three kinds of owner actually holds a given vehicle. This is different from ordinary specialization in exactly one respect worth remembering:

Specialization vs. categorization, in one sentence

Ordinary specialization/generalization has subclasses that all share one common superclass. Categorization has a subclass whose occurrences are drawn from the union of several different, unrelated superclasses — there is no single shared parent, only the category itself sitting above all of them.

Key Takeaways

• Specialization reasons top-down (superclass → subclasses); generalization reasons bottom-up (subclasses → superclass). Both produce the same diagram shape.
• A subclass inherits every attribute and every relationship of its superclass, and may add attributes and relationships of its own.
• Two independent constraints classify every specialization: disjoint vs. overlapping (can an occurrence belong to more than one subclass?) and total vs. partial (must every occurrence belong to some subclass?) — four combinations in total, and getting the wrong one is a real design bug, not a cosmetic choice.
• Aggregation lets a relationship itself act as a single participant in another relationship — needed whenever a fact is genuinely about a pairing, not about either entity alone.
• Composition is aggregation with a strong existence dependency: the part cannot exist, and is not shared, apart from its one whole.
• Categorization (union types) model a subclass drawn from the union of several distinct, unrelated superclasses — different from ordinary specialization, which always has exactly one shared parent.

Lecture 15 applies every construct from this lecture to a full, worked case study from scratch, and Lecture 16 shows exactly how each one gets mapped down into relations.

// share