A data engineer is working with a large dataset of customer transactions. The current `Transactions` table includes `TransactionID`, `CustomerID`, `CustomerName`, `CustomerAddress`, `ProductID`, `ProductName`, `ProductPrice`, and `Quantity`. The engineer observes that `CustomerName` and `CustomerAddress` are repeated for every transaction made by the same customer, and `ProductName` and `ProductPrice` are repeated for every transaction involving the same product. Which normalization form would address these repeating non-key attributes by ensuring that all non-key attributes are fully functionally dependent on the primary key?
- ABoyce-Codd Normal Form (BCNF)
- BThird Normal Form (3NF)
- CFirst Normal Form (1NF)
- DSecond Normal Form (2NF)
Show answer & explanationAnswer & explanation
Correct answer: D. Second Normal Form (2NF)
Second Normal Form (2NF) requires that all non-key attributes in a table are fully functionally dependent on the primary key. If a table has a composite primary key, 2NF addresses partial dependencies, where a non-key attribute depends on only part of the primary key. In this scenario, `CustomerName` and `CustomerAddress` depend only on `CustomerID` (part of a likely composite key `(TransactionID, ProductID)` or similar), and `ProductName`/`ProductPrice` depend only on `ProductID` (another part).
Why the other options are wrong
- A. BCNF is a stricter form of 3NF, dealing with advanced dependency issues not directly described by the repeating non-key attributes depending on parts of a composite key.
- B. 3NF addresses transitive dependencies (non-key attributes depending on other non-key attributes), which is a step beyond partial dependencies.
- C. 1NF deals with atomic values and no repeating groups, which is a prerequisite but doesn't address the specific issue of partial dependencies.
Second Normal Form (2NF)
A relational database normalization form that requires a table to be in First Normal Form (1NF) and that all non-key attributes are fully functionally dependent on the primary key.
- Addresses partial dependencies where a non-key attribute depends on only part of a composite primary key.
- Eliminates redundancy by moving partially dependent attributes to new tables.
- A table with a single-column primary key is automatically in 2NF if it's in 1NF.
Memory trick: 1NF for atoms, 2NF for parts, 3NF for transit.