Normalization in SQL to Reduce Data Redundancy

Problem

Various levels of normalization in SQL can be used to reduce data redundancy and have a better-structured relational data model. This tutorial looks at these various levels with explanations and examples in Microsoft SQL Server for beginners.

Solution

In this tutorial, we will talk about normalization, a process to help organize your relational database design.

What is Normalization in SQL?

It is a process that reduces redundant data, improves data integrity, simplifies table structure, and fixes dependency issues. Normalization has different levels which are covered below.

Database Normalization Levels in a DBMS

The following table summarizes the levels of normalization in a relational model:

First Normal Form (1NF)

For a database table to comply with 1NF, it should meet the following criteria:

• A cell cannot have more than a single value (must be indivisible).
• The rows should contain a primary key to identify each row.
• There is no top-to-bottom nor left-to-right order.
• No duplicated rows are allowed.

1NF Example

The following example shows a table that is not in 1NF:

The game stats are not atomic, we can divide the data. The following example shows how to change this denormalized table to the 1NF:

We divided the game stats in points, assists, rebounds and the game date.

Second Normal Form (2NF)

For the 2NF, the database table must accomplish the 1NF and eliminate partial dependencies. The partial dependency occurs when the columns that are not primary keys are partially dependent on a primary key. In other words, if the non-primary keys are dependent on the primary key, it is in the 2NF.

2NF Example

Let’s say that we have the previous table with basketball statistics in the 1NF:

As you can see, there is some dependency between Team Name, Team City, and Coach Name. There are also dependencies between Arena Name and Arena City. Sponsor does not have a dependency with the other columns, so it can be in a separate table. Finally, the player statistics can also be separated in another table because they do have a dependency with other columns.

To achieve the 2NF, we will divide the table into the following tables:

Players

Teams

Arenas

Sponsors

PlayerStats

Third Second Normal Form (3NF)

The table is in the 3NF when two conditions are accomplished:

• The table is in 2NF.
• The transitive dependency should be removed.

The transitive dependency occurs when a non-key attribute depends on another with no key. It should depend on the primary key instead.

3NF Example

Let’s take a look at an example to understand the 3NF.

To convert from the 2NF to the 3NF, we need to remove the transitive dependencies first. In this example, the Coach Name and Team City depend on the Team Name. To fix this, we will convert to the 3NF by doing this:

Teams

TeamDetails

Dividing the table players into teams and team details will remove the transitive dependencies.

The Boyce-Codd Normal Form (BCNF) Normalization

The BCNF was created to fix some normalization problems that were not fixed in the 2NF. A table is in BCNF if all redundancy based on functional dependency is removed. The following example illustrates the BCNF.

BCNF Example

The following example shows how to convert the tables to the BCNF. We create a table PlayerSponsors to make sure that every determinant is a candidate key and we remove the Sponsor ID column from the PlayerStats table.

PlayerSponsors

PlayerStats

Fourth Normal Form (4NF)

The 4NF is related to the multivalued dependency. A multivalued dependency occurs when one attribute of the table uniquely determines another attribute, and both attributes are independent of each other.

4NF Example

The players can have several sponsors and arenas in different games. That can lead to a multivalued dependency. To fix that, we will modify the PlayerArenas table like this:

PlayerArenas

We will also remove the Arena ID from the PlayerStats table.

PlayerStats

Fifth Normal Form (5NF)

Finally, we have the 5NF. This normalization is also called the PJ AND NF (projection and join normal form). This form removes redundancy by recording multi-valued facts by the isolation of semantically-related multiple relationships. In other words, a table is in the 5NF if every non-trivial join dependency in the table is implied by the primary key. To understand this 5NF, we will use an example.

5NF Example

In this example, we have two tables and we will decompose the PlayerStats table into 2 tables:

PlayerPerformance

PlayerTeams

The decomposition into two tables ensures that the information can be reconstructed and that the join dependencies are consequences of the candidate keys.

Next Steps

For more related information, refer to the following links:

• Implement Dimension Denormalization Using Synapse Mapping Data Flow
• Understanding First Normal Form in SQL Server
• Explore the Role of Normal Forms in Dimensional Modeling
• How to create a SQL Server foreign key
• Drop and Re-Create All Foreign Key Constraints in SQL Server
• SQL Server Foreign Key Update and Delete Rules
• SQL Queries Tutorial
• SQL Quick Reference Guide

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 10 years of experience as a QE and developer for SQL Server related software. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs.

• MSSQLTips Awards: Author of the Year Contender – 2015-2018, 2022, 2023 | Champion (100+ tips) – 2018