We have seen BCNF and 3NF.- It is always possible to obtain a 3NF design without sacri cing lossless-join or dependency preservation.- If we do not eliminate all transitive dependencies, we may need to use null values torepresent some of the meaningful relationships.- Repetition of information occurs in 3NF.
These problems can be illustrated with the following exampleGrade_report(StudNo,StudName,(Major,Adviser,(CourseNo,Ctitle,InstrucName,InstructLocn,Grade)))
Functional dependenciesStudNo -> StudNameCourseNo -> Ctitle,InstrucNameInstrucName -> InstrucLocnStudNo,CourseNo,Major -> GradeStudNo,Major -> Advisor Advisor -> Major
1NF Remove repeating groupsStudent(StudNo,StudName)StudMajor(StudNo,Major,Advisor)StudCourse(StudNo,Major,CourseNo, Ctitle,InstrucName,InstructLocn,Grade)
2NF Remove partial key dependenciesStudent(StudNo,StudName)StudMajor(StudNo,Major,Advisor)StudCourse(StudNo,Major,CourseNo,Grade)Course(CourseNo,Ctitle,InstrucName,InstructLocn)
3NF Remove transitive dependenciesStudent(StudNo,StudName)
The difference between BCNF and 3NF
Using the BCNF definition
If and only if for every one of its dependencies X → Y, at least one of the following conditions hold:
- X → Y is a trivial functional dependency (Y ⊆ X), and
- X is a super key for schema R
and the 3NF definition
If and only if, for each of its functional dependencies X → A, at least one of the following conditions holds:
- X contains A (that is, X → A is trivial functional dependency), or
- X is a superkey, or
- Every element of A-X, the set difference between A and X, is a prime attribute (i.e., each attribute in A-X is contained in some candidate key)
We see the following difference, in simple terms:
- In BCNF: Every partial key (prime attribute) can only depend on a superkey,
- In 3NF: A partial key (prime attribute) can also depend on an attribute that is not a superkey (i.e. another partial key/prime attribute or even a non-prime attribute).
- A prime attribute is an attribute found in a candidate key, and
- A candidate key is a minimal superkey for that relation, and
- A superkey is a set of attributes of a relation variable for which it holds that in all relations assigned to that variable, there are no two distinct tuples (rows) that have the same values for the attributes in this set.Equivalently a superkey can also be defined as a set of attributes of a relation schema upon which all attributes of the schema are functionally dependent. (A superkey always contains a candidate key/a candidate key is always a subset of a superkey. You can add any attribute in a relation to obtain one of the superkeys.)
That is, no partial subset (any non trivial subset except the full set) of a candidate key can be functionally dependent on anything other than a superkey.
A table/relation not in BCNF is subject to anomalies such as the update anomalies mentioned in the pizza example by another user. Unfortunately,
- BNCF cannot always be obtained, while
- 3NF can always be obtained.
3NF Versus BCNF Example
An example of the difference can currently be found at "3NF table not meeting BCNF (Boyce–Codd normal form)" on Wikipedia, where the following table meets 3NF but not BCNF because "Tennis Court" (a partial key/prime attribute) depends on "Rate Type" (a partial key/prime attribute that is not a superkey), which is a dependency we could determine by asking the clients of the database, the tennis club:
Today's Tennis Court Bookings (3NF, not BCNF)
The table's superkeys are:
The 3NF problem: The partial key/prime attribute "Court" is dependent on something other than a superkey. Instead, it is dependent on the partial key/prime attribute "Rate Type". This means that the user must manually change the rate type if we upgrade a court, or manually change the court if wanting to apply a rate change.
- But what if the user upgrades the court but does not remember to increase the rate? Or what if the wrong rate type is applied to a court?
(In technical terms, we cannot guarantee that the "Rate Type" -> "Court" functional dependency will not be violated.)
The BCNF solution: If we want to place the above table in BCNF we can decompose the given relation/table into the following two relations/tables (assuming we know that the rate type is dependent on only the court and membership status, which we could discover by asking the clients of our database, the owners of the tennis club):
Rate Types (BCNF and the weaker 3NF, which is implied by BCNF)
Today's Tennis Court Bookings (BCNF and the weaker 3NF, which is implied by BCNF)
Problem Solved: Now if we upgrade the court we can guarantee the rate type will reflect this change, and we cannot charge the wrong price for a court.
(In technical terms, we can guarantee that the functional dependency "Rate Type" -> "Court" will not be violated.)