Normalisation In Sql in SQL
? Normalization in SQL (Database Normalization)
Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing large tables into smaller related tables and defining relationships between them.
Goals of Normalization:
Eliminate redundant data (e.g., storing the same data in multiple places)
Ensure data dependencies make sense (only store related data in a table)
Make database structure more flexible and efficient for querying and updating
Normal Forms (Common Levels):
| Normal Form | Description |
|---|---|
| 1NF (First NF) | Each column contains atomic (indivisible) values, and each record is unique. |
| 2NF (Second NF) | Meet 1NF + all non-key columns fully depend on the primary key (no partial dependency). |
| 3NF (Third NF) | Meet 2NF + no transitive dependencies (non-key columns depend only on the primary key). |
| BCNF | A stronger version of 3NF; every determinant is a candidate key. |
| Higher NFs | 4NF, 5NF deal with multi-valued dependencies and join dependencies (rarely needed in practice). |
Example of Normalization:
Unnormalized Table:
| OrderID | CustomerName | Product1 | Product2 | Product3 |
|---|---|---|---|---|
| 1 | Alice | Book | Pen | NULL |
| 2 | Bob | Pencil | NULL | NULL |
After 1NF (Atomic values):
| OrderID | CustomerName | Product |
|---|---|---|
| 1 | Alice | Book |
| 1 | Alice | Pen |
| 2 | Bob | Pencil |
After 2NF and 3NF (Splitting into related tables):
Customers Table:
| CustomerID | CustomerName |
|---|---|
| 1 | Alice |
| 2 | Bob |
Orders Table:
| OrderID | CustomerID |
|---|---|
| 1 | 1 |
| 2 | 2 |
OrderDetails Table:
| OrderDetailID | OrderID | Product |
|---|---|---|
| 1 | 1 | Book |
| 2 | 1 | Pen |
| 3 | 2 | Pencil |
Benefits of Normalization:
Reduces data duplication
Improves data consistency and integrity
Easier to maintain and update data
Efficient storage
When NOT to Normalize Fully:
Sometimes denormalization is done for performance reasons in reporting or data warehousing.
Want me to explain each Normal Form in detail or show SQL examples of normalization?
