Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2009
    Posts
    1

    Unanswered: How to normalize this table?

    ProductNo Halyyear UnitPRice Quantity
    1 1HF09 3 1
    1 2HF09 4 2
    1 1HF10 5 3
    1 2HF10 6 1
    1 1HF11 2 1
    1 2HF12 8 3
    2 1HF09 1 1
    2 2HF09 4 2
    2 1HF10 5 1
    2 2HF10 5 1
    2 1HF11 2 2
    2 2HF12 8 3

    can the above table be further normalised? below is the prodt table

    ProdtNo Pname
    1 Camcorder
    2 speaker

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by christianbobby View Post
    can the above table be further normalised?
    nope, you're good
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by r937 View Post
    nope, you're good
    No, *you're* good. Psychic, even.

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by christianbobby View Post
    can the above table be further normalised?
    You haven't specified any keys or dependencies so we can't possibly say, except by guesswork.

    My guess is that (ProductNo, Halyyear) could be the key. If that's correct then I would question the inclusion of UnitPrice in that table. What determines UnitPrice in your example? Couldn't the price per product change during the period specified by "Halyyear"?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dportas View Post
    Couldn't the price per product change during the period specified by "Halyyear"?
    i'm guessing it's a typo, and should actually be "halfyear", with 1HF09 representing the first half of 2009, while 2HF09 is the second half

    do you think the unitprice and quantity should be in separate tables? what would that be, 5NF? 6NF? are unitprice and quantity even separable?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The dependency Dave is talking about is 2NF. 5NF doesn't apply here - for 5NF there must be a composite key of three or more attributes and no none-key attributes.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by r937 View Post
    i'm guessing it's a typo, and should actually be "halfyear", with 1HF09 representing the first half of 2009, while 2HF09 is the second half
    That's what I assumed as well.

    do you think the unitprice and quantity should be in separate tables? what would that be, 5NF? 6NF? are unitprice and quantity even separable?
    Quantity could be a total for the product and time period but I'm not sure what UnitPrice would mean in that context. If it's determined by the ProductNo then it's a partial key dependency in violation of BCNF and it ought to be in another table. On the other hand the sample data suggests otherwise. I guess this just illustrates the near impossibility of normalization based only on a list of column names.
    Last edited by dportas; 11-26-09 at 16:47.

Posting Permissions

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