Database Normalization

There are three common forms of normalization: 1st, 2nd, and 3rd normal form.  There are several additional forms, such as BCNF, but I consider those advanced, and not too necessary to learn in the beginning.


1NF – First Normal Form

• Should be contained only atomic values
• There should not be repeating groups

How do we bring an unnormalized table into first normal form? Consider the following example:


This table is not in first normal form because the [Color] column can contain multiple values. For example, the first row includes values "red" and "green."

To bring this table to first normal form, we split the table into two tables and now we have the resulting tables:



Now first normal form is satisfied, as the columns on each table all hold just one value.


2NF - Second Normal Form

A database is in second normal form if it satisfies the following conditions:

•     It is in first normal form
•     All non-key attributes are fully functional dependent on the primary key

Consider the following example:



This table has a composite primary key [Customer ID, Store ID]. The non-key attribute is [Purchase Location]. In this case, [Purchase Location] only depends on [Store ID], which is only part of the primary key. Therefore, this table does not satisfy second normal form.

To bring this table to second normal form, we break the table into two tables, and now we have the following tables:




3NF - Third Normal Form

A table is in third normal form if:

•     It is in second normal form.
•     There is no transitive functional dependency. (A transitive dependency is a functional dependency in which X → Z (X determines Z) indirectly, by virtue of X → Y and Y → Z (where it is not the case that Y → X))

Consider the following example:




In the table, [Book ID] determines [Genre ID], and [Genre ID] determines [Genre Type]. Therefore, [Book ID] determines [Genre Type] via [Genre ID] and we have transitive functional dependency, and this structure does not satisfy third normal form.

To bring this table to third normal form, we split the table into two as follows: