Re: Education Help

yep,
Just to start things off:

borrowername is dependent on borrowerno only. secondly I would give the relations meaningful names. thirdly I would first start identifying main entities (book title, borrower, library, borrow, book copies) (what is the definition of callno?) and then define the relations between entities.
Then I wonder if you can uniquely identify books by title. What if a book has 2 authors?
Where are your assumptions?

Warren wrote:

> "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? :)

--
Anton Versteeg
DB2 Specialist
IBM Netherlands