August 14, 2008 at 6:55 am
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
August 16, 2008 at 4:43 am
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).
August 16, 2008 at 7:35 am
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.
August 18, 2008 at 5:07 am
thank you both .. its starts to make sense 🙂
~si
September 1, 2008 at 7:11 pm
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.
September 1, 2008 at 8:08 pm
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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 9, 2008 at 9:28 am
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:
September 9, 2008 at 10:52 am
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.
September 9, 2008 at 1:07 pm
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.
April 4, 2010 at 10:48 am
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply