Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Feb 2008
    Posts
    49

    Unanswered: help on normalisation

    hi i just signed up for this forum. im doing a college project and am abit stuck on normalisation. is there any good links or tutorials on normalisation as im been stuck for ages. I need to normalise the attributes into 3rd normal form.

    ive put all my attributes togeather.
    ive attached the file with a breif scenario and all attirbutes.

    if any one could please help it woul be greatly appreciated.

    thanks
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    lots of us here who can help

    what is your question?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by aa_86
    is there any good links or tutorials on normalisation
    Rudy directed me to this link some time ago. It is an excellent article:
    http://www.tonymarston.net/php-mysql...se-design.html

  4. #4
    Join Date
    Feb 2008
    Posts
    49

    normalisation help

    thanks for your help, my question is that i need the attributes in the attached file to be normalised to 3rd normal form.

    also after normalisation is the next step ERD and then map the coneptual design (ERD) to relational.(ER to relational schema)

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Nope - that is your assignment. Rudy asked for your question.

    As a quick overview of how it works here - we will guide, prod, encourage students but we won't do more than a very small percentage of the work, if that. You put in the donkey work. We'll draw attention to anything you need to review.

    Show us your 1NF and let's move on from there.

  6. #6
    Join Date
    Feb 2008
    Posts
    49
    thanks i dont want you to do my assignemnt i just need some advise as reading about normalisation doesnt really help, i appreciate ur gudience and have attached my 1st normal form

    from my understaning i have removed all repaeting attributes, and defined the candidate keys. but how do i show the diffrence between primary key and candidate key as there both an underline.

    thanks
    Attached Files Attached Files

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - please could you split those into your relations\ entities just so I don't go inferring something incorrectly? Please could you also paste directly into the html pane so I don't have to open docs everytime. First glance is you've got off to a good start.

  8. #8
    Join Date
    Feb 2008
    Posts
    49
    hi, sorry i didnt quite ubnderstand what you meant by "please could you split those into your relations\ entities just so I don't go inferring something incorrectly?"

    thanks

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You've got all your attributes in one single set of parentheses. Could you separate each group of attributes so we can see which ones are grouped together as entities? Do you know what I mean by relations, entities and attributes?

  10. #10
    Join Date
    Feb 2008
    Posts
    49
    ok i get you, yeh i know what you mean by relations, entities and attributes.

    here they are them sepearted. i have tried to do 1st and nd 2nf. in the 2nd everything seems to be partially dependandt is this right? so there are only the primary keys in the first table/relation

    0Normal Form

    Customer No, Title, Surname, forename, Address, tel no, email address

    Vehicle reg no, manufacture, model, year, category, fuel type, transmission, hire rate, deposit amount

    Order ID, order date, order time, customer no, date from, date to, booking status, vehicle reg no, no of days, total cost

    Payment amount, payment received, payment type, late charges

    Insurance policy no, insurance company name, insurance company address, insurance company tel no

    1St Normal Form

    CUSTOMER_VEHICLE_ORDER_INSURANCE POLICY NO
    (Customer No, Title, Surname, forename, Address, tel no, email address, vehicle reg no, manufacture, model, year, category, fuel type, transmission, hire rate, deposit amount, order ID, order date, order time, date from, date to, booking status, no of days, total cost, payment amount, payment received, payment type, late charges, insurance policy no, insurance company name, insurance company address, insurance company tel no)
    //customer number was removed as it was repeated in the order attributes
    // vehicle reg no was also repeated in the order attributes so removed.
    // should payment amount be a candidate key?

    2nd Normal Form

    CUSTOMER_VEHICLE_ORDER_INSURANCE POLICY NO

    (Customer No, Vehicle reg no, Order ID),

    CUSTOMER
    (Customer No, Title, Surname, forename, Address, tel no, email address)

    VEHICLE

    (Vehicle reg no, manufacture, model, year, category, fuel type, transmission, hire rate, deposit amount)

    ORDER

    (Order ID, order date, order time, date from, date to, booking status, no of days total cost, payment amount, payment received, payment type, late charges)

    INSURANCE

    (Insurance policy no, insurance company name, insurance company address, insurance company tel no)

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - let's look at one of your questions. "should payment amount be a candidate key?"

    What, in your own words, is the definition of a candidate key? What is its defining characteristic?

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    in the same vein is there any redundancy in your design. are there some rows that have information that is duplicated (in similar rows) or redundant.

    can you have more than one contact for a customer?
    do you insure companies, in which cae there may be

    do you expect to have a single payer, but the name on the policy is different, eg some policies may insist that although you have say one person paying the account, the policy is actually in different persons names.. say for example a car d]=policy which may be specific to a car or a person or both.

  13. #13
    Join Date
    Feb 2008
    Posts
    49
    primary key uniquley detifies the set of attributes, candidate key uniqly idetifys a row? but not quite sure what it means i thinks its also a unique idetifier but the primary key is the most unique idetifer out of all the candidate keys

  14. #14
    Join Date
    Feb 2008
    Posts
    49
    the order table has repaetable attributes such as the customer no, vechile no, payment no.
    -a customers can make many reservations and customer has only one contact no
    -every car is insured from the same company. the company issue a diffrent ploicy no for each reservation. the customer is insured for the duration of the reservation
    - the person who rents the car must pay for it and cant use someone else credit card

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by aa_86
    primary key uniquley detifies the set of attributes, candidate key uniqly idetifys a row? but not quite sure what it means i thinks its also a unique idetifier but the primary key is the most unique idetifer out of all the candidate keys
    Once you have selected a primary key you no longer have any candidate keys - they are now alternate keys. A candidate key is a key that is a candidate to be "promoted" to primary key. So - you have candidate keys. From those you select an approriate primary key, and the remainder are now alternate keys.

    Therefore you are struggling with a distinction that does not exist. Candidate keys, primary keys and alternate keys all share the same basic definition and characteristics.

    What does the article I linked to say? (You can find quickly with a Ctrl+F search).

Posting Permissions

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