How to implement normalization with SQL?

Introduction

Imagine organizing a disorganized garage into a well-lit area where everything is available and arranged appropriately. In the database realm, this procedure is known as normalization. A database works better when its data is well-structured and organized, just like your garage does when it is kept tidy. Eager to learn more? This article will discuss the first three normal forms (1NF, 2NF, and 3NF) along with some practical examples of normalization in SQL. You can learn how to make your databases more scalable and efficient, regardless of your level of experience with database design. Ready to tweak your data? Come on, let’s get started!

General description

• Understand the principles and objectives of database normalization with SQL.
• Apply first normal form (1NF) to ensure atomic values ​​and primary keys.
• Identify and eliminate partial dependencies to achieve second normal form (2NF).
• Eliminate transitive dependencies to comply with third normal form (3NF).
• Implement normalized database structures using practical SQL queries.

What is normalization?

An essential step in relational database architecture is normalization. It facilitates efficient organization of data by reducing redundancy and improving data integrity. To minimize anomalies, the procedure involves dividing a database into tables and establishing rule-based associations between them. Let's dive deeper into each normal form, explaining the principles and providing practical SQL examples.

First normal form (1NF)

Aim: Make sure that every table has a primary key and that every column contains atomic (indivisible) values. A table is in 1NF if it follows these rules:

• Single-valued attributes: Each column must contain only one value per row.
• Unique column names: Each column must have a unique name.
• The storage order is insignificant: The order in which the data is stored does not matter.

Example:

Let's consider a non-normalized table with repeated groups:

This table violates 1NF because the Products and Quantities columns contain multiple values.

Convert to 1NF:

SQL Implementation:

CREATE TABLE Orders (
    OrderID INT,
    CustomerName VARCHAR(255),
    Product VARCHAR(255),
    Quantity INT,
    PRIMARY KEY (OrderID, Product)
);

Second normal form (2NF)

Aim: Make sure the table is in 1NF and all non-key attributes are completely dependent on the primary key. This applies primarily to tables with composite primary keys.

Steps to achieve 2NF:

• Make sure you comply with 1NF: The table must already be in 1NF.
• Remove partial dependencies: Make sure that non-key attributes depend on the entire primary key, not just part of it.

Example:

Consider a table that is in 1NF but has partial dependencies:

Here, CustomerName depends only on CustomerID, not on the composite key (OrderID, ProductID).

Convert to 2NF:

1. Create separate tables for orders and customers:

Order table:

Customer table:

SQL Implementation:

CREATE TABLE Orders (
    OrderID INT,
    CustomerID INT,
    ProductID INT,
    Quantity INT,
    PRIMARY KEY (OrderID, ProductID)
);

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(255)
);

Third normal form (3NF)

Aim: Make sure the table is in 2NF and all attributes depend only on the primary key.

Steps to achieve 3NF:

• Make sure you comply with the 2NF standard: The table must already be in 2NF.
• Remove transitive dependencies: Ensure that non-key attributes do not depend on other non-key attributes.

Example:

Consider a table that is in 2NF but has transitive dependencies:

Here, ProductName depends on ProductID, not directly on OrderID.

Convert to 3NF:

1. Create separate tables for orders and products:

Order table:

Product table:

SQL Implementation:

CREATE TABLE Orders (
    OrderID INT,
    CustomerID INT,
    ProductID INT,
    Quantity INT,
    PRIMARY KEY (OrderID, ProductID)
);

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(255)
);

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(255)
);

Practical example: putting it all together

Let's say we start with the following denormalized data:

Step 1: Convert to 1NF

Split multivalued columns into atomic values:

Step 2: Convert to 2NF

Identify partial dependencies and separate them:

1. Order table:

1. Customer table:

1. Product table:

Step 3: Convert to 3NF

Ensure that there are no transitive dependencies by keeping direct dependencies only on primary keys:

• Tables created in 2NF are already in 3NF since all non-key attributes depend solely on the primary key.

Conclusion

In this article, we discuss how we can implement normalization with SQL. Mastering SQL normalization is essential to building reliable and effective databases. Redundancy can be greatly reduced and data integrity can be improved by understanding and implementing the ideas of the first three normal forms (1NF, 2NF, and 3NF). This procedure improves overall database performance as well as streamlines data management. Now that you have access to useful SQL examples, you can turn any complicated and disjointed collection of data into an efficient and well-structured database. Adopt these strategies to ensure the stability, scalability, and maintainability of your databases.

Frequent questions