Normal Forms in DBMS: 1NF, 2NF, 3NF, BCNF, 4NF & 5NF

What Is Normalization in DBMS?

Normalization in DBMS is the process of arranging data in a database to reduce redundancy and improve data integrity. In simple terms, it means splitting large, messy tables into smaller, better-structured ones.

For example, imagine a table that stores student, course, and teacher details together. If a teacher teaches multiple courses, the teacher’s name and contact number may appear several times. If the teacher’s number changes, you must update it in multiple rows. Missing one row can make the database inconsistent.

Normalization solves this problem by separating data into related tables connected using keys.

Why Is Normalization Important?

Normalization is important because databases must be accurate, consistent, and easy to update. Gartner states that poor data quality costs organizations at least $12.9 million per year on average. This shows why clean data design matters for businesses. 

Here are the main reasons normalization is useful:

1. Reduces Duplicate Data

Duplicate data wastes storage space and increases confusion. Normalization stores each fact only once wherever possible.

2. Improves Data Accuracy

When data is stored in one correct place, updates become easier. This reduces the chance of old or wrong information staying in the system.

3. Prevents Update Anomalies

An update anomaly happens when the same information must be changed in many places. Normalization reduces this issue.

4. Prevents Insert Anomalies

An insert anomaly happens when you cannot add data because some unrelated data is missing. A normalized design avoids such dependency.

5. Prevents Delete Anomalies

A delete anomaly happens when deleting one record also removes important related information. Normalization helps protect useful data.

6. Supports Better Database Design

The Stack Overflow Developer Survey shows that databases like PostgreSQL and MySQL are still widely used by developers. This makes good database design a key skill for developers, data engineers, and DBAs.

Build practical cloud architecture skills across AWS and Azure with Simplilearn’s Cloud Architect Course. Get hands-on experience with tools and services such as AWS EC2, S3, Lambda, IAM, Route 53, Azure SQL Database, and Azure DNS.

Key Concepts You Must Know

• Primary Key: A column or group of columns that uniquely identifies each row in a table.
• Candidate Key: Any column or group of columns that can uniquely identify a row.
• Foreign Key: A column that connects one table to another.
• Functional Dependency: A relationship where one attribute determines another. For example, if Student_ID determines Student_Name, then Student_Name is functionally dependent on Student_ID.
• 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.

First Normal Form (1NF)

A table is in First Normal Form, or 1NF, when:

• Each column has atomic values.
• There are no repeating groups.
• Each row is unique. An atomic value means a value that cannot be divided further in that table.

Example of a Table Not in 1NF

Here, the Courses column has multiple values. This violates 1NF.

Table in 1NF

Now, each cell has only one value. So, the table is in 1NF.

1NF is the starting point of normalization. It makes data easier to search, filter, and update.

Second Normal Form (2NF)

A table is in Second Normal Form, or 2NF, when:

• It is already in 1NF.
• It has no partial dependency.

2NF mainly applies to tables with composite keys. A composite key uses more than one column to identify a record.

Example of a Table Not in 2NF

Here, the primary key can be Student_ID + Course_ID.

But Student_Name depends only on Student_ID. Course_Name depends only on Course_ID. These are partial dependencies.

Tables in 2NF

• Student Table

• Course Table

• Enrollment Table

Now, each non-key attribute depends on the full key. So, the design is in 2NF.

Third Normal Form (3NF)

A table is in Third Normal Form, or 3NF, when:

• It is already in 2NF.
• It has no transitive dependency.

A transitive dependency happens when one non-key column depends on another non-key column.

Example of a Table Not in 3NF

Here, Student_ID determines Dept_ID. Dept_ID determines Dept_Name. So, Dept_Name indirectly depends on Student_ID through Dept_ID. This is a transitive dependency.

Tables in 3NF

• Student Table

• Department Table

Now, department details are stored separately. This avoids repeated department names and makes updates easier.

3NF is one of the most commonly used normal forms in real-world database design. It offers a good balance between data cleanliness and query performance.

Build practical Azure skills in cloud architecture, virtual networking, identity, security, monitoring, and infrastructure management with Simplilearn’s Microsoft Azure Certification. Get hands-on with Azure Key Vault, Monitor, Cosmos DB, SQL Database, DNS, and more.

Boyce-Codd Normal Form (BCNF)

Boyce-Codd Normal Form, or BCNF, is a stronger version of 3NF.

A table is in BCNF when:

• It is already in 3NF.
• For every functional dependency, the left side must be a super key.

In simple words, every determinant must be able to identify a row uniquely

Example of a Table Not in BCNF

Assume these rules:

• One student can take many courses.
• One course can have one instructor.
• One instructor teaches only one course.

Here, Course determines Instructor. But Course may not uniquely identify every row. So, this table may violate BCNF.

