Re: Education Help

Thanks Anton,

I have the following assumptions:

There can be multiple entries for Copy No, BorrowerName, borrowerNo, WhereHeld and CallNo

CallNo also represents Title and Author

BorrowerNo represents borrowerName

there's a 1-to-1 relationship between whereheld and (copyno, callNo)

each callNo. has multiple copyNo's

each borrowerno can have multiple (callno and copyno)'s

for each book there are many copies.

for each copy, there are many lendings

for each person there are many lendings

Which leads to the following relations:

Books(CallNo, Title, Author)

Copies (CallNo, CopyNo, Whereheld)

Lending (Borrower No., Call No., CopyNo.)

Borrowers (Borrower Name, Borrower No.)

"Anton Versteeg" <Anton_Versteeg_at_nl.ibm> wrote in message news:3CBBF21C.96F511CF_at_nl.ibm...
> 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
>
>
Received on Tue Apr 16 2002 - 12:34:57 CEST