A · Compressed recap
1NF: atomic values. 2NF: no non-prime attribute depends on a proper part of a candidate key. 3NF: every non-trivial X → A has X a superkey or A prime (kills transitive dependencies). BCNF: X must be a superkey (no prime escape); two-attribute relations are always BCNF; 3NF-not-BCNF needs overlapping composite keys. 4NF: every non-trivial MVD X →→ Y has X a superkey; independent lists go to separate tables.
Highest-NF procedure: keys → prime set → split FDs → classify (superkey / prime RHS / partial / transitive) → lowest wins. 3NF synthesis (minimal cover, one table per left side, add a key table if missing) is lossless and preserving. BCNF decomposition (split on X → X⁺ − X) is lossless, may lose FDs, and is not unique.
B · Merged rule card
4.4.1 1NF
- 1NF: atomic values, one value per cell; no sets, lists, repeating groups, nested tables.
- Fix multivalued attributes with a separate table (owner key, value).
- Atomicity depends on how values are used.
- BCNF ⊂ 3NF ⊂ 2NF ⊂ 1NF.
4.4.2 2NF (partial dependency)
- Partial dependency: proper subset of a candidate key → non-prime attribute.
- 2NF: 1NF + no partial dependency of any non-prime attribute on any candidate key.
- Single-attribute keys or all-prime attributes → automatically 2NF.
- Fix: move the partially dependent attributes out with the key part they depend on.
4.4.3 3NF (transitive dependency + formal condition)
- Transitive: key → B → C with B not a superkey, C non-prime.
- 3NF: for every non-trivial X → A, X is a superkey or A is prime.
- 3NF ⊂ 2NF.
- Highest-NF walk: superkey → OK; prime RHS → 3NF OK; part of key → 2NF violation; else 3NF violation.
4.4.4 BCNF; 3NF vs BCNF trade-off
- BCNF: every non-trivial X → A has X a superkey.
- BCNF ⊂ 3NF; the gap = FDs with non-superkey X and prime A (needs overlapping composite keys).
- Two-attribute relations are always BCNF.
- 3NF: lossless + preserving always possible. BCNF: lossless always, preserving not always.
4.4.5 Drill: highest normal form of a given relation
- Procedure: keys → prime set → split FDs → classify each (superkey / prime RHS / partial / transitive) → lowest level wins.
- Partial dependency on a non-prime → 1NF. Transitive only → 2NF. Only prime-RHS non-superkey FDs → 3NF. All left sides superkeys → BCNF.
- All attributes prime → at least 3NF.
4.4.6 Algorithms: 3NF synthesis, BCNF decomposition
- 3NF synthesis: minimal cover → one table per left side (X + its right sides) → add a key table if none contains a key → drop subset tables. Lossless + preserving + 3NF.
- BCNF decomposition: pick violating X → Y; split into XY and R − (Y − X); recurse with projected F⁺. Lossless + BCNF; may lose FDs; not unique.
4.4.7 Multivalued dependencies and 4NF
- X →→ Y: Y-values for an X-value independent of the rest (swap-closure).
- Complementation: X →→ Y ⇒ X →→ R − X − Y. Every FD is an MVD.
- Trivial MVD: Y ⊆ X or X ∪ Y = R.
- 4NF: every non-trivial MVD has a superkey left side; 4NF ⊂ BCNF.
- Lossless binary split ⇔ R₁ ∩ R₂ →→ R₁ (or R₂).
C · Interleaved GATE-style questions
Q1 · MCQ · 1 mark(s) — 1NF
A table storing several hobbies in one cell violates:
- (A) BCNF only
- (B) 1NF
- (C) nothing
- (D) 2NF only
Q2 · MCQ · 2 mark(s) — 2NF
R(A, B, C, D) with key AB and F = {AB → C, A → D}. Highest NF?
- (A) 1NF
- (B) 2NF
- (C) 3NF
- (D) BCNF
Q3 · MCQ · 2 mark(s) — 3NF
R(A, B, C, D, E) with F = {A → BCDE, BC → A, D → E}. Highest NF?
- (A) 1NF
- (B) 2NF
- (C) 3NF
- (D) BCNF
Q4 · MCQ · 2 mark(s) — BCNF
R(A, B, C) with F = {AB → C, C → A}. Highest NF?
- (A) 1NF
- (B) 2NF
- (C) 3NF
- (D) BCNF
Q5 · NAT · 2 mark(s) — Drill
R(A, B, C, D, E) with F = {A → B, BC → E, ED → A}. How many candidate keys?
Numerical answer: ________
Q6 · NAT · 2 mark(s) — 3NF synthesis
How many relations does 3NF synthesis produce for R(A, B, C, D, E) with F = {AB → C, C → D, D → E}?
Numerical answer: ________
Q7 · MCQ · 2 mark(s) — BCNF decomposition
BCNF decomposition of R(A, B, C) with F = {AB → C, C → A} gives {CA, CB}. Which FD is lost?
- (A) C → A
- (B) AB → C
- (C) none
- (D) both
Q8 · MCQ · 1 mark(s) — 4NF
Which normal form is designed to remove redundancy caused by multivalued dependencies?
- (A) 4NF
- (B) 2NF
- (C) 3NF
- (D) BCNF
Q9 · MSQ · 2 mark(s) — Facts
Which are true?
- (A) BCNF ⊂ 3NF ⊂ 2NF ⊂ 1NF
- (B) A relation with one candidate key that is in 3NF is in BCNF
- (C) 3NF decompositions can always be made dependency preserving
- (D) Every BCNF relation is in 4NF
D · Answer Vault
Reveal answers when you’re ready
Q1 Answer: (B) · 1 mark(s)
Atomicity.
- (A) is wrong: Fails earlier.
- (C) is wrong: It violates 1NF.
- (D) is wrong: It fails at the first rung.
Re-read: Lesson 4.4.1 — 1NF
Q2 Answer: (A) · 2 mark(s)
A → D is partial on a non-prime attribute.
- (B) is wrong: Partial dependency exists.
- (C) is wrong: No.
- (D) is wrong: No.
Re-read: Lesson 4.4.2 — 2NF (partial dependency)
Q3 Answer: (B) · 2 mark(s)
Keys A, BC. D → E: D not part of a key, E non-prime → transitive.
- (A) is wrong: No partial dependency.
- (C) is wrong: D → E violates 3NF.
- (D) is wrong: No.
Re-read: Lesson 4.4.3 — 3NF (transitive dependency + formal condition)
Q4 Answer: (C) · 2 mark(s)
Keys AB, BC; C → A has prime right side but non-superkey left.
- (A) is wrong: Higher.
- (B) is wrong: Higher.
- (D) is wrong: C is not a superkey.
Re-read: Lesson 4.4.4 — BCNF; 3NF vs BCNF trade-off
Q5 Answer: 3 · 2 mark(s)
ACD, BCD, CDE.
Re-read: Lesson 4.4.5 — Drill: highest normal form of a given relation
Q6 Answer: 3 · 2 mark(s)
Tables ABC, CD, DE; ABC contains the key AB.
Re-read: Lesson 4.4.6 — Algorithms: 3NF synthesis, BCNF decomposition
Q7 Answer: (B) · 2 mark(s)
A and B are never together.
- (A) is wrong: Inside CA.
- (C) is wrong: AB → C is lost.
- (D) is wrong: C → A is kept.
Re-read: Lesson 4.4.6 — Algorithms: 3NF synthesis, BCNF decomposition
Q8 Answer: (A) · 1 mark(s)
4NF.
- (B) is wrong: Partial FDs.
- (C) is wrong: Transitive FDs.
- (D) is wrong: Non-superkey FDs.
Re-read: Lesson 4.4.7 — Multivalued dependencies and 4NF
Q9 Answer: (A) (B) (C) · 2 mark(s)
B holds because the 3NF/BCNF gap needs overlapping keys. D is false (COURSE_INFO).
- (D) is wrong: MVDs can still violate 4NF.
Re-read: Lesson 4.4.4 — BCNF; 3NF vs BCNF trade-off