BCNF; 3NF vs BCNF trade-off

0 · Recall Warm-up

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

1. State the 3NF condition. from Lesson 4.4.3
2. When is a decomposition dependency preserving? from Lesson 4.3.2
3. Name the three anomalies. from Lesson 4.1.1

1 · The Hook

A college assigns tutors: each tutor teaches one subject, and each student has one tutor per subject. TUTORING(student, subject, tutor) with student subject → tutor and tutor → subject. It is in 3NF (subject is prime), yet "Prof. Rao teaches DBMS" repeats for each of her students. Boyce–Codd normal form removes that last redundancy, at a price: one FD can no longer be checked inside one table.

2 · Predict First

In TUTORING with keys (student, subject) and (student, tutor), is tutor → subject a BCNF violation? What happens to student subject → tutor if we split into (tutor, subject) and (student, tutor)?

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

3 · Core Concept

Predict First — the answer

Yes: tutor is not a superkey. After the split, the pieces are lossless (common tutor → subject), but student subject → tutor cannot be checked: student and subject are in different tables. BCNF cost us dependency preservation.

Boyce–Codd normal form (BCNF)

R is in BCNF if for every non-trivial FD X → A that holds on R, X is a superkey of R. No "or A is prime" escape.

BCNF vs 3NF

• BCNF ⊂ 3NF. A 3NF relation fails BCNF exactly when some FD X → A has X not a superkey but A prime.
• This can only happen when the relation has several overlapping composite candidate keys. If R has a single candidate key, or only non-overlapping keys, 3NF and BCNF coincide for practical purposes.
• Any relation with two attributes is in BCNF.

The trade-off

Practice: aim for BCNF; if the only BCNF decompositions lose dependencies you need, stop at 3NF.

Example where BCNF loses a dependency

R(A, B, C), F = {AB → C, C → B}. Keys AB, AC. 3NF (B is prime) but not BCNF (C → B, C not a superkey). The BCNF split {AC, CB} is lossless but does not preserve AB → C (Lesson 4.3.2). No BCNF decomposition of R preserves AB → C.

Checking BCNF

For every FD in F (or minimal cover): is its left side a superkey (closure = R)? One "no" with a non-trivial FD → not BCNF. (When checking a piece after decomposition, use FDs projected from F⁺ onto the piece.)

4 · Visual

TUTORING(student, subject, tutor)
FDs:  student subject -> tutor      tutor -> subject
keys: (student, subject), (student, tutor)

tutor -> subject:  tutor not a superkey, subject prime   => 3NF yes, BCNF no

split:  TS(tutor, subject)   ST(student, tutor)     lossless (tutor -> subject)
        student subject -> tutor   spans both tables -> NOT preserved

5 · Worked Examples

Example 1 Easy

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

Solution

BCNF: every relation with two attributes is in BCNF (both left sides are keys here anyway).

Example 2 Medium

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

Solution

Keys A, B. A → BCD: superkey; B → A: superkey. BCNF.

Example 3 GATE

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

Solution

Keys AB, BC, BD; all attributes prime → 3NF. Not BCNF: C → D (C⁺ = CDA, not R).

8 · Connect the Dots

Lesson 4.4.3 gave 3NF; this lesson removed its escape clause to get BCNF and named the trade-off. Next: a drill lesson applying all four forms to many relations quickly: "highest normal form".

9 · Self-Test

GATE format. Try every question before opening the vault.

Q1 · MCQ · 1 mark(s) — BCNF

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

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

Q2 · MCQ · 1 mark(s) — Two attributes

Any relation with exactly two attributes is:

1. (A) in BCNF
2. (B) in 3NF but possibly not BCNF
3. (C) possibly not in 2NF
4. (D) never in BCNF

Q3 · MCQ · 2 mark(s) — Trade-off

Which is always achievable for any relation?

1. (A) A lossless, dependency-preserving BCNF decomposition
2. (B) A dependency-preserving 4NF decomposition
3. (C) A lossless, dependency-preserving 3NF decomposition
4. (D) A lossy BCNF decomposition only

Q4 · MSQ · 2 mark(s) — BCNF

Which relations are in BCNF?

1. (A) R(A, B, C), F = {AB → C, C → A}
2. (B) R(A, B, C), F = {A → BC}
3. (C) R(A, B), F = {A → B}
4. (D) R(A, B, C, D), F = {AB → C, C → D}

Q5 · MCQ · 2 mark(s) — Gap

A relation is in 3NF but not in BCNF. Which must be true?

1. (A) Some FD X → A has X not a superkey and A prime
2. (B) It has only one candidate key, which is a single attribute
3. (C) It has a partial dependency
4. (D) It has a transitive dependency on a non-prime attribute

11 · Concept Ledger

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

Boyce–Codd normal form (BCNF)3NF vs BCNF gapBCNF dependency-preservation trade-off