Problem with 3NF of Normalisation

[trebor-pl]

Hi, I have a problem with my Normalisation. The case is that I need it to pass the module in college. I have a great difficulty with doing my 3NF of Normalisation, would you be so kind with helping me to achieve this criteria.

This is the table upto 2NF:

[B]UNF[/B]:

[B]Member No[/B]
First Name
Last Name
1st Line of Address
City
Post Code
DOB
Date Registered
Status
ISBN*
Library ID
Title*
Author ID*
Book Stock*
Author Name*
Publisher ID*
Publisher Name*
Loan Start Date*
Loan Expiry Date*
Reservation ID*
Reservation Date*
Overdue*
Loan ID*
Book Reserved
Current Loans

[B]1NF[/B]

[B]Member No[/B]
First Name
Last Name
1st Line of Address
City
Post Code
DOB
Date Registered
Status
Current Loans

[B]Loan ID[/B]
[B][I]Member No[/I][/B]
ISBN
Title
Author ID
Author Name
Publisher ID
Publisher Name
Reservation ID
Title
Book Stock
Overdue
Loan Expiry Date
Loan Start Date
Reservation Date Library ID
Book Reserved

2NF
[B]Member No[/B]
First Name
Last Name
1st Line of Address
City
Post Code
DOB
Date Registered
Status
Current Loans

[B]Loan ID
[I]Member N[/I]o[/B]
Library ID
Loan Expiry Date
Loan Start Date
Overdue

[B]ISBN[/B]
[I][B]Author ID
Publisher ID[/B][/I]
Title
Book Stock

[B]Library ID [/B]
[I]ISBN[/I]
Book Reserved 

[B]Author ID[/B]
[B][I]ISBN[/I][/B]
Author Name

[B]Publisher ID[/B]
[I][B]ISBN[/B][/I]
Publisher Name

[B]Reservation ID[/B]
[B][I]Member No
Library ID[/I][/B]
Reservation Date

Please, help me. I don't get it and need a help of the professionals.

[BitBlt]

The first thing you need to do is identify the Entities. All I see here is a list of Attributes. What are the attributes OF?

So, for instance, in your third group of text above, you seem to be leaning toward a MEMBER, a LOAN (of a book, I assume), a BOOK, and then it sort of falls apart.

Data modeling is not just an academic exercise. It's supposed to represent real-life objects and relationships. Take a step back and think about the real-life things about the hypothetical scenario that are of interest. It helps if you can draw from your own experience. Have you ever checked out a book from the library? Think back to that time. What were the things you saw? Well, obviously you were in a library. You were surrounded by books. You had a library card (or membership, whatever), and you wanted to borrow books (a loan from the library). Look at the book. It has an author who wrote it, and an ISBN number assigned to it, and a title. You borrow the book on a certain day, and have to return it by a certain day. Maybe the book you want isn't available. How do you reserve it? Is there more than one copy of it? Maybe at a different library? For the book, does it have only one author? Has the author written more than one book?

These are just a sampling of the questions you can ask. As you answer these questions, you can look at the attributes you have to select from, and decide which attributes belong to which entities.

Here's a freebie: Look at what appears to be your Author entity above. There should be no ISBN attribute in there...he's the author and doesn't depend upon his book for uniqueness. Now look at your other entities. See a pattern?

If you have your list of rules of normalization, keep them by your side as you go through this exercise...that way if you start to go off-track you can correct yourself.

Hope this helps. Good luck!

[trebor-pl]

Hi, thx for reply ;)

After reading your post I realised that I might wrote teh post too rashly, so I decided to add some extra information.

After speaking to my tutor she informed me that there is something wrong with my 2NF not 3NF, and all she said is follow the rules, which I'am strugguling with.

1st link below is a link to my normalisation document, there you will see everything, if you would be so nice with pointing me what I have had done wrong.

2nd link is a ERD diagram so that you have a full picture of the database ;)

http://dl.dropbox.com/u/15091172/Normalisation.doc
http://dl.dropbox.com/u/15091172/ERD.doc

[BitBlt]

Hmm...perhaps you need to read this:
http://en.wikipedia.org/wiki/Database_normalization#Normal_forms
Pay special attention to this passage:

Newcomers to database design sometimes suppose that normalization proceeds in an iterative fashion, i.e. a 1NF design is first normalized to 2NF, then to 3NF, and so on. This is not an accurate description of how normalization typically works.

Here is a nice little introduction web-site you should read through. It's very short, but very direct:
http://phlonx.com/resources/nf3/
He has a great statement of the three normal forms that is easily understandable. Use that, and start from you UNF column again. HINT: Start with anything that has XxxxxID as your starting point for entities. Break the attributes apart that way.