Normalization and Forms

Normalization is process of database design to make the data more organized, reduce redundancy, reduce inconsistency and ensure data integrity. Types of normalizations are levels of optimization on the database. 1. 1NF - First Normal Form 2. 2NF - Second Normal Form 3. 3NF - Third Normal Form 4. Boyce-Codd Normal Form 5. 4NF Fourth Normal Form 6. 5NF - Fifth Normal Form 7. 6NF - Sixth Normal Form

Quick Summary

• 1NF: Atomic columns, no repeating groups.
• 2NF: No partial dependencies.
• 3NF: No transitive dependencies.
• BCNF: Every determinant is a super key.
• 4NF: No multi-valued dependencies.
• 5NF: No join dependencies.
• 6NF: No non-trivial join dependencies, with a focus on temporal data.

1. 1NF - First Normal Form

• All Columns contain atomic, indivisible values
• All values in a column must be of same type
• Each entry in a column must be unique

Not in 1NF:

ID	Name	Phone Numbers
1	John Smith	123-456, 789-101
2	Jane Doe	234-567

In 1NF:

ID	Name	Phone Number
1	John Smith	123-456
1	John Smith	789-101
2	Jane Doe	234-567

2. 2NF - Second Normal Form

• 1NF applied
• All non-key attributes/values must be functionally dependent on the primary key, and there must not be any partial dependency

Not in 2NF:

• Consider a table where a composite primary key is formed by OrderID and ProductID. Here, ProductName depends only on ProductID, not on the entire composite key.
• The CustomerName is not functionally dependent on the Primary Key, hence it should be removed.

OrderID	ProductID	ProductName	Quantity	CustomerName
1	101	Laptop	2	John
1	102	Phone	1	Elton
2	101	Laptop	1	Joseph

3. 3NF - Third Normal Form

• 1NF, 2NF Applied
• There must be no transitive dependency of non-prime (not part of candidate key) column.

Not in 3NF:

EmployeeID	EmployeeName	DepartmentID	DepartmentName
1	John Smith	10	HR
2	Jane Doe	20	IT
Explanation: DepartmentName is dependent on the DepartmentID which depends on primary key, hence forming a transitive dependency.

In 3NF:

EmployeeID	EmployeeName	DepartmentID
1	John Smith	10
2	Jane Doe	20

DepartmentID	DepartmentName
10	HR
20	IT

What is allowed?

StudentID	CourseId	Instructor
1	101	John
2	102	Jane
1	102	Jane
3	101	John
Here StudentID -(determines)-> CourseId and CourseId -> Instructor. This is allowed, since StudentId + CourseId forms a candidate key. So, instructor can still be identified uniquely.

4. Boyce-Codd Normal Form

• For every functional dependency, A -> B, the attribute set A must be the super key in the table. Here A -> B, means A is the determining value for B.
• For example: In Course → Instructor relation, Course is not a super key in the table. Yet, it determines Instructor, which violates the BCNF condition.

StudentID	Course	Instructor
1	Math	John
2	Science	Jane
1	Science	Jane
3	Math	John
Decomposed Tables: Courses Table Represents the direct relationship where Course determines Instructor. Here Course column is the super key.

Course	Instructor
Math	John
Science	Jane

5. 4NF Fourth Normal Form

• It has no multi-valued dependencies, no attribute sets that are independent but stored together

Not in 4NF:

StudentID	Course	Activity
1	Math	Chess
1	Science	Chess
1	Math	Basketball
2	Math	Chess

In 4NF:

StudentID	Course
1	Math
1	Science
2	Math

StudentID	Activity
1	Chess
1	Basketball
2	Chess

6. 5NF - Fifth Normal Form

• It cannot have any lossless decomposition into smaller tables that can be joined back together without loss of information.

7. 6NF - Sixth Normal Form

• It deals with temporal data and ensures no non-trivial join dependencies, applicable in time-series databases or databases where time is an important factor.