What is Normalisation in SQL: 1NF to 5NF Explained for Beginners

Have you ever updated one value in a database, only to realise you need to change it in multiple places? It is frustrating, time-consuming, and a common source of errors. This is exactly the kind of problem that Normalisation in SQL is designed to solve, helping keep your data clean, organised, and easy to manage.

Whether you are building your first database or managing complex systems, understanding Normalisation in SQL can help you stay in control. It improves accuracy, reduces duplication, and makes updates simple and reliable. Let’s break it down in a simple way so you can understand how it works and why it matters!

Table of Contents

1) What is Normalisation in SQL?

2) Why is Normalisation in SQL Important?

3) When to Use SQL Normalisation?

4) Types of Database Normalisation

5) What is the Difference Between 3NF and BCNF?

6) What is a Functional Dependency in Normalisation?

7) Conclusion

What is Normalisation in SQL?

Normalisation is the process of eliminating data redundancy and increasing data integrity within a SQL table. It also helps to organise data efficiently in a database. In this multi-step procedure, which includes concepts like How to Create a Primary Key in SQL, data is structured into a tabular form while removing duplicated entries from relational tables.

Normalisation in SQL systematically arranges the tables and columns in a database to ensure that integrity constraints are properly maintained. This technique decomposes tables to remove data redundancy and prevents undesirable issues like update, insertion, and deletion anomalies, which are core responsibilities in a SQL Developer Job Description.

Why is Normalisation in SQL Important?

Normalisation is essential in SQL because it helps keep your database clean, organised, and easy to manage. Without it, data can become messy, repeated, and harder to update correctly. Normalisation in SQL is needed for the following key reasons: 

1) Removes Repeated Data: It avoids storing the same information in many places by dividing data into smaller, connected tables.

2) Speeds up Queries: Smaller tables make searching and finding what you need faster.

3) Avoids Errors When Updating: It helps prevent mistakes when adding, changing, or deleting data.

4) Keeps Data Accurate: Normalisation makes sure your data stays correct and consistent.

5) Helps Manage Links Between Data: It makes handling relationships between different parts of the database easier.

6) Fixes Tricky Issues: It deals with problems like partial and transitive dependencies that can cause confusion and errors.

When to Use SQL Normalisation?

Normalisation in SQL organises data into structured tables, reducing duplication, improving accuracy, and simplifying updates, while balancing performance needs. It is useful in the following situations:

1) When There is High Data Redundancy:

If the same data is repeated across multiple rows or tables, it wastes storage and increases the risk of inconsistencies. Normalisation removes duplication by separating data into related tables.

2) When Ensuring Data Integrity is Critical:

Normalisation enforces clear rules for storing data. It ensures values are atomic (First Normal Form), removes partial dependencies (Second Normal Form), and eliminates transitive dependencies (Third Normal Form), improving accuracy and consistency.

3) In Transactional Systems (Online Transaction Processing):

Normalisation is essential in systems that handle frequent inserts, updates, and deletions, such as banking, retail, or inventory management. It ensures reliable and consistent data during daily operations.

4) When Dealing with Complex Data Relationships:

If your database includes one-to-many or many-to-many relationships, Normalisation helps organise these connections into logical tables, making the structure easier to understand and manage.

5) When Planning for Scalability and Extensibility:

A normalised database is easier to expand as business needs grow. New data can be added without disrupting the existing structure, making long-term maintenance simpler.

6) When You Want to Avoid Data Anomalies:

Normalisation prevents update, insert, and delete anomalies, ensuring that changes in one place do not create inconsistencies elsewhere.

7) When Balancing Structure with Performance:

While Normalisation improves data organisation, highly normalised databases may require more joins, which can slow down read performance. In such cases, partial denormalisation may be used for optimisation.

Understand advanced analytics and reporting techniques with our Oracle SQL Advanced Training – Join now!

Types of Database Normalisation

The process of applying formal rules to a database design is called Normal Forms. It ensures the database is normalised, forming a key part of Normalisation in DBMS, and is categorised into the following types within Normalisation in SQL:

First Normal Form (1NF)

