Drill: highest normal form of a given relation

0 · Recall Warm-up

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

1. State the BCNF condition. from Lesson 4.4.4
2. State the 3NF condition. from Lesson 4.4.3
3. How do you find attributes that must be in every key? from Lesson 4.2.3

1 · The Hook

"Consider R(A, B, C, D, E) with the FDs … The highest normal form of R is (A) 1NF (B) 2NF (C) 3NF (D) BCNF." This exact question appears in GATE almost every year. This lesson is a drill: one fixed procedure, then six relations solved with it.

2 · Predict First

R(A, B, C, D) with F = {AB → C, B → D}. Highest normal form?

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

3 · Core Concept

Predict First — the answer

Key AB. B → D: B is a proper subset of the key, D non-prime → partial dependency → 1NF.

The procedure (always the same)

1. Keys: find all candidate keys (closure; never-on-right attributes first).
2. Prime set: union of all keys.
3. Split every FD into single right sides; ignore trivial ones.
4. Classify each FD X → A:
   • X superkey → ✔ BCNF
   • else A prime → ✔ 3NF, ✘ BCNF
   • else X ⊂ some candidate key (proper subset) → ✘ 2NF
   • else → ✔ 2NF, ✘ 3NF
5. Answer = the lowest level any FD reaches.

Always use the FDs given (or a minimal cover). An FD that is derivable (in F⁺) and violates a form also counts, but in practice the given FDs reveal any violation, because a violating derived FD implies a violating given one.

Drill table (all verified by computing closures)

Shortcuts

• One single-attribute key, and no FD whose left side is a non-key → BCNF.
• All attributes prime → at least 3NF; then check left sides for BCNF.
• An FD from part of a key to a non-prime attribute → stop: 1NF.

4 · Visual

Drill #6:  R(ABCDE)  A->B, BC->E, ED->A

never on right: C, D          CD+ = CD
try CD + one: ACD+ = ACDBE ✓   BCD+ = BCDEA ✓   CDE+ = CDEAB ✓
keys: ACD, BCD, CDE  -> prime = A,B,C,D,E (all)

A->B   A not superkey, B prime  -> 3NF ok, BCNF ✗
BC->E  not superkey, E prime    -> 3NF ok
ED->A  not superkey, A prime    -> 3NF ok          highest: 3NF

5 · Worked Examples

Example 1 Easy

R(A, B, C) with F = {A → B, A → C}. Highest NF?

Solution

Key A; every left side is A → BCNF.

Example 2 Medium

R(A, B, C, D, E) with F = {A → B, B → C, CD → E} (key AD). Highest NF?

Solution

Non-prime: B, C, E. A → B: A ⊂ AD, B non-prime → partial → 1NF.

Example 3 GATE

R(A, B, C, D, E, F) with F = {AB → C, C → D, D → E, E → F, F → A}. Highest NF?

Solution

Keys AB, BC, BD, BE, BF: every attribute is prime → at least 3NF. C → D: C⁺ = CDEFA, not a superkey → not BCNF. 3NF.

8 · Connect the Dots

Lessons 4.4.1–4.4.4 defined the forms; this lesson drilled the decision. Next: the algorithms that produce good designs: 3NF synthesis and BCNF decomposition.

9 · Self-Test

GATE format. Try every question before opening the vault.

Q1 · MCQ · 1 mark(s) — Drill

R(A, B, C, D) with F = {A → BCD}. Highest NF?

1. (A) 1NF
2. (B) 2NF
3. (C) 3NF
4. (D) BCNF

Q2 · MCQ · 2 mark(s) — Drill

R(A, B, C, D, E) with F = {AB → CDE, D → E}. Highest NF?

1. (A) 1NF
2. (B) 2NF
3. (C) 3NF
4. (D) BCNF

Q3 · MCQ · 2 mark(s) — Drill

R(A, B, C, D) with F = {AB → C, C → A, BC → D}. Highest NF?

1. (A) 1NF
2. (B) 2NF
3. (C) 3NF
4. (D) BCNF

Q4 · MCQ · 2 mark(s) — Drill

R(A, B, C, D) with F = {A → B, C → D}. Highest NF?

1. (A) 1NF
2. (B) BCNF
3. (C) 3NF
4. (D) 2NF

Q5 · NAT · 2 mark(s) — Drill

R(A, B, C, D, E) with F = {A → B, B → A, C → D, D → C}. How many candidate keys does R have? (Every attribute turns out prime, so R is in 3NF.)

Numerical answer: ________

11 · Concept Ledger

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

highest normal form drillFD classification (superkey, prime RHS, partial, transitive)