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.