# Thread: How to normalize this table?

1. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Originally Posted by christianbobby
can the above table be further normalised?
nope, you're good

3. Registered User
Join Date
Oct 2002
Location
Posts
697
Originally Posted by r937
nope, you're good
No, *you're* good. Psychic, even.

4. Registered User
Join Date
Dec 2007
Location
London, UK
Posts
741
Originally Posted by christianbobby
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. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Originally Posted by dportas
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?

6. King of Understatement
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.

7. Registered User
Join Date
Dec 2007
Location
London, UK
Posts
741
Originally Posted by r937
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
•