A · Compressed recap
Entities: strong → table (simple attributes; composite flattened; derived not stored); weak → table with owner PK + partial key as PK (FK with cascade); multivalued attribute → own table (owner PK, value). Relationships: M:N → own table, PK = both FKs; 1:N → FK on the N side; 1:1 → FK on the total side, or merge when both total; recursive 1:N/1:1 → FK in the same table, recursive M:N → own table; n-ary → table of all keys, the "1" side leaves the PK. Hierarchies: super+sub tables, subs only (total+disjoint), one table with type or flags. Aggregation: reference the aggregated relationship's key.
Minimum tables: M:N 3, 1:N 2, 1:1 2, 1:1 both total 1, plus one per multivalued attribute and weak entity.
B · Merged rule card
2.4.1 Mapping strong and weak entities, composite and multivalued attributes
- Strong entity → table of simple attributes; PK = chosen key; composite → components; derived → not stored.
- Weak entity → table with owner PK (FK, cascade) + own attributes; PK = owner PK + partial key; identifying relationship: no table.
- Multivalued attribute → table (owner PK FK, value); PK = both.
- Tables so far = strong + weak + multivalued attributes.
2.4.2 Mapping 1:1, 1:N, M:N with participation; minimum number of tables
- M:N → new table (PK1, PK2 + attributes); PK = both.
- 1:N → FK (1-side PK) in the N-side table + relationship attributes.
- 1:1 → FK in the total-participation side (unique); both total → merge into one table.
- Recursive 1:N/1:1 → FK in same table; recursive M:N → new table.
- Minimum tables: M:N 3, 1:N 2, 1:1 2, 1:1 both total 1; + multivalued + weak + recursive M:N.
2.4.3 Ternary relationships; specialization/generalization mapping
- n-ary relationship → table with all participants' PKs + attributes; entity on a "1" side is left out of the PK.
- Specialisation: A super + sub (sub PK = FK to super); B subs only (needs total, best disjoint); C one table + type (disjoint, NULLs); D one table + flags (overlapping).
- Aggregation → relationship table references the aggregated relationship's PK.
- Checklist: strong, weak, 1:1, 1:N, M:N, multivalued, n-ary, hierarchy, aggregation.
C · Interleaved GATE-style questions
Q1 · NAT · 1 mark(s) — Minimum tables
E1 —R— E2 is 1:N with total participation on both sides. Minimum tables?
Numerical answer: ________
Q2 · NAT · 2 mark(s) — Minimum tables
E1 —R1— E2 is 1:1 with both total; E2 —R2— E3 is M:N; E3 has 2 multivalued attributes. Minimum tables?
Numerical answer: ________
Q3 · MCQ · 1 mark(s) — Weak entity
DEPENDENT is a weak entity owned by EMPLOYEE(eid), partial key name. The primary key of the DEPENDENT table is:
- (A) (eid, name)
- (B) eid
- (C) a new surrogate key, always
- (D) name
Q4 · MCQ · 1 mark(s) — 1:1
FACULTY 1:1 DEPT through HEADS, DEPT total, FACULTY partial. To avoid NULLs, the FK should be:
- (A) in DEPT, referencing FACULTY
- (B) in a separate HEADS table only
- (C) in both tables
- (D) in FACULTY, referencing DEPT
Q5 · MSQ · 2 mark(s) — Mapping
Which ER constructs always add a table of their own?
- (A) M:N relationship
- (B) Multivalued attribute
- (C) Composite attribute
- (D) 1:N relationship
Q6 · MCQ · 2 mark(s) — Ternary
R(A, B, C) ternary, where for each (a, b) there is at most one c. PK of R's table is:
- (A) (a, b, c)
- (B) (a, b)
- (C) (c)
- (D) (a, c)
Q7 · NAT · 1 mark(s) — Recursive
EMPLOYEE with a recursive 1:N SUPERVISES relationship. Minimum number of tables?
Numerical answer: ________
Q8 · MCQ · 1 mark(s) — Specialisation
Which mapping best suits an overlapping specialisation in one table?
- (A) Subclass tables only
- (B) One table with boolean flags per subclass
- (C) One table with a type attribute
- (D) No mapping exists
D · Answer Vault
Reveal answers when you’re ready
Q1 Answer: 2 · 1 mark(s)
FK on the N side; cannot merge.
Re-read: Lesson 2.4.2 — Mapping 1:1, 1:N, M:N with participation; minimum number of tables
Q2 Answer: 5 · 2 mark(s)
E1+E2 merged (1), E3 (1), R2 (1), multivalued (2): 5.
Re-read: Lesson 2.4.2 — Mapping 1:1, 1:N, M:N with participation; minimum number of tables
Q3 Answer: (A) · 1 mark(s)
Owner PK + partial key.
- (B) is wrong: Several dependents per employee.
- (C) is wrong: Not the standard mapping.
- (D) is wrong: Repeats across employees.
Re-read: Lesson 2.4.1 — Mapping strong and weak entities, composite and multivalued attributes
Q4 Answer: (A) · 1 mark(s)
Every department has a head, so DEPT.head_fid is never NULL.
- (B) is wrong: Extra table.
- (C) is wrong: Redundant.
- (D) is wrong: Most faculty would have NULL.
Re-read: Lesson 2.4.2 — Mapping 1:1, 1:N, M:N with participation; minimum number of tables
Q5 Answer: (A) (B) · 2 mark(s)
1:N becomes an FK; composite becomes columns.
- (C) is wrong: Columns only.
- (D) is wrong: FK only.
Re-read: Lesson 2.4.1 — Mapping strong and weak entities, composite and multivalued attributes
Q6 Answer: (B) · 2 mark(s)
C is determined by (a, b).
- (A) is wrong: Not minimal.
- (C) is wrong: Not unique.
- (D) is wrong: Does not fix b.
Re-read: Lesson 2.4.3 — Ternary relationships; specialization/generalization mapping
Q7 Answer: 1 · 1 mark(s)
FK supervisor_id in EMPLOYEE itself.
Re-read: Lesson 2.4.2 — Mapping 1:1, 1:N, M:N with participation; minimum number of tables
Q8 Answer: (B) · 1 mark(s)
Flags allow an entity to be in several subclasses.
- (A) is wrong: Repeats data for overlapping entities.
- (C) is wrong: One type value cannot show two subclasses.
- (D) is wrong: There are several.
Re-read: Lesson 2.4.3 — Ternary relationships; specialization/generalization mapping