Re: Education help

"Warren" <nospam_at_you.com> wrote in message news:3cbb82d1$0$9995$afc38c87_at_news.optusnet.com.au...
> Hi, just doing an introductory course to database design and was
 wondering
> if some of you may be able to assist with one of my questions. I am going
> to be posting my findings later today after some study, but I am
 interested
> in how 'others' attempt to answer. Anyway here goes:
>
> For the relation below:
>
>
>
> Book (CallNo, Title, Author, CopyNo, BorrowerName, BorrowerNo, WhereHeld)
>
>
>
> Where WhereHeld is the name of a library,
>
>
>
> (a) State any reasonable assumptions.
>
>
There can be multiple entries for Copy No, BorrowerName, borrowerNo, WhereHeld and CallNo.

>
>
>
> (b) Give functional dependencies based on your assumptions.
>
>
Title, Author -->CallNo
CallNo -->CopyNo, WhereHeld
BorrowerName <-->BorrowerNo
>
>
>
> (c) Convert the relation to 1NF to 3NF (1NF, 2NF and 3NF)
>
>
Un-normalised:

Book (Title, Author, {CallNo, CopyNo, BorrowerName, BorrowerNo, WhereHeld})

1NF - remove repeating groups:
Book1 (Title, Author)

Book2 (Title, CallNo, CopyNo, BorrowerName, BorrowerNo, WhereHeld)

2NF - remove partial dependencies:
Book1 (Title, Author)

Book21 (Title, CallNo, CopyNo, BorrowerName, BorrowerNo, WhereHeld)

Book22 (CallNo, CopyNo, BorrowerName, BorrowerNo, WhereHeld)

3NF - remove Transitive dependencies
Book1 (Title, Author)

Book21 (Title, CallNo, CopyNo, BorrowerName, BorrowerNo, WhereHeld)

Book22 (CallNo, CopyNo, BorrowerName, BorrowerNo, WhereHeld)

Book221 (WhereHeld, CallNo, Copy No)

Book222 (WhereHeld, BorrowerName, BorrowerNo)

Have I missed anything out? :) Received on Tue Apr 16 2002 - 06:48:13 CEST