Menu
BCNF is an extension of 3NF and it is has more strict rules than 3NF. Also, it is considered to be more stronger than 3NF. This relation is in BCNF as it is already in 3Nf (there is no prime attribute deriving no prime attribute) and on the left hand side of the functional dependency there is a candidate key.
BCNF is a stronger form of normalization than 3NF because it eliminates the second condition for 3NF, which allowed the right side of the FD to be a prime attribute. Thus, every left side of an FD in a table must be a superkey. Every table that is BCNF is also 3NF, 2NF, and 1NF, by the previous definitions.
"If a table contains only one candidate key, the 3NF and the BCNF are equivalent."
Your pizza can have exactly three topping types:
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.
Using the BCNF definition
If and only if for every one of its dependencies X → Y, at least one of the following conditions hold:
and the 3NF definition
If and only if, for each of its functional dependencies X → A, at least one of the following conditions holds:
whereas
Where
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,
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.
(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.)
All good answers. To put it in simple language [BCNF] No partial key can depend on a key.
i.e No partial subset ( i.e any non trivial subset except the full set ) of a candidate key can be functionally dependent on some candidate key.
Answers by ‘smartnut007’, ‘Bill Karwin’, and ‘sqlvogel’ are excellent. Yet let me put an interesting perspective to it.
Well, we have prime and non-prime keys.
When we focus on how non-primes depend on primes, we see two cases:
Non-primes can be dependent or not.
When not dependent: there can be no-dependency or transitive dependency
What about dependencies among primes?
Now you see, we’re not addressing the dependency relationship among primes by either 2nd or 3rd NF. Further such dependency, if any, is not desirable and thus we’ve a single rule to address that. This is BCNF.
Referring to the example from Bill Karwin's post here, you’ll notice that both ‘Topping’, and ‘Topping Type’ are prime keys and have a dependency. Had they been non-primes with dependency, then 3NF would have kicked in.
Note:
The definition of BCNF is very generic and without differentiating attributes between prime and non-prime. Yet, the above way of thinking helps to understand how some anomaly is percolated even after 2nd and 3rd NF.
Advanced Topic: Mapping generic BCNF to 2NF & 3NF
Now that we know BCNF provides a generic definition without reference to any prime/non-prime attribues, let's see how BCNF and 2/3 NF's are related.
First, BCNF requires (other than the trivial case) that for each functional dependency
For case (1), 3NF takes care of.
For case (3), 2NF takes care of.
For case (2), we find the use of BCNFX -> Y (FD), X should be super-key.
If you just consider any FD, then we've three cases - (1) Both X and Y non-prime, (2) Both prime and (3) X prime and Y non-prime, discarding the (nonsensical) case X non-prime and Y prime.
This is an old question with valuable answers, but I was still a bit confused until I found a real life example that shows the issue with 3NF. Maybe not suitable for an 8-year old child but hope it helps.
Tomorrow I'll meet the teachers of my eldest daughter in one of those quarterly parent/teachers meetings. Here's what my diary looks like (names and rooms have been changed):
Teacher | Date | Room
----------|------------------|-----
Mr Smith | 2018-12-18 18:15 | A12
Mr Jones | 2018-12-18 18:30 | B10
Ms Doe | 2018-12-18 18:45 | C21
Ms Rogers | 2018-12-18 19:00 | A08
There's only one teacher per room and they never move. If you have a look, you'll see that:
(1) for every attribute Teacher, Date, Room, we have only one value per row.
(2) super-keys are: (Teacher, Date, Room), (Teacher, Date) and (Date, Room) and candidate keys are obviously (Teacher, Date) and (Date, Room).
(Teacher, Room) is not a superkey because I will complete the table next quarter and I may have a row like this one (Mr Smith did not move!):
Teacher | Date | Room
---------|------------------| ----
Mr Smith | 2019-03-19 18:15 | A12
What can we conclude? (1) is an informal but correct formulation of 1NF. From (2) we see that there is no "non prime attribute": 2NF and 3NF are given for free.
My diary is 3NF. Good! No. Not really because no data modeler would accept this in a DB schema. The Room attribute is dependant on the Teacher attribute (again: teachers do not move!) but the schema does not reflect this fact. What would a sane data modeler do? Split the table in two:
Teacher | Date
----------|-----------------
Mr Smith | 2018-12-18 18:15
Mr Jones | 2018-12-18 18:30
Ms Doe | 2018-12-18 18:45
Ms Rogers | 2018-12-18 19:00
And
Teacher | Room
----------|-----
Mr Smith | A12
Mr Jones | B10
Ms Doe | C21
Ms Rogers | A08
But 3NF does not deal with prime attributes dependencies. This is the issue: 3NF compliance is not enough to ensure a sound table schema design under some circumstances.
With BCNF, you don't care if the attribute is a prime attribute or not in 2NF and 3NF rules. For every non trivial dependency (subsets are obviously determined by their supersets), the determinant is a complete super key. In other words, nothing is determined by something else than a complete super key (excluding trivial FDs). (See other answers for formal definition).
As soon as Room depends on Teacher, Room must be a subset of Teacher (that's not the case) or Teacher must be a super key (that's not the case in my diary, but thats the case when you split the table).
To summarize: BNCF is more strict, but in my opinion easier to grasp, than 3NF:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With