Ternary relationships; specialization/generalization mapping

0 · Recall Warm-up

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

1. Where does the foreign key go for a 1:N relationship? from Lesson 2.4.2
2. What is the minimum number of tables for E1 —M:N— E2? from Lesson 2.4.2
3. What are disjoint and total specialisation? from Lesson 1.2.4

1 · The Hook

Three last ER features need mapping rules: a relationship among three entity types, a superclass with subclasses, and aggregation. They appear less often in GATE than binary relationships, but when they do, the question is usually "what is the primary key of the relationship table?" or "which mapping avoids NULLs?"

2 · Predict First

TEACHES(FACULTY, COURSE, SEMESTER) is ternary: a faculty member teaches a course in a semester. If each course in a given semester has exactly one teacher, what is the primary key of the TEACHES table?

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

3 · Core Concept

Predict First — the answer

(cid, sem). Knowing the course and semester fixes the faculty member, so fid is not needed in the key. Without that rule, the key would be all three: (fid, cid, sem).

Rule 8 · Ternary (n-ary) relationships

Create a table with the PKs of all participating entity types as foreign keys, plus the relationship's attributes.

• If all sides are "many", the PK is all the foreign keys.
• If one entity type E has "1" on its side (for each combination of the others there is at most one E), E's key is left out of the PK.

TEACHES(fid, cid, sem): PK (fid, cid, sem) in general; PK (cid, sem) when each (course, semester) has one teacher.

Rule 9 · Specialisation / generalisation

Superclass STUDENT(roll, name, cgpa) with subclasses UG(year) and PG(thesis):

Minimum tables: 1 (options C/D). In option A, every subclass table's PK is also an FK to the superclass.

Rule 10 · Aggregation

Map the aggregated relationship as usual (WORKS_ON(roll, pid)). A relationship involving the aggregate references the aggregated relationship's PK: MONITORS(roll, pid, fid) with (roll, pid) → WORKS_ON. If each (student, project) has one monitor, the PK is (roll, pid); the FK fid → FACULTY.

Full mapping checklist

1. Strong entities → tables. 2. Weak entities → tables (owner PK + partial key). 3. 1:1 → FK on total side (or merge). 4. 1:N → FK on N side. 5. M:N → table. 6. Multivalued → table. 7. n-ary → table. 8. Specialisation → choose A–D. 9. Aggregation → reference the aggregated relationship's key.

4 · Visual

A ternary relationship becomes a table with all three keys; the side marked 1 drops out of the primary key.

5 · Worked Examples

Example 1 Easy

SUPPLIES(SUPPLIER, PART, PROJECT) with attribute qty; all sides many. Give the table and its PK.

Solution

SUPPLIES(sid, pno, jno, qty), each a foreign key; PK = all three.

Example 2 Medium

VEHICLE(regno, owner) is specialised into CAR(seats) and TRUCK(load), total and disjoint. What is the fewest tables without NULLs, and what are they?

Solution

2 tables (option B): CAR(regno, owner, seats) and TRUCK(regno, owner, load). Total means no vehicle is lost; disjoint means no vehicle is repeated.

Example 3 GATE

For the specialisation above, which option is wrong if the specialisation is partial, and why?

Solution

Option B (subclasses only) is wrong: a vehicle that is neither a car nor a truck would have no table to live in.

8 · Connect the Dots

Chapter 2.4 is complete: every ER construct now has a mapping rule. Next: the Chapter 2.4 Lock-in, then the Phase 2 checkpoint, and then Phase 3: asking questions of these tables with relational algebra, calculus and SQL.

9 · Self-Test

GATE format. Try every question before opening the vault.

Q1 · MCQ · 1 mark(s) — Ternary

SUPPLIES(SUPPLIER, PART, PROJECT) where each (part, project) is supplied by exactly one supplier. The PK of SUPPLIES(sid, pno, jno) is:

1. (A) (sid, pno)
2. (B) (pno, jno)
3. (C) (sid, pno, jno)
4. (D) (sid)

Q2 · MCQ · 1 mark(s) — Specialisation

Which mapping of a specialisation loses entities when the specialisation is partial?

1. (A) One table with a type attribute
2. (B) Subclass tables only
3. (C) One table with boolean flags
4. (D) Superclass table + subclass tables

Q3 · NAT · 1 mark(s) — Minimum tables

A superclass with 3 subclasses is mapped with the "superclass + subclass tables" option. How many tables?

Numerical answer: ________

Q4 · MSQ · 2 mark(s) — Mapping

For superclass STUDENT(roll) and subclass PG(thesis) mapped as STUDENT and PG tables, which are true?

1. (A) A PG student appears in both tables.
2. (B) PG must repeat name and cgpa.
3. (C) PG.roll is a foreign key to STUDENT.roll.
4. (D) PG's primary key is roll.

Q5 · MCQ · 2 mark(s) — Aggregation

WORKS_ON(STUDENT, PROJECT) is aggregated and related to FACULTY by MONITORS, where each student-project pair has at most one monitor. MONITORS's primary key is:

1. (A) (roll, pid, fid)
2. (B) roll
3. (C) fid
4. (D) (roll, pid)

11 · Concept Ledger

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

mapping ternary (n-ary) relationshipsspecialisation mapping options A–Dmapping aggregationER-to-relational checklist