Database Normalization – Normal Forms 1NF 2NF 3NF Table Examples – Normal Forms are important for ensuring that data is structured logically, reducing redundancy, and maintaining data integrity.

Database Normalization – Normal Forms 1NF 2NF 3NF:

If a table is not properly normalized and has data redundancy(repetition) then it will not only consume extra memory space but will also make it difficult for you to handle and update the data in the database, without losing data.

Insertion, Updation, and Deletion Anomalies are very frequent if the database is not normalized.

1. First Normal Form (1NF): Eliminating Duplicate Records

A table is in 1NF if it satisfies the following conditions:

All columns contain atomic values (i.e., indivisible values).
Each row is unique (i.e., no duplicate rows).
Each column has a unique name.
The order in which data is stored does not matter.

1NF (First Normal Form):

A raw data table with repeating groups and multi-valued attributes:

Problems:

• Multi-valued fields (Hobbies, Languages).

• Nested repeating group (Courses).

1NF (First Normal Form)

👉 Break down repeating groups → atomic values.

Atomic values, but still redundant.

2. Second Normal Form (2NF): Eliminating Partial Dependency

A relation is in 2NF if it satisfies the conditions of 1NF and additionally. No partial dependency exists, meaning every non-prime attribute (non-key attribute) must depend on the entire primary key, not just a part of it.

What is Partial Dependency?

When a table has a primary key that is made up of two or more columns, then all the columns(not included in the primary key) in that table should depend on the entire primary key and not on a part of it. If any column(which is not in the primary key) depends on a part of the primary key then we say we have Partial dependency in the table.

2NF (Second Normal Form)

Remove partial dependency (non-key attributes depend only on part of composite key).
Composite key in 1NF = (StudentID, Course, Hobby, Language).
But StudentName depends only on StudentID, and DeptName depends only on StudentID.

Split into:

Students

Enrollments

StudentHobbies

StudentLanguages

3. Third Normal Form (3NF): Eliminating Transitive Dependency

A relation is in 3NF if it satisfies 2NF and additionally, there are no transitive dependencies. In simpler terms, non-prime attributes should not depend on other non-prime attributes.

What is Transitive Dependency?

In a table we have some column that acts as the primary key and other columns depends on this column. But what if a column that is not the primary key depends on another column that is also not a primary key or part of it? Then we have Transitive dependency in our table.

3NF (Third Normal Form)

Remove transitive dependency (non-key attributes depending on other non-key attributes).

Here: DeptName → DeptHead, Course → Room, etc.

Split further:

Departments

Courses

4. Boyce-Codd Normal Form (BCNF): The Strongest Form of 3NF

BCNF is a stricter version of 3NF where for every non-trivial functional dependency (X → Y), X must be a superkey (a unique identifier for a record in the table).

BCNF (Boyce–Codd Normal Form)

Every determinant must be a candidate key.

Issue: In Courses, functional dependency Professor → Course (one prof teaches one course), but Professor is not a key.

So we split:

Courses

Professors

5. Fourth Normal Form (4NF): Removing Multi-Valued Dependencies

A table is in 4NF if it is in BCNF and has no multi-valued dependencies. A multi-valued dependency occurs when one attribute determines another, and both attributes are independent of all other attributes in the table.

4NF (Fourth Normal Form)

Remove multi-valued dependencies.

Issue: In Students, Hobbies and Languages are independent sets → redundancy.

We already separated them into StudentHobbies and StudentLanguages in 2NF, so 4NF satisfied.

6. Fifth Normal Form (5NF): Eliminating Join Dependency

5NF is achieved when a table is in 4NF and all join dependencies are removed. This form ensures that every table is fully decomposed into smaller tables that are logically connected without losing information.

5NF (Fifth Normal Form)

Remove join dependency anomalies.

Case: Enrollments mixes Students, Courses, and Professors. If professors can change for a course, we’d get redundancy.

So we split into:

StudentCourses

CourseProfessors

StudentProfessors

No redundancy, and tables can be joined back without anomalies.

---

Final Summary

• 1NF → Remove repeating groups (atomic values).

• 2NF → Remove partial dependencies.

• 3NF → Remove transitive dependencies.

• BCNF → Determinants must be candidate keys.

• 4NF → Remove multi-valued dependencies.

• 5NF → Remove join dependency anomalies.

Why is Normalization Important?

• Reduces Data Redundancy: Duplicate data is stored efficiently, saving disk space and reducing inconsistency.
• Improves Data Integrity: Ensures the accuracy and consistency of data by organizing it in a structured manner.
• Simplifies Database Design: By following a clear structure, database designs become easier to maintain and update.
• Optimizes Performance: Reduces the chance of anomalies and increases the efficiency of database operations.

What are the different Normal Forms in DBMS?

Following are the different Database Normal Forms:

1. First Normal Form also known as 1NF
2. Second Normal Form or 2NF
3. Third Normal Form or 3NF
4. Boyce-Codd Normal Form or BCNF
5. Fourth Normal Form or 4NF
6. Fifth Normal Form or 5NF or PJNF (Project-Join Normal Form)

What is a Primary Key in DBMS?

A Primary key is a column that can be used to uniquely identify each row in a table. It can be a single column, or it can be multiple columns together. Yes, a primary key can have two columns or even more than two columns in it.

What are non-key attribute in a Table?

All the columns that are not a primary key or not a part of the primary key are called as non-Key columns in a Table.

For example, if we have a table Students with columns student_id, student_name, student_address, and student_id is the primary key in this table, then student_name and student_address will be the non-Key attributes.

What is the fullform of BCNF?

BCNF stands for Boyce-Codd Normal Form. BCNF is a higher version of the Third Normal Form.

Is BCNF and Third Normal Form the same?

No. BCNF is a higher version of the Third Normal Form. The purpose of the Third Normal Form or 3NF is to remove Transitive dependency whereas BCNF is more strict than 3NF, and it focuses on removing all non-trivial functional dependencies.

When to Use Normalization and Denormalization?

Normalization is best suited for transactional systems where data integrity is paramount, such as banking systems and enterprise applications.
Denormalization is ideal for read-heavy applications like data warehousing and reporting systems where performance and query speed are more critical than data integrity.

What is PJNF?

PJNF stands for Project-Join Normal Form. This is a name given to the Fifth Normal Form because the Fifth Normal Form or 5NF is used to fix Join dependency in tables.

Which Normal Form is called PJNF?

The Fifth Normal Form is also known as PJNF or Project-Join Normal Form. The fifth normal form fixes the Join dependency in tables hence it is called PJNF. This is an advanced Normal form that helps in reducing Data redundancy and Updation anomaly.