0

This a follow up on the answer Difference between 3NF and BCNF in simple terms (must be able to explain to an 8-year old). Why is the following table in 3NF? Pizaa,Topping is the candidate key and Topping type is partially dependent on Topping, hence it is not even in 2NF. 

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

Please explain in detail.

Community
  • 1
  • 1
Srinivas
  • 332
  • 4
  • 18
  • What do you mean Topping Type is partially dependent on Topping? A partial dependency means there's a functional dependency x -> y where x is a proper part of candidate key. A functional dependency x -> y means that for any given x there's a single value for y. I see no such pattern between Topping and Topping Type, or for that matter between Pizza and Topping Type. – reaanb Sep 16 '15 at 18:00
  • Pizza and Topping is the primary key. mozarella can't be of Topping type meat. Hence, the given table has inconsistency and has to be normalized further. Given Topping mozarella, Topping type can be uniquely identified as cheese. Hence topping type is partially dependent on topping. – Srinivas Sep 17 '15 at 07:51
  • 1
    Normalization is based on the assumption that the data is consistent. If not, repair it before considering normalization. – reaanb Sep 17 '15 at 18:04
  • 1
    Normalization is not based on any such assumption. A motivation for normalization is to ensure correctness of data through proper design. It is up to the database designer to decide what the integrity rules should be. – nvogel Sep 19 '15 at 16:28
  • 2
    @Srinivas From your comment here about "normalized further" and "partially dependent" and below about "prime attribute" you don't seem to be learning & using the *definitions* of these *technical terms*. – philipxy Sep 24 '15 at 03:52
  • @philipxy: Any help would be great. I used MSDN and silberschatz text book. Not sure if I'm understanding it right though. – Srinivas Sep 25 '15 at 09:13
  • 1
    @Srinivas I've only ever seen trash on vendor sites, and most of SO database introduction, foundations and theory is nonsense too. (Good are sqlvogel & Mike Sherrill 'Cat Recall'.) Wikipedia too, although 2NF+ are not horrible. There are a few texts online, and lecture notes/slides from universities/colleges, eg stanford.edu. You need to memorize definitions then force yourself to use words precisely according to them. A lot of common database stuff is lurking in the exercises in Silberschatz et al instead of the main text. – philipxy Sep 25 '15 at 09:54

1 Answers1

2

The answer you linked to states that there are two keys of this table: (Pizza,Topping) and (Pizza,Topping Type). The dependency Topping->Topping Type does not violate 2NF or 3NF because Topping Type is a prime attribute (part of a candidate key). 2NF and 3NF would be violated only if Topping Type was non-prime.

This example shows the essential difference between BCNF and 3NF. BCNF is concerned with partial or non-key dependencies for all attributes, not just non-prime attributes.

nvogel
  • 24,981
  • 1
  • 44
  • 82
  • While considering partial dependency, shouldn't a prime(or a non-prime) attribute be relative to the chosen candidate key? Say, if (Pizza, Topping) is my candidate key, shouldn't Topping Type be non prime relative to the chosen candidate key? – Srinivas Sep 20 '15 at 05:11
  • 1
    No. An attribute is prime if it is part of any candidate key, otherwise non-prime. – nvogel Sep 20 '15 at 10:40