For an instructor lead, in-depth look at learning SQL click below.
Database normalization is a process used to organize a database into tables and columns. The goal with normalization is to minimize duplication and ensure data integrity.
Why Normalize a Database?
The main reasons for database normalization are to eliminate redundant (useless) data, ensure data dependencies make sense and to prevent data anomalies. Now let’s dive into the different forms of normalization with some SQL examples.
First Normal Form (1NF)
First Normal Form is achieved by ensuring the values in each column of a table are atomic. That is, they cannot be broken down any further.
|
1 2 3 4 5 6 7 |
-- For instance, consider the following 'books' table. CREATE TABLE books ( BookID int, Title varchar(255), Author varchar(255), Genre varchar(255) ); |
In this table, all columns are atomic. The title, author’s name or genre cannot be usefully broken down any further, hence it is 1NF.
Second Normal Form (2NF)
A table is said to be in 2NF, if it is already in 1NF and all non-key columns are fully dependent on the primary key.
|
1 2 3 4 5 6 7 |
-- Consider the table 'books' to be modified as: CREATE TABLE books ( BookID int, Title varchar(255), Genre varchar(255), AuthorID int ); |
In this modified ‘books’ table, the ‘Author’ column is replaced by ‘AuthorID’, which refers to a separate table ‘authors’ that stores all author details. Therefore, ‘AuthorID’ fully depends on the primary key ‘BookID’, and the table is in 2NF.
Third Normal Form (3NF)
A table is in 3NF, if it is in 2NF and there are no transitive functional dependencies.
|
1 2 3 4 5 6 7 8 |
-- Consider the following 'authors' table: CREATE TABLE authors ( AuthorID int NOT NULL, FirstName varchar(255), LastName varchar(255), DateOfBirth date, PRIMARY KEY (AuthorID) ); |
This ‘authors’ table is in 3NF as it is in 2NF, and there are no columns that are transitively dependent on the primary key.
In Conclusion
Database normalization is an essential part of database design. A good understanding of the underlying principles of normalization helps pave the way for creating robust, scalable SQL databases. Hopefully, these SQL examples have helped to visualize the concepts.