3nf, functional dependancy and head exploding

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Database Design » Relational Theory » 3nf, functional dependancy and head exploding...

3nf, functional dependancy and head exploding

Rate Topic

Display Mode

Topic Options

Author

Message

Simon_L

Posted Thursday, August 14, 2008 6:55 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, August 16, 2012 9:02 AM
Points: 389, Visits: 1,074

whilst enjoying a relatively quite period Im revisiting basics etc and dusting off some mental cobwebs.

3nf .. is violated if a non-key attribute is a fact about another non-key attribute .. that I can live with

for example a book table with isbn, title , price publisher_name and a publisher_city.. the city is bad and should be in a separate entity etc..

but to describe publisher city as functionally dependant on publisher I just cant get...

if something is functionally dependant it can be uniquely determined by another attribute. But a publisher_city cant be said to be uniquely identified by a publisher..

so I can see why it should be excluded due to being a fact about another column .. but as a way of checking to see of a table breaks 3nf I dont get how I'd arrive at that through functional dependency ?

make any sense or do I need more/less coffee ?

~simon

Post #552631

sqlvogel

Posted Saturday, August 16, 2008 4:43 AM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:15 AM
Points: 406, Visits: 2,851

Ask yourself what are the determinants of publisher_city? If they are not super keys of this table then it is not in BCNF (probably not 3NF either but BCNF is more important and fundamental).

David

Post #553908

Kevin Butterworth

Posted Saturday, August 16, 2008 7:35 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, July 12, 2012 1:44 AM
Points: 52, Visits: 52

It depends on what your understanding of publisher_city actually is. For an attribute to be functionally dependant on another attribute (lets use P and C, for publisher and publisher_city) then for each value of P there must only be one value of C, for that P value (note there can be lots of different P values) So in this case P -> C, or C is functionally dependant on P if

Publisher (P) City (C)

Penguin London
Darby London
Horst New York

If however Penguin had London for some values of C and New York for others, then C would not be functionally dependant on P.

Quick edit, you might want to look at Publisher and title there as well.

Post #553914

Simon_L

Posted Monday, August 18, 2008 5:07 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, August 16, 2012 9:02 AM
Points: 389, Visits: 1,074

thank you both .. its starts to make sense Smile

~si

Post #554212

Andrew Peterson-472853

Posted Monday, September 01, 2008 7:11 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 7:40 AM
Points: 136, Visits: 259

A quick way to remember Boyce-Codd normal form, which is the typical end point (its a little past 3nF) is with this saying:

An attribute should be dependent on the key,
the whole key, and nothing but the key, so help me Codd.

As for all those defintions, they can get so complex, that they make no sense.
Chris Date stated that normalization is just the codification of common sense.

If your normalization references are twisting your mind - ditch them and get ones that make sense.

The more you are prepared, the less you need it.

Post #562143

RBarryYoung

Posted Monday, September 01, 2008 8:08 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

Simon_L (8/14/2008)

but to describe publisher city as functionally dependant on publisher I just cant get...

if something is functionally dependant it can be uniquely determined by another attribute. But a publisher_city cant be said to be uniquely identified by a publisher..

If you remember from math class "a is functionally dependent on b" means that you can construct a function A so that for any b there exists exactly one a such that:

 a = A(b)

Thus, b determines a, or if you know b then you can determine what a is. Translating this to your city example:

publisher_city = PUBLISHERS(Publisher_name).City

Putting this in SQL/relational terms just means that you can construct a PUBLISHERS table with Publisher_name as the primary key and City as one of the data columns. Or "each publisher is uniquely determined by its name" and "each publisher has exactly one publisher_city".

Hope this helps.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #562158

Simon_L

Posted Tuesday, September 09, 2008 9:28 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, August 16, 2012 9:02 AM
Points: 389, Visits: 1,074

An attribute should be dependent on the key,
the whole key, and nothing but the key, so help me Codd.
If your normalization references are twisting your mind - ditch them and get ones that make sense.

Could well be true.. I was starting to get a grip on it all but the fact that county and city should be in separate tables due to functional dependencies (according to the current tome I'm reading) has now finally blown my mind .... Hehe

Post #566266

Kevin Butterworth

Posted Tuesday, September 09, 2008 10:52 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, July 12, 2012 1:44 AM
Points: 52, Visits: 52

I find it very hard to teach normalisation due to the poor examples in a number of textbooks. If people use a bad one they can confuse themselves horribly, on what should be a fairly common sence thing.

Lets look at country being functionally dependant on city, and thus needing to be in another table.

City Country
London England
Cardiff Wales
Sheffield England
Lincoln England

looks good, for each city there is exactly one country value.

The problem is that we haven't really understood the relationship, and are using only a tiny subset of the data.
City Country
London England
Cardiff Wales
Sheffield England
Lincoln England
Lincoln USA

Oops, now that shouldn't have happened, and our relationship that was based on specific instances of the data suddenly appears to be utterly wrong.

This means that you need to look at more than just the data, but at the reality of the relationship. Does city name actually define which country it is in, no. If you add geo data to it then yes, but then the country is functionally dependant on that geo data, not the city name still.

Can't read and I see you've put county not country. Interestingly the same applies as Lincoln is in Lincolnshire or Lancaster County depending on the USA or UK.

Post #566316

Andrew Peterson-472853

Posted Tuesday, September 09, 2008 1:07 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 7:40 AM
Points: 136, Visits: 259

The amazing thing is that normalization is really simple and straight forward. 4th normal form can get a bit much, but other than that, it all makes sense.

Unfortunately, I see many definitions that seem to be focused on making the author look authoritative.

In reality, if someone really knows what they are talking about, they can explain it in simple terms. If they really do not understand something, then it tends to sound complex.

That said, some of the text books focused on the algebra of relational databases do take time, but that is an another matter.

The more you are prepared, the less you need it.

Post #566429

L' Eomot Inversé

Posted Sunday, April 04, 2010 10:48 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:10 PM
Points: 7,084, Visits: 7,137

David Portas (8/16/2008)

Ask yourself what are the determinants of publisher_city? If they are not super keys of this table then it is not in BCNF (probably not 3NF either but BCNF is more important and fundamental).

The trouble with BCNF is that a requirement to be in BCNF may prevent complete representability of functional dependencies (ie it may not be possible for the key constraints in the DB to enforce all the simple functional dependencies in the data model); so I prefer to stick to EKNF (which is 3NF plus extra separation, but doesn't go quite as far as BCNF). Every schema which is in EKNF is also in BCNF unless there are multifunctional dependencies in the data model which would constrain BCNF to miss complete representability of simple functional dependencies (and a similar statement can be made about EKNF and 4NF), so the cases where EKNF has problems which BCNF doesn't are exactly the cases where BCNF has problems which EKNF doesn't - so there's always a trade-off on which set of problems you want to live with (coding round anomalies caused by multifunctional dependencies which could have been eliminated by BCNF [or in some cases, by 4NF] or coding to enforce simple functional dependencies which woul have been key constraints in EKNF; since EKNF generally delivers better performance that BCNF when they are actually different (because the joins use a smaller number of tables) and because I object to having to write code (as opposed to key constraints) to enforce simple functional dependencies more than I generally object having to code out multifunctional dependency related anomalies I usually reckon the trade-off is best resolved by going for EKNF.

Tom
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)

Post #896432

« Prev Topic | Next Topic »

Permissions