I've browse the quote : data is dependent around the key [1NF], the entire key [2NF] and absolutely nothing the answer [3NF].

However, I'm getting trouble understanding 3.5NF or BCNF as it is known as. Here's what I realize :

  • BCNF is more stringent than 3NF
  • left side associated with a FD within the table should be a superkey (or atleast an applicant key)

So why do then, that some 3NF tables aren't in BCNF? I am talking about, the 3NF quote clearly states "nothing the answerInch and therefore all characteristics depend exclusively around the primary key. The main secret is in the end an applicant key until it's selected to become our primary key.

Contrary is amiss regarding my understand to date, please correct me and thank you for any assist you to can offer.

Your pizza might have exactly three topping types:

  • one sort of cheese
  • one sort of meat
  • one sort of vegetable

Therefore we order two pizzas and select the next toppings:

Pizza    Topping    Topping Type
-------- ---------- -------------
1        mozarella  cheese
1        pepperoni  meat
1        olives     vegetable
2        mozarella  meat
2        sausage    cheese
2        peppers    vegetable

Wait another, mozarella can not be both a cheese along with a meat! And sausage is not a cheese!

We have to prevent these kinds of mistakes, to create mozarella always be cheese. We ought to make use of a separate table with this, therefore we write lower this in just one place.

Pizza    Topping
-------- ----------
1        mozarella
1        pepperoni
1        olives
2        mozarella 
2        sausage
2        peppers

Topping    Topping Type
---------- -------------
mozarella  cheese
pepperoni  meat
olives     vegetable
sausage    meat
peppers    vegetable

Re your comment:

Your question needed that the 8-year-old could comprehend the explanation. :-)

BCNF functions in a different way from 3NF only if you will find multiple overlapping candidate secrets.

This is because the running dependency X -> Y is obviously true if Y is really a subset of X. So in almost any table which has just one candidate key and it is in 3NF, it's already in BCNF because there's no column (either key or non-key) that's functionally determined by anything on top of that key.

Because each pizza should have exactly among each topping type, we all know that (Pizza, Topping Type) is really a candidate key. We know without effort that the given topping cannot fit in with different kinds concurrently. So (Pizza, Topping) should be unique and for that reason is another candidate key. Therefore we have two overlapping candidate secrets.

I demonstrated an anomaly where we marked mozarella because the wrong topping type. We all know this really is wrong, however the rule that causes it to be wrong is really a dependency Topping -> Topping Type which isn't a legitimate dependency for BCNF with this table. It is a reliance upon something apart from an entire candidate key.

To solve this, we take Topping Type from the Pizzas table and turn it into a non-key attribute inside a Toppings table.