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 Wednesday, July 27, 2011 10:13 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:34 PM
Points: 8,282, Visits: 8,732
Comments posted to this topic are about the item Third Normal Form

Tom
Post #1149799
Posted Thursday, July 28, 2011 12:54 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 12:04 AM
Points: 581, Visits: 2,715
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.



Post #1149839
Posted Thursday, July 28, 2011 3:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:41 PM
Points: 12,201, Visits: 9,156
Great and clear example. Thanks Tom.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1149899
Posted Thursday, July 28, 2011 6:45 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,
Thanks for the series, it has been very interesting thus far.

However, I think more distinction needs to be made between 2NF and 3NF. The example seems to be about 2NF. The Candidate key for the original table is City. The Phone number is not dependent upon City at all, it is dependent upon Group. I think that you are trying to show a transitive relationship from the Key to the phone number. But that relationship doesn't seem to exist in the example.

In the wikipedia example of 3NF http://en.wikipedia.org/wiki/Third_normal_form this transitivity is expressed by having two overlapping candidate keys, one of which determines the non-prime attribute, but the other doesn't. This seems to imply that the difference between 2NF and 3NF is that in 2NF non-prime attributes need to depend on A candidate key, while in 3NF the non-prime attributes need to depend on ALL candidate keys.

So, can you please expand on this and clear up my confusion?

Thanks,
JimFive
Post #1150079
Posted Thursday, July 28, 2011 7:08 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 02, 2013 6:30 AM
Points: 346, Visits: 691
Due to the extreme flexibility requirements of our reporting system users need at least a nodding acquaintence with the database schema--primarily how specific data can be found in specific tables, and how the tables are related to each other.

Rather than try and explain anything about normal forms we use the SPOT and Path metaphor. SPOT = Single Point Of Truth (ie the data is only stored in one single place *once*) and there are always paths between tables, so for example a Zone (set of contiguous zip codes) is always connected to only one Office which in turn is in one Region.

Of course the data has to be 3NF to support this, but I always thought SPOT and Path was a much more natural way to visualize 3NF anyway.
Post #1150109
Posted Thursday, July 28, 2011 8:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 11, 2012 10:44 PM
Points: 4, Visits: 71
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."
Post #1150212
Posted Thursday, July 28, 2011 10:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 10:26 AM
Points: 200, Visits: 611
NB: 40 Tree Rd became 30 Tree Rd at some point.
Post #1150346
Posted Thursday, July 28, 2011 11:46 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:34 PM
Points: 8,282, Visits: 8,732
James Goodwin (7/28/2011)
Tom,
Thanks for the series, it has been very interesting thus far.

However, I think more distinction needs to be made between 2NF and 3NF. The example seems to be about 2NF. The Candidate key for the original table is City. The Phone number is not dependent upon City at all, it is dependent upon Group. I think that you are trying to show a transitive relationship from the Key to the phone number. But that relationship doesn't seem to exist in the example.

The Wikipedia article also refers to Carlo Zaniolo's new definition of 3NF, which is provably equivalent to the original definition, and that was the definition I was using - it's much simpler, easier to explain, and easier to see the relationship between 3NF, EKNF, and BCNF when you use Zaniolo's definition. If you prefer to think in terms of the original definition, there is a transitive dependency there: City -> Group (because of the stated business rules that only one group has a depot in any given city - but anyway as (City) is a candidate key we know that City -> Group is an FD of this relation) and Group -> Phone number (because of the stated business rule that each group uses a single order phone number); and the partition of the table represents the Group -> Phone number FD in a separate table, removing Phone number from the original table to eliminate the transitive FD.
In the wikipedia example of 3NF http://en.wikipedia.org/wiki/Third_normal_form this transitivity is expressed by having two overlapping candidate keys, one of which determines the non-prime attribute, but the other doesn't. This seems to imply that the difference between 2NF and 3NF is that in 2NF non-prime attributes need to depend on A candidate key, while in 3NF the non-prime attributes need to depend on ALL candidate keys.

Even in 1NF every attribute, prime or not, is dependent on every superkey (that's the definition of a superkey) and hence on every candidate key (as a candidate key is a minimal superkey). 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 (2NF says "the whole key" while 3NF says "nothing but the key").

So, can you please expand on this and clear up my confusion?

Thanks,
JimFive

I hope the above is clear enough - if not, get back to me and I'll try again.

Regards

Tom


Tom
Post #1150459
Posted Thursday, July 28, 2011 11:55 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:34 PM
Points: 8,282, Visits: 8,732
roger.plowman (7/28/2011)
Due to the extreme flexibility requirements of our reporting system users need at least a nodding acquaintence with the database schema--primarily how specific data can be found in specific tables, and how the tables are related to each other.

Rather than try and explain anything about normal forms we use the SPOT and Path metaphor. SPOT = Single Point Of Truth (ie the data is only stored in one single place *once*) and there are always paths between tables, so for example a Zone (set of contiguous zip codes) is always connected to only one Office which in turn is in one Region.

Of course the data has to be 3NF to support this, but I always thought SPOT and Path was a much more natural way to visualize 3NF anyway.

The trouble with the Spot and Path approach is that there are some collections of business rules for which it doesn't work, because some data has to be stored in more than one place (but a combination of foreign key constraints, null constraints, and unique constraints ensure that any attempt to update one copy without updating the other fails). There's an example of this in Wikipedia, on the BCNF page, in the section "Achievability of BCNF". What's going on is that some data is involved in several business rules (which should become database constraints) which can't all be represented in one table.

Where Spot and Path is applicable (which is very often) it seems to be a reasonable approach.


Tom
Post #1150466
Posted Thursday, July 28, 2011 12:02 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:34 PM
Points: 8,282, Visits: 8,732
Michael.Beeby (7/28/2011)
NB: 40 Tree Rd became 30 Tree Rd at some point.

Ouch! I will try to get that fixed. Thanks for pointing it out.

edit (later): Steve has corrected it for me now - he really is good at fixing my mistakes.


Tom
Post #1150469
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse