Re: Plea for help - Normalisation Problem

From: André Gamache <andre.gamache_at_ift.ulaval.ca>
Date: Tue, 03 Dec 2002 08:27:15 -0500
Message-ID: <3DECB133.3060606_at_ift.ulaval.ca>


Hi:

1- The passage from 1NF to 2NF does not end up to the second relation CDs Borrowed(#LoanNo, #cdID). This relation is redondant. The primary key of the first relation should be a composite key (#LoanNo, #cdID).

2- The diagram tool does not often allow one designing with a composite key.

Keep going.
AG

skotske wrote:
> Hi Everyone,
>
> Hope this is the right place to post..... if not could anyone point me
> in the right direction? I had a scan of the google archive and saw
> simlilar posts so here goes:
>
> I've actually got two problems, one theorectical and one kinda
> practical... The theoretical one first....
>
> I did a bit of normalisation at uni and the way were were taught was
> that repeating groups were removed into a seperate relation to achieve
> 1FN (method one), but in several books i've bought to help with a
> learning project they use a different method of adding another
> qualifying attribute (method two) to the flat database creating a
> Compostite Key (thus repeating data in rows which I was taught not to
> do). To confuse things further I have another book which advocates the
> method I was originally taught.... which is the right method or are
> they interchangable....? (and have I got them right?)
>
> To illustrate the problem I'll show the relations at each stage (no idea
> what the correct way to represent an underline for the keys so I'll use
> a # before attribute name).
>
> It's a basic CD library application that I'm using as a learning project
> (i've also removed some attibutes like address1, 2, forename etc to save
> space here).
>
> Un-normalised:
>
> LoanNo. Surname, Telephone, E-mail, DateOut, DueBack
> Artist, Title, Label, DateBought (repeating groups)
>
> 1NF First Method:
>
> Loans(#LoanNo, Surname, Telephone, E-mail, DateOut, DueBack)
> CDs Borrowed(#LoanNo, #cdID, Artist, Title, Label, DateBought)
>
> We were taught to remove the repeating groups, create an attribute (in
> this case cdID) to replace the muliple values (Artist, Title,
> DateBought) that make up the qualifying value to the LoanNo.
>
> 1NF Second Method:
>
> Loans(#LoanNo, #cdID, Surname, Telephone, E-mail, DateOut,
> DueBack, Artist, Title, Label, DateBought)
>
> Where cdID is again the qualifying attribute.
>
> 2NF Both Methods
>
> Loans(#LoanNo, Surname, Telephone, E-mail, DateOut, DueBack)
> CDs Borrowed(#LoanNo, #cdID)
> CDs Bought(#cdID, Artist, Title, Label, DateBought)
>
> The second method required me to split the relation twice to get it the
> same is this right?
>
> 3NF Both Methods
>
> Loans(#LoanNo, BorrowerID, DateOut, DueBack)
> Borrower(#BorrowerID, Telephone, E-mail)
> CDs Borrowed(#LoanNo, #cdID)
> CDs Bought(#cdID, releaseID, DateBought)
> Released CDs(releaseID, Artist, Title, Label)
>
> The second more real problem was that I tried playing around with a
> diagramming tool ad it said that the CDs Borrowed relation didn't have a
> primary key.... and wont let you define a Composite Key as such. This
> suggests that somewhere in the above I've mucked it up? Although I
> can't see where and adding another specific Key to the relation would
> seem a bit redundant?
>
> Anyway hope this has been clear enough for someone to help.... i'm being
> asked more and more to put together databases as part of my job (always
> ducked it so far) and I'd prefer to get to grips with things properly so
> that I feel confident doing database work.
>
> TIA
>
> S
>
Received on Tue Dec 03 2002 - 14:27:15 CET

Original text of this message