Database normalization is a systematic approach to designing relational databases to reduce data redundancy and improve data integrity. It involves organizing columns (attributes) and tables (relations) of a database to ensure that their dependencies are properly enforced by database integrity constraints. The process involves using a series of so-called "normal forms" to achieve a desirable level of normalization.
What is Normalization?
Normalization aims to:
- Eliminate redundant data: Avoid storing the same information in multiple places.
- Ensure data dependencies make sense: Data should be logically stored. For example, a student's address should be stored with the student record, not repeated in every course they are enrolled in.
- Improve data integrity: Reduce the risk of inconsistencies and errors.
- Simplify database design and maintenance: Makes it easier to update, delete, and insert data without unintended side effects.
Why is Normalization Important?
Without proper normalization, databases can suffer from several problems:
- Update Anomalies: If a piece of data is duplicated across multiple records, updating it in one place doesn't update it everywhere, leading to inconsistencies.
- Insertion Anomalies: It might be impossible to add certain information unless some other unrelated information is also added, due to the way data is structured.
- Deletion Anomalies: Deleting a record might unintentionally remove other important information that was stored in the same record.
The Normal Forms (NF)
There are several normal forms, with the first three (1NF, 2NF, 3NF) being the most commonly applied. Higher normal forms exist but are less frequently used in practice.
First Normal Form (1NF)
A relation is in 1NF if and only if:
- Each column contains atomic (indivisible) values.
- There are no repeating groups of columns.
- Each row is unique.
Example:
Before 1NF (with repeating groups)
| OrderID | CustomerID | ProductNames | Quantities |
|---|---|---|---|
| 101 | C001 | Laptop, Mouse | 1, 2 |
This violates 1NF because ProductNames and Quantities are not atomic.
After 1NF
| OrderID | CustomerID | ProductName | Quantity |
|---|---|---|---|
| 101 | C001 | Laptop | 1 |
| 101 | C001 | Mouse | 2 |
Each cell now contains a single value.
Second Normal Form (2NF)
A relation is in 2NF if it is in 1NF and every non-prime attribute is fully functionally dependent on every candidate key. This essentially means that if a table has a composite primary key (a key made up of two or more columns), then all other columns must depend on the *entire* composite key, not just a part of it.
Key Concepts:
- Prime Attribute: An attribute that is part of any candidate key.
- Non-Prime Attribute: An attribute that is not part of any candidate key.
- Full Functional Dependency: Attribute B is fully functionally dependent on attribute A if A determines B, and no proper subset of A determines B.
Example: Consider an OrderDetails table with a composite key (OrderID, ProductID).
Before 2NF
| OrderID | ProductID | Quantity | ProductName | OrderDate |
|---|---|---|---|---|
| 101 | P001 | 1 | Laptop | 2023-10-26 |
| 101 | P002 | 2 | Keyboard | 2023-10-26 |
Here, the primary key is (OrderID, ProductID). Quantity depends on both. However, ProductName only depends on ProductID, and OrderDate only depends on OrderID. This violates 2NF.
After 2NF (Decomposed into multiple tables)
- Orders Table:
- Products Table:
- OrderDetails Table:
| OrderID | OrderDate |
|---|---|
| 101 | 2023-10-26 |
| ProductID | ProductName |
|---|---|
| P001 | Laptop |
| P002 | Keyboard |
| OrderID | ProductID | Quantity |
|---|---|---|
| 101 | P001 | 1 |
| 101 | P002 | 2 |
Now, each table satisfies 2NF.
Third Normal Form (3NF)
A relation is in 3NF if it is in 2NF and no non-prime attribute is transitively dependent on any candidate key. A transitive dependency exists when a non-key attribute depends on another non-key attribute, which in turn depends on the primary key.
Example: Consider a Students table.
Before 3NF
| StudentID | StudentName | DepartmentID | DepartmentName | DepartmentHead |
|---|---|---|---|---|
| S001 | Alice | D01 | Computer Science | Dr. Smith |
| S002 | Bob | D01 | Computer Science | Dr. Smith |
| S003 | Charlie | D02 | Physics | Dr. Jones |
The primary key is StudentID. DepartmentName and DepartmentHead depend on DepartmentID, which in turn depends on StudentID. This is a transitive dependency (StudentID -> DepartmentID -> DepartmentName, DepartmentHead).
After 3NF (Decomposed into multiple tables)
- Students Table:
- Departments Table:
| StudentID | StudentName | DepartmentID |
|---|---|---|
| S001 | Alice | D01 |
| S002 | Bob | D01 |
| S003 | Charlie | D02 |
| DepartmentID | DepartmentName | DepartmentHead |
|---|---|---|
| D01 | Computer Science | Dr. Smith |
| D02 | Physics | Dr. Jones |
Now, DepartmentName and DepartmentHead are directly dependent on DepartmentID, and DepartmentID is directly dependent on StudentID. The transitive dependency is removed.
Other Normal Forms
While 1NF, 2NF, and 3NF are the most critical, other normal forms exist:
- Boyce-Codd Normal Form (BCNF): A stricter version of 3NF. For every non-trivial functional dependency X -> Y, X must be a superkey.
- Fourth Normal Form (4NF): Deals with multi-valued dependencies.
- Fifth Normal Form (5NF): Deals with join dependencies.
- Domain Key Normal Form (DKNF): The highest level, where all constraints are direct consequences of domain constraints and key constraints.
Denormalization
While normalization is generally beneficial, in some performance-critical applications, a process called denormalization might be applied. This involves intentionally introducing some redundancy back into the database by combining tables or adding duplicate data to speed up read operations, at the cost of increased complexity in writes and potential data integrity issues.
This article provides a foundational understanding of database normalization. Exploring specific use cases and advanced normal forms can further enhance your database design skills.