1NF, 2NF, 3NF, and BCNF

Normalization is the process of organizing data to reduce redundancy and eliminate anomalies. Each normal form is a level of refinement.

• 1NF: Atomic columns (no repeating groups).
• 2NF: 1NF + no partial dependencies.
• 3NF: 2NF + no transitive dependencies.
• BCNF: 3NF + every determinant is a candidate key.

This chapter walks through each normal form with clear examples.

---

Learning Objectives

After completing this chapter, you will be able to:

• Identify which normal form a table is in.
• Convert a table from unnormalized to 1NF, 2NF, 3NF, and BCNF.
• Recognize partial, transitive, and non-key determinant dependencies.
• Understand when BCNF differs from 3NF.
• Apply normalization to real-world schemas.
• Answer normalization interview questions.

---

Sample Unnormalized Table

Consider this table storing student-course data.

StudentCourses (Unnormalized)

Student_ID	Student_Name	Course_ID	Course_Name	Instructor	Instructor_Office
101	Rahul	C01, C02	DBMS, OS	Dr. Sharma, Dr. Verma	A101, B202
102	Priya	C01	DBMS	Dr. Sharma	A101
103	Amit	C02, C03	OS, Networks	Dr. Verma, Dr. Gupta	B202, C303

Problems:

• Multiple values in one cell (violates atomicity).
• Repeating groups.
• Updating a course name requires scanning multiple rows.

---

First Normal Form (1NF)

Rule

• Every column must contain atomic (indivisible) values.
• There should be no repeating groups of columns.
• Each row must be uniquely identifiable.

Fixing 1NF

Split the multi-valued columns into separate rows.

StudentCourses (1NF)

Student_ID	Student_Name	Course_ID	Course_Name	Instructor	Instructor_Office
101	Rahul	C01	DBMS	Dr. Sharma	A101
101	Rahul	C02	OS	Dr. Verma	B202
102	Priya	C01	DBMS	Dr. Sharma	A101
103	Amit	C02	OS	Dr. Verma	B202
103	Amit	C03	Networks	Dr. Gupta	C303

Result

• Every cell has a single value.
• No more repeating groups.
• The primary key must now be {Student_ID, Course_ID} (composite).

Remaining Problems

• Redundancy: Student_Name repeats for each course a student takes.
• Anomalies:
  • Update: If Rahul changes his name, you must update multiple rows.
  • Insert: Cannot add a course if no student is enrolled (NULL in Student_ID).
  • Delete: Deleting Amit’s last enrollment loses his student info.

---

Second Normal Form (2NF)

Rule

• Must be in 1NF.
• No partial dependency: Every non-key attribute must be fully functionally dependent on the entire primary key (not just part of it).

Analyzing 1NF Table

Primary key: {Student_ID, Course_ID}

Dependencies

• Student_ID → Student_Name (partial dependency: Name depends only on Student_ID, not on Course_ID)
• Course_ID → Course_Name, Instructor, Instructor_Office (partial dependency: Course info depends only on Course_ID)

Fixing 2NF

Remove partial dependencies by splitting into separate tables.

Students (2NF)

Student_ID	Student_Name
101	Rahul
102	Priya
103	Amit

Courses (2NF)

Course_ID	Course_Name	Instructor	Instructor_Office
C01	DBMS	Dr. Sharma	A101
C02	OS	Dr. Verma	B202
C03	Networks	Dr. Gupta	C303

Enrollments (2NF)

Student_ID	Course_ID
101	C01
101	C02
102	C01
103	C02
103	C03

Result

• No partial dependencies.
• Each fact is stored exactly once.

Remaining Problems

Courses table: Course_ID → Instructor → Instructor_Office

Instructor_Office depends on Instructor, not directly on Course_ID.

This is a transitive dependency.

---

Third Normal Form (3NF)

Rule

• Must be in 2NF.
• No transitive dependency: A non-key attribute must not depend on another non-key attribute.

Analyzing 2NF Courses Table

Course_ID	Course_Name	Instructor	Instructor_Office
C01	DBMS	Dr. Sharma	A101
C02	OS	Dr. Verma	B202

