Advanced Data Modelling (EER)

ER modelling and the design process

ER modelling is a very effective tool for standard relational database tasks. It provides semantic information, but that is not always sufficient — it is less adapted to more complex semantics (e.g. “a pilot is a kind of employee with extra attributes”). This is why the Enhanced ER model was developed.

The database design process

ER/EER diagrams belong to the conceptual stage; they are then mapped to relations (logical) and finally to tables, indexes and storage (physical).

Problems with ER models: connection traps

When designing a conceptual model, problems called connection traps can arise, usually because the meaning of certain relationships was misinterpreted. The two main types:

Fan trap — example and fix

Two 1:* relationships fan out from the same entity (Division):

• One or more staff are located in exactly one division.
• One division operates one or more branches.

Question: At which branch office does staff number SG37 work?

Looking at the occurrences (semantic net): SG37 is linked to division D1, and D1 operates both B003 and B007. We cannot tell which one — the pathway is ambiguous.

Restructuring to remove the fan trap

Re-order the entities so the path is a chain: Division —Operates→ Branch —Has→ Staff.

Now each staff member links to exactly one branch, and each branch to exactly one division. The semantic net shows unambiguously: SG37 works at branch B003.

Chasm trap — example and fix

• A branch has one or more staff; each staff member belongs to exactly one branch.
• A staff member oversees zero or more properties; a property is overseen by zero or one staff member (optional participation).

Question: At which branch office is property PA14 available?

PA14 is not yet assigned to any staff member, so there is no path from PA14 through Staff to a Branch. We can’t answer the question — this is the “chasm”.

Restructuring to remove the chasm trap

Add the missing relationship Offers directly between Branch (1..1) and PropertyForRent (1..*):

Now every property is linked directly to its branch, whether or not a staff member oversees it.

The Enhanced (Extended) ER model

The main new constructs: supertypes/subtypes, specialization hierarchies, inheritance, subtype discriminators, disjoint/overlapping and completeness constraints.

Entity supertypes and subtypes

Why? — nulls created by unique attributes

Imagine one EMPLOYEE table for all employees, including pilots. Pilot-only columns (EMP_LICENSE, EMP_RATINGS, EMP_MED_TYPE) are NULL for every non-pilot:

Rob & Coronel, Fig 6.1 (abridged). Splitting PILOT out as a subtype removes these nulls.

Specialization hierarchy

• Depicts the arrangement of higher-level supertypes (parent entities) and lower-level subtypes (child entities).
• Relationships are described as “IS-A” relationships (a pilot IS-A employee).
• A subtype exists only within the context of its supertype, and every subtype has only one supertype it is directly related to.
• There can be many levels of supertype/subtype relationships.

Based on Rob & Coronel Fig 6.2. Shared attributes live in the supertype; unique attributes live in subtypes. “d” = disjoint; EMP_TYPE is the subtype discriminator. Double line under the circle (not shown) would mean total completeness.

A specialization hierarchy lets you:

• support attribute inheritance;
• define a special supertype attribute called the subtype discriminator;
• define disjoint/overlapping constraints and complete/partial constraints.

Inheritance

• Enables an entity subtype to inherit the attributes and relationships of the supertype (e.g. PILOT inherits EMP_LNAME and EMPLOYEE’s has relationship with DEPENDENT).
• All entity subtypes inherit their primary key from their supertype (PILOT’s PK is EMP_NUM, which is also an FK to EMPLOYEE).
• At implementation level, a supertype and each of its subtypes maintain a 1:1 relationship.

Rob & Coronel Fig 6.3 (abridged): the EMPLOYEE–PILOT supertype/subtype relationship. Each pilot row matches exactly one employee row.

Subtype discriminator

The default comparison condition for the discriminator is equality.

Disjoint and overlapping constraints

In Rob & Coronel Fig 6.4, PERSON →(o)→ EMPLOYEE, STUDENT; EMPLOYEE →(o)→ ADMINISTRATOR, PROFESSOR; STUDENT →(d)→ GRADUATE, UNDERGRAD.

With overlapping subtypes a single discriminator value can’t work, so you use one Y/N discriminator per subtype:

Table 6.1 — Discriminator attributes with overlapping subtypes.

Completeness constraint

Specifies whether each supertype occurrence must also be a member of at least one subtype.

• Partial completeness (single line): not every supertype occurrence must belong to a subtype.
• Total completeness (double line): every supertype occurrence must belong to at least one subtype.

Table 6.2 — Specialization hierarchy constraint scenarios. Memorize this grid — it combines both constraints.

Specialization vs generalization

Quick review