This content originally appeared on DEV Community and was authored by Pranav Bakare
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.
This content originally appeared on DEV Community and was authored by Pranav Bakare
Pranav Bakare | Sciencx (2024-10-23T14:52:18+00:00) Normalisation | 2 NF. Retrieved from https://www.scien.cx/2024/10/23/normalisation-2-nf/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.