Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!elnk-pas-nf1!newsfeed.earthlink.net!stamper.news.pas.earthlink.net!newsread3.news.pas.earthlink.net.POSTED!90a49e3f!not-for-mail
From: Jonathan Leffler <jleffler@earthlink.net>
Organization: Randomly Disorganized
User-Agent: Mozilla Thunderbird 1.0.5 (Macintosh/20050711)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: comp.databases.theory
Subject: Re: 2NF There are two Definitions which is right
References: <3k7t18Ft6e3bU1@individual.net>
In-Reply-To: <3k7t18Ft6e3bU1@individual.net>
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit
Lines: 65
Message-ID: <9nHDe.1570$0C.822@newsread3.news.pas.earthlink.net>
Date: Thu, 21 Jul 2005 06:35:49 GMT
NNTP-Posting-Host: 66.245.5.56
X-Complaints-To: abuse@earthlink.net
X-Trace: newsread3.news.pas.earthlink.net 1121927749 66.245.5.56 (Wed, 20 Jul 2005 23:35:49 PDT)
NNTP-Posting-Date: Wed, 20 Jul 2005 23:35:49 PDT
Xref: dp-news.maxwell.syr.edu comp.databases.theory:32394

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@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2005.01 -- http://dbi.perl.org/