Hi.
I've got to make a database for a car hire firm who would keep a record for all its cars' hire history

the Raw database structure is like this

CUSTOMER (Customer Number, Company Name, Town, Postcode, [Car Registration, Make, Model, Date Hired, Date Returned])

[...] indicates repeating field....

now... i would like to normalise it in first normal form...
that comes to...
CUSTOMER (Customer Number, Customer name, Company Name, Town, Post Code, Car regstration (foreign key)

CARS (Car Registration, Make, Model, Date Hired, Date Returned)

--------------------

2nd NOrmal FORM:

CUSTOMER (Customer Number, Customer name, Company Name, Town, Post Code[/I]

CUSTOMER_HIRED (Customer Number, Car Registration, Date Hired, Date Returned)

CARS ( Car Registration, Make, Model)
-------------

NOW, IM HAVING PROBLEMS GETTING THIS INTO 3RD NORMAL FORM......
IM NOT SURE IF MY SECOND NF IS CORRECT EITHER... ANY HELP WOULD BE REALLY APPRETIATED

homework, homework, homework!

your 1NF design isn't quite correct -- check your primary and foreign keys

is it one customer, multiple car registrations, or one car registration, multiple customers?

thanks for the reply...
yeah, this is a past examination question im working my way through..
at least im better than the other guys who just ask the answer.... ive given it a shot and im sort of stuck....

but yeh.... its one many relationship.... one customer... many registrations....


ive checked my 1st NF.... i really dont see whats wrong there.. i actually showed it to the professor and he said its correct

there are only two possible explanations for this highly improbable fact:

1. your professor just noticed that you had two tables with approximately the right names, and did not look closely enough to see the problem with the keys, in which case this oversight might be forgiven

or

2. your professor is just plain wrong

which do you think it is?

I think he's just wrong...
theres only me in the class.... yeh.. im the only senior student to take Computing....

i have to forward my thanks to you though.. helping me out here...

so ive looked at it again and come up with this

1NF:
CUSTOMER (Customer Number Name, Company Name, Town, Post Code)

CARS ([U]Car Registration[/U, Make, Model, Date Hired, Date Returned, Customer Number

YEh?

yes, as far as the foreign key is concerned, that is now correct

however, it looks like you can hire a car only once!!

shocked

Im all confused now....

what don't you understand?

however, it looks like you can hire a car only once!!

does that mean the layout itself is wrong or what i am trying to do cannot be done....

2nd NF:
CUSTOMER (Customer Number Customer Name, Company Name, Town, Post code)

CARS (Car Registration Make, Model, Date Hired, Date returned)

CUSTOMER_HIRED (Customer Number, Car Registration)

I dont know if that is write... i think it is.. since... it contains no dependancies... but you're the expert sir

Is there a golden rule you could suggest me when normalising tables?

yes, two in fact:

1. pay attention to the concept of identity, which is manifested in the primary key

if the PK of the Cars table is Registration Number, then each Registration Number can be in that table only once

think about what this implies for Date Hired

2. foreign keys are always found on the "many" side of a one-to-many relationship

hmmm....
i really cant think of anything else other than creating a fourth table with Date hired and date returned fields...

this has taken longer than i thought.. ive been hacking my way through this for over an hour now... still cant get it right...

that's right, another table

although if you are smart, you will combine it with what you already have in the CustomerHired table

remember, pay attention to what actually has to be unique ( ==> PK )

Is faraz's depiction of 2NF in the original post actually correct?

In this case, wouldn't 3NF be the same as shown in the original post's 2NF?

This whole thread made a lot more sense when I realized that Hire means Rent.