Normalization in DBMS

Purpose of Normalization

Normalization is a process in database design that organizes data to reduce redundancy and improve data integrity. It involves dividing large tables into smaller, more manageable ones while ensuring relationships between them are maintained using foreign keys.

Main Objectives:

1. Eliminate Data Redundancy – Avoid storing the same data in multiple places.

2. Ensure Data Integrity – Keep data consistent and accurate across the database.

3. Avoid Update Anomalies – Prevent issues like insertion, update, and deletion anomalies.

4. Improve Query Performance – Organized data can lead to better indexing and query optimization.

Normal Forms

1. First Normal Form (1NF)

A table is in 1NF if:

• It contains only atomic values (indivisible values).

• Each field contains only one value per record.

• All entries in a column are of the same data type.

Example: Before 1NF:

Student	Courses
Raj	Math, Science

After 1NF:

Student	Course
Raj	Math
Raj	Science

2. Second Normal Form (2NF)

A table is in 2NF if:

• It is in 1NF.

• All non-key attributes are fully functionally dependent on the primary key.

• Partial dependencies are removed (i.e., no attribute should depend on part of a composite key).

Example: Before 2NF (with composite key):

StudentID

CourseID

StudentName

CourseName

Here, StudentName depends only on StudentID, not on the full key (StudentID, CourseID), so it's a partial dependency.

After 2NF (decomposed):

• Student Table:

  
  

  | StudentID | StudentName |

• Course Table:

  
  

  | CourseID | CourseName |

• Enrollment Table:

  
  

  | StudentID | CourseID |

3. Third Normal Form (3NF)

A table is in 3NF if:

• It is in 2NF.

• It has no transitive dependency (non-key attribute depending on another non-key attribute).

Example:

EmpID

EmpName

DeptID

DeptName

Here, DeptName depends on DeptID, and DeptID depends on EmpID. So DeptName transitively depends on EmpID.

After 3NF:

• Employee Table:

  
  

  | EmpID | EmpName | DeptID |

• Department Table:

  
  

  | DeptID | DeptName |

Boyce-Codd Normal Form (BCNF)

BCNF is a higher version of 3NF, stricter in its approach.

A table is in BCNF if:

• It is in 3NF.

• For every functional dependency X → Y, X should be a super key.

Example: Consider:

StudentID

Course

Instructor

Suppose:

• Each course has one instructor: Course → Instructor

• A student can enroll in multiple courses: StudentID, Course is the primary key

Here, Course → Instructor violates BCNF because Course is not a super key.

Solution (BCNF decomposition):

• Course-Instructor Table:

  
  

  | Course | Instructor |

• Student-Course Table:

  
  

  | StudentID | Course |

Decomposition in Normalization

Decomposition is the process of splitting a table into two or more tables to eliminate anomalies and redundancy.

Types of Decomposition:

1. Lossless Decomposition – Ensures original table can be reconstructed without data loss.

2. Lossy Decomposition – Causes data loss; should be avoided.

D

ependency Preservation

When decomposing a table:

• Dependency preservation means that all functional dependencies are preserved in the decomposed tables.

• It allows us to enforce constraints without joining tables.

Ideal Decomposition:

• Lossless Join + Dependency Preservation = Good Design

Summary Table: Normal Forms

Normal Form	Condition	Removes
1NF	Atomic values, unique entries	Repeating groups
2NF	1NF + Full dependency on the key	Partial dependency
3NF	2NF + No transitive dependency	Transitive dependency
BCNF	3NF + Every determinant is a super key	Anomalies from non-super key dependencies