GATE CS 2004 Q50 — Normal Forms

To find the highest normal form, we first determine the candidate keys of the relation $R(\text{name, courseNo, rollNo, grade})$:

1. Since $\text{courseNo}$ does not appear on the right-hand side of any functional dependency (FD), it must be part of every candidate key.
2. Taking the closure of $\{\text{name, courseNo}\}$: $$\{\text{name, courseNo}\}^+ = \{\text{name, courseNo, grade, rollNo}\}$$ Thus, $\{\text{name, courseNo}\}$ is a candidate key.
3. Taking the closure of $\{\text{rollNo, courseNo}\}$: $$\{\text{rollNo, courseNo}\}^+ = \{\text{rollNo, courseNo, grade, name}\}$$ Thus, $\{\text{rollNo, courseNo}\}$ is also a candidate key.

From these candidate keys, we classify the attributes:

• Prime attributes (part of any candidate key): $\{\text{name, rollNo, courseNo}\}$
• Non-prime attributes: $\{\text{grade}\}$

Now, we test the normal forms:

• BCNF: For a relation to be in BCNF, the left-hand side of every non-trivial FD $X \rightarrow Y$ must be a superkey. For the FD $\text{name} \rightarrow \text{rollNo}$, the LHS $\{\text{name}\}$ is not a superkey. Thus, the relation is not in BCNF.
• 3NF: For a relation to be in 3NF, for every non-trivial FD $X \rightarrow Y$, either $X$ must be a superkey or $Y$ must be a prime attribute.
  • $\text{name, courseNo} \rightarrow \text{grade}$ : LHS is a superkey.
  • $\text{rollNo, courseNo} \rightarrow \text{grade}$ : LHS is a superkey.
  • $\text{name} \rightarrow \text{rollNo}$ : RHS $\{\text{rollNo}\}$ is a prime attribute.
  • $\text{rollNo} \rightarrow \text{name}$ : RHS $\{\text{name}\}$ is a prime attribute.
  Since all FDs satisfy the 3NF condition, the relation is in 3NF.

Therefore, the highest normal form of the relation is 3NF.