Database Normalization

Normalization

"Every non-key attribute must provide a fact about the key, the whole key, and nothing but the key: so help me Codd".

The third normal form (3NF) is a normal form used in database normalization. 3NF was originally defined by E.F. Codd in 1971. Codd's definition states that a table is in 3NF if and only if both of the following conditions hold:

Understanding the above is very important; I have joined projects where the stated design-objective was that the database would be third normal form; however this was not what was actually being implemented.

Consider a simple example where a table consists of a supplier key, supplier city and city status. 

S_KEY  S_CITY    C_STATUS
1      London    20
2      Paris     10
3      New York  30
4      Paris     10
5      London    20

The breach of 3NF occurs because the non-prime attribute C_Status is transitively dependent on S_Key via the non-prime attribute S_City. The fact that C_Status is functionally dependent on S_City makes the table vulnerable to logical inconsistencies, as there is nothing to stop the same city from being shown with different status values on different records.

In order to express the same facts without violating 3NF, it is necessary to split the table into two: 

S_KEY  S_CITY           C_CITY    C_STATUS
1      London           London    20
2      Paris            Paris     10
3      New York         New York  30   
4      Paris
5      London