Database Normalization

Consider a org chart that has to be represented in a database


1 NF


Table A
Column Header > Person ID  |  Person Name | Report ID 1 | Report ID 2 | Report ID 3
  Row >                    23                Jack                  24                     14               10


Here, multiple manager-report relationships are represented in one row.
In 1NF, each relationship is represented in it's own row.


The new structure would look like:
Table A

 Column Header >  Person ID  |   Person Name

  Row >                      23                Jack      

Table B

 Column Header >  Person ID  | Report ID  |

  Row >                      23                   24      

  Row >                      23                   14  

  Row >                      23                   10




2 NF

Consider we are storing addess for each Person in Table A


Table A
Column Header > Person Id | Person Name | Address   |   City   |  State | Zip
  Row                      1                 Jack            10 R rd      NYC     NY     100001
  Row                      2                 Jill               12 Z Rd     NYC     NY     100001


The last 3 columns in the two rows contain duplicate information and can be converted into its on data unit.


Table C
Column Header   Place Id  | City |  State  | Zip
  Row                   1              NYC   NY    100001


Table A

Column Header > Person Id | Person Name | Address   |   Place Id

  Row                      1                 Jack            10 R rd         1

  Row                      2                 Jill               12 Z Rd        1

2NF involves exposing a hidden relationship within the same unit of data. In the example above, data about a person (name, address, city, state, zip) could be split in two two units of data with a relationship i.e. Person (name,  address, Place Id) and Place (City, State, Zip)


In general, normalization improves (update/delete/add) efficiency, but reduces read efficiency. For data, which is not updated frequently (thus not requiring write efficiency), we might want to keep the data in denormalized form, instead of splitting in over multiple tables. Data redundancy increases, storage requirement increases, data modifications are more complex and need to be made in several places/rows/tables.

3 NF


Consider
Orders Table
Column Header  |   Order Id  |  UnitPrice |  Quantity  |  Total
Row                           1               10            2               20 

Row                           2                5             6               30


This table can be reduced to

Column Header  |   Order Id  |  UnitPrice |  Quantity 

Row                           1               10            2           

Row                           2                5             6           

Our Data (Unit Price, Quantity, Total) has an implicit relationship Total = Unit Price * Quantity.  Our data can thus be reduced to (Unit Price, Quantity). Since the relationship is implicit, we do not need to store the relationship in some second table (unlike 1NF, 2NF).

4 NF


Consider a data struct (A, B, C)
A to B is a 1:many relationship.
A to C is a 1:many relationship.
B and C have no relationship between them.


Storying (A,B,C) in a table would give us composite key uniqueness, but would involve more keys than necessary.
e.g
A takes values (1,2,3)
B takes values (x,y,z)
C takes values (P,Q,R)


(A,B) have an explicit relationship where only certain pairs are possible.
(A,C) have an explicit relationship where only certain pairs are possible.
(B,C) have an implicit relationship. Any pair is possible.


We can leverage the fact that there is an implicit relationship, to reduce the number of rows. The implicit relationship does not need to be represented.
Each explicit relationship can be expressed in its own table.


We'll now have upto 3 * 3 * 3 = 27 rows (Table 1), when the same information can be represented in upto 18 rows over two tables.(Tables 2, 3)


Table 1   A    B    C  (27 rows)
               1     x    P
               1     x    Q
               .....
               3      z    R


Table 2   A   B   (upto 9 rows)
              1     x
              ...
              3     z


Table 3  A   C   (upto 9 rows)
              1    P
              ...
              3    R


4NF involves exposing multiple relationships in the same unit of data.
Here the reduction is possible only because there is no relationship between B, C.
Otherwise 4 NF would not have been possible. e.g. if the (B,C) combination of (y, Q) is not possible, it implies that B,C are coupled and we would probably have to see if 5 NF could be applied.


Check out http://en.wikipedia.org/wiki/Fourth_normal_form#Example for a better 4NF example.


A unit of data should be one, where any two fields cannot be expressed as a seperate unit of data. A relationship can only be formed between independent units of data.




5NF
Consider a table with 3 columns, A, B, C
Assume that (A,B), (B,C), (A,C) are relationships that are independent of each other.
Then, we can break the table into 3 tables, leading to lesser number of rows.
We break (A,B,C) into (A,B), (B,C) and (A,C)


If the relationships were not independent of each other, 5 NF would not have been possible.


Check out http://en.wikipedia.org/wiki/Fifth_normal_form#Example for a great example.