Normalization: Normal Form 2 (2NF)

Second Normal Form (2NF) builds upon the foundation of First Normal Form (1NF) and addresses issues related to partial dependencies.

What is 2NF?

A relation is in 2NF if and only if:

It is in 1NF.
Every non-prime attribute is fully functionally dependent on the entire primary key.

In simpler terms, if the primary key is a composite key (made up of two or more attributes), then no non-key attribute should be dependent on only a part of that composite key. Such a dependency is called a partial dependency.

Understanding Partial Dependencies

Consider a table with a composite primary key. If an attribute can be determined by only *some* of the attributes in the primary key, it's a partial dependency. This leads to redundancy and update anomalies.

Example: A 1NF Table with Partial Dependencies

Original 1NF Table: `OrderItems`

Let's imagine a table that stores information about items in an order. The primary key here is a composite key made of OrderID and ProductID.

OrderID (PK)	ProductID (PK)	ProductName	Quantity	PricePerUnit	OrderDate
101	A1	Widget A	2	10.00	2023-10-26
101	B2	Gadget B	1	25.00	2023-10-26
102	A1	Widget A	3	10.00	2023-10-27

In this table:

OrderID and ProductID together form the primary key.
ProductName and PricePerUnit are dependent on ProductID (a part of the primary key). This is a partial dependency. If we know the ProductID, we know the ProductName and PricePerUnit, regardless of the OrderID.
OrderDate is dependent on OrderID (another part of the primary key). This is also a partial dependency.
Quantity is dependent on both OrderID and ProductID, so it is fully functionally dependent on the entire primary key.

This table violates 2NF due to the partial dependencies of ProductName, PricePerUnit, and OrderDate.

Achieving 2NF

To bring the table into 2NF, we decompose it into multiple tables, removing the partial dependencies. Each new table will have a single attribute or a set of attributes that fully functionally determine the remaining attributes.

Decomposed Tables in 2NF:

Table: `Orders`

This table stores information related to each order.

OrderID (PK)	OrderDate
101	2023-10-26
102	2023-10-27

Table: `Products`

This table stores information about each product.

ProductID (PK)	ProductName	PricePerUnit
A1	Widget A	10.00
B2	Gadget B	25.00

Table: `OrderDetails`

This table links orders and products, storing order-specific item details.

OrderID (PK, FK)	ProductID (PK, FK)	Quantity
101	A1	2
101	B2	1
102	A1	3

Notice how:

The Orders table now contains the OrderDate, which is fully dependent on OrderID.
The Products table contains ProductName and PricePerUnit, fully dependent on ProductID.
The OrderDetails table now only has attributes (Quantity) that are fully dependent on the composite primary key (OrderID, ProductID).

These decomposed tables are all in 2NF.

Benefits of 2NF

Reduced Redundancy: Information like product names is stored only once per product, not per order item.
Improved Data Integrity: Updates are easier and less prone to errors. For example, if a product's price changes, you only need to update it in one place (the Products table).
Easier Maintenance: The database structure is cleaner and more logical.

When is 2NF Important?

2NF is particularly important when dealing with tables that have composite primary keys. If your tables have simple primary keys (single attribute), they are automatically in 2NF as long as they are in 1NF.

The next step in normalization is Third Normal Form (3NF), which addresses transitive dependencies.