Database Normalization

Database normalization

Database normalization is the process of organizing data into separate tables based on their logical relationships to reduce data redundancy and improve data integrity. The goal of normalization is to minimize data duplication and ensure that data dependencies are properly maintained.

There are several normalization levels defined in the normalization theory, the most common of which are first normal form (1NF), second normal form (2NF), third normal form (3NF), and so on.

First Normal Form (1NF): A table is in first normal form if each column contains only atomic values, which means that the values in each column cannot be further decomposed into smaller parts. Each row in the table represents a single, unique instance of data.
Example: A table of customers with the following data:

+----+----------+-----------------+
| ID | Name      | Address         |
+----+----------+-----------------+
|  1 | John Doe  | 123 Main St     |
|  2 | Jane Doe  | 456 Elm St      |
+----+----------+-----------------+

Second Normal Form (2NF): A table is in second normal form if it is in 1NF and all non-key columns are dependent on the entire primary key. In other words, the data in each non-key column must depend on the entire primary key and not just a portion of it.
Example: Consider the following table of customers and orders:

+----+----------+-----------------+-------+
| ID | Name      | Address         | Order |
+----+----------+-----------------+-------+
|  1 | John Doe  | 123 Main St     |   100 |
|  2 | Jane Doe  | 456 Elm St      |   200 |
+----+----------+-----------------+-------+

In this example, the table is not in second normal form because the "Order" column depends only on the "ID" column and not the entire primary key. To bring this table to 2NF, we can create two separate tables: one for customers and one for orders.

Customers Table:
+----+----------+-----------------+
| ID | Name      | Address         |
+----+----------+-----------------+
|  1 | John Doe  | 123 Main St     |
|  2 | Jane Doe  | 456 Elm St      |
+----+----------+-----------------+

Orders Table:
+----+-------+
| ID | Order |
+----+-------+
|  1 |   100 |
|  2 |   200 |
+----+-------+

In this example, the two tables are in second normal form because each non-key column depends on the entire primary key.

Third Normal Form (3NF): A table is in third normal form if it is in 2NF and there are no transitive dependencies. In other words, all non-key columns must be dependent only on the primary key and not on any other non-key columns.
Example: Consider the following table of customers, orders, and products:

+----+----------+-----------------+-------+---------+
| ID | Name      | Address         | Order | Product |
+----+----------+-----------------+-------+---------+
|  1 | John Doe  | 123 Main St     |   100 | Apple