Normalization

Normalization:

Eliminating redundant data (for example, storing the same data in more than one table) and

Ensuring data dependencies make sense (only storing related data in a table).

1NF:

·         Eliminate duplicative columns from the same table.

·         Create separate tables for each group of related data and identify each row with a unique column (the primary key).

Example:

Each attribute must be atomic

·          No repeating columns within a row.

·          No multi-valued columns.

1NF simplifies attributes

·          Queries become easier.

2NF:

·         Remove subsets of data that apply to multiple rows of a table and place them in separate tables.

·         Create relationships between these new tables and their predecessors through the use of foreign keys.

Example:

Each attribute must be functionally dependent on the primary key.

·          Functional dependence - the property of one or more attributes that uniquely determines the value of other attributes.

·          Any non-dependent attributes are moved into a smaller (subset) table.

2NF improves data integrity.

·          Prevents update, insert, and delete anomalies.

3NF:

·         Already meet the requirements of both 1NF and 2NF

·         Remove columns that are not fully dependent upon the primary key.

Example:

Remove transitive dependencies.

•            Transitive dependence - two separate entities exist within one table.

•            Any transitive dependencies are moved into a smaller (subset) table.

3NF  further improves data integrity.

•            Prevents update, insert, and delete anomalies.

Other Normal Forms:

Boyce-Codd Normal Form (BCNF)

•            Strengthens 3NF by requiring the keys in the functional dependencies to be superkeys (a column or columns that uniquely identify a row)

Fourth Normal Form (4NF)

•            

•           Eliminate trivial multivalued dependencies.

Fifth Normal Form (5NF)

•            Eliminate dependencies not determined by keys.