Database Normalization – 1NF, 2NF, 3NF and BCNF
Quick Answer: Database normalization organizes data into well-structured related tables. It reduces unnecessary duplication and helps prevent data problems. The main normal forms are 1NF, 2NF, 3NF and BCNF.
1. Why Do We Need Database Normalization?
Imagine Lakshmi manages student registrations for a school with 1,000 students. Every student can register for multiple subjects with different teachers.
Student details, subject details and teacher details appear repeatedly. Changing one teacher's name may require updating many records.
How can Lakshmi organize this information without unnecessary duplication?
The problem without database normalization
We could store every registration inside one large table. This looks simple, but repeated information creates problems.
Example 1: Store school registrations in one table
This SQL Server example creates a table with repeated student and teacher details.
CREATE TABLE SchoolRegistrations
(
StudentId INT,
StudentName VARCHAR(50),
SubjectId INT,
SubjectName VARCHAR(50),
TeacherId INT,
TeacherName VARCHAR(50),
Marks INT,
PRIMARY KEY (StudentId, SubjectId)
);
INSERT INTO SchoolRegistrations
VALUES
(101, 'Ravi', 1, 'Maths', 10, 'Anil', 85),
(101, 'Ravi', 2, 'Science', 11, 'Meera', 90),
(102, 'Priya', 1, 'Maths', 10, 'Anil', 78);
SELECT * FROM SchoolRegistrations;
Expected output
StudentId StudentName SubjectId SubjectName TeacherId TeacherName Marks 101 Ravi 1 Maths 10 Anil 85 101 Ravi 2 Science 11 Meera 90 102 Priya 1 Maths 10 Anil 78
Notice how Ravi's name and Anil's name appear repeatedly. This design creates several problems.
- Updating student information requires changing multiple rows.
- Deleting a registration may remove the only copy of useful information.
- Inserting a subject may require an existing student registration.
- Repeated values increase storage and maintenance work.
Now consider hundreds of subjects and thousands of students. Maintaining these records becomes harder.
We need to separate information based on its meaning and dependencies.
The solution: Database Normalization
Normalization = organizing database tables to reduce duplication and data problems.
Functional Dependency = one column or group of columns determines another value.
Candidate Key = a minimal set of columns that uniquely identifies a record.
Partial Dependency = a non-key attribute depends on only part of a composite key.
Transitive Dependency = a non-key attribute depends on another non-key attribute.
We apply different normal forms step by step. Each normal form adds specific rules to the database design.
Animation 1: How database normalization works
Watch Lakshmi transform repeated school records into organized tables.
Each normal form adds rules that improve the structure and reliability of database records.
2. Understand 1NF, 2NF, 3NF and BCNF
1. First Normal Form (1NF)
1NF = every column contains one atomic value in each row.
Atomic value = one individual value that we treat as a single field value.
Suppose Lakshmi stores multiple subjects inside one StudentSubjects column. This makes searching and updating individual subjects harder.
| Before 1NF | After 1NF |
|---|---|
| Ravi | Maths, Science | Ravi | Maths |
| Priya | Maths, English | Ravi | Science |
| Priya | Maths | |
| Priya | English |
Each subject now occupies a separate row. Every column stores one value.
Remember: 1NF does not automatically remove duplicate information. It ensures atomic column values and valid rows.
2. Second Normal Form (2NF)
2NF = the table must satisfy 1NF and remove partial dependencies.
In our example, the primary key contains StudentId and SubjectId together.
StudentName depends only on StudentId. SubjectName depends only on SubjectId.
These are partial dependencies because they depend on only part of the composite key.
We separate the information into three tables:
| Table | Columns |
|---|---|
| Students | StudentId, StudentName |
| Subjects | SubjectId, SubjectName, TeacherId, TeacherName |
| Registrations | StudentId, SubjectId, Marks |
Student details now depend on StudentId. Subject details depend on SubjectId.
3. Third Normal Form (3NF)
3NF = the table must satisfy 2NF and remove transitive dependencies.
In our Subjects table, TeacherName depends on TeacherId. TeacherId depends on SubjectId.
We should store teacher details separately. This avoids repeating teacher information across subjects.
The final 3NF structure becomes:
| Table | Columns |
|---|---|
| Students | StudentId, StudentName |
| Teachers | TeacherId, TeacherName |
| Subjects | SubjectId, SubjectName, TeacherId |
| Registrations | StudentId, SubjectId, Marks |
4. Boyce-Codd Normal Form (BCNF)
BCNF = every determinant in a non-trivial functional dependency must be a superkey.
Superkey = a column or group of columns that uniquely identifies each row.
BCNF is stricter than 3NF. Some tables can satisfy 3NF but still violate BCNF.
Imagine Lakshmi maintains teacher assignments. Each teacher teaches only one subject, but many teachers may teach different subjects.
Consider these rules:
- A student can study multiple subjects.
- A student can have different teachers for different subjects.
- Each teacher teaches exactly one subject.
The functional dependencies are:
- StudentId + SubjectId → TeacherId
- TeacherId → SubjectId
Here, TeacherId determines SubjectId. However, TeacherId alone does not identify a student.
So TeacherId is not a superkey. This violates BCNF, even though the relation satisfies 3NF because SubjectId is a prime attribute.
We split the table into two relations:
- TeacherSubjects (TeacherId, SubjectId)
- StudentTeachers (StudentId, TeacherId)
Now TeacherId identifies a subject inside TeacherSubjects. The remaining student-teacher relationship stays separate.
Design tip: Check functional dependencies before choosing a table's primary key. BCNF is especially useful when multiple candidate keys create dependencies.
3. Implement Database Normalization in SQL Server
Let's implement Lakshmi's school database using normalized tables.
Foreign Key = a column that references a key in another table.
Composite Key = a key made from two or more columns.
Example 2: Create normalized school tables and retrieve registrations
Run this script in SQL Server Management Studio.
CREATE TABLE Students
(
StudentId INT PRIMARY KEY,
StudentName VARCHAR(50)
);
CREATE TABLE Teachers
(
TeacherId INT PRIMARY KEY,
TeacherName VARCHAR(50)
);
CREATE TABLE Subjects
(
SubjectId INT PRIMARY KEY,
SubjectName VARCHAR(50),
TeacherId INT,
FOREIGN KEY (TeacherId)
REFERENCES Teachers(TeacherId)
);
CREATE TABLE Registrations
(
StudentId INT,
SubjectId INT,
Marks INT,
PRIMARY KEY (StudentId, SubjectId),
FOREIGN KEY (StudentId)
REFERENCES Students(StudentId),
FOREIGN KEY (SubjectId)
REFERENCES Subjects(SubjectId)
);
INSERT INTO Students
VALUES
(101, 'Ravi'),
(102, 'Priya');
INSERT INTO Teachers
VALUES
(10, 'Anil'),
(11, 'Meera');
INSERT INTO Subjects
VALUES
(1, 'Maths', 10),
(2, 'Science', 11);
INSERT INTO Registrations
VALUES
(101, 1, 85),
(101, 2, 90),
(102, 1, 78);
SELECT
S.StudentName,
SB.SubjectName,
T.TeacherName,
R.Marks
FROM Registrations R
INNER JOIN Students S
ON R.StudentId = S.StudentId
INNER JOIN Subjects SB
ON R.SubjectId = SB.SubjectId
INNER JOIN Teachers T
ON SB.TeacherId = T.TeacherId;
The Students table stores each student's information once. The Teachers table stores teacher details separately.
The Registrations table connects students and subjects. The final query joins all four tables to retrieve complete information.
Expected output
StudentName SubjectName TeacherName Marks ----------- ----------- ----------- ----- Ravi Maths Anil 85 Ravi Science Meera 90 Priya Maths Anil 78
This design satisfies 3NF and BCNF, assuming the stated functional dependencies.
4. Quick Summary
1NF vs 2NF vs 3NF vs BCNF
| Normal Form | Main Rule |
|---|---|
| 1NF | Every column stores atomic values. |
| 2NF | Remove partial dependencies. |
| 3NF | Remove transitive dependencies. |
| BCNF | Every determinant must be a superkey. |
The important terms to remember
- Normalization: Organizing data into related tables.
- Functional Dependency: One attribute determines another.
- Partial Dependency: Dependency on part of a composite key.
- Transitive Dependency: Indirect dependency through another attribute.
- Superkey: Attributes that uniquely identify a row.
5. Common Mistakes and Best Practices
1. Thinking 1NF removes all duplication
1NF makes values atomic. It does not remove every repeated value.
Better approach: Apply 2NF and 3NF to address dependency-related duplication.
2. Confusing 2NF with 3NF
2NF removes partial dependencies. 3NF addresses transitive dependencies.
Better approach: Identify candidate keys and functional dependencies before decomposing tables.
3. Applying BCNF without checking candidate keys
A table can satisfy 3NF and still violate BCNF.
Better approach: Check every non-trivial functional dependency against its determinant.
6. How Database Normalization Comes Up in Interviews
Question 1: What is database normalization?
What the interviewer expects: Explain why tables are separated.
Strong answer: "Normalization organizes data into related tables. It reduces duplication and prevents insertion, update and deletion anomalies."
Question 2: What is the difference between 2NF and 3NF?
What the interviewer expects: Explain both types of dependency.
Strong answer: "2NF removes partial dependencies on composite keys. 3NF removes transitive dependencies between non-key attributes."
Question 3: Can a table satisfy 3NF but violate BCNF?
What the interviewer expects: Understand candidate keys and prime attributes.
Strong answer: "Yes. 3NF allows certain dependencies when the dependent attribute is prime. BCNF requires every determinant to be a superkey."
7. Hands-On Practice
Beginner: Hospital patient records
Create a patient table with repeated doctor details. Apply 1NF and separate multiple treatments into individual records.
Expected result: Each field contains one value.
Intermediate: Bank transactions
Design customer, account and transaction tables. Identify partial and transitive dependencies.
Hint: Start with primary keys and functional dependencies.
Advanced: University course assignments
Design student, subject and instructor relationships. Identify candidate keys and test the BCNF rules.
Challenge: Find a relation that satisfies 3NF but violates BCNF. Decompose it without losing information.
8. Next Steps and Community
Previous article: User-Defined Functions, Triggers and Dynamic SQL
Next article: SQL Server Indexes – Clustered, Nonclustered and Composite Indexes
Practice normalization using Lakshmi's school database. Share your table designs and discuss different normalization choices with other learners.