Normal Forms

Question

Normal Forms

Steve Jones - SSC Editor

SSC Guru

Points: 734418
More actions
April 28, 2011 at 11:05 am

#389408

OK, I hesitate to ask this, but I would like to round this out.
I am looking for a basic set of articles that explains first, second, third, maybe more, normal forms. Not like the argument going on in the Relational Theory, not from a high academic, this is correct, Date v Codd.
I am looking for a basic explanation of what the normal forms mean from a practical perspective. Take a couple of simple tables and break them down to be normalized. Something like this, perhaps by working backwards in AdventureWorks or building a simple Order/Sales set of tables.
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

Viewing 13 posts - 1 through 12 (of 12 total)

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

TomThomson SSC Guru Points: 104773 More actions · Answer 1

How quickly do you want it?

I guess it's just the definitions, examples of how it changes something (before and after) and example of what goes wrong if you don't do it. No high-falutin' theory or any of that junk, and nothing controversial? Certainly no mention of NULLs because that would start a war.

Incidentally, I think I saw something a few weeks ago - an article on SQL Server Central (or perhaps written by a threadizen but posted somewhere else) that covered 1NF through 3NF very well.

If covering normal forms 1 through 5 WITHOUT the intermediates like EKNF and BCNF because the article can get too long, and leaving out 6NF (other than to say that it is applicable to schemas that have to handle time-dependent data, and avoids some of the problems that people often run into with that) because it is too hard for this sort of article, and leaving out DKNF (because most things don't have a DKNF form, there's no way to normalise them to DKNF) would be OK, I can do it if it's not urgent. But I'm heading back to the UK next Wednesday so wouldn't be able to start on it before about May 7th. Also, I'm not sure that covering all of forms 1 to 5 in a single article is going to produce something short enough, it might be better to split it in half with 1NF, 2NF and 3NF in one article; 4NF, 5NF and the non-technical one or two liner on 6NF in another.

Incidentally, the 6NF mentioned above is a Date invention and I regard it as a very good thing despite disagreeing with him on a couple of other things (MVLs and NULL, mostly). The other thing that used to be called 6NF is what is now called DKNF and is pretty useless (its uselessness is something else that Date and I have a common view on). Don't get the impression from the stuff going on in the normalisation topic in the relational theory forum that I think Date is wrong about everything, or that David Portas thinks Codd was wrong about everything.

Tom

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 2

Really the basics, Tom. Something between the DB Design one Paul White wrote and the crazy relational debate. A "normalization" for dummies, and I'd like to have a separate article for each one.

1NF

2NF

3NF

building on each other. You could go 4 and 5, or BNF if you want.

No hurry. Just filling Spackle holes here with some ideas to round out content on the site.

TomThomson SSC Guru Points: 104773 More actions · Answer 3

OK, Steve, separate article for each understood. I'll take it.

Tom

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

I can take a swing at this if Tom is feeling time pressured, I've pretty much avoided the entire debate out of self defense. Did you want these spackle-style (page and a half or so) or more complete full article per NF? I will say that Tom or someone of his knowledge and research would be able to complete the 4NF+ articles much faster then I. I know the basics of them but would need to re-research to keep from having foot in mouth disease.

EDIT: D'oh, sniped. All yours Tom. 🙂

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 5

Steve Jones - SSC Editor (4/28/2011)
OK, I hesitate to ask this, but I would like to round this out.
I am looking for a basic set of articles that explains first, second, third, maybe more, normal forms. Not like the argument going on in the Relational Theory, not from a high academic, this is correct, Date v Codd.
I am looking for a basic explanation of what the normal forms mean from a practical perspective. Take a couple of simple tables and break them down to be normalized. Something like this, perhaps by working backwards in AdventureWorks or building a simple Order/Sales set of tables.
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

You must be a glutton for punishment. 😀 What are you going to ask for next? An article that justifies why NULLs should be removed from all database systems? :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 6

HA, I like NULLs. I might ask for two articles, one pro and one con.

TomThomson SSC Guru Points: 104773 More actions · Answer 7

Steve Jones - SSC Editor (4/30/2011)
HA, I like NULLs. I might ask for two articles, one pro and one con.

maybe David Portas could write the pro and Jeff could write the con; that way they would be extremely interesting articles! :hehe::hehe::hehe:

Tom

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 8

Tom.Thomson (4/30/2011)
Steve Jones - SSC Editor (4/30/2011)
HA, I like NULLs. I might ask for two articles, one pro and one con.
maybe David Portas could write the pro and Jeff could write the con; that way they would be extremely interesting articles! :hehe::hehe::hehe:

I'm not sure which side is which there. If you mean that I'd have to write an article that says we should do away with NULLs, I have to ask... do you know what "Obese Opportunity" means? 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 9

Steve Jones - SSC Editor (4/30/2011)
HA, I like NULLs. I might ask for two articles, one pro and one con.

I like NULLs, as well. I was just thinking of the insanely long threads where people keep saying that NULLs aren't a part of a true relational database and Codd this and Date that and yada-yada-yada. I keep thinking "who gives a rat's patooti?" and that I hope I never run into a true relational database because it will even make Oracle look good. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TomThomson SSC Guru Points: 104773 More actions · Answer 10

Jeff Moden (4/30/2011)
Steve Jones - SSC Editor (4/30/2011)
HA, I like NULLs. I might ask for two articles, one pro and one con.
I like NULLs, as well. I was just thinking of the insanely long threads where people keep saying that NULLs aren't a part of a true relational database and Codd this and Date that and yada-yada-yada. I keep thinking "who gives a rat's patooti?" and that I hope I never run into a true relational database because it will even make Oracle look good. 😀

I like NULLs too. Of course I believe that a true relational system has to support them (after all, it has to support outer union and outer join, and they will certainly produce nulls). So I don't think a true relational system has to do badly.

But I do love reminding the anti-null fundamentalists that Ted Codd, the inventor of their holy relational model, stated clearly and plainly and very publicly that a system without decent NULL support could not be a relational system.

Tom

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 11

That's why I'm glad you decided to take this on, Tom. No BS and personal opinions would be identified as such instead of being identified as gospel. I think you'd bring the practical side to the article that Steve is looking for someone to write and that I'd like to read. I almost can't wait.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 12

I don't care who does it, but if Tom gets one in while anyone else's is in the Q, I'll go with Tom.

Tom, no great hurry and I'll let you know if I get something else. Doesn't have to be long. I'd figure 1-1.5 page of text with a few examples that show tables being moved from non-1NF->1NF would be fine.