Boyce-Codd Normal Form (BCNF / 3.5NF)
This is an extension of 3NF and it is sometime treated as 3.5NF. This makes the 3NF more stronger by making sure that every non-primary-key determinant is a candidate key with identified functional dependencies.
A database table is in BCNF if and only if there are no non-trivial functional dependencies of attributes on anything other than a superset of a candidate key.What does it exactly means? You have already seen that we can identify many functional dependencies in a relation and we pick one for defining the primary key. The determinants of other identified functional dependencies can be candidate keys for the primary key or they might not be qualified for the primary key.
The determinant of a functional dependency is the attribute or group of attributes on the left-hand side of the arrow in the functional dependency, whereas the consequent of a functional dependency is the attribute or group of attributes on the right-hand side of the arrow.
| If you see that all determinants are qualified, means you can mark them as the primary key if need, then your relation (table) is in BCNF. Take this example. Assume that business rules related to this relation are as follows: |
|
- Course has one or more subjects.
- Course is managed by one or more lecturers.
- Subject is taught by one or more lecturers.
- Lecturer teaches only one subject.