Relational Databases  «Prev  Next»

Lesson 8The first, second, and third normal forms
Objective Understand what the different normal forms are.

1st, 2nd, 3rd Normal Forms used in Relational Databases

Tables are normalized to eliminate redundant information, to make updates easier, and to save storage space.
There are three different normalization levels, or forms.
The following slideshow describes the three normal forms.
  1. first,
  2. second, and .
  3. third normal forms

1) Normalized Data 1 2) Normalized Data 2 3) Normalized Data 3 4) Normalized Data 4
  1. There are 3 different values stored in a single column, the table is not considered normalized.
  2. In the first normal form, information items have been put into their own columns
  3. The second normal form introduces a unique value that describes each row, and only that row. Typically the unique identifier has nothing to do with the data in the table, it is usually a counter.
  4. In third normal form, the information within each table is not duplicated, and the tables are tied together by the Item name.


First Normal Form

An entity is in First Normal Form (1NF) when all tables are two-dimensional with no repeating groups. A row is in first normal form (1NF) if all underlying domains contain atomic values only. 1NF eliminates repeating groups by putting each into a separate table and connecting them with a one-to-many relationship.

First Step to achieve Normalization

Make a separate table for each set of related attributes and uniquely identify each record with a primary key.
  1. Eliminate duplicative columns from the same table.
  2. Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).

Second Normal Form

An entity is in Second Normal Form (2NF) when it meets the requirement of being in First Normal Form (1NF) and additionally:
  1. Does not have a composite primary key. Meaning that the primary key can not be subdivided into separate logical entities.
  2. All the non-key columns are functionally dependent on the entire primary key.
  3. A row is in second normal form if, and only if, it is in first normal form and every non-key attribute is fully dependent on the key.
  4. 2NF eliminates functional dependencies on a partial key by putting the fields in a separate table from those that are dependent on the whole key. An example is resolving many to many many relationships using an intersecting entity as described here Convert Many-to-many to one-to-many

Third Normal Form

An entity is in Third Normal Form (3NF) when it meets the requirement of being in Second Normal Form (2NF) and additionally:
  1. Functional dependencies on non-key fields are eliminated by putting them in a separate table. At this level, all non-key fields are dependent on the primary key.
  2. A row is in third normal form if and only if it is in second normal form and if attributes that do not contribute to a description of the primary key are moved into a separate table. An example is creating look-up tables.