Plea for help - Normalisation Problem

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 - 08:10:31 CET