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/
Received on Thu Jul 21 2005 - 08:35:49 CEST
