Question

A database designer is refining a relation that has a composite primary key. Some non-key attributes depend only on part of this key, leading to redundancy. Removing such dependencies ensures the relation satisfies a higher normal form. The correct statement describing this transformation is _______.

• A. First Normal Form (1NF) allows repeating groups and multi-valued attributes
• B. Second Normal Form (2NF) eliminates partial dependencies on a composite key
• C. Third Normal Form (3NF) allows transitive dependencies
• D. Boyce-Codd Normal Form (BCNF) is weaker than 3NF

Hint:

Partial dependencies are only possible if you have a composite primary key. If your primary key consists of only one column, the table is automatically in 2NF (provided it is already in 1NF).

Answer 1

The Correct Option is B

Concept:
The scenario described is the classic definition of a Partial Dependency. This occurs specifically when a table has a composite primary key (a key made of two or more columns) and a non-key column depends on only one of those columns rather than all of them.

Step 1: Identifying the problem.
Suppose we have a table Orders(OrderID, ProductID, ProductName). The primary key is (OrderID, ProductID).
• ProductName depends only on ProductID, not on the OrderID.
• This is a partial dependency. If we have the same product in 100 orders, we repeat the name 100 times.

Step 2: Applying the 2NF transformation.
To reach Second Normal Form (2NF), we must eliminate these partial dependencies. We do this by:
• Taking the partial attribute and the part of the key it depends on.
• Moving them to a new, separate table.
• Keeping the original key in the original table.

Step 3: Result.
The original table now only contains "Full Functional Dependencies." By definition, a relation in 1NF is in 2NF if and only if every non-prime attribute is fully functionally dependent on every candidate key.