Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Third Normal Form Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, July 28, 2011 12:05 PM
 SSCrazy Eights Group: General Forum Members Last Login: Today @ 6:34 AM Points: 9,823, Visits: 11,892
 olsonea (7/28/2011)When I was studying Relational Database Concepts in college, there was a catchy mnemonic for 3NF: "The key, the whole key, and nothing but the key, so help me Codd."It's a good catch phrase. Been around a long long time.Did they tell you that:1NF = The key2NF = The whole key3NF = Nothing but the key? Tom
Post #1150471
 Posted Thursday, July 28, 2011 2:00 PM
 Ten Centuries Group: General Forum Members Last Login: Wednesday, January 6, 2016 2:10 PM Points: 1,323, Visits: 1,107
 The difference between 2NF and 3NF is that 2NF does not permit any non-prime attribute to have a dependency on something which is a proper subset of a candidate key, while in 3NF a non-prime attribute may not depend on anything which is not a superkey My confusion is that the original table in the example is, then, not in 2NF because the phone number depends on something (Group) that is not the whole key, or in fact, even part of the key. So it seems that you took a table from 1NF to 2NF which was then trivially in 3NF (in the sense that no extra work was required to get it into 3NF).What I would like to see is a table that is in 2NF converted into 3NF.--JimFive
Post #1150543
 Posted Thursday, July 28, 2011 2:42 PM
 SSCrazy Eights Group: General Forum Members Last Login: Today @ 6:34 AM Points: 9,823, Visits: 11,892
 James Goodwin (7/28/2011)The difference between 2NF and 3NF is that 2NF does not permit any non-prime attribute to have a dependency on something which is a proper subset of a candidate key, while in 3NF a non-prime attribute may not depend on anything which is not a superkey My confusion is that the original table in the example is, then, not in 2NF because the phone number depends on something (Group) that is not the whole key, or in fact, even part of the key. So it seems that you took a table from 1NF to 2NF which was then trivially in 3NF (in the sense that no extra work was required to get it into 3NF).What I would like to see is a table that is in 2NF converted into 3NF.--JimFiveAh, a confusion about 2NF. You hit the crucial point when you said "or [not] in fact, even part of [the key]", because that is of course allowed by 2NF. There's nothing in 2NF to stop an attribute being dependent on some set of attributes that includes something that isn't part of a key. The rule is that if a non-prime attribute is dependent on something that is a subset of a key that subset must be the whole key. Or putting it another way, the whole key is not the only thing it can be dependent on, the rule is simply that it must never be dependent on a partial key. So the original table inthe 3NF article is indeed in 2NF. Tom
Post #1150559
 Posted Thursday, July 28, 2011 3:20 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Tuesday, November 8, 2016 4:15 PM Points: 189, Visits: 1,615
 Thanks for the articles Tom. I am really enjoying them; your examples are nice and clear. I am looking forward to the rest!Cheers, Nicole BowmanNothing is forever.
Post #1150572
 Posted Thursday, July 28, 2011 4:20 PM
 Ten Centuries Group: General Forum Members Last Login: Wednesday, November 23, 2016 9:59 AM Points: 1,144, Visits: 2,788
 Thanks Tom. I've enjoyed all 3 of these articles. I also like your examples. ---------------------------------------------------------------------Use Full Links:KB Article from Microsoft on how to ask a question on a Forum
Post #1150596
 Posted Thursday, July 28, 2011 5:53 PM
 SSC Rookie Group: General Forum Members Last Login: Sunday, November 20, 2016 6:37 PM Points: 31, Visits: 539
 Hi Tom,I was also confused about whether the 3NF example was in 2NF or not.I had just submitted a really long question, but then saw your reply to James:There's nothing in 2NF to stop an attribute being dependent on some set of attributes that includes something that isn't part of a key. The rule is that if a non-prime attribute is dependent on something that is a subset of a key that subset must be the whole key. Or putting it another way, the whole key is not the only thing it can be dependent on, the rule is simply that it must never be dependent on a partial key.This is a great summary, and IMO would be a good addition to the 2NF article.Thanks, John
Post #1150615
 Posted Thursday, July 28, 2011 6:10 PM
 SSCrazy Eights Group: General Forum Members Last Login: Today @ 6:34 AM Points: 9,823, Visits: 11,892
Post #1150617
 Posted Thursday, July 28, 2011 6:12 PM
 SSCrazy Eights Group: General Forum Members Last Login: Today @ 6:34 AM Points: 9,823, Visits: 11,892
 dogramone (7/28/2011)Thanks for these articles Tom. I have ensure the application developers here are reading all of them. Of course the DBA's already get it but like a lot of small shops the DBAs don't do development and although there are a lot of senior developers they are all building to a OLTP style and are always making these common mistakes.I'm anxiously looking forward to the next in the series and the Fourth and Fifth are for me as I've only ever gotten to 3.5 or what I was taught as Boyce Codd Normal Form.Thanks for the encouragement; it's good to know that my attempts are appreciated. Tom
Post #1150619
 Posted Friday, July 29, 2011 6:52 AM
 Ten Centuries Group: General Forum Members Last Login: Wednesday, January 6, 2016 2:10 PM Points: 1,323, Visits: 1,107
 Tom,Much clearer. Thanks for the response.--JimFive
Post #1150876
 Posted Monday, August 1, 2011 8:40 AM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, October 11, 2016 7:42 AM Points: 2, Visits: 44
 Tom.Thomson (7/28/2011)James Goodwin (7/28/2011)The difference between 2NF and 3NF is that 2NF does not permit any non-prime attribute to have a dependency on something which is a proper subset of a candidate key, while in 3NF a non-prime attribute may not depend on anything which is not a superkey My confusion is that the original table in the example is, then, not in 2NF because the phone number depends on something (Group) that is not the whole key, or in fact, even part of the key. So it seems that you took a table from 1NF to 2NF which was then trivially in 3NF (in the sense that no extra work was required to get it into 3NF).What I would like to see is a table that is in 2NF converted into 3NF.--JimFiveAh, a confusion about 2NF. You hit the crucial point when you said "or [not] in fact, even part of [the key]", because that is of course allowed by 2NF. There's nothing in 2NF to stop an attribute being dependent on some set of attributes that includes something that isn't part of a key. The rule is that if a non-prime attribute is dependent on something that is a subset of a key that subset must be the whole key. Or putting it another way, the whole key is not the only thing it can be dependent on, the rule is simply that it must never be dependent on a partial key. So the original table inthe 3NF article is indeed in 2NF.
Post #1151865

 Permissions