3.4 Normal Forms

Level: 3 | Version: v1.1 | Author: Meptrasoft

Overview

Normal forms are levels of database design used in normalization.

Each normal form introduces additional rules for organizing relational data and reducing different types of redundancy or dependency problems.

The commonly discussed normal forms are:

1NF
 ↓
2NF
 ↓
3NF
 ↓
BCNF
 ↓
4NF
 ↓
5NF

For SQL and database beginners, 1NF, 2NF, and 3NF are the most important starting points.

Each higher normal form builds on the rules of the previous one.

First Normal Form (1NF)

First Normal Form (1NF) requires each column value to be atomic.

In simple terms:

One cell should contain one value, not a list of values.

For example, this violates 1NF:

The Products cell contains three values.

A better structure is:

Now each cell contains a single value.

What Does Atomic Mean?

Atomic means that a value is treated as one indivisible value for the purpose of that column.

For example:

Good:
Product = Laptop

Problem:
Products = Laptop, Mouse, Keyboard

Similarly, this is problematic when a column stores multiple phone numbers in one field:

Phone = 9876543210, 9123456780

A design that follows 1NF stores the values separately according to the data model.

1NF helps keep data in a form that can be stored, searched, and manipulated consistently.

Violating 1NF — Practice Table Setup

Consider this table:

CREATE TABLE orders_unnormalized (
    order_id INT,
    customer_name VARCHAR(50),
    products VARCHAR(100),
    quantities VARCHAR(50)
);

INSERT INTO orders_unnormalized
(order_id, customer_name, products, quantities)
VALUES
(1, 'John Smith', 'Laptop, Mouse, Keyboard', '1, 2, 1');

The data looks like:

The problem is that both products and quantities contain multiple values.

This violates the atomic-value requirement of 1NF.

Satisfying 1NF

We can represent each product as a separate row:

CREATE TABLE orders_normalized (
    order_id INT,
    customer_name VARCHAR(50),
    product VARCHAR(50),
    quantity INT
);

INSERT INTO orders_normalized
(order_id, customer_name, product, quantity)
VALUES
(1, 'John Smith', 'Laptop', 1),
(1, 'John Smith', 'Mouse', 2),
(1, 'John Smith', 'Keyboard', 1);

Now the table contains:

Each cell contains one value.

MULTIPLE VALUES IN ONE CELL
            ↓
        1NF DESIGN
            ↓
ONE VALUE PER CELL

Second Normal Form (2NF)

Second Normal Form (2NF) builds on 1NF.

A table is in 2NF when:

1. It is already in 1NF, and
2. Every non-key attribute depends on the whole primary key, not just part of a composite key.

The second rule is called removing partial dependency.

Simple Example

Consider:

Suppose the primary key is:

(Student_ID, Course_ID)

Student_Name depends only on Student_ID.

Course_Name depends only on Course_ID.

They do not depend on the whole composite key.

That is a partial dependency.

A better design separates the information:

Students
Student_ID → Student_Name

Courses
Course_ID → Course_Name

Enrollments
Student_ID + Course_ID → Mark

2NF removes partial dependency on part of a composite key.

Third Normal Form (3NF)

Third Normal Form (3NF) builds on 2NF.

A table is in 3NF when:

1. It is already in 2NF, and
2. Non-key attributes do not depend on another non-key attribute.

This removes transitive dependency.

Simple Example

Consider:

Here:

Student_ID
   ↓
Dept_ID
   ↓
Department_Name

Department_Name depends on Dept_ID, rather than directly on the student key.

A normalized design can separate the department information:

Students
Student_ID → Student_Name, Dept_ID

Departments
Dept_ID → Department_Name

3NF removes transitive dependency so non-key attributes depend on the key, not on another non-key attribute.

BCNF

BCNF stands for Boyce-Codd Normal Form.

It is a stronger version of 3NF.

BCNF focuses on functional dependencies and requires that every determinant in a relation be a candidate key.

For beginner SQL learning, it is enough to remember:

BCNF applies stricter dependency rules than 3NF.

Fourth Normal Form (4NF)

Fourth Normal Form (4NF) deals with multivalued dependencies.

It is useful when a table contains two or more independent multi-valued relationships that should not be stored together.

For example, if an employee independently has multiple skills and multiple languages, storing both lists together in one table can create unnecessary combinations.

4NF helps separate those independent multi-valued facts.

4NF reduces unnecessary duplication caused by independent multi-valued relationships.

