Database normalization is a process used to organize a database into tables and columns to reduce redundancy and improve data integrity. It ensures that data is stored efficiently and updates, insertions, or deletions do not lead to inconsistencies.
1. First Normal Form (1NF)
Definition: A table is in 1NF if:
- Each column contains only atomic (indivisible) values.
- Each record is unique.
- There are no repeating groups or arrays.
Key Points:
- Eliminate duplicate columns from the same table.
- Create separate tables for related data.
- Identify a primary key for each table.
Example:
Before 1NF:
CustomerID | CustomerName | PhoneNumbers
1 | John Doe | 12345, 67890
2 | Jane Smith | 54321
After 1NF:
CustomerID | CustomerName | PhoneNumber
1 | John Doe | 12345
1 | John Doe | 67890
2 | Jane Smith | 54321
2. Second Normal Form (2NF)
Definition: A table is in 2NF if:
- It is already in 1NF.
- All non-key attributes are fully functionally dependent on the primary key.
Key Points:
- Remove partial dependencies (when a column depends only on part of a composite key).
- Create separate tables for attributes that depend on a part of the key.
Example:
Before 2NF:
OrderID | ProductID | ProductName | Quantity
101 | 1 | Laptop | 2
101 | 2 | Mouse | 5
After 2NF:
Orders Table:
OrderID | ProductID | Quantity
101 | 1 | 2
101 | 2 | 5
Products Table:
ProductID | ProductName
1 | Laptop
2 | Mouse
3. Third Normal Form (3NF)
Definition: A table is in 3NF if:
- It is already in 2NF.
- All non-key attributes are not transitively dependent on the primary key (i.e., no attribute depends on another non-key attribute).
Key Points:
- Remove transitive dependencies.
- Each non-key attribute must depend only on the primary key.
Example:
Before 3NF:
StudentID | StudentName | CourseID | CourseName | Instructor
1 | Alice | C101 | Math | Mr. Smith
2 | Bob | C102 | English | Mrs. Johnson
After 3NF:
Students Table:
StudentID | StudentName
1 | Alice
2 | Bob
Courses Table:
CourseID | CourseName | Instructor
C101 | Math | Mr. Smith
C102 | English | Mrs. Johnson
Summary
- 1NF: Eliminate repeating groups, ensure atomic values.
- 2NF: Remove partial dependencies, ensure all attributes fully depend on the primary key.
- 3NF: Remove transitive dependencies, ensure attributes depend only on the primary key.
Normalization helps maintain data consistency, avoids redundancy, and simplifies database management.