Understanding Database Normalization: 1NF, 2NF, 3NF, and BCNF
0.0|0 ratingsLog in to rate
Learn the guidelines and normal forms (1NF, 2NF, 3NF, and BCNF) to design clean database tables and eliminate redundancy.
#dbms#sql#database-design#normalization
Why Normalize?
Database normalization is the process of structuring a relational database to minimize data redundancy and prevent anomalies (insertion, update, and deletion anomalies).
Let's review the normal forms step-by-step:
- First Normal Form (1NF): Requires atomic values (no repeating columns or groups of arrays) and a defined Primary Key.
- Second Normal Form (2NF): Must be in 1NF, and all non-key columns must depend entirely on the primary key (eliminates partial dependencies when composite keys are used).
- Third Normal Form (3NF): Must be in 2NF, and no non-key columns can depend on other non-key columns (eliminates transitive dependencies).
- Boyce-Codd Normal Form (BCNF): A stricter version of 3NF. For every functional dependency A -> B, A must be a super key.
Normalization Table Transformations
sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- Unnormalized Table
-- [StudentID, StudentName, Courses(array)]
-- 1NF Form (Atomic values)
CREATE TABLE StudentCourses (
StudentID INT,
StudentName VARCHAR(50),
CourseName VARCHAR(50),
PRIMARY KEY (StudentID, CourseName)
);
-- 2NF Form (Eliminate partial dependency on composite key)
-- Move StudentName out of relationship table since it depends only on StudentID, not CourseName
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(50)
);
CREATE TABLE Enrollments (
StudentID INT,
CourseName VARCHAR(50),
PRIMARY KEY (StudentID, CourseName),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);Discussion
Loading discussion...