Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Third Normal Form


Third Normal Form

Author
Message
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10784 Visits: 12019
Comments posted to this topic are about the item Third Normal Form

Tom

dogramone
dogramone
SSChasing Mays
SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)

Group: General Forum Members
Points: 630 Visits: 3625
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.



Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16546 Visits: 13210
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
James Goodwin
James Goodwin
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1363 Visits: 1107
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
roger.plowman
roger.plowman
SSChasing Mays
SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)

Group: General Forum Members
Points: 628 Visits: 1127
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.
olsonea
olsonea
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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."
Michael.Beeby
Michael.Beeby
SSC Veteran
SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)

Group: General Forum Members
Points: 236 Visits: 673
NB: 40 Tree Rd became 30 Tree Rd at some point.
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10784 Visits: 12019
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

TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10784 Visits: 12019
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

TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10784 Visits: 12019
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. Blush:-)

Tom

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search