Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!fu-berlin.de!uni-berlin.de!individual.net!not-for-mail
From: Jens Haase <jens@haase.to>
Newsgroups: comp.databases.theory
Subject: Re: 2NF There are two Definitions which is right
Date: Thu, 21 Jul 2005 09:40:36 +0200
Lines: 91
Message-ID: <3k91rlFt11n8U1@individual.net>
References: <3k7t18Ft6e3bU1@individual.net> <9nHDe.1570$0C.822@newsread3.news.pas.earthlink.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
X-Trace: individual.net 9W777qhVv3Dl1toJ1Y3WKAbT+K554CwsoWnJ4iQw1ykrlMa4Tr
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
X-Accept-Language: de-DE, de, en-us, en
In-Reply-To: <9nHDe.1570$0C.822@newsread3.news.pas.earthlink.net>
Xref: dp-news.maxwell.syr.edu comp.databases.theory:32395

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