Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Third Normal Form Expand / Collapse
Author
Message
Posted Thursday, July 28, 2011 12:05 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:06 PM
Points: 8,283, Visits: 8,733
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 key
2NF = The whole key
3NF = Nothing but the key
?


Tom
Post #1150471
Posted Thursday, July 28, 2011 2:00 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, January 27, 2014 10:14 AM
Points: 1,322, Visits: 1,091
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

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:06 PM
Points: 8,283, Visits: 8,733
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.
--
JimFive

Ah, 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

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 4:06 PM
Points: 186, Visits: 1,521
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 Bowman

Nothing is forever.
Post #1150572
Posted Thursday, July 28, 2011 4:20 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, March 10, 2014 3:52 PM
Points: 1,380, Visits: 2,681
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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, March 09, 2014 4:06 PM
Points: 29, Visits: 449
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

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:06 PM
Points: 8,283, Visits: 8,733
john.rees-894283 (7/28/2011)
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

Hi John,

Thanks for your input.

This is one of those times where I wish I had had people's comments before I published the 2NF article! It's not the first time, if you look at the comments you'll see that I needed a revision after one of the first few comments.


Tom
Post #1150617
Posted Thursday, July 28, 2011 6:12 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:06 PM
Points: 8,283, Visits: 8,733
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

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, January 27, 2014 10:14 AM
Points: 1,322, Visits: 1,091
Tom,
Much clearer. Thanks for the response.
--
JimFive
Post #1150876
Posted Monday, August 01, 2011 8:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 16, 2014 5:51 AM
Points: 2, Visits: 41
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.
--
JimFive

Ah, 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
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse