Normalisation in SQL enables us to solve the problem of data redundancy and organise data using different forms in the database. As Normalisation eliminates data redundancy, it increases data integrity. The goal of Normalisation in SQL is to create a database structure which is scalable and efficient to maintain.
Normalisation divides larger tables into special, more specialised tables and uses foreign keys for establishing relationships between them, eliminating undesirable characteristics like insertion, and updation anomalies which can ensure that database integrity constraints properly execute the dependencies.
SQL for Data Science + Data Analytics + Data Visualization
Last Updated: 2022-07-21
With Azure Data Studio to Become a SQL Expert on Queries for your Business Logic for real world problems!
Level of Normalisation in SQL:
There are several levels of normalisation in SQL that are known as normal forms, defining the requirements for a table to become normalised. There are three most common normal forms:
- First Normal Form (1 NF):
Each column of the table contains atomic (indivisible) values so that it cannot be divided further. Each value in a column is a single, indivisible entity. If a value cannot be divided into smaller components, it should be stored in a separate table.
If you have a table where you want to specify an address, and you store the entire address in one column violates how 1NF works, because storing value in 1NF, you require to divide it into further smaller atomic values, which cannot be further separated, like a street address, city, pin code, etc.
Each column must have a unique room, as well as a unique identifier in each row, which is known as the primary key, that facilitates the unique identification of each row in the table. So a table with duplicate rows, each row has a unique identifier to distinguish it from other rows. And this is how the table is in the first normal form (1NF). - Second Normal Form (2 NF):
Second Normal Form (2NF) is the next level of normalization in SQL database design, which builds on the first normal form (1NF) requirements. In 2NF, a table must meet the following requirements:
The elements in a table must be in 1NF. All non-key columns in the table must be functionally dependent on the entire primary key. Functional dependence means that the value of a column can be uniquely determined by the value(s) in the primary key column(s). If a non-key column is not functionally dependent on the primary key, it should be moved to a separate table.
For example, consider a table called “Orders” with columns “Order ID”, “Customer Name”, “Customer Address”, “Product Name”, and “Product Price”. The primary key is “Order ID”. However, the “Product Name” and “Product Price” columns are not functionally dependent on the “Order ID” column, as they depend on “Product ID” instead. Therefore, “Product Name” and “Product Price” should be moved to a separate table, such as a “Products” table, which has its own primary key and includes the “Product ID” column.
This is how a table is said can be in second normal form (2NF). - Third Normal Form (3 NF):
This level of normalisation is built on the first normal form (1 NF) and second normal form (2 NF), and all non-key columns in the table must not be transitively dependent on the primary key, i.e., a non-key column must depend on another non-key column which further depends on the primary key.
For example, if a table with student details, columns with student name, student class, and class teacher name, the primary key is ‘student name’, the next column ‘student class’ is dependent on the primary key, and the third non-key ‘class teacher name’ is dependent on the student class, and not on the primary key directly. Therefore the ‘class teacher name’ should be moved to a separate table called ‘Stream Details’ along with ‘student class’ details.
This is how a table is said to be in third normal form (3 NF).
