Detailed explanation
Boyce-Codd Normal Form (BCNF) is a relational database normal form that strengthens Third Normal Form (3NF). A relation is in BCNF if, for every non-trivial functional dependency X → Y that holds, X is a superkey. Non-trivial means Y is not a subset of X; a superkey is any set of attributes that uniquely identifies a row. BCNF matters because dependencies whose determinants are not superkeys can cause repeated facts and insertion, update, or deletion anomalies.
The difference from 3NF is important in interviews. For every non-trivial dependency X → A, where A is a single attribute, 3NF permits either X to be a superkey or A to be a prime attribute—an attribute belonging to at least one candidate key. BCNF removes that second allowance. Therefore, every BCNF relation is in 3NF, but a 3NF relation can violate BCNF, particularly when candidate keys overlap. Normalisation must be evaluated against business-rule dependencies, not merely patterns visible in sample rows.
Consider teacher_subject(student, subject, teacher). Assume each teacher teaches exactly one subject, giving teacher → subject, and each student has at most one teacher per subject, giving (student, subject) → teacher. The candidate keys are (student, subject) and (student, teacher). The relation satisfies 3NF because every attribute is prime, but violates BCNF because teacher does not determine student and therefore is not a superkey. A teacher's subject is repeated across student assignments, so changing it requires multiple updates; deleting the last assignment can also lose that teacher's subject information.
Decompose it into teacher(teacher, subject) and student_teacher(student, teacher). Both relations are in BCNF under these assumptions. The decomposition is lossless: their shared attribute, teacher, determines the entire teacher relation, so joining reconstructs the original relation without spurious rows. However, it does not preserve (student, subject) → teacher for enforcement within individual tables. Two teachers of the same subject could be assigned to one student unless additional cross-table enforcement is provided. Interviewers often expect this trade-off: BCNF decomposition can be lossless without being dependency-preserving.
Key points to remember
- BCNF requires the determinant of every non-trivial functional dependency to be a superkey.
- BCNF is stricter than 3NF because it has no exception for prime attributes.
- Find candidate keys using functional dependencies and attribute closures before checking BCNF.
- In the example, teacher → subject violates BCNF because teacher cannot uniquely identify a student assignment.
- Lossless BCNF decomposition is always possible, but dependency preservation is not guaranteed.
Common mistakes candidates make
- Saying every determinant must be a candidate key rather than a superkey, or forgetting the non-trivial dependency condition.
- Claiming the example is in 3NF without stating the business rules and identifying its candidate keys.
- Assuming a lossless decomposition automatically preserves all dependencies for local constraint enforcement.
Likely follow-up questions
How do you check whether a determinant is a superkey?
Compute its attribute closure using the given functional dependencies. If that closure includes every attribute in the relation, the determinant is a superkey.
Why is the example's decomposition lossless?
The two relations intersect on teacher, and teacher → subject makes teacher a key of the teacher relation. For a binary decomposition, having the common attributes determine either component guarantees a lossless join.
When might you choose 3NF instead of BCNF?
Choose 3NF when preserving dependencies for enforcement within individual tables is more important than eliminating the remaining dependency-related redundancy. A lossless, dependency-preserving 3NF decomposition can always be constructed, whereas BCNF cannot always guarantee both properties.
Interview tip: State the formal rule, identify the violating dependency in the example, and finish with the lossless-versus-dependency-preserving trade-off.