A table is considered to be in its First Normal Form if the atomicity of the table is 1. Here, atomicity states that a single cell can't hold multiple values. It must have only a single-valued attribute. The First standard form disallows the multi-valued attribute, composite attribute, and their combinations.

Let’s explore 1NF with an example:

Original Table (Not in 1NF)

In this table, the Product, Quantity, and Prices columns contain multiple values in a single cell. This is not compliant with 1NF.

Transformed Table (In 1NF)

In the transformed table, each column contains atomic values, and there are no repeating groups. Each row showcases a single product with its corresponding quantity and price, complying with the rules of 1NF.

Dive into SQL and speak the language of databases fluently! Sign up for our Introduction to SQL Course now!

Second Normal Form (2NF)

Second Normal Form (2NF) requires that the table first meet the criteria for First Normal Form (1NF). The table should also not have any partial dependencies. A partial dependency occurs when a non-prime attribute is dependent on a part of a candidate key, rather than on the whole candidate key. A non-prime attribute is an attribute that is not part of any candidate key.

Let’s explore 2NF with an example:

Consider a table that tracks orders with the structure as shown below:

In this table, the composite primary key is (OrderID, ProductID). But the ProductName and UnitPrice attributes are dependent only on ProductID, not the entire composite key. This creates a partial dependency and violates 2NF.

You can resolve this by splitting the data into two tables:

Orders Table:

Products Table:

In the Orders table, (OrderID, ProductID) is the composite key, and Quantity is functionally dependent on this composite key. There are no instances of partial dependencies here.

In the Products table, ProductID is the primary key, where ProductName and UnitPrice are non-prime attributes. There are no partial dependencies because both ProductName and UnitPrice are entirely dependent on the ProductID.

This separation ensures that the database structure complies with 2NF, eliminating partial dependencies and maintaining data integrity.

Third Normal Form (3NF)

To illustrate Third Normal Form (3NF), remember that a table must first fulfill the Second Normal Form (2NF) criteria. Additionally, it must not show any transitive dependencies which occurs when a non-prime attribute is dependent on another non-prime attribute rather than directly on the primary key.

We explore 3NF below with an example

In this table, StudentID is the primary key. The CourseName and Instructor attributes depend on CourseID rather than on StudentID, which creates a transitive dependency. This situation violates 3NF.

To remove transitive dependencies and achieve 3NF, we can split the table into two:

Students Table:

Courses Table:

In the Students table, StudentID is the primary key, and CourseID is a foreign key referring to the Courses table.

In the Courses table, CourseID is the primary key, and CourseName and Instructor depend directly on CourseID. This structure eliminates the transitive dependency by ensuring that non-key attributes are dependent only on the primary key. Thus, the database design now adheres to 3NF, preventing redundant data and maintaining data integrity.

Understand data integrity for efficient performance with our SQL Courses – Join now!

Boyce-Codd Normal Form (BCNF)

Boyce-Codd Normal Form (BCNF) is an advanced version of the Third Normal Form (3NF) in SQL Database Normalisation. A table is in BCNF if it's in 3NF and X is a superkey for every one of its non-trivial functional dependencies X→Y.

Let’s explore BCNF with an example:

Consider a table StudentCourse with the following attributes:

a) StudentID

b) CourseID

c) InstructorID

d) InstructorName

Assume the following functional dependencies:

a) StudentID, CourseID→InstructorID

b) InstructorID→InstructorName

Here are the steps to Achieve BCNF:

1) Identify Functional Dependencies:

a) StudentID, CourseID→InstructorID

b) InstructorID→InstructorName

2) Check for BCNF Violations:

To be in BCNF, every determinant must be a superkey.

a) StudentID, CourseID is a composite key and therefore a superkey for the table.

b) InstructorID is not a superkey for the table, as it does not uniquely determine all attributes of the table.

The dependency InstructorID → InstructorName violates BCNF because InstructorID is not a superkey

3) Decompose the Table

To resolve this BCNF violation, decompose the table into two tables:

Table 1: StudentCourse

Table 2: Instructor

4) Verify BCNF

a) In StudentCourse, the composite key StudentID, CourseID uniquely determines InstructorID.

b) In Instructor, InstructorID is the primary key and uniquely determines InstructorName.

Both tables are now in BCNF because every determinant is a superkey.

Here’s how the final BCNF tables look like

StudentCourse Table:

Instructor Table:

These tables are in Boyce-Codd Normal Form, as each non-trivial functional dependency has a superkey as its determinant.

Transform data into decisions becomes easy with the power of SQL! Sign up for our SQL Server Reporting Services (SSRS) Training now!

Fourth Normal Form (4NF)

4NF deals with multi-valued dependencies, which exist when one attribute can have multiple dependent attributes, and these dependent attributes are independent of the primary key.

Consider the table StudentActivities with the following attributes:

a) StudentID

b) Activity

c) Hobby

Let’s assume the following data:

In this table, Activity and Hobby are independent multi-valued attributes of StudentID. This means that a student's hobbies and activities are independent of each other and should not be combined in a single table.

Here’s how you can achieve 4NF:

1) Identify Multi-valued Dependencies:

The multi-valued dependencies are:

a) StudentID ->> Activity

b) StudentID ->> Hobby

2) Decompose the Table

To resolve the multi-valued dependencies, the table must be decomposed into two separate tables:

Table 1: StudentActivities

Table 2: StudentHobbies

3) Verify 4NF

a) In StudentActivities, StudentID uniquely determines Activity.

b) In StudentHobbies, StudentID uniquely determines Hobby.

Both tables are now in 4NF because other than a candidate key, there are no non-trivial multi-valued dependencies.

The final 4NF tables will look like this:

StudentActivities Table:

StudentHobbies Table:

These tables are in 4NF because they do not contain two or more independent multi-valued dependencies. Each table contains only one multi-valued dependency per candidate key.

Begin your MySQL journey and command the world of data! Sign up for our Introduction to MySQL Course now!

Fifth Normal Form (5NF)

5NF is the most complex form of Normalisation that removes join dependencies. In this situation, data needs to be joined from multiple tables to answer a specific query, even if those tables are already aligned with 4NF.

In simpler terms, 5NF ensures that all the information in the database can be completely reconstructed from its separate tables without needing any extra data.

Let’s illustrate 5NF with an example. Consider a scenario where the table ProjectStaffSkill keeps track of projects, the staff members working on them, and the skills they are employing:

The steps to achieve decomposition into 5NF is as follows:

Step 1: Identify Decomposition

The table ProjectStaffSkill has a potential problem where combinations of ProjectID, StaffID, and Skill may lead to anomalies and redundancy. To achieve 5NF, you need to decompose it into smaller tables that circumvent these issues.

Step 2: Decompose into Three Relations

You can decompose the original table into three smaller tables:

ProjectStaff Table:

StaffSkill Table:

ProjectSkill Table:

Step 3: Verify 5NF

Now, the original information can be reconstructed from these three tables without any loss of data or redundancy:

a) ProjectStaff showcases which staff members are working on which projects.

b) StaffSkill captures the skills of individual staff members.

c) ProjectSkill captures the skills required for each project.

What is the Difference Between 3NF and BCNF?

3NF removes transitive dependencies and allows a non-superkey to determine a value only if that value is part of a candidate key. BCNF is stricter as it says every determinant must be a superkey. So, while all BCNF tables meet 3NF rules, not all 3NF tables meet BCNF rules.

What is a Functional Dependency in Normalisation?

A functional dependency is represented as X → Y, where X and Y are sets of attributes. This notation says that any two rows in the table with the same value for X will also have same value for Y. So, the value of Y is functionally dependent on the value of X.

Conclusion

Normalisation in SQL plays a vital role in creating efficient, accurate, and well-structured databases. By reducing redundancy and improving data integrity, it ensures smooth Data Management and consistency. Applying proper Normalisation techniques helps build scalable systems, making databases easier to maintain, update, and optimise for long-term performance.

Looking to learn advanced techniques for optimising database performance? Sign up for our PostgreSQL Administration Training now!