Page 1 of 4 123 ... LastLast
Results 1 to 15 of 49
  1. #1
    Join Date
    Jan 2007
    Posts
    24

    Normalization 3NF

    Just started Database design at college, and have a assignment coming out in 3 weeks on 3NF.

    We have been given this example to do. it is in unnormalized form, and we have to put it nto 1NF, then 2NF , and then finaly 3NF.

    I am happy with 1NF, taking out duplicate rows columns, having only 1 piece of data in a cell, ect. But really struggling going to 2NF and then 3NF. Could sombody help me please ???

    http://img329.imageshack.us/img329/3...izationvv5.jpg

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the way this forum works with regard to doing homework assignments is like this --

    you do some work and show your results, asking specific questions about anything that you have done

    we provide comments and suggestions

    you go back and do some more work, and post revised results

    we provide comments and suggestions

    etc.

    at no time will we do your assignment for you

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

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    kutnessworthy -
    you can be one of the ones that puts in some effort:
    http://www.dbforums.com/showthread.php?t=1607109
    or one of those that does not:
    http://www.dbforums.com/showthread.p...75#post6226875

    Your choice dude\ dudette
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jan 2007
    Posts
    24
    ok, sorry i get your point,

    i have had a go at putting it into 1NF. I got confused with the no repeating data part , as some of the the order numbers are the same, but have diffrent part numbers.

    http://img258.imageshack.us/img258/82/1nf8eu.png

    am i getting this right ???

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kutnessworthy
    ...some of the the order numbers are the same, but have diffrent part numbers.
    that's because a single order can have more than one item, yes? how would you feel if you went to the grocery store and were required to go through the checkout with only one item at a time?

    okay, so an order can have multiple items, right?

    your next challenge is to figure out which combination of column values in your 1NF table are unique, and so could be used as the primary key

    obvioulsy, the order number is not unique, because it will be on multiple rows -- all the items in the order

    so let me give you a hint, and suggest that order number and item number together are unique

    your next step, 2NF, is to ensure that every column is dependent on the entire primary key

    does the customer depend on the order number and item number together? no, because customer is dependent only on the order number

    does this make sense?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2007
    Posts
    24
    so are you saying my 1NF is correct ???

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    since each column value is atomic, yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2007
    Posts
    24
    brilliant, busy doing 2NF now.

    cheers

  9. #9
    Join Date
    Jan 2007
    Posts
    24
    changed my 1NF table abit, split the customer and sales assistant names into First and Surnames. this correct ?

    http://img408.imageshack.us/img408/2964/1nf2wh.png

    now to put it into 2NF would i have to add more fields, like a Customer_ID and Sales_ID

    then have 4 tables

    Order No , Date.
    Customer_ID,Customer Surname,Customer Firstname,Cust Phone.
    Sales_ID,Sales Assit Surnname,Sales Assit Firstname,Sales Assit Phone.
    Part No, Part, Qty, Price.

    ???

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kutnessworthy
    Order No , Date.
    Customer_ID,Customer Surname,Customer Firstname,Cust Phone.
    Sales_ID,Sales Assit Surnname,Sales Assit Firstname,Sales Assit Phone.
    Part No, Part, Qty, Price.
    very close, very nice

    there's a problem with the last one -- how do you relate parts to orders?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jan 2007
    Posts
    24
    Quote Originally Posted by r937
    very close, very nice

    there's a problem with the last one -- how do you relate parts to orders?
    put Order No with it ?

  12. #12
    Join Date
    Jan 2007
    Posts
    24

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, that's not quite right, but it's a real good effort

    the red table is wrong

    as a hint for the normalization process, make sure that you have identified at least one candidate key for every relation you build along the way

    in your case, there is only one possible candidate key for each of the red, blue, and green tables

    as soon as you identify what makes each row in the red table unique, you will see the error

    at least, i think you should
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Aug 2004
    Posts
    330
    The blue table has two candidate keys.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by urquel
    The blue table has two candidate keys.
    no, it doesn't -- take a closer look
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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