BCNF, 4NF & 5NF

By the end of this lesson you will be able to test a relation for BCNF, decompose it into BCNF step by step, explain how BCNF differs from 3NF, and describe the problems that 4NF (multivalued dependencies) and 5NF (join dependencies) solve. Most real designs stop at 3NF or BCNF, but these higher forms explain some redundancy that FDs alone cannot.

Boyce-Codd normal form (BCNF)

A relation R is in BCNF if, for every non-trivial FD X → A that holds in R, X is a super key.

Compare it with 3NF from Normal Forms: 1NF, 2NF & 3NF:

So BCNF is stricter. Every BCNF relation is in 3NF, but a 3NF relation can fail BCNF when a non-key attribute set determines a prime attribute. In plain words: in BCNF, "every determinant is a candidate key".

A 3NF table that is not BCNF

A sports academy in Jaipur records which coach trains each student in each sport:

• Each coach trains exactly one sport: coach → sport.
• For each sport, a student has exactly one coach: roll_no, sport → coach.

COACHING(roll_no, sport, coach)
F = { roll_no, sport → coach,   coach → sport }

Candidate keys: {roll_no, sport} and {roll_no, coach}
Prime attributes: roll_no, sport, coach   (all of them)

Is it in 3NF? Check coach → sport: coach is not a super key, but sport is prime. 3NF allows this. The other FD has a super key on the left. So COACHING is in 3NF.

Is it in BCNF? coach → sport has a left side that is not a super key, so COACHING is not in BCNF.

Step 1: see the redundancy that 3NF allowed.

CREATE TABLE coaching (roll_no INTEGER, sport VARCHAR(15), coach VARCHAR(20),
  PRIMARY KEY (roll_no, sport));
INSERT INTO coaching VALUES
  (2401, 'Cricket', 'Mr. Dahiya'), (2402, 'Cricket', 'Mr. Dahiya'),
  (2403, 'Cricket', 'Ms. Kaur'),   (2401, 'Chess', 'Ms. Saini'),
  (2404, 'Chess', 'Ms. Saini');

SELECT coach, sport, COUNT(*) AS times_stored
FROM coaching
GROUP BY coach, sport
ORDER BY coach;

coach      | sport   | times_stored
-----------+---------+-------------
Mr. Dahiya | Cricket | 2
Ms. Kaur   | Cricket | 1
Ms. Saini  | Chess   | 2
(3 rows)

You should see "Mr. Dahiya coaches Cricket" stored twice. If he switches to hockey, two rows must change. Also, a new coach cannot be recorded until a student joins.

BCNF decomposition algorithm

1. Find a non-trivial FD X → Y in R where X is not a super key.
2. Replace R with two relations: R1 = X⁺ (X with everything it determines, inside R) and R2 = R − (X⁺ − X).
3. Repeat on R1 and R2 (with their projected FDs) until every relation is in BCNF.

The shared attributes X form a key of R1, so the split is always lossless (you will prove this in Lossless Join & Dependency Preservation).

Step 2: apply it to COACHING with coach → sport.

X = coach,  X⁺ inside R = {coach, sport}
R1 = COACH_SPORT(coach, sport)        key: coach        BCNF
R2 = STUDENT_COACH(roll_no, coach)    key: {roll_no, coach}   BCNF

Step 3: check the result.

CREATE TABLE coach_sport (coach VARCHAR(20) PRIMARY KEY, sport VARCHAR(15));
CREATE TABLE student_coach (roll_no INTEGER, coach VARCHAR(20), PRIMARY KEY (roll_no, coach));
INSERT INTO coach_sport VALUES ('Mr. Dahiya', 'Cricket'), ('Ms. Kaur', 'Cricket'), ('Ms. Saini', 'Chess');
INSERT INTO student_coach VALUES (2401, 'Mr. Dahiya'), (2402, 'Mr. Dahiya'), (2403, 'Ms. Kaur'),
  (2401, 'Ms. Saini'), (2404, 'Ms. Saini');

SELECT sc.roll_no, cs.sport, sc.coach
FROM student_coach sc JOIN coach_sport cs ON cs.coach = sc.coach
ORDER BY sc.roll_no, cs.sport;

roll_no | sport   | coach
--------+---------+-----------
2401    | Chess   | Ms. Saini
2401    | Cricket | Mr. Dahiya
2402    | Cricket | Mr. Dahiya
2403    | Cricket | Ms. Kaur
2404    | Chess   | Ms. Saini
(5 rows)

You should see the same five facts as before. Each coach's sport is now stored once.

The price of BCNF

The FD roll_no, sport → coach spans both new tables, so no single table can enforce it. Nothing now stops this insert into student_coach: (2401, 'Ms. Kaur'). Student 2401 would have two cricket coaches, which breaks the rule. The decomposition did not preserve that dependency.

This is the classic trade-off: a 3NF decomposition can always keep every FD and be lossless; a BCNF decomposition is always lossless but may lose some FDs. Designers usually aim for BCNF and accept 3NF when BCNF would lose an important rule.

