2nd Normal Form Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gamehack - 08 Feb 2008 16:21 GMT

Hi all,

I'm currently evaluating whether a relation is in 2NF. The relation is
defined as follows:
<Year | Winner Name | Winner Votes | Party | Home State> in the
context of an election. I've given a sample relation below.
1946 | MyName | 453 | MyParty | California
The primary key for this relation is 'Year'.

Now the question is whether this relation is in 2NF? What confuses me
is that some books say the following:
"Note that when a 1NF table has no composite candidate keys (candidate
keys consisting of more than one attribute), the table is
automatically in 2NF."

Now, let's evaluate the functional dependencies for this relation.
Year -> Winner Name
Year -> Winner Votes
Winner Name -> Party
Winner Name -> Home State

The definition for 2NF is as follows: "A 1NF table is in 2NF if and
only if, given any candidate key and any attribute that is not a
constituent of a candidate key, the non-key attribute depends upon the
whole of the candidate key rather than just a part of it." (Wikipedia)
which is consistent with the books I'm reading on databases.

Now as this relation doesn't have a composite primary key, it follows
that it is automatically in 2NF. But as we can see two non-prime
attributes (party & home state) are functionally dependent only a
subset of the primary key (the subset being the empty set). This must
imply that it is not in 2NF.

My question is, is this relation is 2NF or not?

Thanks,
g

Reply to this Message

Jan Hidders - 08 Feb 2008 16:39 GMT

> Hi all,
>
[quoted text clipped - 25 lines]
> Now as this relation doesn't have a composite primary key, it follows
> that it is automatically in 2NF.

A small note here: you should check if there is a composite
*candidate* key. It is in principle possible that the candidate key
you picked as primary keys is not composite while at the same time
there is another candidate that *is* composite, in which case the
relation could very well not be in 2NF.

> But as we can see two non-prime
> attributes (party & home state) are functionally dependent only a
> subset of the primary key (the subset being the empty set). This must
> imply that it is not in 2NF.

They are not dependent on the empty set, but on the set {"Winner
Name"}, which is clearly not a proper subset of a candidate key. So no
2NF violation there.

-- Jan Hidders

Reply to this Message

gamehack - 08 Feb 2008 16:55 GMT

> [snip]
>
[quoted text clipped - 6 lines]
> Name"}, which is clearly not a proper subset of a candidate key. So no
> 2NF violation there.

But isn't it a requirement for 2NF that all non-prime attribs (party &
home state) _have to be_ dependent on the whole of a candidate key (as
we only have 1 candidate key here which is the primary key) which is
not the case in this relation. Am I wrong?

> -- Jan Hidders

Thanks,
g

Reply to this Message

Jan Hidders - 08 Feb 2008 18:30 GMT

> > [snip]
>
[quoted text clipped - 11 lines]
> we only have 1 candidate key here which is the primary key) which is
> not the case in this relation. Am I wrong?

Sorry, but yes, you are wrong. The attributes "Party" and "Home State"
are both dependent on "Year". Note that "dependent on" means here that
there is a functional dependency. It indeed holds that "Year"--

>"Party" and "Year"-->"Home State". This is not in contradiction with

the fact that it is also true that "Winner Name"-->"Party" and "Winner
Name"-->"Home State". An attribute can be dependent on several
different sets of attributes.

-- Jan Hidders

Reply to this Message

Bob Badour - 08 Feb 2008 16:39 GMT

> Hi all,
>
[quoted text clipped - 10 lines]
> keys consisting of more than one attribute), the table is
> automatically in 2NF."

I am not sure where you read that. It sounds like a typo or a mistake.
Composite keys are important at the higher normal forms.

Reply to this Message

Jan Hidders - 08 Feb 2008 16:59 GMT

> > Hi all,
>
[quoted text clipped - 13 lines]
> I am not sure where you read that. It sounds like a typo or a mistake.
> Composite keys are important at the higher normal forms.

Of course, but all that it says is that if you have determined all the
candidate keys and they happen all to be not-composite then a 1NF is
always also in 2NF. I'm sure you agree that this is correct.

-- Jan Hidders

Reply to this Message

Bob Badour - 08 Feb 2008 17:41 GMT

>>>Hi all,
>>
[quoted text clipped - 19 lines]
>
> -- Jan Hidders

What can I say? I was tired. Your answer was so much better than mine I
cancelled mine almost immediately.

Sigh. Alas, you have immortalized it.

Reply to this Message

David Portas - 08 Feb 2008 20:08 GMT

>> > Hi all,
>>
[quoted text clipped - 19 lines]
>
> -- Jan Hidders

Here's a counter example. PatientId is the candidate key but the relation is
not in 2NF. This is an unusual case but I don't think it should be ignored.

Pregnancies {PatientId, Gender, DueDate}

FDs:
{PatientId} -> {DueDate}
{} -> {Gender}

Signature

David Portas

Reply to this Message

paul c - 08 Feb 2008 21:00 GMT

...

> Here's a counter example. PatientId is the candidate key but the relation is
> not in 2NF. This is an unusual case but I don't think it should be ignored.
[quoted text clipped - 4 lines]
> {PatientId} -> {DueDate}
> {} -> {Gender}

If you have {} -> {Gender} don't you also have {PatiendId} -> {Gender,
DueDate} ?

Reply to this Message

David Portas - 08 Feb 2008 22:48 GMT

> ...
>> Here's a counter example. PatientId is the candidate key but the relation
[quoted text clipped - 9 lines]
> If you have {} -> {Gender} don't you also have {PatiendId} -> {Gender,
> DueDate} ?

Yes.

Signature

David Portas

Reply to this Message

Jan Hidders - 08 Feb 2008 22:26 GMT

On 8 feb, 21:08, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote:

> >> > Hi all,
>
[quoted text clipped - 20 lines]
> Here's a counter example. PatientId is the candidate key but the relation is
> not in 2NF. This is an unusual case but I don't think it should be ignored.

Yeah, yeah, yeah. I know, I know. I wanted to give Brian Selzer the
pleasure of correcting me on this twice, but you beat him to it. :-)

-- Jan Hidders

Reply to this Message