In the 1NF, 2NF & 3NF chapter we saw that 3NF gives prime attributes a free pass: an FD X → A is allowed even when X is not a superkey, as long as A is part of some candidate key.
Most of the time that exception never matters. But when a table has several overlapping candidate keys, it lets redundancy slip through, and the familiar anomalies come back.
Boyce-Codd Normal Form (BCNF) removes the exception. It is a stricter version of 3NF, which is why it is sometimes called 3.5NF.
A relation R is in BCNF if, for every non-trivial functional dependency X → Y that holds on R, X is a superkey of R.
In simple words: the only things allowed to determine other things are keys.
A Table That Is in 3NF but Still Has Problems
A coaching institute has these rules:
Each teacher teaches only one course.
A course can have several teachers (different batches).
For each course a student takes, the student is in one teacher's batch.
They store everything in one table:
CREATE TABLE stc (
student VARCHAR(10),
course VARCHAR(10),
teacher VARCHAR(12),
PRIMARY KEY (student, course)
);
INSERT INTO stc VALUES
('Aman', 'DBMS', 'Prof. Rao'),
('Bina', 'DBMS', 'Prof. Rao'),
('Chirag', 'DBMS', 'Prof. Sen'),
('Aman', 'OS', 'Prof. Das'),
('Bina', 'OS', 'Prof. Das');
SELECT * FROM stc ORDER BY course, teacher, student;Output:
+---------+--------+-----------+
| student | course | teacher |
+---------+--------+-----------+
| Aman | DBMS | Prof. Rao |
| Bina | DBMS | Prof. Rao |
| Chirag | DBMS | Prof. Sen |
| Aman | OS | Prof. Das |
| Bina | OS | Prof. Das |
+---------+--------+-----------+The rules give us two FDs:
student, course → teacher (one teacher per student per course)
teacher → course (each teacher teaches one course)Candidate keys
student appears on no right side, so it is in every key.
{student}+ = {student} ✗
{student, course}+ = {student, course, teacher} ✓ key
{student, teacher}+ = {student, teacher, course} ✓ key (teacher → course)Keys: {student, course} and {student, teacher}. They overlap in student. All three attributes are prime.
Is it in 3NF?
student, course → teacher: left side is a key. Fine.teacher → course:teacheris not a superkey, butcourseis prime. 3NF allows it.
So stc is in 3NF. Yet look at the data:
"Prof. Rao teaches DBMS" is stored twice, once per student in his batch. If Prof. Rao switches to teaching OS, every one of those rows must change (update anomaly).
A new teacher, Prof. Iyer, who will teach DBMS, cannot be recorded until a student joins her batch, because
studentis part of the primary key (insertion anomaly).If Chirag leaves, we lose the fact that Prof. Sen teaches DBMS (deletion anomaly).
The culprit is teacher → course: a determinant (teacher) that is not a key. BCNF forbids exactly this.
Understanding the BCNF Definition
Compare the two definitions side by side:
Normal form | For every non-trivial X → A… |
|---|---|
3NF | X is a superkey OR A is a prime attribute |
BCNF | X is a superkey (no exceptions) |
So BCNF is just 3NF with the "OR A is prime" escape removed. That immediately tells us:
Every BCNF relation is in 3NF (and therefore 2NF and 1NF).
A 3NF relation fails BCNF only through an FD X → A where X is not a superkey and A is prime.
A popular informal phrasing is "every determinant is a candidate key". Strictly, the left side only needs to be a superkey, but if you write FDs with minimal left sides, the two statements agree.
Why "superkey" kills redundancy
If X is a superkey, no two different rows can have the same X value. So the fact "X determines Y" is stored at most once. There is nothing to repeat, and nothing to update in many places. That is the whole intuition behind BCNF.
How to Test a Relation for BCNF
For the original relation R with a given FD set F, the test is simple:
For each non-trivial FD X → Y in F:
compute X+
if X+ does not contain all attributes of R → R is NOT in BCNF
If no FD failed → R is in BCNFIt is enough to check the FDs actually listed in F. If every listed FD has a superkey on its left, every FD derived from them by Armstrong's axioms also does.
Worked check
R(A, B, C, D, E), F = { AB → CDE, D → A }
(AB)+ = {A, B, C, D, E} → AB is a superkey ✓
(D)+ = {D, A} → D is NOT a superkey ✗D → A violates BCNF. (It does satisfy 3NF, because A is prime: the keys are AB and BD.) So R is in 3NF but not BCNF.
Careful: testing a decomposed table
When you test a table that came out of a decomposition, checking only the FDs from F that "fit" inside it is not enough. You must consider all FDs implied on that table (the projection of F⁺).
Example: R(A, B, C, D), F = { A → B, B → C }. Suppose a decomposition produces R1(A, C, D).
Neither A → B nor B → C fits inside R1 (B is missing), so a careless check says "no FDs, BCNF".
But A → C is implied (A → B → C), and A, C are both in R1.
The key of R1 is AD, and A alone is not a superkey of R1. So R1 is not in BCNF.
A practical way to check a sub-table Ri: for each subset X of Ri, compute X⁺ using the full F, and look at X⁺ ∩ Ri. If it contains attributes beyond X but not all of Ri, then X → (X⁺ ∩ Ri) violates BCNF in Ri.
BCNF Decomposition Algorithm
When a relation is not in BCNF, we split it using the violating FD:
result = { R }
while some Ri in result is not in BCNF:
pick a non-trivial FD X → Y that holds on Ri with X not a superkey of Ri
replace Ri by:
R1 = X⁺ ∩ Ri (X together with everything it determines in Ri)
R2 = Ri − (R1 − X) (the rest, keeping X so the two can be joined)Why keep X in both pieces? Because X is the common attribute set, and X → (R1) holds, so X is a superkey of R1. That is exactly the condition that makes the split lossless (proved in the Lossless Join & Dependency Preservation chapter). This is why BCNF decomposition never creates spurious rows.
Each split makes the tables strictly smaller, so the algorithm always terminates. A two-attribute table is always in BCNF (see below), so in the worst case you stop there.
Worked Example 1
R(A, B, C, D, E), F = { AB → C, C → D, D → E }
Keys: A and B appear on no right side; (AB)⁺ = {A, B, C, D, E}. So AB is the only key.
Round 1. Check C → D: C⁺ = {C, D, E}, not a superkey. Violation.
R1 = C⁺ ∩ R = (C, D, E)
R2 = R − {D, E} = (A, B, C)Round 2. Check R1(C, D, E). Its key is C. The FD D → E holds inside it, and D⁺ ∩ R1 = {D, E}: D is not a superkey of R1. Violation.
R11 = (D, E)
R12 = (C, D)R2(A, B, C) has only AB → C, with AB its key. It is in BCNF.
Result:
(A, B, C) key AB FD: AB → C
(C, D) key C FD: C → D
(D, E) key D FD: D → EEvery original FD sits inside one table, so here we lost nothing. But that is not always the case.
Worked Example 2: The stc table
The violation is teacher → course. teacher⁺ = {teacher, course}.
R1 = (teacher, course) key: teacher
R2 = (student, teacher) key: (student, teacher)Both are in BCNF (both have just two attributes). The redundancy is gone: "Prof. Rao teaches DBMS" is now stored exactly once, and Prof. Iyer can be added to teacher_course without any student.
But where did student, course → teacher go? No table contains all three attributes, so no table can enforce it.
The Price of BCNF: Lost Dependencies
Let's see the consequence in MySQL. We build the BCNF tables from the original data, then insert one innocent-looking row:
CREATE TABLE teacher_course (
teacher VARCHAR(12) PRIMARY KEY,
course VARCHAR(10)
);
CREATE TABLE student_teacher (
student VARCHAR(10),
teacher VARCHAR(12),
PRIMARY KEY (student, teacher),
FOREIGN KEY (teacher) REFERENCES teacher_course(teacher)
);
INSERT INTO teacher_course SELECT DISTINCT teacher, course FROM stc;
INSERT INTO student_teacher SELECT student, teacher FROM stc;
-- Aman also joins Prof. Sen's batch
INSERT INTO student_teacher VALUES ('Aman', 'Prof. Sen');
SELECT st.student, tc.course, st.teacher
FROM student_teacher st
JOIN teacher_course tc ON tc.teacher = st.teacher
ORDER BY st.student, tc.course, st.teacher;Output:
+---------+--------+-----------+
| student | course | teacher |
+---------+--------+-----------+
| Aman | DBMS | Prof. Rao |
| Aman | DBMS | Prof. Sen |
| Aman | OS | Prof. Das |
| Bina | DBMS | Prof. Rao |
| Bina | OS | Prof. Das |
| Chirag | DBMS | Prof. Sen |
+---------+--------+-----------+Every individual table accepted the insert: ('Aman', 'Prof. Sen') is a new primary key value, and Prof. Sen exists in teacher_course. But the combined data now says Aman is in two DBMS batches, which breaks the rule student, course → teacher.
In the original stc table, the primary key (student, course) would have rejected this immediately. After decomposition, enforcing the rule needs a join, which means a trigger or application code.
BCNF decomposition is always lossless, but it is not always dependency preserving.
And for this stc example there is no BCNF decomposition that preserves student, course → teacher: any table containing all three attributes would contain teacher → course with teacher not a key.
3NF or BCNF: Which Should You Choose?
Property | 3NF | BCNF |
|---|---|---|
Condition on non-trivial X → A | X superkey or A prime | X superkey |
Redundancy due to FDs | Small amount possible | None |
Lossless decomposition always possible? | Yes | Yes |
Dependency-preserving decomposition always possible? | Yes | No |
Practical difference | The two differ only when a relation has overlapping composite candidate keys | |
The usual advice:
Aim for BCNF. In the large majority of real schemas, the 3NF design is already in BCNF.
If reaching BCNF would lose an important FD (like the
stccase), it is reasonable to stay in 3NF and accept a little redundancy, so the DBMS can enforce the rule with a key.
Handy Facts for Exams
1. Every relation with two attributes is in BCNF
Take R(A, B). The only possible non-trivial FDs are A → B and B → A.
If A → B holds, then A⁺ = {A, B}, so A is a superkey.
If B → A holds, then B is a superkey.
If neither holds, there are no non-trivial FDs at all.
In all cases, every left side is a superkey.
2. 3NF with only single-attribute keys implies BCNF
Suppose R is in 3NF and every candidate key is a single attribute. If X → A holds with X not a superkey, 3NF says A must be prime, so A is itself a candidate key. Then X → A → (everything), which makes X a superkey. Contradiction. So no violation exists and R is in BCNF.
That is why the 3NF/BCNF gap only shows up with composite, overlapping candidate keys.
3. A relation whose only key is all its attributes
If no non-trivial FD holds at all (every attribute together forms the only key), the relation is trivially in BCNF. It may still have multi-valued redundancy, which is what 4NF (next chapter) deals with.
Practice Problems
Q1. R(A, B, C), F = { AB → C, C → B }. Highest normal form?
Keys: AB and AC (A is in every key; (AB)⁺ = ABC; (AC)⁺ = ACB). All attributes are prime, so 3NF holds. C → B has C not a superkey, so BCNF fails. Answer: 3NF. BCNF decomposition: (C, B) and (A, C), which loses AB → C.
Q2. R(A, B, C, D), F = { A → BCD, BC → A }. Is R in BCNF?
A⁺ = ABCD, a superkey. (BC)⁺ = ABCD (BC → A, then A → BCD), a superkey. Every left side is a superkey. Yes, BCNF. (Keys: A and BC.)
Q3. R(A, B, C, D), F = { A → B, B → C, C → D }. Decompose into BCNF.
Key: A. B → C violates (B⁺ = BCD). Split into (B, C, D) and (A, B). In (B, C, D), C → D violates (C⁺ ∩ BCD = CD). Split into (C, D) and (B, C). Result: (A, B), (B, C), (C, D), all FDs preserved.
Q4. R(A, B, C, D, E), F = { A → B, BC → D, D → A }. Decompose into BCNF and say whether dependencies are preserved.
Keys: ACE, BCE, CDE (E and C are on no right side). A → B violates: split into (A, B) and (A, C, D, E). In (A, C, D, E), D → A holds and D is not a superkey: split into (A, D) and (C, D, E). Result: (A, B), (A, D), (C, D, E). BC → D is not preserved: no table contains B, C and D together, and the closure test in the Lossless Join & Dependency Preservation chapter confirms it cannot be derived from the FDs local to these tables.
Common Mistakes
Checking whether the left side is a candidate key "exactly". Any superkey on the left is fine.
Applying the 3NF exception. In BCNF, "the right side is prime" does not save an FD.
Testing a decomposed table with only the listed FDs. Use implied FDs (projection of F⁺).
Dropping X from the second table during decomposition. X must stay in both pieces; otherwise the join is lossy.
Assuming BCNF decomposition is unique. Picking violations in a different order can give a different (equally valid) set of tables.
Assuming BCNF keeps every FD. It can lose dependencies, as in the stc example.
Interview Questions
What is BCNF?
A relation is in BCNF if for every non-trivial FD X → Y, X is a superkey. In other words, only keys determine other attributes.
How is BCNF different from 3NF?
3NF allows X → A when X is not a superkey if A is a prime attribute. BCNF does not allow that exception, so BCNF is stricter.
Give an example of a relation in 3NF but not in BCNF.
R(student, course, teacher) with {student, course} → teacher and teacher → course. Keys are {student, course} and {student, teacher}, so all attributes are prime (3NF), but teacher is not a superkey (not BCNF).
Is BCNF decomposition always lossless? Always dependency preserving?
Always lossless, because each split is on X → Y with X a superkey of one piece. Not always dependency preserving, as the student–course–teacher example shows.
Why might a designer stop at 3NF instead of BCNF?
Because a 3NF decomposition that is both lossless and dependency preserving always exists, while BCNF may make an important FD impossible to enforce without joins or triggers.
Is every two-attribute relation in BCNF?
Yes. Any non-trivial FD on R(A, B) is A → B or B → A, and its left side is then a superkey.
Summary
BCNF: for every non-trivial X → Y, X must be a superkey.
BCNF = 3NF without the "right side is prime" exception, so BCNF ⊂ 3NF.
3NF and BCNF differ only when there are overlapping composite candidate keys.
Test the original relation using the FDs in F; test decomposed tables using implied FDs.
Decompose by splitting Ri into (X⁺ ∩ Ri) and Ri − (X⁺ − X); this is always lossless.
BCNF may lose dependencies; 3NF can always keep them. Choose based on which matters more.
In BCNF, every arrow starts from a key.