Re: 2NF There are two Definitions which is right

Jonathan Leffler wrote:
> 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.)

R(A,F) means a Relation R with attributes A and functional dependencies F
> 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.

This definition (the second) is from Elmasri/Navate Fundamentals of Database Systems Second Edition Page 411. On Page 408 in the same book the authors state:

An attribute of relation schema R is called a prime attribute of R if it is a member of some candidate key of R. An attribute is called nonprime if it is not a prime attribute—that is, if it is not a member of any candidate key.

A superkey of a relation schema is a set of attributes S part of R with the property that no two tuples t1 and t2 in any legal relation state r of R will have t1[S] = t2[S]. A key K is a superkey with the additional property that removal of any attribute from K will cause K not to be a superkey any more. The difference between a key and a superkey is that a key has to be minimal; that is, if we have a key K={A1,A2,....Ak} of R, then K - {Ai} is not a key of R for any i, l <= i <= k.

If a relation schema has more than one key, each is called a candidate key. One of the candidate keys is arbitrarily designated to be the primary key, and the others are called secondary keys

According to this definition there is a difference between the two definitions.

> 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.

A->D tells us that D is only dependant on A and so only on a part of the primary key.

>
> 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 Ullmans definition of 3NF:
A relation R is in third normal form in whenever X -> A holds in R and A is not in X, then either X is a candidate key for R, or A is prime attribute

it is in 3NF since:

A->D holds 3NF since D is prime attribute AB->C holds 3NF because AB is candidate key C->D holds 3NF because D is prime attribute D->A holds 3NF because A is prime attribute

> 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?

Well
since C->D and D->A with Armstrongs 3. Axiom we get C->A that gives us C->DA and with Armstrongs 2. Axiom we get BC->BDA, so BC is a candidate key.

since D->A with Armstrongs 2. Axiom we get BD->AB and with AB->C with Armstrongs 3. Axiom we get BD->C so we get BD->ABC, so BD also is a candidate key.

The hole problem i am facing, is that the first definition is from a german script and the second is from an english book (Elmasri/Navathe) and my thougt is, that there is a confusion between "Primary key" and "prime".

Jens Received on Thu Jul 21 2005 - 09:40:36 CEST