Normalization Denormalization in Sql

 

1. Normalization

Normalization is a process used in database design to minimize redundancy and dependency by organizing fields and tables. The goal is to divide a database into smaller tables and link them using relationships, ensuring data integrity and efficient data retrieval.

2. Denormalization

Denormalization is the reverse process of normalization. It involves combining normalized tables to improve read performance by reducing the number of joins. However, it may introduce redundancy, which can lead to data anomalies.

3. First Normal Form (1NF)

Definition: A table is in 1NF if:

• It contains only atomic (indivisible) values, meaning no repeating groups or arrays.
• Each column contains unique values.
• There is a unique identifier (primary key) for each row.

Example:

ID	Name	Phone Numbers
1	John	12345, 67890
2	Jane	54321, 98765

This table violates 1NF because the "Phone Numbers" column contains multiple values. To normalize:

ID	Name	Phone Number
1	John	12345
1	John	67890
2	Jane	54321
2	Jane	98765

4. 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 entire primary key (no partial dependencies).

Example: Consider a table where the primary key consists of two columns (Order ID, Product ID), and the table also includes other information like product description:

Order ID	Product ID	Product Description	Quantity
1	101	Laptop	2
1	102	Mouse	3
2	101	Laptop	1

In this case, "Product Description" depends only on "Product ID" and not on the full primary key. To bring this table into 2NF, we must split the table:

Order Table:

Order ID	Product ID	Quantity
1	101	2
1	102	3
2	101	1

Product Table:

Product ID	Product Description
101	Laptop
102	Mouse

5. Third Normal Form (3NF)

Definition: A table is in 3NF if:

• It is already in 2NF.
• There are no transitive dependencies (non-key attributes do not depend on other non-key attributes).

Example: Consider a table that includes Customer information along with Order data:

Order ID	Customer ID	Customer Name	Customer City
1	201	Alice	New York
2	202	Bob	Los Angeles
3	201	Alice	New York

Here, "Customer City" depends on "Customer ID," which is a non-key attribute, so there is a transitive dependency between "Customer City" and "Order ID." To normalize this into 3NF:

Order Table:

Order ID	Customer ID
1	201
2	202
3	201

Customer Table:

Customer ID	Customer Name	Customer City
201	Alice	New York
202	Bob	Los Angeles

This removes the transitive dependency.

---

Summary of Normal Forms:

• 1NF: Eliminate repeating groups (each cell contains atomic values).
• 2NF: Remove partial dependencies (every non-key column depends on the whole primary key).
• 3NF: Remove transitive dependencies (non-key columns depend only on the primary key).

These forms ensure that the database is efficiently organized and prevents redundancy.