You normalize a relation and split it, then a query returns rows that were never inserted. Or a familiar FD suddenly needs a join. Decomposition is not free: it must be lossless and should preserve dependencies. Each property has its own mechanical test, and the 3NF versus BCNF trade-off is exactly the case where a split passes the first and fails the second.
1. What a decomposition must guarantee
A decomposition replaces schema R with R1, R2, ..., Rn whose attribute union is R. Two properties decide whether the split is useful.
Lossless join: joining the decomposed relations recreates exactly the original relation. For a binary decomposition, R1 and R2 are lossless if
(R1 intersection R2) -> R1or(R1 intersection R2) -> R2holds in F+.Dependency preservation: the projected FDs inside the relations derive every FD in F, so constraints need no join.
A lossy decomposition creates extra, spurious tuples. It does not mean tuples go missing. A lost dependency makes constraint enforcement a multi-table operation.
2. Lossless-join decomposition, fully worked
Take R(A, B, C), F = {A -> B}, and this instance:
A | B | C |
|---|---|---|
a1 | b1 | c1 |
a2 | b1 | c2 |
The repeated b1 is what makes B a poor choice of common attribute. Split R into R1(A, B) = {(a1, b1), (a2, b1)} and R2(B, C) = {(b1, c1), (b1, c2)}. Each R1 row matches both R2 rows on B:
A | B | C | Result |
|---|---|---|---|
a1 | b1 | c1 | original |
a1 | b1 | c2 | spurious |
a2 | b1 | c1 | spurious |
a2 | b1 | c2 | original |
The join produces 2 x 2 = 4 tuples, including two never inserted. The common attribute B determines neither AB nor BC under F+, so the decomposition is lossy.
Now use R1(A, B) and R2(A, C) = {(a1, c1), (a2, c2)}. Their intersection is {A}. Since A -> B, A -> AB, all of R1. Joining on A returns exactly (a1, b1, c1) and (a2, b1, c2), so the split is lossless.
3. Dependency preservation on R(A, B, C) with a second FD
Now let F = {A -> B, B -> C}. Consider S1 with R1(A, B) and R2(A, C). It is lossless because A is common and A -> AB. The projected dependencies include F1 = {A -> B} and F2 = {A -> C}. However, under F1 union F2, the closure of B is only B+ = {B}. It does not contain C, so B -> C cannot be derived.
For a counter-instance, let R1 contain (a1, b1) and (a2, b1), while R2 contains (a1, c1) and (a2, c2). Each table satisfies its projected FDs. Their join gives b1 with c1 and c2, violating B -> C. S1 is not dependency preserving.
For S2, use R1(A, B) and R2(B, C). The intersection is {B}, and B -> BC, so the split is lossless. Its projections retain A -> B and B -> C, deriving all of F.
The general check is (F1 union F2 union ...)+ = F+. In an exam, test each original FD X -> Y by computing X+ under the union of the projected dependencies.
4. The 3NF versus BCNF trade-off
Consider R(Street, City, Zip) with F = {Street City -> Zip, Zip -> City}. Its candidate keys are {Street, City} and {Street, Zip}, so every attribute is prime. The relation is in 3NF. It is not in BCNF because Zip -> City has a determinant that is not a superkey.
Decomposing on that violation gives R1(Zip, City) and R2(Zip, Street). The split is lossless because Zip is common and Zip -> Zip City. But Street City -> Zip cannot be derived from Zip -> City and the trivial FDs in R2. The dependency is lost.
A lossless, dependency-preserving 3NF decomposition always exists through 3NF synthesis. A lossless BCNF decomposition always exists, but dependency preservation may be impossible. For the ladder behind this trade-off, read Normalization in DBMS from 1NF to BCNF.
5. Multivalued dependencies and 4NF
Consider CTB(Course, Teacher, Book):
Course | Teacher | Book |
|---|---|---|
DBMS | Sanchit | Korth |
DBMS | Sanchit | Navathe |
DBMS | Ankit | Korth |
DBMS | Ankit | Navathe |
Teachers and books are independent for a course. With 2 teachers and 2 books, 2 x 2 = 4 combinations appear. There are no nontrivial FDs, and the only key is {Course, Teacher, Book}. CTB is in BCNF, yet redundant.
The cause is Course ->> Teacher and Course ->> Book. Informally, for a fixed Course, teachers are independent of books. Formally, if two tuples agree on X, an MVD X ->> Y requires the two tuples formed by swapping their Y values while retaining the remaining values.
Every FD X -> Y is also an MVD X ->> Y. That is why 4NF is stronger than BCNF. A relation is in 4NF when, for every nontrivial MVD X ->> Y, X is a superkey.
Decompose into CT(Course, Teacher) = {(DBMS, Sanchit), (DBMS, Ankit)} and CB(Course, Book) = {(DBMS, Korth), (DBMS, Navathe)}. Four rows become 2 + 2. A third book needs one CB row instead of two CTB rows. Both relations are in 4NF, and their join restores all four combinations. Decomposition on an MVD is lossless.
6. Traps students actually fall into
Using the binary test on three relations at once. The common-attribute test is for a binary decomposition. Decompose two at a time and retest, or use the chase or tableau method.
Treating the two properties as equivalent. Section 3's S1 is lossless but not dependency preserving. Section 2's first split preserves its only FD, A -> B, inside R1 but is lossy.
Calling BCNF fully normalized. CTB is in BCNF and still redundant because of nontrivial MVDs.
Forgetting the nesting. 4NF implies BCNF, which implies 3NF, then 2NF, then 1NF. A relation cannot be in 4NF but not BCNF.
7. How GATE and interviews test decomposition
This topic appears under DBMS in the official GATE CS syllabus published by the year's organizing institute. Questions ask you to classify a split, count spurious tuples, identify the highest normal form with FDs and MVDs, or find the required relations under 3NF or BCNF decomposition.
Interviews often ask the design version: why was a table normalized or denormalized, and can you prove that a proposed split is lossless? Both answers come from the same two tests, run out loud rather than on paper. What is worth checking is whether you can run them cold and against the clock. The DBMS normalization MCQs turn the rules above into exactly that kind of timed verdict.
8. The short version, and the next step
For two relations, the split is lossless when their common attributes determine either whole relation.
For dependency preservation, project F onto each relation and test the original FDs using closures under the union.
3NF synthesis can keep losslessness and dependency preservation. BCNF cannot always keep both.
Every FD is an MVD, but an MVD need not be an FD.
In 4NF, the determinant of every nontrivial MVD must be a superkey.
For structured, subject-wise preparation, use GATE Guidance by Sanchit Sir. Then practise these closure, join, and verdict questions under time pressure in the GATE Test Series, Mocks & Topic-wise Tests. The GATE CS Exam Preparation page brings the wider course set together.
