A data engineer is examining a database table named `Orders` with columns `OrderID`, `CustomerID`, `OrderDate`, `ProductID`, `Quantity`, `Price`, and `CustomerName`. They notice that `CustomerName` is repeated for every order placed by the same `CustomerID`. To achieve Second Normal Form (2NF), which action is primarily required?
- AEnsure every non-key attribute is dependent on the primary key, and only the primary key.
- BRemove all transitive dependencies.
- CEliminate repeating groups by creating new tables.
- DEnsure all columns depend on the entire primary key.
Show answer & explanationAnswer & explanation
Correct answer: D. Ensure all columns depend on the entire primary key.
To achieve 2NF, a table must first be in 1NF, and all non-key attributes must be fully functionally dependent on the entire primary key. In this scenario, `CustomerName` depends only on `CustomerID`, which is a part of the composite primary key (`OrderID`, `ProductID` is a likely composite PK for `Orders` table, or `OrderID` is PK and `ProductID` is part of a compound key for line items). To fix this, `CustomerName` should be moved to a separate `Customers` table, with `CustomerID` as its primary key.
Why the other options are wrong
- A. This is a general statement about functional dependency, but 'entire primary key' is specific to 2NF for composite keys.
- B. Removing transitive dependencies is a requirement for Third Normal Form (3NF), not 2NF.
- C. Eliminating repeating groups is the primary requirement for First Normal Form (1NF).
Second Normal Form (2NF)
A database normalization form that requires a table to be in 1NF and all non-key attributes to be fully functionally dependent on the entire primary key.
- Addresses partial dependencies where a non-key attribute depends on only part of a composite primary key.
- Typically involves moving partially dependent attributes to a new table.
- Reduces data redundancy and improves data integrity.
Memory trick: 1NF is 'First' for 'Flat', 2NF 'Depends All', 3NF 'No Transitive'.