Multivalued dependencies and 4NF

Some redundancy has nothing to do with FDs. A professor at a college in Pune teaches several courses and also mentors several student clubs. Courses and clubs are independent of each other.

FACULTY_INFO(faculty, course, club)

Dr. Malik | DBMS | Coding Club
Dr. Malik | DBMS | Music Club
Dr. Malik | OS   | Coding Club
Dr. Malik | OS   | Music Club

To record "Dr. Malik teaches 2 courses and mentors 2 clubs", every combination must appear: 2 × 2 = 4 rows. Add a third club and two more rows are needed. No FD holds here (a faculty member has many courses), yet the data is clearly redundant.

This is a multivalued dependency (MVD), written faculty →→ course ("faculty multi-determines course"). It means: for one faculty member, the set of courses is independent of the set of clubs. Whenever X →→ Y holds in R, X →→ (R − X − Y) also holds, so faculty →→ club holds as well.

A relation is in 4NF if it is in BCNF and, for every non-trivial MVD X →→ Y, X is a super key. FACULTY_INFO has key {faculty, course, club}, so faculty →→ course breaks 4NF. The fix splits the independent facts:

CREATE TABLE faculty_courses (faculty VARCHAR(20), course VARCHAR(10), PRIMARY KEY (faculty, course));
CREATE TABLE faculty_clubs (faculty VARCHAR(20), club VARCHAR(20), PRIMARY KEY (faculty, club));
INSERT INTO faculty_courses VALUES ('Dr. Malik', 'DBMS'), ('Dr. Malik', 'OS');
INSERT INTO faculty_clubs VALUES ('Dr. Malik', 'Coding Club'), ('Dr. Malik', 'Music Club'), ('Dr. Malik', 'Quiz Club');

SELECT
  (SELECT COUNT(*) FROM faculty_courses) + (SELECT COUNT(*) FROM faculty_clubs) AS rows_in_4nf,
  (SELECT COUNT(*) FROM faculty_courses fc JOIN faculty_clubs fk ON fk.faculty = fc.faculty) AS rows_if_combined;

rows_in_4nf | rows_if_combined
------------+-----------------
5           | 6
(1 row)

You should see 5 rows in the 4NF design against 6 rows for the combined table. With 10 courses and 10 clubs the gap becomes 20 against 100.

Join dependencies and 5NF (overview)

Fifth normal form (5NF), also called project-join normal form (PJNF), deals with join dependencies: a relation that can be rebuilt from three or more of its projections, but not from any two.

Example: an electrical goods distributor records which dealer sells which brand's product, under a business rule: "if a dealer represents a brand, and sells a type of product, and the brand makes that product, then the dealer sells that brand's product".

SUPPLY(dealer, brand, product)
Ravi   | Havells | Fan
Ravi   | Bajaj   | Fan
Ravi   | Havells | Iron
Sunita | Havells | Fan

Joining only two projections, (dealer, brand) and (brand, product), produces a false row: Sunita | Havells | Iron. Joining all three projections, (dealer, brand), (brand, product) and (dealer, product), gives back exactly the original four rows. When the business rule holds, SUPPLY should be stored as those three tables. That is 5NF.

5NF cases are rare in practice and hard to spot, because the rule must hold for all future data. Exams usually ask only for the definition.

Common mistakes

• Assuming 3NF implies BCNF. It does not when a non-super-key determines a prime attribute, as in COACHING.
• Forgetting that BCNF may lose dependencies. Always check which FDs can still be enforced inside one table.
• Treating an MVD as an FD. faculty →→ course does not mean one course per faculty; it means the course set is independent of other attributes.
• Splitting dependent facts as if they were independent. 4NF decomposition is right only when the two sets really are independent.

Key points

• BCNF: for every non-trivial X → A, X is a super key. Every BCNF relation is in 3NF.
• 3NF but not BCNF happens when a non-super-key determines a prime attribute.
• BCNF decomposition is always lossless but may not preserve every FD; 3NF can always preserve both.
• An MVD X →→ Y says Y's values are independent of the rest; 4NF requires X to be a super key for each non-trivial MVD.
• 5NF removes redundancy from join dependencies, where a table equals the join of three or more projections.

(a) A appears on no right side. A⁺ = {A}. (AB)⁺ = {A, B, C, D}. (AC)⁺ = {A, C, B, D}.
    (AD)⁺ = {A, D}. Candidate keys: AB and AC.  Prime: A, B, C.  Non-prime: D.
(b) C → D: C is part of the key AC and D is non-prime, so this is a partial
    dependency. R is not even in 2NF. Highest normal form: 1NF.
    So R is in neither 3NF nor BCNF.
(c) Use C → BD (C⁺ = {B, C, D}):  R1(C, B, D) key C,  R2(A, C) key AC.  Both BCNF.
    AB → C needs A, B and C together; no single table has all three,
    and it cannot be derived from C → B, C → D. Not preserved.