Fifth Normal Form (5NF)

Fifth Normal Form (5NF) deals with join dependencies.

It focuses on situations where a table can be decomposed into smaller tables and reconstructed correctly through joins without introducing unwanted combinations or losing information.

For most beginner SQL work, 5NF is less commonly encountered than 1NF–3NF.

5NF addresses complex join dependencies in relational designs.

Normal Forms at a Glance

Memory Trick

1NF  → ONE VALUE
2NF  → WHOLE KEY
3NF  → ONLY THE KEY
BCNF → STRONGER KEY RULE
4NF  → MULTI-VALUED DEPENDENCIES
5NF  → JOIN DEPENDENCIES

Common Beginner Mistakes

Placement Quick Points

1NF  → ATOMIC VALUES
2NF  → NO PARTIAL DEPENDENCY
3NF  → NO TRANSITIVE DEPENDENCY
BCNF → STRONGER FUNCTIONAL DEPENDENCY RULE
4NF  → NO UNNECESSARY MULTIVALUED DEPENDENCY
5NF  → JOIN DEPENDENCY

• 1NF requires atomic values and removes repeating groups.
• 2NF builds on 1NF and removes partial dependency on part of a composite key.
• 3NF builds on 2NF and removes transitive dependency.
• BCNF applies a stronger rule to functional dependencies.
• 4NF deals with multivalued dependencies.
• 5NF deals with join dependencies.
• In practical SQL learning, 1NF, 2NF, and 3NF are the most important normal forms to understand first.

Interview Questions

What is 1NF?

1NF requires each cell to contain an atomic value and removes repeating groups or multiple values stored together.

What is 2NF?

2NF requires a table to be in 1NF and ensures that non-key attributes depend on the entire primary key, eliminating partial dependency.

What is 3NF?

3NF requires a table to be in 2NF and removes transitive dependency so non-key attributes depend on the key rather than another non-key attribute.

What is BCNF?

BCNF is a stronger normal form than 3NF. It requires every determinant in a relation to be a candidate key.

What is the difference between 1NF, 2NF, and 3NF?

TEXT

1NF → Atomic values
2NF → No partial dependency
3NF → No transitive dependency

Each form builds on the previous one.

What is a partial dependency?

A partial dependency occurs when a non-key attribute depends on only part of a composite key instead of the complete key.

What is a transitive dependency?

A transitive dependency occurs when a non-key attribute depends on another non-key attribute rather than directly on the key.

Are 4NF and 5NF commonly required in everyday SQL development?

1NF, 2NF, and 3NF are much more commonly used as foundational normalization concepts. 4NF and 5NF are relevant for more specialized relational designs.

Does higher normalization always improve performance?

No. Normalization primarily improves organization and data integrity. Higher normalization can sometimes require more joins, so performance depends on the workload and database design.

Practice & Hands-On Exercises

Beginner Practice

1. What is the main purpose of 1NF?
2. What problem does 2NF solve?
3. What problem does 3NF solve?
4. What is BCNF?
5. What are 4NF and 5NF mainly concerned with?

Classify the Problem

Identify the normal form that addresses each situation:

6. Products = 'Laptop, Mouse, Keyboard' in one cell.
7. A non-key column depends only on part of a composite key.
8. A non-key column depends on another non-key column.
9. Two independent multi-valued relationships create unnecessary combinations.

SQL Connection

Consider:

10. Identify the likely composite key.
11. Which columns depend only on Student_ID?
12. Which columns depend only on Course_ID?
13. How would you split this table to move toward 2NF?

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50)
);

CREATE TABLE courses (
    course_id VARCHAR(5) PRIMARY KEY,
    course_name VARCHAR(50)
);

CREATE TABLE enrollments (
    student_id INT REFERENCES students(student_id),
    course_id VARCHAR(5) REFERENCES courses(course_id),
    mark INT,
    PRIMARY KEY (student_id, course_id)
);

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

1NF
→ ONE VALUE PER CELL
       ↓
2NF
→ FULL DEPENDENCY ON WHOLE KEY
       ↓
3NF
→ NO TRANSITIVE DEPENDENCY
       ↓
BCNF
→ STRONGER DEPENDENCY RULE
       ↓
4NF / 5NF
→ ADVANCED DEPENDENCY RULES

Normal forms provide progressively stronger rules for organizing relational data and reducing unnecessary dependencies and redundancy.

End of Normal Forms