Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2006
    Posts
    6

    Lightbulb Normalisation Problem

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2006
    Posts
    6
    Quote Originally Posted by r937
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by faraz
    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?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2006
    Posts
    6
    Quote Originally Posted by r937
    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?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2006
    Posts
    6
    Quote Originally Posted by r937
    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....

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what don't you understand?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Mar 2006
    Posts
    6
    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?

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by faraz
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Mar 2006
    Posts
    6
    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...

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Mar 2006
    Posts
    43
    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?

  14. #14
    Join Date
    Aug 2004
    Posts
    330
    This whole thread made a lot more sense when I realized that Hire means Rent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •