Second Normal Form (2NF) is a normalization concept in database management systems (DBMS) that builds upon the First Normal Form (1NF) by addressing dependencies between non-key attributes.
To understand 2NF, let’s briefly recap the First Normal Form (1NF). In 1NF, a table must have a primary key, and each column in the table should contain atomic values (indivisible). 1NF ensures that there are no repeating groups and that each row and column intersection contains only a single value.
2NF takes this a step further by eliminating partial dependencies. A partial dependency occurs when an attribute is functionally dependent on only a portion of the primary key rather than the entire key.
To achieve 2NF, a table must satisfy the following conditions:
- It must already be in 1NF.
- All non-key attributes should be fully functionally dependent on the entire primary key.
In other words, each non-key attribute must be dependent on the entire primary key, not just a part of it. If an attribute is dependent on only a subset of the primary key, it should be moved to a separate table along with the subset of the key it depends on.
By eliminating partial dependencies, 2NF ensures that each non-key attribute relates directly to the entire primary key, promoting data integrity and reducing data redundancy. It helps in organizing and structuring the data in a way that minimizes inconsistencies and anomalies.
Example of DBMS Second Normal Form (2NF):
Let’s consider an example to illustrate the concept of Second Normal Form (2NF). Suppose we have a table called “Orders” with the following columns:
- OrderID (Primary Key)
- CustomerID (Primary Key)
- CustomerName
- ProductID (Primary Key)
- ProductName
- Quantity
In this case, the table is not in 2NF because there is a partial dependency between the non-key attribute “CustomerName” and the subset of the primary key “CustomerID.” The attribute “CustomerName” is functionally dependent on “CustomerID” rather than the entire primary key “OrderID” and “CustomerID.”
To convert this table into 2NF, we need to split it into two separate tables:
Table 1: Orders
- OrderID (Primary Key)
- CustomerID (Primary Key)
- ProductID (Primary Key)
- Quantity
Table 2: Customers
- CustomerID (Primary Key)
- CustomerName
In this new structure, we have created a separate table for customer information. The “CustomerName” attribute is moved to the “Customers” table, which is now related to the “Orders” table through the common “CustomerID” key.
Now, the “Orders” table is in 2NF because all non-key attributes (“Quantity”) are fully functionally dependent on the entire primary key (“OrderID,” “CustomerID,” and “ProductID”). The “Customers” table is also in 2NF because the “CustomerName” attribute is fully functionally dependent on the primary key (“CustomerID”).
By eliminating the partial dependency and separating the tables, we achieve a more normalized and efficient database structure that adheres to 2NF.