Normalization – SS2 Data Processing Lesson Note (First Term)

Normalization is the process of organising data in a database so that redundant data is minimised and data dependencies make sense. It is a logical design method that reduces duplicate data and design flaws. Its two goals are to eliminate redundant data and to ensure that only related data is stored in a table.

Behavioural objectives

By the end of the lesson, students should be able to:

• define normalization;
• state the two goals of the normalization process;
• explain what a normal form is;
• state the rules of the first, second and third normal forms.

What is normalization?

A database management system is a collection of computer software that enables users to define, create and maintain a database. Defining a database involves specifying constraints for the data stored in it. Normalization is a logical design method which minimises duplicate, or redundant, data and design flaws.

Normalization is the process of effectively organising data in a database. Its primary purpose is to allow the update, insert and delete operations to be performed on a single database table and propagated throughout the database using the defined relationship.

What are the goals of normalization?

There are two goals of the normalization process:

1. Eliminating redundant data (for example, storing the same data in more than one table).
2. Ensuring data dependencies make sense (only storing related data in a table).

What is a normal form?

The normal form (NF) of relational database theory is used to determine a table’s degree of vulnerability to logical inconsistencies and anomalies. The higher the normal form applicable to a table, the less vulnerable it is.

Each table has a “Highest Normal Form” (HNF). By definition, a table always meets the requirements of its HNF and all normal forms lower than its HNF. Also by definition, a table fails to meet the requirements of any normal form higher than its HNF.

Normal form theory deals with how to reduce the amount of data redundancy within a given table. Each normal form represents a level. To satisfy the requirements of a certain level, the requirements of the previous level must be met.

To reach the optimal normal form for the tables within a database, the creator starts with a large list of all the data to be held in the database. The creator then works through the normal forms until the data can no longer be broken down into smaller tables.

What are the types of normal form?

First Normal Form (1NF)

First Normal Form (1NF) says that all column values must be atomic. For every row-by-column position in a given table, there is only one value, not an array or list of values. In 1NF, the following rules are observed:

• Eliminate repeating information.
• Create separate tables for related data.

Second Normal Form (2NF)

The second normal form (2NF) further addresses the removal of duplicate data. The rules for the second normal form are:

• Remove subsets of data that apply to multiple rows of a table and place them in a separate table.
• Create relationships between these new tables and their predecessors through the use of foreign keys.

Note: 2NF attempts to reduce the amount of redundant data in a table by extracting it, placing it in a new table or tables, and creating relationships between those tables.

Third Normal Form (3NF)

There are two basic requirements for a database to be in Third Normal Form:

• The requirements of both 1NF and 2NF must have been met.
• Remove columns that are not fully dependent upon the primary key.

Fourth Normal Form (4NF) and Fifth Normal Form (5NF)

The fourth and fifth normal forms are beyond the scope of this syllabus.

Note: Both the fourth and fifth normal forms still follow the goals of the normalization process, which are:

1. Eliminating redundant data.
2. Ensuring data dependencies make sense.

Evaluation

1. Define normalization.
2. What does First Normal Form (1NF) say about column values?
3. State the two rules of Second Normal Form (2NF).
4. State the two requirements for a database to be in Third Normal Form (3NF).

Assignment

1. State the two goals of the normalization process.