Normal Forms in DBMS�

Prof.manoj kumar padhi

Introduction

• Database normalization is a process to organize data efficiently.
• It removes redundancy and ensures data integrity.
• Developed by E.F. Codd in 1970.

​

Objectives of Normalization

• Reduce data redundancy
• Ensure data consistency
• Improve data structure clarity
• Reduce duplicate data and wasted storage.
• Improve data consistency and integrity.
• Make the schema easier to maintain and evolve.

​

​

Types of Normal Forms

1. First Normal Form (1NF)
2. Second Normal Form (2NF)
3. Third Normal Form (3NF)
4. Boyce–Codd Normal Form (BCNF)
5. (Optional) 4NF and 5NF

​

First Normal Form (1NF)

• Each cell contains atomic values.
• No repeating groups or arrays.
• Example:

​

❌

​

✅ (after normalization)

​

Purpose of 1NF

• To eliminate repeating groups in tables.
• To simplify data structure.
• To prepare data for higher normalization levels (2NF, 3NF, etc.).

​

Advantages

• Ensures that data is stored in a simple, tabular format.
• Makes it easier to query and update data.
• Reduces data redundancy.

​

Limitations

• Does not remove partial or transitive dependencies.
• Some redundancy may still exist.
• More refinement is needed for 2NF and 3NF.

​

Partial dependency

• A partial dependency occurs when a non-prime attribute (non-key attribute) depends on part of a composite primary key, not on the whole key.

​

In simple words:

If your table has a composite key (two or more columns as the primary key), and one of the non-key columns depends on only one part of that key, that’s called partial dependency.

Example

Primary Key: (StudentID, CourseID) — composite key

StudentName depends only on StudentID

CourseName depends only on CourseID

👉 Here,

StudentName → StudentID (partial dependency)

CourseName → CourseID (partial dependency)

​

Transitive Dependency

• A transitive dependency happens when a non-key column depends on another non-key column, instead of depending directly on the primary key.
• 👉 In other words:
• A non-key attribute gets its value indirectly through another non-key attribute.

• Formula�if A=B AND B=C�THEN A=C

​

​

• A = primary key
• B = non-key attribute
• C = another non-key attribute

​

Simple Example

• Dependencies:
• StudentID → DeptID ✅ (Each student belongs to one department)
• DeptID → DeptName ✅ (Each department has one name)
• So, indirectly:�StudentID → DeptName ❌ (Transitive dependency)

​

How to Remove Transitive Dependency:

• Student Table

• Department Table

Second Normal Form (2NF)

• A table is said to be in Second Normal Form (2NF) when:
• It is already in First Normal Form (1NF), and
• All non-key attributes depend on the whole primary key, not just a part of it.
• 👉 In simple words:�There should be no partial dependency in the table.

​

Why Do We Need 2NF?

• In 1NF, we only ensure that data is atomic (no repeating values),�but there can still be redundancy and partial dependency.
• 2NF helps to:
• Remove unnecessary data duplication
• Make the database more organized and reliable

​

Example

Before 2NF Student table

How to Convert to 2NF

• Student table Course Table

​

​

​

• Enrollment Table

​

Third Normal Form (3NF)�

• A table is said to be in Third Normal Form (3NF) when:
• It is already in Second Normal Form (2NF), and
• There are no transitive dependencies between non-key attributes.
• 👉 In simple words:
• Every non-key attribute should depend only on the primary key, and not on another non-key attribute.

​

Rules for 3NF

Example (Before 3NF)

• Table: Student_Details

​

​

​

​

• Primary Key: StudentID

• Check the Dependencies
• StudentName depends on StudentID ✅
• DeptID depends on StudentID ✅
• DeptName and DeptHOD depend on DeptID, not directly on StudentID ❌
• So,�StudentID → DeptID and DeptID → DeptName, DeptHOD�⇒ Transitive dependency exists → ❌ violates 3NF.

​

How to Convert to 3NF

• Student Table

• Student Table

Advantages of 3NF

• Removes transitive dependency.
• Further reduces data redundancy.
• Increases data consistency and integrity.
• Easier to update and maintain data.

​

Disadvantages of 3NF

• Increases the number of tables (more joins needed in queries).
• Slightly reduces performance in large databases.

​

Boyce–Codd Normal Form (BCNF)

• Boyce–Codd Normal Form (BCNF) is an advanced version of Third Normal Form (3NF).
• It’s used to make sure that the database does not have any kind of redundancy.
• Simple meaning:�BCNF makes sure that every dependency in a table starts from a key.

​

BCNF Rule (Main Condition)

• A table is in BCNF if:
• Table must be in 3NF
• For every functional dependency (X → Y),�X must be a super key.
• ✅ Super key: A column (or set of columns) that can uniquely identify a record.�❌ If X is not a super key, the table is not in BCNF.

​

Why Do We Need BCNF?

• Even after applying 3NF, some tables may still have:
• Redundancy (same data repeated)
• Update anomalies (wrong data after updates)
• Insertion anomalies (difficulty adding data)
• Deletion anomalies (loss of important data)
• So BCNF comes in to fix these remaining issues.

​

Example

• Table: Hostel_Room_Student Table

​

How to solve

• Table 1: Room_Warden

​

​

​

​

• Where Room number is Super key or primary key

• Table 2: Student_Room

​

​

​

​

​

• Where room number is Foreign Key and Student name is unique key
• Now, both tables are in Boyce–Codd Normal Form (BCNF).

​

Advantages

• Removes Redundancy – Eliminates repeated data and saves space.
• Prevents Anomalies – No update, insert, or delete anomalies.
• Improves Data Integrity – Each fact stored only once; data is consistent.
• Stronger than 3NF – Fixes issues that 3NF cannot.
• Better Data Structure – Makes database more logical and accurate.

​

Disadvantages

​

• More Tables – Leads to table splitting and complex design.
• Slower Queries – More joins required, reducing performance.
• Difficult to Design – Requires deep understanding of dependencies.
• Not Always Practical – Sometimes 3NF is enough for real systems.
• Complex Insertion/Updates – Data spread across multiple tables.

​

Summary

• 1NF: Remove repeating groups�2NF: Remove partial dependency�3NF: Remove transitive dependency�BCNF: Handle advanced dependency anomalies�4NF: Remove multi-valued dependencies�5NF: Remove join dependency redundancy

THANK YOU