Extended ER: specialization, generalization, aggregation

0 · Recall Warm-up

Answer from memory first. Answers are in the Answer Vault at the bottom.

1. What is the key of a weak entity type? from Lesson 1.2.3
2. In a 1:N relationship, which entity can absorb a relationship attribute? from Lesson 1.2.2
3. What is the degree of a relationship? from Lesson 1.2.1

1 · The Hook

All students have a roll, name and CGPA. But PhD students also have a thesis title and a guide, and undergraduates have a year of study. Putting thesis_title on every student leaves it NULL for thousands of undergraduates. Extended ER lets you say: "every PhD student is a student, plus some extra attributes of its own".

2 · Predict First

STUDENT has subclasses UG and PG. Does a PG entity have a roll number? Does every STUDENT have to be in UG or PG? Can a student be in both?

Make a guess in your head. The answer opens Section 3.

3 · Core Concept

Predict First — the answer

A PG entity does have a roll number: it inherits every attribute of STUDENT. Whether every student must be in a subclass, and whether one can be in both, are design choices: the completeness (total/partial) and disjointness (disjoint/overlapping) constraints.

Superclass, subclass, IS-A

A subclass is a subset of an entity type's entities that has extra attributes or relationships. The original type is the superclass. PG IS-A STUDENT. A subclass entity is the same real-world entity as its superclass entity, seen in a more specific role.

Inheritance: a subclass has all attributes and relationships of its superclass, plus its own (local) ones. PG has roll, name, cgpa (inherited) and thesis_title (local), and takes part in ENROLLS (inherited) and GUIDED_BY (local).

Specialisation and generalisation

• Specialisation is top-down: start from STUDENT and define subclasses UG and PG by what distinguishes them.
• Generalisation is bottom-up: start from CAR and TRUCK, notice shared attributes (reg_no, owner), and create the superclass VEHICLE.

The result looks the same (superclass above, subclasses below, joined through a triangle or circle labelled ISA); the difference is the direction of design.

Constraints on specialisation

Four combinations are possible: disjoint-total, disjoint-partial, overlapping-total, overlapping-partial. A specialisation may be attribute-defined (subclass decided by an attribute such as level ∈ {UG, PG}) or user-defined.

Aggregation

ER cannot draw a relationship between a relationship and an entity. Suppose students work on projects (WORKS_ON(STUDENT, PROJECT)) and a faculty member monitors each student–project pair. Aggregation draws a box around WORKS_ON and its two entity types and treats the whole box as one higher-level entity, which can then take part in MONITORS(⟨WORKS_ON⟩, FACULTY).

Without aggregation you would need a ternary relationship STUDENT–PROJECT–FACULTY, which wrongly allows a faculty member to monitor a pair that is not even working together.

4 · Visual

Disjoint (d), total (double line) specialisation of STUDENT into UG and PG; each subclass adds local attributes.

5 · Worked Examples

Example 1 Easy

Specialisation or generalisation? (a) Starting from EMPLOYEE, the designer defines SECRETARY and ENGINEER. (b) Starting from SAVINGS_ACCOUNT and CURRENT_ACCOUNT, the designer defines ACCOUNT.

Solution

(a) specialisation (top-down). (b) generalisation (bottom-up).

Example 2 Medium

PERSON is specialised into STUDENT and FACULTY. A teaching assistant is both. Every person in the database is one or the other or both. Name the constraints.

Solution

Overlapping (a person can be in both) and total (every person is in at least one).

Example 3 GATE

Why is aggregation used instead of a ternary relationship for "a faculty member monitors a student's work on a project"?

Solution

Monitoring applies to an existing (student, project) pair. Aggregation makes the WORKS_ON relationship an entity, so MONITORS can only link to pairs that exist in WORKS_ON. A ternary relationship would allow monitoring combinations of student and project that are not in WORKS_ON.

8 · Connect the Dots

Lessons 1.1.1–1.2.3 built the core ER model; this lesson added subclasses and aggregation. Next: the Chapter 1.2 Lock-in, and then a full modelling exercise turning an English description into a complete ER diagram.

9 · Self-Test

GATE format. Try every question before opening the vault.

Q1 · MCQ · 1 mark(s) — Inheritance

PG is a subclass of STUDENT. Which attributes does a PG entity have?

1. (A) None until assigned
2. (B) STUDENT's attributes plus its local attributes
3. (C) Only STUDENT's attributes
4. (D) Only its local attributes

Q2 · MCQ · 1 mark(s) — Direction

Bottom-up creation of a superclass from existing entity types is:

1. (A) aggregation
2. (B) normalisation
3. (C) specialisation
4. (D) generalisation

Q3 · MCQ · 2 mark(s) — Constraints

In a disjoint, partial specialisation of VEHICLE into CAR and TRUCK:

1. (A) a vehicle is in at most one of CAR, TRUCK and may be in neither
2. (B) every vehicle is a car or a truck, possibly both
3. (C) every vehicle is exactly one of CAR, TRUCK
4. (D) a vehicle may be both, and may be neither

Q4 · MCQ · 1 mark(s) — Aggregation

Aggregation is used to:

1. (A) merge two entity types into one
2. (B) store derived attributes
3. (C) identify weak entities
4. (D) let a relationship (with its entities) take part in another relationship

Q5 · MSQ · 2 mark(s) — Constraints

Which combinations of specialisation constraints are allowed?

1. (A) disjoint and total
2. (B) disjoint and partial
3. (C) overlapping and partial
4. (D) overlapping and total

11 · Concept Ledger

Unlocked in this lesson. From here on, these terms may be used.

superclasssubclassIS-Ainheritancelocal attributespecialisation (top-down)generalisation (bottom-up)disjoint vs overlappingtotal vs partial specialisationaggregation