tutoring
| student | subject | tutor |
|---|---|---|
| Ada | Math | Ines |
| Ben | Math | Ines |
| Ada | Physics | Karl |
| Cleo | Math | Luis |
(student, subject) → tutor
tutor → subject
candidate keys: (student, subject) and (student, tutor)Every attribute is prime, so the table is in 3NF. But tutor → subject has a determinant that is not a superkey: "Ines teaches Math" is stored once per student of Ines.
A table is in (BCNF) when, for every nontrivial FD X → Y, X is a superkey. In short: every determinant is a candidate key (or contains one).
| X is a superkey | or A is prime | |
|---|---|---|
| 3NF | allowed | allowed |
| BCNF | allowed | not allowed |
Decompose
For an FD X → Y that breaks BCNF, split R into:
X ∪ Y: here(tutor, subject), keytutor,R − (Y − X): here(student, tutor), key(student, tutor).
The split is lossless: the common attribute tutor is the key of one part, so the natural join gives back exactly the original rows.
The cost
The FD (student, subject) → tutor now spans two tables. No key can enforce it: nothing stops Ada from getting two Math tutors. A decomposition that keeps every FD inside one table is dependency preserving. 3NF can always be reached with one; BCNF sometimes cannot. That is why designers sometimes stop at 3NF.