Re: 2NF There are two Definitions which is right

Jens Haase wrote:
> I found two definitions for 2NF:
>
> 1: A relation R(A,F) is in 2NF, when every attribute not belonging to
> the primary key of R is fully functionally dependent on the primary key
> of R

Can you give a hint as to the significance of the parenthesized "(A,F)"; neither A nor F is referenced in the definition quoted. I don't think it alters the answer, but I'm curious. (The comma after 2NF should be omitted too.)

> 2: A relation schema R is in 2NF if every nonprime attribute A in R is
> fully functionally dependent on the primary key of R

Assuming 'nonprime attributes' are the attributes that do not belong to the primary key, it seems to me that the two definitions are the same.

> According to the first definition the following schema would not be in
> 2NF, according to the second it would be:
>
> R = (A,B,C,D)
>
> with:
> A->D
> AB->C
> C->D
> D->A
>
> According to the first definition when the primary key is AB, then A->D
> violates 2NF, because D is not part of the primary key.

I agree that AB is the primary key - or at least a valid primary key. A->D itself cannot violate 2NF; it is a functional dependency, and relation schemas are what satisfy or violate 2NF. And the reasoning the that D is not part of the primary key is bogus too.

As I see it, AB->C and C->D implies that AB->D so the primary key determines both C and D, as required. Granted, it is neither in 3NF nor BCNF - the mnemonic is "the key, the whole key, and nothing but the key", and the functional dependency A->D means that 'the whole key' part of the rule is violated. (The C->D part is also problematic, but doesn't cause the relation schema to violate 2NF.)

> According to the second definition R is in 2NF because the candidate
> keys are AB, BC, BD and since that, every attribute is prime attribute,
> so there is no violation of 2NF.

Now you introduce a term - candidate key - not mentioned in the definitions of 2NF that we're dissecting. I would dispute that BC is a candidate key anyway; likewise BD. Given a relation schema R { A, B, C, D, E } with candidate key AB, by definition, AB->CDE (and, by the rules of trivial functional dependencies (or FDs), AB->ABCDE). That is, a candidate key functionally determines every attribute in the relation schema. How do you apply Armstrong's Axioms to the relation schema and list of FDs to deduce that BC->AD?

> Which definition is rigth?

Both - they're the same.

-- 
Jonathan Leffler                   #include <disclaimer.h>
Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com
Guardian of DBD::Informix v2005.01 -- http://dbi.perl.org/