Question 33

Analyze the original schema:

• Relation attributes: $R(\text{VOLUME}, \text{NUMBER}, \text{START PAGE}, \text{END PAGE}, \text{TITLE}, \text{YEAR}, \text{PRICE})$
• Primary key: $PK = (\text{VOLUME}, \text{NUMBER}, \text{START PAGE}, \text{END PAGE})$
• Functional dependencies:
  1. $(\text{VOLUME}, \text{NUMBER}, \text{START PAGE}, \text{END PAGE}) \to \text{TITLE}$
  2. $(\text{VOLUME}, \text{NUMBER}) \to \text{YEAR}$
  3. $(\text{VOLUME}, \text{NUMBER}, \text{START PAGE}, \text{END PAGE}) \to \text{PRICE}$

Check normal forms for the original schema:

• The non-prime attribute $\text{YEAR}$ depends on $(\text{VOLUME}, \text{NUMBER})$, which is a proper subset of the candidate key $PK$.
• This represents a partial functional dependency.
• Because 2NF requires every non-prime attribute to be fully functionally dependent on every candidate key, the original schema violates 2NF. It is only in 1NF.

Analyze the redesigned schemas:

1. $R_1(\text{VOLUME}, \text{NUMBER}, \text{START PAGE}, \text{END PAGE}, \text{TITLE}, \text{PRICE})$: The primary key is the full four-attribute set. All non-prime attributes ($\text{TITLE}, \text{PRICE}$) are fully dependent on the entire key. There are no partial dependencies.
2. $R_2(\text{VOLUME}, \text{NUMBER}, \text{YEAR})$: The primary key is $(\text{VOLUME}, \text{NUMBER})$, and $\text{YEAR}$ is fully dependent on it. There are no partial dependencies.

Because both decomposed relations eliminate the partial dependency, the new database satisfies 2NF (as well as 3NF and BCNF). Since the question asks for the weakest normal form satisfied by the new database that the old one does not satisfy, the answer is 2NF.