The second normal form (2NF) is a concept in database normalization, a process used to organize data in a relational database to minimize redundancy and ensure data integrity. To transform a relation (table) into the second normal form, the following conditions must be met:
-
The relation must be in the first normal form (1NF): This means the table should not contain any repeating groups, and all attributes must be atomic (each attribute contains only one value).
-
Every non-key attribute must depend fully on the entire primary key: This means no non-key attribute should depend on just a part of a composite key. This rule aims to eliminate partial dependencies.
Example of Second Normal Form
Let's assume we have an Orders table with the following attributes:
- OrderID (Primary Key)
- ProductID (part of the composite key)
- CustomerName
- CustomerAddress
- ProductName
- Quantity
In this case, the composite key would be OrderID, ProductID because an order can contain multiple products.
To bring this table into the second normal form, we need to ensure that all non-key attributes (CustomerName, CustomerAddress, ProductName, Quantity) fully depend on the entire composite key. If this is not the case, we need to split the table.
Step 1: Decompose the Orders table:
-
Create an Orders table with the attributes:
- OrderID (Primary Key)
- CustomerName
- CustomerAddress
-
Create an OrderDetails table with the attributes:
- OrderID (Foreign Key)
- ProductID (part of the composite key)
- ProductName
- Quantity
Now we have two tables:
Orders:
- OrderID (Primary Key)
- CustomerName
- CustomerAddress
OrderDetails:
- OrderID (Foreign Key)
- ProductID (Primary Key)
- ProductName
- Quantity
By splitting the original table this way, we have ensured that all non-key attributes in the Orders and OrderDetails tables fully depend on the primary key. This means both tables are now in the second normal form.
Applying the second normal form helps to avoid update anomalies and ensures a consistent data structure.
