GATE CS 2008 Q69 — Normal Forms

To determine the normal form of the relations, we first identify the candidate keys for both schemas.

1. Candidate Keys for Book:

• Given that $\{\text{Author, Title}\}$ is a key, we have: $$\{\text{Author, Title}\}^+ = \{\text{Author, Title, Catalog\_no, Publisher, Year, Price}\}$$
• Using FD (II), $\text{Catalog\_no} \rightarrow \text{Title, Author, Publisher, Year}$, we also find: $$\{\text{Catalog\_no}\}^+ = \{\text{Catalog\_no, Title, Author, Publisher, Year, Price}\}$$

Thus, the candidate keys for Book are $\{\text{Author, Title}\}$ and $\{\text{Catalog\_no}\}$.

• Prime attributes: $\{\text{Author, Title, Catalog\_no}\}$
• Non-prime attributes: $\{\text{Publisher, Year, Price}\}$

2. Normal Form of Book:

• Consider the FD: $\text{Publisher, Title, Year} \rightarrow \text{Price}$. The LHS is not a superkey, and the RHS ($\text{Price}$) is a non-prime attribute. This violates 3NF.
• To check for 2NF, we look for partial dependencies (proper subset of a candidate key $\rightarrow$ non-prime attribute). The proper subsets of candidate keys are $\{\text{Author}\}$ and $\{\text{Title}\}$. Neither of these determines any non-prime attribute. Thus, there is no partial dependency, meaning Book is in 2NF.

3. Normal Form of Collection:

• The attributes are $\{\text{Title, Author, Catalog\_no}\}$.
• The applicable FDs are:
  1. $\text{Title, Author} \rightarrow \text{Catalog\_no}$
  2. $\text{Catalog\_no} \rightarrow \text{Title, Author}$
• Since the LHS of both functional dependencies are candidate keys, Collection is in BCNF, which implies it is also in 3NF.

Therefore, Book is in 2NF and Collection is in 3NF.