BCNF Design

• Student_Course Table

• Course_Instructor Table

Now, each dependency is placed in a better table. BCNF is useful when 3NF still leaves some redundancy due to complex dependencies.

Fourth Normal Form (4NF)

A table is in Fourth Normal Form, or 4NF, when:

• It is already in BCNF.
• It has no unwanted multivalued dependency.

A multivalued dependency happens when one attribute has multiple independent values related to the same key.

Example of a Table Not in 4NF

Here, Ravi has multiple skills and multiple hobbies. But skills and hobbies are independent of each other. Combining them creates unnecessary row combinations.

Tables in 4NF

• Student_Skill Table

• Student_Hobby Table

Now, skills and hobbies are stored separately. This removes unnecessary combinations.

4NF is useful in systems where one entity has many independent multi-value attributes.

Fifth Normal Form (5NF)

A table is in Fifth Normal Form, or 5NF, when:

• It is already in 4NF.
• It cannot be decomposed further without losing information.
• All join dependencies are properly handled.

5NF is also called Project-Join Normal Form.

It is used in complex cases where data can be split into smaller tables and then joined back without producing incorrect records.

Simple Example

Suppose a supplier supplies certain parts to certain projects.

If supplier, part, and project relationships are independent but connected, we may need separate tables for Supplier-Part, Supplier-Project, and Part-Project relationships.

5NF ensures that when these tables are joined again, they recreate only valid data and do not create false combinations.

In everyday applications, 5NF is less common than 1NF, 2NF, and 3NF. But it is useful in highly complex database systems.

Comparison of 1NF, 2NF, 3NF, BCNF, 4NF, and 5NF

How to Identify the Highest Normal Form

To identify the highest normal form of a table, follow a step-by-step method.

Step 1: Check for 1NF

Ask this question: Does every cell contain only one value?

If yes, the table is in 1NF. If no, it is not normalized.

Step 2: Check for 2NF

Ask this question: Does every non-key attribute depend on the complete primary key?

If yes, the table may be in 2NF. If there is partial dependency, it is only in 1NF.

Step 3: Check for 3NF

Ask this question: Does any non-key column depend on another non-key column?

If yes, there is a transitive dependency. If no, the table is in 3NF.

Step 4: Check for BCNF

Ask this question: Is every determinant a super key?

If yes, the table is in BCNF. If no, it may only be in 3NF.

Step 5: Check for 4NF

Ask this question: Are there independent multivalued facts in the same table?

If yes, the table may violate 4NF.

Step 6: Check for 5NF

Ask this question: Can the table be decomposed further and joined back without creating false records?

If yes, 5NF may be needed.

A simple way to remember this is: each higher normal form solves a more specific data design problem.

This step-by-step Cloud Engineer roadmap is designed for professionals seeking to understand the full scope of the profession. Explore the skills, tools, salary potential, and career roadmap needed to build a successful career as a Cloud Engineer.

Conclusion

Normal forms help developers design clean and reliable databases. 1NF removes repeating groups, 2NF removes partial dependencies, and 3NF removes transitive dependencies. BCNF, 4NF, and 5NF address more complex dependency and join problems.

The best level of normalization depends on the system. Transaction-heavy systems usually benefit from normalized tables, while reporting systems may use some denormalization to improve read performance.

Key Takeaways

• Normalization is the process of organizing database tables to reduce redundancy and improve data integrity.
• 1NF removes repeating groups and ensures atomic values.
• 2NF removes partial dependency.
• 3NF removes transitive dependency.
• BCNF is a stricter version of 3NF.
• 4NF removes multivalued dependency.
• 5NF handles complex join dependencies.

FAQs

1. What are some common database normalization interview questions?

Database normalization interview questions often test concepts such as functional dependencies, candidate keys, partial dependencies, transitive dependencies, and database anomalies. Candidates may be asked to identify the normal form of a table, convert a table from 1NF to 3NF, explain the difference between 3NF and BCNF, or describe when denormalization may be appropriate.

2. Is normalization used in data warehouses?

Normalization can be used in data warehouses, particularly in snowflake schemas where dimension tables are divided into related tables. However, many data warehouses use denormalized star schemas because they require fewer joins and make analytical queries simpler and faster. The choice depends on storage requirements, query performance, data complexity, and reporting needs.

3. How is normalization applied in SQL database design?

Normalization in SQL database design involves dividing large tables into smaller related tables and connecting them using primary and foreign keys. Designers first identify entities, attributes, candidate keys, and functional dependencies. They then remove repeating groups, partial dependencies, and transitive dependencies. SQL constraints such as PRIMARY KEY, FOREIGN KEY, UNIQUE, and NOT NULL help enforce the resulting structure and maintain data integrity.