1NF vs 2NF vs 3NF vs BCNF: Key Differences and Usage

In DBMS, 1NF ensures atomic data without repeating groups, 2NF removes partial dependency on a part of a composite key, 3NF removes transitive dependency, and BCNF is a stricter form of 3NF that resolves anomalies caused by overlapping candidate keys.

Quick Comparison

This table summarizes the main features and differences of 1NF, 2NF, 3NF, and BCNF in database normalization.

Normal Form	Focus	Dependency Removed	Key Requirement	Example Issue Fixed
1NF	Atomicity of data	Repeating groups	Each column contains atomic values	No multi-valued attributes
2NF	Partial dependency	Partial dependency on composite key	Table must be in 1NF	Eliminates partial dependency
3NF	Transitive dependency	Transitive dependency	Table must be in 2NF	Removes indirect dependency
BCNF	Candidate key dependency	Anomalies from overlapping candidate keys	Stricter than 3NF	Fixes anomalies not handled by 3NF

Key Differences

1NF requires that all columns in a table hold atomic values, meaning no lists or sets inside a single column. This is the foundation of normalization to avoid repeating groups and ensure each field contains only one value.

2NF builds on 1NF by removing partial dependencies, which happen when a non-key column depends only on part of a composite primary key. This means every non-key attribute must depend on the whole key, not just a part.

3NF goes further by removing transitive dependencies, where a non-key column depends on another non-key column instead of directly on the primary key. This ensures that all attributes relate only to the key.

BCNF (Boyce-Codd Normal Form) is a stricter version of 3NF that handles special cases where multiple candidate keys overlap and cause anomalies. It requires that every determinant is a candidate key, fixing rare but problematic dependency cases.

Code Comparison

Here is an example of a table in 1NF and how it looks before normalization.

sql

CREATE TABLE StudentCourses (
  StudentID INT,
  StudentName VARCHAR(100),
  CourseIDs VARCHAR(100) -- multiple courses stored as comma-separated values
);

INSERT INTO StudentCourses VALUES (1, 'Alice', '101,102,103');

Output

Table stores multiple courses in one column violating 1NF atomicity.

2NF Equivalent

After applying 2NF, the table is split to remove partial dependency by separating courses into a different table.

sql

CREATE TABLE Students (
  StudentID INT PRIMARY KEY,
  StudentName VARCHAR(100)
);

CREATE TABLE Enrollments (
  StudentID INT,
  CourseID INT,
  PRIMARY KEY (StudentID, CourseID)
);

INSERT INTO Students VALUES (1, 'Alice');
INSERT INTO Enrollments VALUES (1, 101), (1, 102), (1, 103);

Output

Data split into two tables removing partial dependency and storing atomic values.

When to Use Which

Choose 1NF as the basic requirement to ensure atomic data and no repeating groups. Use 2NF when your table has composite keys and you want to remove partial dependencies for better data integrity.

Apply 3NF to eliminate transitive dependencies and ensure all data relates directly to the primary key, improving clarity and reducing redundancy.

Use BCNF in complex schemas where multiple candidate keys exist and 3NF does not fully resolve anomalies, ensuring the highest level of normalization.

Key Takeaways

1NF ensures atomic columns with no repeating groups.

2NF removes partial dependencies on parts of composite keys.

3NF eliminates transitive dependencies between non-key attributes.

BCNF handles special cases with overlapping candidate keys beyond 3NF.

Use higher normal forms to reduce redundancy and improve data integrity.