Dependency

Course_ID → Instructor → Instructor_Office

Instructor_Office depends on Instructor, not directly on Course_ID.

Problems

• Update: If Dr. Sharma changes office, you must update every course they teach.
• Insert: Cannot add a new instructor’s office without assigning them a course.
• Delete: Deleting the last course by Dr. Verma loses their office information.

Fixing 3NF

Remove transitive dependencies by splitting again.

Courses (3NF)

Course_ID	Course_Name	Instructor
C01	DBMS	Dr. Sharma
C02	OS	Dr. Verma
C03	Networks	Dr. Gupta

Instructors (3NF)

Instructor	Office
Dr. Sharma	A101
Dr. Verma	B202
Dr. Gupta	C303

Result

• Every non-key attribute depends on nothing but the key.
• Course_ID determines Instructor.
• Instructor determines Office.
• Both tables are now in 3NF.

---

Boyce-Codd Normal Form (BCNF)

Rule

• Must be in 3NF.
• Every determinant must be a candidate key.

A determinant is any attribute (or set of attributes) that determines another attribute.

BCNF is a stricter version of 3NF. A table in 3NF is not automatically in BCNF.

Example Where 3NF ≠ BCNF

Consider a table tracking student advisors:

Student	Major	Advisor
Rahul	CS	Dr. A
Priya	CS	Dr. A
Amit	EE	Dr. B
Sneha	EE	Dr. C

Assumptions

• A student has one major and one advisor.
• Each major has multiple advisors.
• An advisor advises only one major (Dr. A advises only CS).

Candidate Keys

• {Student, Major} — a student-major pair is unique.
• {Student, Advisor} — a student-advisor pair is unique.

Dependencies

• Student → Major (a student has one major).
• Advisor → Major (an advisor advises only one major).

Is this 3NF?

Yes. All non-key attributes depend on the key.

But Advisor → Major — Advisor is not a candidate key (Advisor alone is not unique across the table — Dr. A appears twice).

Violation

Advisor is a determinant but not a candidate key.

Problems

• Update: If Dr. A moves to EE, you must update both rows.
• Redundancy: Dr. A teaches CS, and this is stored twice.

Fixing BCNF

Split the table.

Students (BCNF)

Student	Advisor
Rahul	Dr. A
Priya	Dr. A
Amit	Dr. B
Sneha	Dr. C

Advisors (BCNF)

Advisor	Major
Dr. A	CS
Dr. B	EE
Dr. C	EE

Now:

• Students: {Student} is the candidate key. Student → Advisor.
• Advisors: {Advisor} is the candidate key. Advisor → Major.

Every determinant is a candidate key. Both tables are in BCNF.

---

Summary Table

Normal Form	Rule	How to Fix
1NF	Atomic columns, no repeating groups	Split multi-valued cells into separate rows
2NF	1NF + No partial dependency (all non-key attributes depend on the full PK)	Split tables so each non-key attribute depends on the entire PK
3NF	2NF + No transitive dependency (no non-key attribute depends on another non-key attribute)	Move transitive dependencies to separate tables
BCNF	3NF + Every determinant is a candidate key	Split tables where non-key attributes determine other attributes

---

Quick Check: Which Normal Form?

Scenario	Current NF	Problem
Column has comma-separated values	Not 1NF	Non-atomic data
Student_Name depends only on Student_ID (part of composite PK)	1NF	Partial dependency → go to 2NF
Instructor_Office depends on Instructor (non-key)	2NF	Transitive dependency → go to 3NF
Advisor → Major, but Advisor is not a candidate key	3NF	Determinant is not a key → go to BCNF

---

Interview Deep Dive

---

Key Takeaways

• 1NF requires atomic values and no repeating groups.
• 2NF removes partial dependencies (every attribute depends on the full key).
• 3NF removes transitive dependencies (every attribute depends on the key, nothing but the key).
• BCNF requires every determinant to be a candidate key (stricter than 3NF).
• Normalization eliminates update, insert, and delete anomalies.
• Each normal form is a refinement of the previous one.
• Denormalization is acceptable for read-heavy analytical systems.