What is Normal Forms and Normalization in MySQL

Normalization in MySQL is a process of structuring a relational database to minimize redundancy and improve data integrity. This guide introduces you to the concept of normalization and how it is applied in MySQL.

What is Normalization?

Normalization involves organizing data in a database into tables and columns in a way that reduces redundancy and dependency. It involves dividing large tables into smaller, interrelated tables and defining relationships between them.

First Normal Form (1NF)

The First Normal Form requires that each table cell should contain a single value, and each record needs to be unique.

Example: Converting a Table to 1NF

Suppose we have an Orders table that violates 1NF because it contains multiple values in the Items column.

Before 1NF:

To convert this table to 1NF, we need to ensure that each field contains only atomic (indivisible) values.

After 1NF:

CREATE TABLE Orders (    OrderID INT,    Item VARCHAR(100)
);

INSERT INTO Orders (OrderID, Item) VALUES
(1, 'Pen'),
(1, 'Notebook'),
(1, 'Eraser'),
(2, 'Pencil'),
(2, 'Eraser');

Each Item is now in its own row, adhering to 1NF.

Second Normal Form (2NF)

A table is in 2NF if it is in 1NF and all non-key columns are fully dependent on the primary key

Example: Converting a Table to 2NF

Let's consider a StudentCourses table that is in 1NF but not in 2NF due to partial dependency.

Before 2NF:

The Instructor is dependent only on the Course, not on the composite key (StudentID, Course).

After 2NF:

CREATE TABLE StudentCourses (    StudentID INT,    Course VARCHAR(100)
);

CREATE TABLE Courses (    Course VARCHAR(100),    Instructor VARCHAR(100)
);

INSERT INTO StudentCourses (StudentID, Course) VALUES
(101, 'Math'),
(101, 'Science'),
(102, 'Math');

INSERT INTO Courses (Course, Instructor) VALUES
('Math', 'Dr. Smith'),
('Science', 'Dr. Jones');

The Courses table is created to remove the partial dependency, achieving 2NF.

Third Normal Form (3NF)

Example: Converting a Table to 3NF

Consider a Customers table in 2NF but not in 3NF due to transitive dependency.

Before 3NF:

The City and Country are transitively dependent on CustomerID through ZipCode.

After 3NF:

CREATE TABLE Customers (    CustomerID INT AUTO_INCREMENT PRIMARY KEY,    Address VARCHAR(255),    ZipCode INT
);

CREATE TABLE ZipCodes (    ZipCode INT PRIMARY KEY,    City VARCHAR(100),    Country VARCHAR(100)
);

INSERT INTO Customers (CustomerID, Address, ZipCode) VALUES
(001, '123 Lane', 90001),
(002, '456 Avenue', 10001);

INSERT INTO ZipCodes (ZipCode, City, Country) VALUES
(90001, 'Los Angeles', 'USA'),
(10001, 'New York', 'USA');

The ZipCodes table is created to remove the transitive dependency, adhering to 3NF.