LATEST 🚀 Welcome to Upgradix by Shrash Studio – Your destination for free technology learning and career growth.
🕘 Updated:
MS SQL Server

Database Normalization – 1NF, 2NF, 3NF and BCNF

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.

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.

  1. Updating student information requires changing multiple rows.
  2. Deleting a registration may remove the only copy of useful information.
  3. Inserting a subject may require an existing student registration.
  4. 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.

Lakshmi School administrator
starts with repeated data
Unorganized Table Repeated student and teacher details
make values atomic
1NF One value in each column
remove partial dependencies
2NF Separate student and subject data
remove transitive dependencies
3NF Separate teacher information
check every determinant
BCNF Every determinant is a superkey
Unorganized data Atomic data Decomposition Normalized design Current stage

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.

Example of data before and after applying 1NF
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:

Tables created after applying 2NF
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:

Tables created after applying 3NF
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

Important rules of each normal form
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.

Comments & Ratings (0)

Log in or create a student account to comment and rate this article.

Be the first to comment on this article.