1NF,2NF,3NF,BCNF

Normalization is performed in stages called normal forms. Each form builds on the previous one and addresses a specific type of problem.

1. First Normal Form (1NF)

A table is in 1NF if:

• All data is atomic (indivisible)
• There are no repeating groups or arrays

Example (Not in 1NF):

StudentID	Name	Subjects
101	Riya	Math, Science
102	Aman	English

Improved (In 1NF):

StudentID	Name	Subject
101	Riya	Math
101	Riya	Science
102	Aman	English

Here, we split subjects into individual rows for atomicity.

---

2. Second Normal Form (2NF)

A table is in 2NF if:

• It is already in 1NF
• No partial dependency exists (i.e., no attribute depends on part of a primary key)

This applies mainly when a table has a composite primary key.

Example (Not in 2NF):

StudentID	CourseID	StudentName	CourseName
1	C1	Riya	DBMS
2	C1	Aman	DBMS

Here, CourseName depends only on CourseID, not on the full key (StudentID, CourseID).

Improved (In 2NF):

Student_Course Table:

StudentID	CourseID
1	C1
2	C1

Courses Table:

CourseID	CourseName
C1	DBMS

Now, non-key columns only depend on the whole primary key.

---

3. Third Normal Form (3NF)

A table is in 3NF if:

• It is already in 2NF
• No transitive dependency exists (i.e., non-key columns should not depend on other non-key columns)

Example (Not in 3NF):

StudentID	Name	DeptID	DeptName
101	Riya	D1	Science
102	Aman	D1	Science

Here, DeptName depends on DeptID, which is a non-key.

Improved (In 3NF):

Student Table:

StudentID	Name	DeptID
101	Riya	D1
102	Aman	D1

Department Table:

DeptID	DeptName
D1	Science

Now all non-key columns directly relate to the primary key.

---

Boyce-Codd Normal Form (BCNF)

A stricter version of 3NF. It removes situations where:

• A non-trivial functional dependency exists
• The determinant is not a super key

It ensures every determinant is a candidate key.

---

Higher Normal Forms: 4NF and 5NF

These forms deal with multi-valued dependencies and join dependencies, which are relevant in complex database systems. They’re usually not required for most everyday applications.

---

Advantages of Normalization

Reduces data redundancy
Ensures data integrity and consistency
Improves storage efficiency
Makes maintenance and updates easier
Speeds up queries with proper indexing

---

Disadvantages of Normalization

More tables mean more complex queries using JOINs

Slower performance for read-heavy applications
Over-normalization may reduce performance for large-scale apps

To handle such issues, developers often denormalize (merge tables) in specific use cases like data warehouses or read-heavy systems.

---

Real-World Example

Let’s say you have a hospital management database. Initially, you store everything in one table — patient name, doctor name, department, room number, etc. As data grows, you’ll face:

• Inconsistent data (Doctor name typed differently)
• Difficulty updating (If doctor changes department)
• Wasted storage (repeating department name)

By normalizing:

• You move doctors into a Doctors table
• Departments into a Departments table
• Create Appointments or Visits table to link data

Now, the data is structured, easier to manage, and more reliable.

---

Conclusion

Normalization in DBMS is like organizing a cluttered room. It ensures every item has its place, making it easier to find and use. By dividing data into well-structured tables and defining clear relationships, normalization enhances the performance, accuracy, and scalability of your database systems.

While over-normalizing can be inefficient in some cases, applying the right level of normalization (usually up to 3NF or BCNF) is a smart practice for most applications.