Normalization

Normalization

Normalization is a process used in database design to organise data in a way that reduces redundancy and improves data integrity. It involves dividing a database into smaller, related tables and defining relationships between them. This process helps to eliminate duplicate data and ensures that data dependencies are properly enforced.

Purpose of Normalization

The main purposes of normalization are:

• To eliminate data redundancy, which occurs when the same piece of data is stored in multiple places.
• To ensure data integrity by enforcing relationships between tables.
• To simplify data management and improve query performance.

Normal Forms

Normalization is achieved through a series of stages known as normal forms. Each normal form has specific requirements that must be met. The most commonly used normal forms are:

1. First Normal Form (1NF)
2. Second Normal Form (2NF)
3. Third Normal Form (3NF)

First Normal Form (1NF)

A table is in the first normal form if it meets the following criteria:

• Each column contains atomic (indivisible) values.
• Each entry in a column is of the same data type.
• Each column must have a unique name.
• The order in which data is stored does not matter.

To convert a table into 1NF, you must ensure that all attributes contain only atomic values. For example, consider a table that stores information about students and their courses:

StudentID | StudentName | Courses

1         | John Doe    | Math, Science

This table is not in 1NF because the Courses column contains non-atomic values (multiple courses in one field). To convert it to 1NF, you should separate the courses into individual rows:

StudentID | StudentName | Course

1         | John Doe    | Math

1         | John Doe    | Science

Second Normal Form (2NF)

A table is in the second normal form if it is already in 1NF and meets the following criteria:

• All non-key attributes are fully functionally dependent on the primary key.

A functional dependency means that the value of one attribute depends on the value of another attribute. For example, consider the following table that is already in 1NF:

StudentID | CourseID | Instructor

1         | C101     | Dr. Smith

1         | C102     | Dr. Jones

In this table, StudentID and CourseID together form the composite primary key. However, the Instructor attribute depends only on CourseID. This violates the rules of 2NF. To convert this table into 2NF, we need to separate the data into two tables:

Table 1: StudentCourses

StudentID | CourseID

1         | C101

1         | C102

Table 2: CourseInstructors

CourseID | Instructor

C101     | Dr. Smith

C102     | Dr. Jones

Third Normal Form (3NF)

A table is in the third normal form if it is already in 2NF and meets the following criteria:

• There are no transitive dependencies.

A transitive dependency occurs when a non-key attribute depends on another non-key attribute. For instance, consider the following table that is in 2NF:

StudentID | CourseID | Instructor | InstructorOffice

1         | C101     | Dr. Smith   | Room 101

1         | C102     | Dr. Jones   | Room 102

In this case, the InstructorOffice attribute depends on the Instructor, not on the primary key (StudentID and CourseID). To convert this table to 3NF, you should create a new table for instructors:

Table 1: StudentCourses

StudentID | CourseID | Instructor

1         | C101     | Dr. Smith

1         | C102     | Dr. Jones

Table 2: Instructors

Instructor | InstructorOffice

Dr. Smith   | Room 101

Dr. Jones   | Room 102

Benefits of Normalization

Normalization provides several benefits, including:

• Reduced data redundancy, which saves storage space.
• Improved data integrity, as there are fewer chances for data anomalies.
• Enhanced query performance, as well-structured tables allow for faster data retrieval.

Denormalization

While normalization is essential for database design, there are cases where denormalization is beneficial. Denormalization is the process of combining normalized tables back into a single table. This can improve performance in certain scenarios, such as when read operations are more frequent than write operations. However, denormalization can lead to data redundancy and potential integrity issues.

Summary

• Normalization is a process to organise data in a database.
• It reduces redundancy and improves data integrity.
• Normal forms include 1NF, 2NF, and 3NF.
• Normalization has benefits, but denormalization can be used for performance improvements.

Check your understanding

1. What is the purpose of normalization in database design?
2. Explain the criteria for a table to be in the first normal form (1NF).
3. What is a transitive dependency, and why is it important for third normal form (3NF)?
4. When might you consider denormalization in a database design?