Difference between 3NF and BCNF in simple terms must be able to explain to an 8-year old

Question

I have read the quote   data depends on the key  1NF   the whole key  2NF  and nothing but the key  3NF     However  I am having trouble understanding 3 5NF or BCNF as it s called  Here is what I understand     BCNF is stricter than 3NF left side of any FD in the table must be a superkey  or at least a candidate key    So why is it then  that some 3NF tables are not in BCNF  I mean  the 3NF quote explicitly says  nothing but the key  meaning that all attributes depend solely on the primary key  The primary key is  after all  a candidate key until it is chosen to be our primary key   If anything is amiss regarding my understanding so far  please correct me and thanks for any help you can provide

User · Accepted Answer

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.

User · Answer

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    in most of cases  BCNF is identical to 3NF  in other cases  BCNF is what you think hope 3NF is

User · Answer

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

User · Answer

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 dependent  we see they must depend on a full candidate key  This is 2NF  When not dependent  there can be no-dependency or transitive dependency   Not even transitive dependency  Not sure what normalization theory addresses this   When transitively dependent  It is deemed undesirable  This is 3NF     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  amp  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 X - gt  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  For case  1   3NF takes care of  For case  3   2NF takes care of  For case  2   we find the use of BCNF

User · Answer

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

User · Answer

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

[database] Difference between 3NF and BCNF in simple terms (must be able to explain to an 8-year old)

Examples related to database

Examples related to relational-database

Examples related to database-normalization

Examples related to 3nf