Re: Plea for help - Normalisation Problem

In article <vRaH9.2806$8e2.1139_at_news-binary.blueyonder.co.uk>, skotske <skotske_at_hotmail.com> wrote:
>André Gamache wrote:
>> 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).
>>
>
>Thanks for replying - do you mind if I ask a follow-up?
>
>The first method that removes the repeating group to a new relation
>specifies (and I'm quoting directly from the book here)
>
>* remove the repeating items from the original table and form them into a
> new table
>* create a new attribute of the table which is the key of the original
> table
>* make the primary key of the new table this key value together with a
> qualifying value

That is the correct procedure, but keep in mind that this qualifying value consists of columns that should already be there and not some specially for this purpose introduced artificial identifier. If any of the books you have read said that you must introduce new identifiers (instead of identifying the new keys over the existing attributes) in order to normalize you should get rid of them.

So what should you have done in your case? The result should have been:

  Loans(#LoanNo, Surname, Telephone, E-mail, DateOut, DueBack)   CDs_Borrowed(#LoanNo, Artist, Title, Label, DateBought)

with a foreign key from CDs_borrowed.#LoanNo to Loans.#LoanNo, and as primary key of CDs_borrowed the key {#LoandNo, Artist, Label, DateBought}. Note that there is no special #cdID and also note that if there had been you should still have added the key constraint that is now the primary key.

>but surely if this is the case (and the all other attibutes in the table
>are dependent on the qualifying value) then it'll always end up with
>this redundant table after 2NF and 3NF?

In the tables above there are no more update anomalies and therefore there is no more redundancy. (This is assuming that there are no more dependencies over the CD attributes. If there is a dependency lik Artist, Title -> Label then there is still some redundancy.) Note that this is even true if the same CD reappears in CDs_Borrowed for more than one loan. It may look like a repeating group, but it doesn't have to be.

Still, even though there is officially no redundancy it seems a bit wasteful that the CD information is repeated for every loan that it is in, so you could replace it with an artificial identifier and move the CD information to another table, so you get:

  CDs_Borrowed(#LoanNo, #CDNo)
  CD(#CDNo, Artist, Title, Label, DateBought)

with a foreign key from CDs_Borrowed.#CDNo to CD.#CDNo, a primary key {#LoanNo, #CDNo) for CDs_Borrowed, a primary key {#CDNo} for CD, and also a candidate key {Artist, Title, Label, DateBought} for CD.

Let me stress again, however, that although this will perhaps make your database a bit smaller, this is not normalization and does not resolve any update anomalies in this case. These had already been removed in the previous tables and if they still had been there then this extra table would not have solved these problems.

Also note that, as some other people here will be glad to explain to you, the introduction of artificial identifiers that do not directly correspond to something in the organization you are describing can sometimes cause some problems of their own. But in this case it is not unlikely that the library uses some kind of number or code to identify certain CDs, in which case it would not be an artificial identifier.

>Otherwise is there a qualifying rule or method that tells you how to
>decided that the compostite key should be in the first relation rather
>than the second?

It must always be in the second. If you can put it in the first then there wouldn't have been a repeating group in the first place.

• Jan Hidders