Second Normal Form (2NF) in SQL

Introduction

Second Normal Form helps organize data to avoid repeating groups and partial duplicates. It makes your database cleaner and easier to update.

When you want to remove partial dependencies in a table with a composite key.

When you notice some columns depend only on part of a multi-column key.

When you want to reduce data duplication and improve data integrity.

When designing a database for a small business to keep customer and order data clear.

When updating a database schema to make queries faster and more reliable.

Syntax

SQL

No direct SQL command; 2NF is a design rule:
- Ensure table is in 1NF (no repeating groups).
- Remove columns that depend on only part of a composite primary key.
- Create new tables for those columns.
- Link tables with foreign keys.

2NF applies only if the table has a composite primary key (more than one column).

If the primary key is a single column, the table is automatically in 2NF.

Examples

ProductName depends only on ProductID, part of the key, so violates 2NF.

SQL

Table: Orders
Columns: OrderID, ProductID, ProductName, Quantity
Primary Key: (OrderID, ProductID)

This removes partial dependency and achieves 2NF.

SQL

Split into two tables:
Products(ProductID, ProductName)
OrderDetails(OrderID, ProductID, Quantity)

Sample Program

This example shows how to fix partial dependency by creating separate tables for products and order details, linking them with keys.

SQL

-- Original table with partial dependency
CREATE TABLE Orders (
  OrderID INT,
  ProductID INT,
  ProductName VARCHAR(50),
  Quantity INT,
  PRIMARY KEY (OrderID, ProductID)
);

-- Insert sample data
INSERT INTO Orders VALUES (1, 101, 'Pen', 10);
INSERT INTO Orders VALUES (1, 102, 'Notebook', 5);

-- Normalize to 2NF by splitting tables
CREATE TABLE Products (
  ProductID INT PRIMARY KEY,
  ProductName VARCHAR(50)
);

CREATE TABLE OrderDetails (
  OrderID INT,
  ProductID INT,
  Quantity INT,
  PRIMARY KEY (OrderID, ProductID),
  FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

-- Insert data into new tables
INSERT INTO Products VALUES (101, 'Pen');
INSERT INTO Products VALUES (102, 'Notebook');
INSERT INTO OrderDetails VALUES (1, 101, 10);
INSERT INTO OrderDetails VALUES (1, 102, 5);

-- Query to show normalized data
SELECT od.OrderID, od.ProductID, p.ProductName, od.Quantity
FROM OrderDetails od
JOIN Products p ON od.ProductID = p.ProductID
ORDER BY od.OrderID, od.ProductID;

Important Notes

Always check if your table has a composite key before applying 2NF.

2NF helps reduce data duplication but does not remove all redundancy; 3NF is next step.

Summary

2NF removes columns that depend only on part of a composite key.

It requires splitting tables to separate related data.

Helps keep data consistent and easier to maintain.