Normalisation | 2 NF

Second Normal Form (2NF)

A table is in Second Normal Form (2NF) if:

• It is already in First Normal Form (1NF) (i.e., all data is atomic, no repeating groups).
• All non-key attributes are fully dependent on the entire primary key, not just part of it. In other words, there should be no partial dependencies.

Partial Dependency

Partial Dependency refers to a situation in a database design where a non-key attribute is dependent on only a part of a composite primary key. To help you grasp this concept, let's use an analogy along with an example.

Analogy: Pizza Order System

Imagine you run a pizza restaurant. Each pizza order consists of various details:

• OrderID: A unique identifier for each order (e.g., 001, 002).
• CustomerName: The name of the customer placing the order.
• PizzaSize: The size of the pizza (e.g., Small, Medium, Large).
• Topping: The specific topping on the pizza (e.g., Pepperoni, Mushrooms).
• Order Table Structure

In this example, the primary key is a composite key consisting of OrderID and Topping. This means that each combination of OrderID and Topping uniquely identifies a row in the table.

Identifying Partial Dependencies

In the Order Table:

The CustomerNameis dependent only on the OrderID(it doesn't depend on the specific topping).
The PizzaSizeis also dependent only on the OrderID.

These attributes (CustomerNameand PizzaSize) are not dependent on the entire composite key (OrderID, Topping). This creates a partial dependency because they rely on only part of the primary key (the OrderID).

Implications of Partial Dependency

Having partial dependencies can lead to data redundancy and update anomalies.

For example:

• If Alice changes her name, you must update every instance of her name in the table.
• If the size of Bob's pizza changes, you also have to update every record associated with Bob's order.

Resolving Partial Dependency

To resolve partial dependencies and bring the table into Second Normal Form (2NF), we should separate the table into two:

Orders Table: Holds order details and customer information.
OrderDetails Table: Contains specific details about each pizza.
Revised Tables

Orders Table:

OrderID CustomerName    PizzaSize
001 Alice   Medium
002 Bob Large
003 Charlie Small

OrderDetails Table:

OrderID Topping
001 Pepperoni
001 Mushrooms
002 Sausage
002 Olives
003 Pepperoni

Summary

• Partial Dependency occurs when a non-key attribute is dependent only on part of a composite primary key.
• In the Pizza Order System analogy, CustomerName and PizzaSize are partially dependent on OrderID.
• Resolving partial dependencies helps eliminate redundancy and maintain data integrity by creating separate tables that link together using the primary key.

Conclusion

Partial Dependency: Occurs when non-key attributes depend only on part of a composite key.

Second Normal Form (2NF): Ensures that all non-key attributes are fully dependent on the entire primary key, removing partial dependencies.

Pizza Order System Example: By splitting the original table into Orders and OrderDetails, we eliminate redundancy and ensure data integrity, effectively bringing the design into 2NF.

This process highlights how normalization helps streamline database structures, making them more efficient, reliable, and easier to maintain.