Supertypes, Subtypes & Inheritance

When one entity type isn't enough

The basic E-R model from Module 2 forces an awkward choice when *some* instances of an entity differ from others. Picture an EMPLOYEE entity at a consulting firm. Most employees have a salary; consultants instead have a *billing rate* and a *contract number*; hourly staff have an *hourly rate*. You have three bad options:

1. One entity, all attributes. Put Salary, Billing_Rate, Contract_Number, Hourly_Rate all on EMPLOYEE. Now most rows have nulls in most of those columns — the *sparse-matrix* problem — and every program that touches EMPLOYEE must handle every combination.
2. Separate entities. Make SALARIED_EMP, CONSULTANT, HOURLY_EMP independent entities. Now the shared attributes (Employee_Number, Name, Address, Date_Hired) and shared relationships are *repeated* across all three — redundancy, the thing data modeling exists to kill.
3. A supertype with subtypes. Keep the common structure in one place and the distinct structure in another. This is the EER answer.

The EER vocabulary

The Enhanced Entity-Relationship (EER) model extends the basic E-R model with new constructs — chiefly supertype/subtype relationships — to capture this kind of structure precisely. (It is, not coincidentally, semantically close to object-oriented modeling: supertype ≈ superclass, subtype ≈ subclass, inheritance is inheritance.)

- A supertype is a *generic* entity type that holds the attributes and relationships shared by all of its subtypes — including the identifier, which applies to every instance regardless of subtype. - A subtype is a meaningful *subgrouping* of a supertype's instances that has attributes or relationships distinct from the other subgroupings.

Drawn out, the supertype sits on top with a connecting line down through a small circle to each subtype. The circle is where the constraints (Lesson 3.3) get annotated.

The two-condition test for a subtype

Not every category deserves to be a subtype. A subtype is worth modeling only if at least one of these is true:

1. The subtype has attributes unique to that subgrouping (not applicable to all supertype instances), or
2. The subtype participates in a relationship unique to that subgrouping (one that does not apply to all supertype instances).

Either condition alone is enough; having both makes the case stronger. If a proposed subtype has *neither* — no unique attribute and no unique relationship — there is no benefit to drawing it. It can stay an undifferentiated instance of the supertype. (You will see in Lesson 3.3 why this is exactly what makes a specialization *partial*.)

Inheritance — the reason all of this works

Attribute inheritance is the property by which a subtype instance automatically receives the values of all attributes and instances of all relationships of its supertype. This is the single most important property of the structure: it is *why* you never repeat the supertype's attributes down in the subtypes. Put each attribute at the highest level where it applies, and inheritance distributes it downward for free. (Lesson 3.3 builds multi-level hierarchies on top of this; a FACULTY instance ends up holding PERSON attributes + EMPLOYEE attributes + its own Rank.)

Worked example — EMPLOYEE / CONSULTANT

A consulting firm models employees:

- Shared by all employees → live on the supertype EMPLOYEE: Employee_Number (the identifier), Employee_Name, Address, Date_Hired. - Unique to a subtype → live on that subtype only: - HOURLY_EMPLOYEE: Hourly_Rate. - SALARIED_EMPLOYEE: Annual_Salary, Stock_Option. - CONSULTANT: Billing_Rate, Contract_Number.

Now a CONSULTANT instance *inherits* Employee_Number, Employee_Name, Address, and Date_Hired from EMPLOYEE, and adds only Billing_Rate and Contract_Number. No nulls for attributes that don't apply; no repeated common attributes. That is the supertype/subtype payoff — the common is centralized, the distinct is isolated, and inheritance glues them together.