Beyond 3NF, what is Boyce-Codd Normal Form (BCNF)? Explain what kind of anomaly it solves and provide an example.
Go & Rust interview question for Advanced practice.
Answer
Boyce-Codd Normal Form (BCNF) is a higher level of database normalization than Third Normal Form (3NF). A table is in BCNF if, for every one of its non-trivial functional dependencies X → Y, X is a superkey (i.e., X is either a candidate key or a superset of a candidate key). Anomaly Solved by BCNF: BCNF addresses certain anomalies that can still exist in a table that is in 3NF. These anomalies typically occur when a table has multiple overlapping candidate keys. Example: Consider a table that tracks which professor teaches which subject in which department: (Professor, Subject, Department). Assume the following business rules: 1. Each professor teaches only one subject. 2. Each department offers multiple subjects, but for each subject, there is only one professor teaching it. From these rules, we get two functional dependencies: Professor → Subject Subject → Professor This means both (Professor, Department) and (Subject, Department) can serve as candidate keys. Now, let's add another dependency: Subject → Department. For example, 'History' is only taught by the 'Arts' department. The table is still in 3NF because Department is part of a candidate key. However, it is not in BCNF because the dependency Subject → Department exists, and Subject is not a superkey on its own. This can lead to an update anomaly: if the department for a subject changes, you have to update multiple rows corresponding to every professor teaching that subject. To resolve this and bring the schema to BCNF, you would decompose the table into two: (Professor, Subject) and (Subject, Department).
Explanation
Every table in BCNF is also in 3NF. However, a table in 3NF is not necessarily in BCNF.