Third Normal Form 3NF

In the previous lesson, you learned about Second Normal Form (2NF) and how it eliminates Partial Dependencies.

A table in 2NF ensures that every non-key attribute depends on the entire primary key.

However, even after removing partial dependencies, some redundancy may still remain.

This happens because certain non-key columns may depend on other non-key columns instead of directly depending on the primary key.

This problem is known as:

To eliminate transitive dependencies and further improve database design, we use:

Third Normal Form is one of the most commonly used normalization levels in real-world database systems because it significantly reduces redundancy while maintaining practical database performance.

What is Third Normal Form (3NF)?

A table is said to be in Third Normal Form (3NF) if:

In simple words:

and not on another non-key column.

Why is 3NF Important?

Even in a 2NF table, duplicate information may still exist.

Example:

Notice:

depends on:

instead of:

This creates redundancy.

3NF removes this issue.

Understanding Transitive Dependency

A Transitive Dependency occurs when:

In other words:

instead of directly depending on the primary key.

Simple Example

Table:

Dependencies:

Since:

depends on:

and not directly on:

a Transitive Dependency exists.

Why is Transitive Dependency a Problem?

Transitive dependencies cause:

Example:

If:

changes to:

Every employee row must be updated.

Missing even one row creates inconsistent data.

Example of a Table Not in 3NF

Primary Key:

Problem:

Therefore:

Converting to Third Normal Form

To remove transitive dependency, separate department information into its own table.

Employees Table

Departments Table

Now:

and:

Dependency Analysis

Before 3NF:

After 3NF:

and

stored in a separate table.

Another Example

Before 3NF:

Problem:

After 3NF

Students

Courses

Redundancy removed.

Real-World Example: University System

Wrong Design:

Department information repeats.

Correct Design:

Students

Departments

Now the design follows 3NF.

Real-World Example: Banking

Wrong Design:

BranchName depends on BranchID.

Correct Design:

Accounts

Branches

Now the database is in 3NF.

Real-World Example: E-Commerce

Wrong Design:

ProductName depends on ProductID.

Correct Design:

Orders

Products

Redundancy eliminated.

Rules for Achieving 3NF

A table must:

and

and

Every non-key attribute must depend:

Benefits of Third Normal Form

Reduces Data Redundancy

Duplicate information is minimized.

Eliminates Transitive Dependency

Main objective of 3NF.

Improves Data Consistency

Updates occur in one location.

Simplifies Maintenance

Database becomes easier to manage.

Better Data Integrity

Relationships remain accurate.

Widely Used In Industry

Many production databases are designed up to 3NF.

Limitations of 3NF

Although 3NF removes transitive dependencies, some dependency issues may still remain.

Certain complex dependency scenarios require:

which is stricter than 3NF.

Common Mistakes

Confusing Partial and Transitive Dependency

2NF removes:

3NF removes:

Storing Reference Data Repeatedly

Examples:

should often be moved to separate tables.

Overlooking Dependency Chains

Always analyze:

relationships.

Best Practices

Normalize Step by Step

Always follow:

Identify Indirect Dependencies

Look for non-key to non-key relationships.

Create Lookup Tables

Separate reference information.

Use Foreign Keys

Maintain relationships properly.

Document Dependencies

Understand how data relates.

Common Interview Questions

What is Third Normal Form (3NF)?

A table is in 3NF if it is in 2NF and contains no transitive dependencies.

What is a Transitive Dependency?

A non-key attribute depending on another non-key attribute.

What problem does 3NF solve?

Transitive dependency and related redundancy.

Does 3NF require 2NF?

Yes.

A table must already satisfy 2NF before it can satisfy 3NF.

Is 3NF enough for most databases?

Yes.

Many real-world systems are designed up to 3NF.

Summary

Third Normal Form (3NF) is a database normalization stage that removes transitive dependencies and ensures that every non-key attribute depends directly on the primary key. It reduces redundancy, improves consistency, and creates cleaner, more maintainable database structures.

In this lesson, you learned:

• What Third Normal Form is
• Transitive Dependency
• Why Transitive Dependency is a problem
• Converting tables to 3NF
• Real-world examples
• Benefits and limitations
• Best practices
• Interview questions

Mastering 3NF is essential because it is one of the most widely used normalization levels in modern database design.

Next Step

Continue to the next lesson:

Boyce-Codd Normal Form (BCNF) →