Give a table that is in Third Normal Form but violates Boyce-Codd Normal Form, and explain what structural feature makes that possible.

## The one-clause difference between the two forms Both normal forms are stated over non-trivial functional dependencies X -> A. - **BCNF:** X must be a superkey. Full stop. - **3NF:** X must be a superkey **or** A must be a *prime attribute* — a column belonging to at least one candidate key. Everything in BCNF is therefore in 3NF, and the only tables that separate them are those with a non-superkey determinant pointing at a prime attribute. For a prime attribute to be determined by something that is not a superkey, the table must have more than one candidate key and those keys must share at least one column. That structural feature — **overlapping composite candidate keys** — is the whole answer to "what makes the gap possible". ## The canonical example, worked Relation `enrollment(student, course, teacher)` under two business rules: 1. Every course is taught by exactly one teacher: `course -> teacher`. 2. For a given course, a student is taught by exactly one teacher: `(student, course) -> teacher`. Because a teacher teaches only their own courses, `(student, teacher) -> course` also holds. Compute closures: `(student, course)` closes to all three columns, and so does `(student, teacher)`. Both are minimal, so both are candidate keys, and they overlap on `student`. Every column is prime. Now test `course -> teacher`. Is `course` a superkey? No — its closure is `{course, teacher}`, which misses `student`. So BCNF is violated. Is `teacher` prime? Yes, it is in the key `(student, teacher)`. So 3NF is satisfied. The table sits exactly in the gap. ## The redundancy is not theoretical With thirty students enrolled in *Databases*, the pair (Databases, Kim) is stored thirty times. If Kim hands the course to Lee, all thirty rows must change together; a partial update leaves the table asserting two teachers for one course, which the business rules say is impossible. You also cannot record who teaches a brand-new course until the first student enrolls, and dropping the last student erases the assignment. These are the classic update, insertion and deletion anomalies — 3NF simply does not promise to remove them when the redundant column happens to be prime. ## A smaller example to keep in your pocket `address(street, city, zip)` with `zip -> city` and `(street, city) -> zip`. Candidate keys: `(street, city)` and `(street, zip)`, overlapping on `street`. `zip -> city` violates BCNF because `zip` is not a superkey, while 3NF is happy because `city` is prime. The redundancy: the mapping from a postcode to its city is repeated on every address in that postcode. ## Fixing it Decompose on the violating dependency. For the enrollment case: `course_teacher(course PRIMARY KEY, teacher)` and `enrollment(student, course)` with `(student, course)` as the key. Now each course-teacher fact is stored once, and the join on `course` reconstructs the original relation exactly — the split is lossless because `course` is a key of the first table. The cost shows up immediately: the dependency `(student, teacher) -> course` no longer lives inside any single table, so the database can no longer enforce it with a key alone. That loss of **dependency preservation** is the standard reason a designer may consciously stop at 3NF, and it is the natural next question in an interview. ## How to recognise the situation in a real schema The smell is a table whose natural key you could write two different ways, both composite, both sharing a column — typically a junction table that has quietly absorbed an attribute belonging to one of its parents. `enrollment` acquired `teacher`, which is really a property of `course`. Pushing the attribute back to the entity that owns it is the same move as the BCNF decomposition, arrived at by modelling instinct instead of dependency algebra.