Database normalization

Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy and dependency. It involves decomposing a table into less redundant (and smaller) tables without losing information and ensuring data dependencies make sense. The goal is to reduce data anomalies and improve data integrity.

Normalization typically involves several stages, known as normal forms. The most common are:

• First Normal Form (Atomicity & Uniqueness)
• Second Normal Form (No partial Dependencies)
• Third Normal Form (No transitive Dependencies)

First Normal Form (1NF)

A table is in 1NF if:

1. Atomicity

All the values in a table are atomic, meaning each column contains unique, indivisible values.

2. Uniqueness

each row must be uniquely identifiable. This is achieved through a primary key.

3. No Repeating Groups

There should be no repeating groups of columns or implied groups.

1NF Example

Before 1NF - Student_Course Table

After 1NF - - Student_Course Table

Summary 1NF Example

• StudentID and Name: These fields remain the same as they already contain atomic values.
  
• Course: Each course is split into separate rows so that each row contains only one course.
• Instructor: Each course is matched with its respective instructor in individual rows.
• Department: Each course is matched with its respective department in individual rows.

1NF and First / Last Names

Do first and last names need to be separated for the table to be in 1NF? 

Some people would argue that first and last names are separate pieces of information and therefore should be separated into different fields in order for the table to be in 1NF. However in practice it largely depends on what you intend to do with the data.

If you are going to want to be editing the first or last name separately, or if you want to search/sort/filter by first or last name then it is certainly good practice to store each piece of data separately, but it's not strictly required for 1NF

Second Normal Form - No partial dependency

A table is in 2NF if:

• It is already in 1NF.
• Full Functional Dependency (No partial dependency)

A table is not in 2NF if you can guess the value of a non-key column using only part of a composite primary key.

If there is no composite key (the primary key is a single column), the table is automatically in 2NF as long as it’s in 1NF. This is because partial dependencies only occur with composite keys.

2NF Example: Before

Before 2NF - - Student_Course Table

StudentID	Name	Course	Instructor	Department
1	Alice Johnson	Math	Prof A	Math
1	Alice Johnson	Science	Prof B	Science
2	Bob Lee	English	Prof C	English
2	Bob Lee	History	Prof D	Humanities

Issues in the 1NF Table

• The Name depends only on StudentID and not on Course.
• The Instructor and Department depend only on Course and not on StudentID.

Steps to Achieve 2NF

1. Separate the attributes that are not fully dependent on the primary key into different tables.
2. Create tables where each non-key attribute depends on the whole primary key.

2NF Example: Result

Students Table

StudentID	Name
1	Alice Johnson
2	Bob Lee

Courses Table

Course	Instructor	Department
Math	Prof A	Math
Science	Prof B	Science
English	Prof C	English
History	Prof D	Humanities

StudentCourses Table

StudentID	Course
1	Math
1	Science
2	English
2	History

3NF (Third Normal Form) - No Transitive Dependencies

For 3NF, the table must already be in 2NF, and there must be no transitive dependencies. Each non-key attribute must depend only on the primary key.

A table is in 3NF if you can’t figure out the value of one non-key column using another non-key column; everything must come directly from the primary key.

Example of Transitive Dependency

Courses Table

Course	Instructor	Department
Math	Prof A	Math
Science	Prof B	Science
English	Prof C	English
History	Prof D	Humanities

The Courses Table is not in 3NF because it has a transitive dependency.

• Primary Key: The primary key here is Course.

• Problem: Instructor depends on Course, but Department depends on Instructor, which in turn depends on Course.

3NF Result

To make the table 3NF, you should eliminate the transitive dependency by removing the link between Instructor and Department. You can do this by creating a new table for instructors.

Now, Department depends directly on Instructor, and there is no transitive dependency. The tables are in 3NF.