GATE CS 2003 Q85 — Normal Forms

To determine the normal form of the relation $R(\text{Roll\_number}, \text{Name}, \text{Date\_of\_birth}, \text{Age})$, let us analyze its functional dependencies (FDs) and candidate keys.

1. Identify Relevant Functional Dependencies

From the given set of FDs, the ones applicable to the attributes in $R$ are:

• $\text{Date\_of\_birth} \to \text{Age}$
• $\text{Name} \to \text{Roll\_number}$
• $\text{Roll\_number} \to \text{Name}$

2. Find the Candidate Keys

Let us compute the attribute closures to find the candidate keys:

• $(\text{Roll\_number}, \text{Date\_of\_birth})^+ = \{\text{Roll\_number}, \text{Date\_of\_birth}, \text{Name}, \text{Age}\}$
• $(\text{Name}, \text{Date\_of\_birth})^+ = \{\text{Name}, \text{Date\_of\_birth}, \text{Roll\_number}, \text{Age}\}$

Since $\text{Date\_of\_birth}$ does not appear on the right-hand side of any FD, it must be part of every candidate key. Thus, the candidate keys of $R$ are:

$$\text{Candidate Keys} = \{\text{Roll\_number}, \text{Date\_of\_birth}\} \text{ and } \{\text{Name}, \text{Date\_of\_birth}\}$$

3. Classify Attributes

• Prime attributes (part of any candidate key): $\{\text{Roll\_number}, \text{Name}, \text{Date\_of\_birth}\}$
• Non-prime attribute: $\{\text{Age}\}$

4. Check for Normal Forms

• Second Normal Form (2NF): A relation is in 2NF if and only if there are no partial dependencies (i.e., no non-prime attribute depends on a proper subset of any candidate key).
  Consider the FD: $$\text{Date\_of\_birth} \to \text{Age}$$ Here, $\text{Date\_of\_birth}$ is a proper subset of the candidate key $\{\text{Roll\_number}, \text{Date\_of\_birth}\}$ and it determines the non-prime attribute $\text{Age}$. This is a partial dependency.

Since the relation violates 2NF, it is not in 2NF. Consequently, it cannot be in 3NF or BCNF.

Therefore, the relation is in none of the above normal forms.