Heath Normal Form

  • TomThomson

    SSC Guru

    Points: 104773

    Comments posted to this topic are about the item Heath Normal Form

    Tom

  • Ron McCullough

    SSC Guru

    Points: 63877

    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).

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • TomThomson

    SSC Guru

    Points: 104773

    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

  • M&M

    SSC-Insane

    Points: 21699

    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

  • Paul White

    SSC Guru

    Points: 150442

    I really liked this question. Not least because I didn't have to read past answer possibility #1 to get my two points ๐Ÿ™‚

  • SQLRNNR

    SSC Guru

    Points: 281252

    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

  • TomThomson

    SSC Guru

    Points: 104773

    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

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    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.

  • Koen Verbeeck

    SSC Guru

    Points: 258985

    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

  • This was removed by the editor as SPAM

  • paul s-306273

    SSChampion

    Points: 10615

    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?

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Andrew Watson-478275

    SSCarpal Tunnel

    Points: 4652

    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!

  • martin.whitton

    SSCrazy

    Points: 2577

    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!

  • TomThomson

    SSC Guru

    Points: 104773

    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 27 total)

You must be logged in to reply to this topic. Login to reply