• James Goodwin (7/28/2011)


    Tom,

    Thanks for the series, it has been very interesting thus far.

    However, I think more distinction needs to be made between 2NF and 3NF. The example seems to be about 2NF. The Candidate key for the original table is City. The Phone number is not dependent upon City at all, it is dependent upon Group. I think that you are trying to show a transitive relationship from the Key to the phone number. But that relationship doesn't seem to exist in the example.

    The Wikipedia article also refers to Carlo Zaniolo's new definition of 3NF, which is provably equivalent to the original definition, and that was the definition I was using - it's much simpler, easier to explain, and easier to see the relationship between 3NF, EKNF, and BCNF when you use Zaniolo's definition. If you prefer to think in terms of the original definition, there is a transitive dependency there: City -> Group (because of the stated business rules that only one group has a depot in any given city - but anyway as (City) is a candidate key we know that City -> Group is an FD of this relation) and Group -> Phone number (because of the stated business rule that each group uses a single order phone number); and the partition of the table represents the Group -> Phone number FD in a separate table, removing Phone number from the original table to eliminate the transitive FD.

    In the wikipedia example of 3NF http://en.wikipedia.org/wiki/Third_normal_form this transitivity is expressed by having two overlapping candidate keys, one of which determines the non-prime attribute, but the other doesn't. This seems to imply that the difference between 2NF and 3NF is that in 2NF non-prime attributes need to depend on A candidate key, while in 3NF the non-prime attributes need to depend on ALL candidate keys.

    Even in 1NF every attribute, prime or not, is dependent on every superkey (that's the definition of a superkey) and hence on every candidate key (as a candidate key is a minimal superkey). The difference between 2NF and 3NF is that 2NF does not permit any non-prime attribute to have a dependency on something which is a proper subset of a candidate key, while in 3NF a non-prime attribute may not depend on anything which is not a superkey (2NF says "the whole key" while 3NF says "nothing but the key").

    So, can you please expand on this and clear up my confusion?

    Thanks,

    JimFive

    I hope the above is clear enough - if not, get back to me and I'll try again.

    Regards

    Tom

    Tom