Second Normal Form (2NF)

Definition

Second Normal Form (2NF) is a stage in database normalization that ensures a table is already in First Normal Form (1NF) and that all non-key attributes are fully functionally dependent on the entire primary key. In simpler terms, 2NF removes "partial dependencies," where a piece of data depends on only part of a composite primary key rather than the whole thing.

Main Content

1. Requirement of 1NF

• A table must be in 1NF before it can be considered for 2NF.
• This means the table must have atomic values (no lists or sets in a cell) and a defined primary key.

2. Partial Dependency

• This occurs when an attribute depends on only a part of a composite primary key.
• If a table has a composite key (two or more columns), every non-key column must relate to the combination of those keys, not just one of them.

3. Full Functional Dependency

• This is the goal of 2NF.
• It implies that an attribute is uniquely determined by the combination of all parts of the primary key. If you remove any part of the key, you can no longer determine the value of the attribute.

Working / Process

1. Identify the Primary Key

• Analyze the table to determine which columns serve as the primary key.
• If the primary key is made of two or more columns (Composite Key), you are at risk of partial dependency.

2. Detect Partial Dependencies

• Look for non-key columns that only rely on one part of the composite key.
• Example: In an Order_Items table, if ProductName depends only on ProductID but not on the OrderID, it is a partial dependency.

3. Decompose the Table

• Create new tables to isolate the attributes that are only partially dependent.
• Move the partially dependent attributes and the specific part of the key they depend on into a new, separate table.

BEFORE 2NF (Partial Dependency):
[OrderID, ProductID] -> Quantity, ProductName
(ProductName depends only on ProductID)

AFTER 2NF:
Table 1: [OrderID, ProductID] -> Quantity
Table 2: [ProductID] -> ProductName

Advantages / Applications

• Reduces data redundancy by ensuring that descriptive data isn't repeated unnecessarily across rows.
• Improves data integrity by preventing update anomalies, such as having to update a product name in every single order row.
• Optimizes storage space by organizing related data into smaller, logical table structures.

Summary

2NF is the process of removing partial functional dependencies from a database table that is already in 1NF. It ensures that every non-key column is fully dependent on the entire primary key, resulting in a cleaner and more efficient relational database design.

Important terms to remember: Composite Primary Key, Partial Dependency, Full Functional Dependency, and Table Decomposition.