MSDN Documentation

Normalization

Normalization is a systematic approach to designing relational databases that reduces data redundancy and improves 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.

Why is Normalization Important?

Effective database normalization helps in:

• Reducing Data Redundancy: Eliminates storing the same data multiple times, saving storage space and preventing inconsistencies.
• Improving Data Integrity: Ensures that data is accurate, consistent, and reliable. When data is updated, it only needs to be changed in one place.
• Simplifying Data Maintenance: Makes it easier to update, insert, and delete data without introducing anomalies.
• Enhancing Query Performance: Well-normalized databases can often lead to more efficient queries, though sometimes a degree of denormalization is used for performance optimization in specific scenarios.

The Normal Forms

Normalization is typically achieved through a series of guidelines called normal forms. The most commonly used normal forms are:

First Normal Form (1NF)

A relation is in 1NF if it is a valid relation with no repeating groups and each attribute contains atomic values.

• Each column must contain atomic (indivisible) values.
• There should be no repeating groups of columns.

Second Normal Form (2NF)

A relation is in 2NF if it is in 1NF and all non-key attributes are fully functionally dependent on the primary key. This applies to tables with composite primary keys.

• Must be in 1NF.
• All non-key attributes must be fully dependent on the *entire* primary key.

Third Normal Form (3NF)

A relation is in 3NF if it is in 2NF and all non-key attributes are non-transitively dependent on the primary key.

• Must be in 2NF.
• No non-key attribute should be dependent on another non-key attribute.

Example: Normalizing Customer Orders

Consider a simple table storing customer orders:

                OrderTable (
                    OrderID,
                    OrderDate,
                    CustomerID,
                    CustomerName,
                    CustomerAddress,
                    ProductID,
                    ProductName,
                    ProductPrice,
                    Quantity
                )
                

This table violates normalization rules:

• Redundancy: Customer details (Name, Address) are repeated for every order placed by the same customer. Product details (Name, Price) are repeated for every order containing the same product.
• Update Anomalies: If a customer changes their address, it needs to be updated in multiple rows.
• Deletion Anomalies: If a customer places no orders, their details might be lost.

To normalize, we can break this into multiple tables:

1. Customers Table (1NF, 2NF, 3NF):

                    Customers (
                        CustomerID (PK),
                        CustomerName,
                        CustomerAddress
                    )
                    

2. Products Table (1NF, 2NF, 3NF):

                    Products (
                        ProductID (PK),
                        ProductName,
                        ProductPrice
                    )
                    

3. Orders Table (1NF, 2NF, 3NF):

                    Orders (
                        OrderID (PK),
                        OrderDate,
                        CustomerID (FK)
                    )
                    

4. OrderDetails Table (1NF, 2NF, 3NF - composite PK):

                    OrderDetails (
                        OrderID (FK, PK),
                        ProductID (FK, PK),
                        Quantity
                    )
                    

In the OrderDetails table, the primary key is a composite of OrderID and ProductID. Quantity is dependent on both the order and the product within that order.

Higher Normal Forms

While 3NF is often sufficient for many applications, there are higher normal forms such as Boyce-Codd Normal Form (BCNF), 4NF, and 5NF that address more complex dependency issues.

Normalization is a powerful tool for creating robust and maintainable databases. Understanding its principles is crucial for any database professional.