Can someone please give an example of 1NF, 2NF and 3NF in plain english?

Question

Hey everyone,

I am trying to really wrap my head around the database normalization concept. I understand that it is used to avoid duplicate/redundant data, and it involves creating separate tables for certain things, but I'm not quite sure I understand 1NF, 2NF and 3NF fully. I have read some of the questions on here regarding normalization in general, as well as the wiki article, but I'd really appreciate a bit more help.

Can someone please give an example of 1NF, 2NF and 3NF in plain english?

Thanks very much,

Answer 1

http://en.wikipedia.org/wiki/1NF
http://en.wikipedia.org/wiki/2NF
http://en.wikipedia.org/wiki/3NF

Answer 2

I've never had a good memory for exact wording, but in my database class I think the professor always said something like:

The data depends on the key [1NF], the whole key [2NF] and nothing but the key [3NF].

Answer 3

Here's a quick, admittedly butchered response, but in a sentence:

1NF : Your table is organized as an unordered set of data, and there are no repeating columns.

2NF: You don't repeat data in one column of your table because of another column.

3NF: Every column in your table relates only to your table's key -- you would't have a column in a table that describes another column in your table which isn't the key.

For more detail, see wikipedia...

Answer 4

1NF is the most basic of normal forms - each cell in a table must contain only one piece of information, and there can be no duplicate rows.

2NF and 3NF are all about being dependent on the primary key. Recall that a primary key can be made up of multiple columns. As Chris said in his response:

The data depends on the key [1NF], the whole key [2NF] and nothing but the key [3NF] (so help me Codd).

2NF

Say you have a table containing courses that are taken in a certain semester, and you have the following data:

|-----Primary Key----|               uh oh |
                                           V
CourseID|  Semester  |  #Places   |  Course Name |
-------------------------------------------------|
IT101   |   2009-1   |  100       | Programming  |
IT101   |   2009-2   |  100       | Programming  |
IT102   |   2009-1   |  200       | Databases    |
IT102   |   2010-1   |  150       | Databases    |
IT103   |   2009-2   |  120       | Web Design   |

This is not in 2NF, because the fourth column does not rely upon the entire key - but only a part of it. The course name is dependent on the Course's ID, but has nothing to do with which semester it's taken in. Thus, as you can see, we have duplicate information - several rows telling us that IT101 is programming, and IT102 is Databases. So we fix that by putting the course name into another table, where CourseID is the ENTIRE key.

Primary Key |

CourseID    |  Course Name |
---------------------------|
IT101       | Programming  |
IT102       | Databases    |
IT103       | Web Design   |

No redundancy!

3NF

Okay, so let's say we also add the name of the teacher of the course, and some details about them, into the RDBMS:

|-----Primary Key----|                           uh oh |
                                                       V
Course  |  Semester  |  #Places   |  TeacherID  | TeacherName  |
---------------------------------------------------------------|
IT101   |   2009-1   |  100       |  332        |  Mr Jones    |
IT101   |   2009-2   |  100       |  332        |  Mr Jones    |
IT102   |   2009-1   |  200       |  495        |  Mr Bentley  |
IT102   |   2010-1   |  150       |  332        |  Mr Jones    |
IT103   |   2009-2   |  120       |  242        |  Mrs Smith   |

Now it should be obvious that TeacherName is dependent on TeacherID - so this is not in 3NF. To fix this, we do much the same as we did in 2NF - take TeacherName out of this table, and put it in its own, which has TeacherID as the key.

 Primary Key |

 TeacherID   | TeacherName  |
 ---------------------------|
 332         |  Mr Jones    |
 495         |  Mr Bentley  |
 242         |  Mrs Smith   |

No redundancy!!

One important thing to remember is that if something is not in 1NF, it is not in 2NF or 3NF either. So each additional Normal Form requires everything that the lower ones had, plus some extra conditions, which must all be fulfilled.