Normalization is the process for assigning attributes to entities. Properly executed, the normalization process eliminates uncontrolled data redundancies, thus eliminating the data anomalies and the data integrity problems that are produced by such redundancies.

Normalization does not eliminate data redundancy; instead, it produces the carefully controlled redundancy that lets us properly link database tables.

A table is in 1NF when all the key attributes are defined (no repeating groups in the table) and when all remaining attributes are dependent on the primary key. However, a table in 1NF still may contain partial dependencies, i.e., dependencies based on only part of the primary key and/or transitive dependencies that are based on a non-key attribute.

A table is in 1st Normal Form (1NF) when it meets the following criteria:

1. Each row of the table represents a single, unique entity (i.e. there are no repeating groups of data in a single row)
2. Each column of the table has a single, atomic (indivisible) value (i.e. no cells contain multiple values)
3. The table has a primary key, which uniquely identifies each row in the table.

A table is in 2NF when it is in 1NF and it includes no partial dependencies. However, a table in 2NF may still have transitive dependencies, i.e., dependencies based on attributes that are not part of the primary key.

A table is in 2nd Normal Form (2NF) when it meets the following criteria:

1. It is in 1st Normal Form (1NF)
2. All non-primary key columns are fully dependent on the primary key. In other words, non-primary key columns cannot be functionally dependent on only a subset of the primary key.

A table in 2NF is considered to be free of partial dependencies.

A table is in 3NF when it is in 2NF and it contains no transitive dependencies.

A table is in 3rd Normal Form (3NF) when it meets the following criteria:

1. It is in 2nd Normal Form (2NF)
2. There are no transitive dependencies in the table, which means that non-primary key columns are not dependent on other non-primary key columns.

A table in 3NF is considered to be free of transitive dependencies, and all the data in the table is directly tied to the primary key.

Table in BCNF

A table is in Boyce-Codd Normal Form (BCNF) when it is in 3NF and every determinant in the table is a candidate key.

For example, if the table is in 3NF and it contains a nonprime attribute that determines a prime attribute, the BCNF requirements are not met.

A table is in Boyce-Codd Normal Form (BCNF) when it meets the following criteria:

1. It is in 3rd Normal Form (3NF)
2. For every non-trivial functional dependency X -> Y, X must be a superkey.

A table in BCNF is considered to be free of all types of anomalies that can occur in 3NF such as insertion, update, and deletion anomalies. It is a stronger form of 3NF and is less prone to data inconsistencies. It is also known as 3.5NF.

A partial dependency exists when an attribute is dependent on only a portion of the primary key. This type of dependency is associated with 1NF.

The most common anomalies considered when data redundancy exists are: update anomalies, addition anomalies, and deletion anomalies. All these can easily be avoided through data normalization. Data redundancy produces data integrity problems, caused by the fact that data entry failed to conform to the rule that all copies of redundant data must be identical.

New Tables

A surrogate key is an artificial PK introduced by the designer with the purpose of simplifying the assignment of primary keys to tables. Surrogate keys are usually numeric, they are often automatically generated by the DBMS, they are free of semantic content (they have no special meaning), and they are usually hidden from the end users.

A dependency based on only a part of a composite primary key is called a partial dependency. Therefore, if the PK is a single attribute, there can be no partial dependencies.

This condition is known as a transitive dependency. A transitive dependency is a dependency of one nonprime attribute on another nonprime attribute. (The problem with transitive dependencies is that they still yield data anomalies.)