By using normalization, we can organize database to reduce redundancy and improve data integrity.
Business Scenario: Customer Orders
Unnormalized Table
Suppose you have an unnormalized table that stores customer orders along with their order details:
CREATE TABLE CustomerOrders (
OrderID INT,
CustomerName VARCHAR(100),
OrderDate DATE,
ProductIDs VARCHAR(100),
ProductNames VARCHAR(100),
Quantities VARCHAR(100)
);
-- Sample data
INSERT INTO CustomerOrders (OrderID, CustomerName, OrderDate, ProductIDs, ProductNames, Quantities)
VALUES (1, 'Alice', '2025-01-01', '101,102', 'Laptop,Mouse', '1,2');
In this example, the ProductIDs
, ProductNames
, and Quantities
columns contain multiple values separated by commas, which violates the 1NF rule.
First Normal Form (1NF)
To achieve the First Normal Form (1NF), ensure that each column contains atomic values and there are no repeating groups. We need to separate the order details into individual rows:
CustomerOrders Table:
CREATE TABLE CustomerOrders (
OrderID INT,
CustomerName VARCHAR(100),
OrderDate DATE
);
-- Sample data
INSERT INTO CustomerOrders (OrderID, CustomerName, OrderDate)
VALUES (1, 'Alice', '2025-01-01');
OrderDetails Table:
CREATE TABLE OrderDetails (
OrderDetailID INT PRIMARY KEY,
OrderID INT,
ProductID INT,
ProductName VARCHAR(100),
Quantity INT,
FOREIGN KEY (OrderID) REFERENCES CustomerOrders(OrderID)
);
-- Sample data
INSERT INTO OrderDetails (OrderDetailID, OrderID, ProductID, ProductName, Quantity)
VALUES
(1, 1, 101, 'Laptop', 1),
(2, 1, 102, 'Mouse', 2);
Second Normal Form (2NF)
To achieve the Second Normal Form (2NF), remove partial dependencies. This means that non-key attributes should depend on the whole primary key. We need to separate the customer and order details into two tables:
CustomerOrders Table:
CREATE TABLE CustomerOrders (
OrderID INT PRIMARY KEY,
CustomerName VARCHAR(100),
OrderDate DATE
);
-- Sample data
INSERT INTO CustomerOrders (OrderID, CustomerName, OrderDate)
VALUES
(1, 'Alice', '2025-01-01'),
(2, 'Bob', '2025-01-02');
OrderDetails Table:
CREATE TABLE OrderDetails (
OrderDetailID INT PRIMARY KEY,
OrderID INT,
ProductID INT,
ProductName VARCHAR(100),
Quantity INT,
FOREIGN KEY (OrderID) REFERENCES CustomerOrders(OrderID)
);
-- Sample data
INSERT INTO OrderDetails (OrderDetailID, OrderID, ProductID, ProductName, Quantity)
VALUES
(1, 1, 101, 'Laptop', 1),
(2, 1, 102, 'Mouse', 2),
(3, 2, 103, 'Keyboard', 1);
Third Normal Form (3NF)
To achieve the Third Normal Form (3NF), remove transitive dependencies. This means that non-key attributes should not depend on other non-key attributes. We need to separate the product details into another table:
Products Table:
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100)
);
-- Sample data
INSERT INTO Products (ProductID, ProductName)
VALUES
(101, 'Laptop'),
(102, 'Mouse'),
(103, 'Keyboard');
OrderDetails Table (Updated):
CREATE TABLE OrderDetails (
OrderDetailID INT PRIMARY KEY,
OrderID INT,
ProductID INT,
Quantity INT,
FOREIGN KEY (OrderID) REFERENCES CustomerOrders(OrderID),
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);
-- Sample data
INSERT INTO OrderDetails (OrderDetailID, OrderID, ProductID, Quantity)
VALUES
(1, 1, 101, 1),
(2, 1, 102, 2),
(3, 2, 103, 1);
Boyce-Codd Normal Form (BCNF)
BCNF is a stricter version of 3NF. To achieve BCNF, ensure that for every functional dependency (A -> B), A is a superkey. The tables above are already in BCNF.
Fourth Normal Form (4NF)
To achieve the Fourth Normal Form (4NF), remove multi-valued dependencies. This means that a record should not contain two or more independent multi-valued facts about an entity. Suppose we have a table that stores employee skills and projects:
EmployeeSkillsProjects Table:
CREATE TABLE EmployeeSkillsProjects (
EmployeeID INT,
Skill VARCHAR(100),
Project VARCHAR(100)
);
-- Sample data
INSERT INTO EmployeeSkillsProjects (EmployeeID, Skill, Project)
VALUES
(1, 'C#', 'ProjectA'),
(1, 'SQL', 'ProjectB'),
(2, 'Java', 'ProjectA');
To achieve 4NF, we separate the skills and projects into two tables:
EmployeeSkills Table:
CREATE TABLE EmployeeSkills (
EmployeeID INT,
Skill VARCHAR(100)
);
-- Sample data
INSERT INTO EmployeeSkills (EmployeeID, Skill)
VALUES
(1, 'C#'),
(1, 'SQL'),
(2, 'Java');
EmployeeProjects Table:
CREATE TABLE EmployeeProjects (
EmployeeID INT,
Project VARCHAR(100)
);
-- Sample data
INSERT INTO EmployeeProjects (EmployeeID, Project)
VALUES
(1, 'ProjectA'),
(1, 'ProjectB'),
(2, 'ProjectA');
Fifth Normal Form (5NF)
To achieve the Fifth Normal Form (5NF), ensure that the table is free from join dependencies. This means that the table should not contain any non-trivial join dependencies. The tables above are already in 5NF.
Top comments (0)