[deviate1966]

What would be the best way to normalise the below. I have got it so far but end up getting really stuck. This needs to go to 3NF

0NF
ISBN Number
Book Name
Supplier Contact
Supplier
Supplier Address
Purchase Price Paid
Member No
Member Name
Member Address
Date of Rental Out
Date of Rental Due In
Date of Actual Return
Fine to be Charged?

Any help would be welcome

[r937]

hmmm, looks like a homework assignment

not gonna do it for you, but i will give you a hint that the independent entities are book, supplier, and member

there appears to be an intersection entity bookpurchase, and another, bookrental, which will have relationships to book and supplier, and book and member respectively

you should be able to work out the primary and foreign keys from that


rudy
http://r937.com/

[sundialsvcs]

Originally posted by deviate1966
What would be the best way to normalise the below.
ISBN Number
Book Name
---
Supplier Contact
Supplier
Supplier Address
Purchase Price Paid
---
Member No
Member Name
Member Address
---
Date of Rental Out
Date of Rental Due In
Date of Actual Return
Fine to be Charged?

Yes indeed, this looks like homework. But notice where I have placed the dotted lines. Here in a single poorly-designed table we see information about at least four "things": books, suppliers, members, and rentals. Observe also that there may sensibly be "more than one" of these things in several cases, some related to a Book and some not, at least not directly.

When you transform the structure of this database, you won't simply re-arrange these fields; you'll make copies of some of them to form foreign-key relationships.

Set aside the computer-lingo a minute and grab a deck of 3x5 cards (if you can find them anymore). How many stacks of cards would you need and what information would you put on each card? "What can happen" to these data in real life that you might need to represent, and what would you need to do to which cards in each case?

Imagine only that each of these stacks of cards will be put into a magic box that has one magic trick it can do: when you speak a number or word out loud and tap the side of the box three times with your magic wand while mumbling the magic word, "query," all of the cards having that number or word on them will magically float into the air, neatly assemble themselves and place themselves on your outstretched hand in a pile marked "query result."

How would you solve this problem with 3x5 cards and a few of these magic boxes? Forget the computer-lingo and think about the problem itself. You really could solve this problem and manage this data with 3x5 cards, although instead of magic boxes you'd have to use Graduate Students.

[deviate1966]

Thanks guys. New to all this and it takes a while for the penny to drop!