November 12, 2011 at 1:45 pm
Comments posted to this topic are about the item Heath Normal Form
Tom
November 12, 2011 at 1:52 pm
Without prior knowledge of the QOD author I surmised it was you...
Now a general question for yourself .... in your experiences, around the world, what would you say are the percentage of DBs in 4NF and / or higher (5NF,6NF).
November 13, 2011 at 3:34 am
bitbucket-25253 (11/12/2011)
Without prior knowledge of the QOD author I surmised it was you...Now a general question for yourself .... in your experiences, around the world, what would you say are the percentage of DBs in 4NF and / or higher (5NF,6NF).
Well, I would guess about 50%, or maybe a bit lower, are in 4NF or higher.
actual counts from experience:
In 2007: system with 9 databases, 7 in 4NF or higher, 1 in 2NF (denormalised Management Information database), 1 not even in 1NF (arrogant and incompetent people who wouldn't follow instructions).
In 2002: system with 3 databases, 1 in BCNF and 2 not even in 1NF.
in 2000: system with 2 DBs, both in 4NF or higher.
Before 2000: most everything 4NF or higher, except MI stuff.
Many data warehouse/business intelligence/management information databases are not in 4NF, because they've been deliberately denormalised (which is reasonably safe because the usual concerns about update and insertion anomalies don't apply, the method of getting data into them is very different from other databases) to reduce join complexity of big queries in exchange for an increase of storage requirement through the increased redundancy of the data. Sometimes some parts of such databases aren't even in 3NF, some not even in 2NF.
At the other end, some databases have never had any normalisation done at all. They are not in 2NF, because nobody involved in the design knew anything about normalisation and thought they just had to code around all the anomalies, often not in 1NF because someone thought nonatomic fields were a good idea and primary keys an uneeded luxury. Unless these systems are very simple and don't get caught up in enhancements to provide extra features they can be a real pain.
It's fairly common for people to aim for 3NF (whether by guess or by using Bernstein's algorithm) and that usually ends up conforming to EKNF or BCNF (if they use Bernstein's algorithm it's guaranteed to be at least EKNF). Since any 3NF table which doesn't have overlapping candidate keys is guaranteed to be in BCNF, and overlapping candidate keys are rare, it's usually BCNF. Multivalued dependencies are also pretty rare, so probably most things that have been aimed at 3NF actually hit 4NF. I don't have any real feel for how rare join dependencies are, so I don't have a feel for how often 5NF is hit by accident, but I suspect it's quite often.
It's less common for people to aim for 4NF or for 5NF than for 3NF or EKNF, but that probably doesn't cause the proportion of DBs in 4NF or higher to be much lower, simply because most 3NF schemas are 4NF as noted above.
Of course some people will tell you that almost all SQL databases are not in any normal form because they have at least one column in at least one table that allows null. If you believe that pernicious claptrap, use a figure of about 1% instead of the about 50% I suggested above.
Tom
November 13, 2011 at 8:33 am
Tom,
When I saw this question related to "normal forms", I knew it must be from you. Thanks for the question as we got to know some interesting facts.
M&M
November 13, 2011 at 9:00 am
I really liked this question. Not least because I didn't have to read past answer possibility #1 to get my two points 🙂
November 13, 2011 at 2:35 pm
Thanks Tom for continuing to educate us on NF
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 13, 2011 at 3:38 pm
SQL Kiwi (11/13/2011)
I really liked this question. Not least because I didn't have to read past answer possibility #1 to get my two points 🙂
Well, I'm glad you liked it, even for that reason. At last I have managed to produce a question that most people get right before they can read the answer in the next day's update from sqlservercentral. massive 67% so far. 😎
But I wonder how long it will be before someone claims it's a trick question? :hehe:
Tom
November 13, 2011 at 5:16 pm
L' Eomot Inversé (11/13/2011)
SQL Kiwi (11/13/2011)
I really liked this question. Not least because I didn't have to read past answer possibility #1 to get my two points 🙂Well, I'm glad you liked it, even for that reason. At last I have managed to produce a question that most people get right before they can read the answer in the next day's update from sqlservercentral. massive 67% so far. 😎
But I wonder how long it will be before someone claims it's a trick question? :hehe:
I owe 2 points to google on this one.
I first assumed it was a typo from a enguish as my 8th language. Then I saw it was your question so I assumed word play. Then wiki[pedia] gave it to me straight :-D.
November 13, 2011 at 11:46 pm
Nice question Tom, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 14, 2011 at 1:44 am
This was removed by the editor as SPAM
November 14, 2011 at 1:50 am
I'd like to say I don't like these questions - but looking at the posts I appear to be in the minority!
Anybody else out there with me?
November 14, 2011 at 2:34 am
L' Eomot Inversé (11/13/2011)
But I wonder how long it will be before someone claims it's a trick question? :hehe:
Trick question! 😛
Just kidding, of course. Thanks for the question. I think I encountered the term Heath Normal Form once, but too long ago to be able to answer the question without the help of my good friend Google.
November 14, 2011 at 2:45 am
Google too (it's been a while since college, and theat's the only time I've really known all the names of things). Of course by now, the top hit on Google for "Heath Normal Form" is this question itself!
November 14, 2011 at 3:24 am
paul s-306273 (11/14/2011)
I'd like to say I don't like these questions - but looking at the posts I appear to be in the minority!Anybody else out there with me?
I have to say I'm with you on this one, Paul.
I do acknowledge that different people have different styles of learning and working, and for some people memorizing all the nuances of normalization theory may genuinely help them in their work.
However, the fact that Hugo admits to having to Google this one might suggest that this question is a little on the esoteric side, and that you could be quite good at your job without knowing or caring about the answer!
November 14, 2011 at 3:34 am
martin.whitton (11/14/2011)
paul s-306273 (11/14/2011)
I'd like to say I don't like these questions - but looking at the posts I appear to be in the minority!Anybody else out there with me?
I have to say I'm with you on this one, Paul.
I do acknowledge that different people have different styles of learning and working, and for some people memorizing all the nuances of normalization theory may genuinely help them in their work.
However, the fact that Hugo admits to having to Google this one might suggest that this question is a little on the esoteric side, and that you could be quite good at your job without knowing or caring about the answer!
I don't for a moment imagine that knowing that Chris Date suggested that Heath Normal Form night be a more suitable name than Boyce-Codd Normal Form would help anyone in their work (unless perhaps their work was writing a book about the history of relation theory development in the early years). But a lot of people find this kind of trivia fun, and there's always the hope that some people will read up either the papers referenced in the explanation or some of the things they find when googling for an answer and learn something useful from that.
Tom
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply