Difference between 3NF and BCNF in simple terms (must be able to explain to an 8-year old)
Your pizza can have exactly three topping types:
- one type of cheese
- one type of meat
- one type of vegetable
So we order two pizzas and choose the following toppings:
Pizza Topping Topping Type
-------- ---------- -------------
1 mozzarella cheese
1 pepperoni meat
1 olives vegetable
2 mozzarella meat
2 sausage cheese
2 peppers vegetable
Wait a second, mozzarella can't be both a cheese and a meat! And sausage isn't a cheese!
We need to prevent these sorts of mistakes, to make mozzarella always be cheese. We should use a separate table for this, so we write down that fact in only one place.
Pizza Topping
-------- ----------
1 mozzarella
1 pepperoni
1 olives
2 mozzarella
2 sausage
2 peppers
Topping Topping Type
---------- -------------
mozzarella cheese
pepperoni meat
olives vegetable
sausage meat
peppers vegetable
That was the explanation that an 8 year-old might understand. Here is the more technical version.
BCNF acts differently from 3NF only when there are multiple overlapping candidate keys.
The reason is that the functional dependency X -> Y is of course true if Y is a subset of X. So in any table that has only one candidate key and is in 3NF, it is already in BCNF because there is no column (either key or non-key) that is functionally dependent on anything besides that key.
Because each pizza must have exactly one of each topping type, we know that (Pizza, Topping Type) is a candidate key. We also know intuitively that a given topping cannot belong to different types simultaneously. So (Pizza, Topping) must be unique and therefore is also a candidate key. So we have two overlapping candidate keys.
I showed an anomaly where we marked mozarella as the wrong topping type. We know this is wrong, but the rule that makes it wrong is a dependency Topping -> Topping Type which is not a valid dependency for BCNF for this table. It's a dependency on something other than a whole candidate key.
So to solve this, we take Topping Type out of the Pizzas table and make it a non-key attribute in a Toppings table.
The subtle difference is that 3NF makes a distinction between key and non-key attributes (also called non-prime attributes) whereas BCNF does not.
This is best explained using Zaniolo's definition of 3NF, which is equivalent to Codd's:
A relation, R, is in 3NF iff for every nontrivial FD (X->A) satisfied by R at least ONE of the following conditions is true:
(a) X is a superkey for R, or
(b) A is a key attribute for R
BCNF requires (a) but doesn't treat (b) as a special case of its own. In other words BCNF requires that every nontrivial determinant is a superkey even its dependent attributes happen to be part of a key.
A relation, R, is in BCNF iff for every nontrivial FD (X->A) satisfied by R the following condition is true:
(a) X is a superkey for R
BCNF is therefore more strict.
The difference is so subtle that what many people informally describe as 3NF is actually BCNF. For example, you stated here that 3NF means "data depends on the key[s]... and nothing but the key[s]", but that is really an informal description of BCNF and not 3NF. 3NF could more accurately be described as "non-key data depends on the keys... and nothing but the keys".
You also stated:
the 3NF quote explicitly says "nothing but the key" meaning that all attributes depend solely on the primary key.
That's an oversimplification. 3NF and BCNF and all the Normal Forms are concerned with all candidate keys and/or superkeys, not just one "primary" key.
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), or
- 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,
whereas
- 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).
Where
- 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)
Court Start Time End Time Rate Type
------- ---------- -------- ---------
1 09:30 10:30 SAVER
1 11:00 12:00 SAVER
1 14:00 15:30 STANDARD
2 10:00 11:30 PREMIUM-B
2 11:30 13:30 PREMIUM-B
2 15:00 16:30 PREMIUM-A
The table's superkeys are:
S1 = {Court, Start Time}
S2 = {Court, End Time}
S3 = {Rate Type, Start Time}
S4 = {Rate Type, End Time}
S5 = {Court, Start Time, End Time}
S6 = {Rate Type, Start Time, End Time}
S7 = {Court, Rate Type, Start Time}
S8 = {Court, Rate Type, End Time}
ST = {Court, Rate Type, Start Time, End Time}, the trivial superkey
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)
Rate Type Court Member Flag
--------- ----- -----------
SAVER 1 Yes
STANDARD 1 No
PREMIUM-A 2 Yes
PREMIUM-B 2 No
Today's Tennis Court Bookings (BCNF and the weaker 3NF, which is implied by BCNF)
Member Flag Court Start Time End Time
----------- ----- ---------- --------
Yes 1 09:30 10:30
Yes 1 11:00 12:00
No 1 14:00 15:30
No 2 10:00 11:30
No 2 11:30 13:30
Yes 2 15:00 16:30
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.)