The GATE Grind

GATE 2019 CS – Question 42

Databases · Integrity Constraints and Normal Forms · 2 marks · Multiple choice

Let the set of functional dependencies $F=\{QR\to S,\ R\to P,\ S\to Q\}$ hold on a relation schema $X=(PQRS)$. $X$ is not in BCNF. Suppose $X$ is decomposed into two schemas $Y$ and $Z$, where $Y=(PR)$ and $Z=(QRS)$.

Consider the two statements given below.

I. Both $Y$ and $Z$ are in BCNF

II. Decomposition of $X$ into $Y$ and $Z$ is dependency preserving and lossless

Which of the above statements is/are correct?

  1. Both I and II
  2. I only
  3. II only
  4. Neither I nor II

Practise this question in The GATE Grind →

Show answer and explanation

Correct answer: (C) II only

Explanation

$X=(P,Q,R,S)$ with $F=\{QR\to S,\ R\to P,\ S\to Q\}$, decomposed into $Y=(P,R)$ and $Z=(Q,R,S)$.

**Statement I (both in BCNF?)**
- $Y=(P,R)$: the only FD is $R\to P$, and $R$ is a key of $Y$. BCNF.
- $Z=(Q,R,S)$: the FDs that hold on $Z$ are $QR\to S$ and $S\to Q$ (so also $RS\to Q$). The candidate keys are $QR$ and $RS$. The FD $S\to Q$ has the left side $S$, which is not a superkey of $Z$. So $Z$ is **not** in BCNF.

I is false.

**Statement II (lossless and dependency preserving?)**
- Lossless: $Y\cap Z=\{R\}$ and $R\to P$ means $R$ is a key of $Y$. Lossless.
- Dependency preserving: $R\to P$ lies in $Y$, and $QR\to S$ and $S\to Q$ lie in $Z$. All FDs are preserved.

II is true. Answer: II only, option (C).

More Integrity Constraints and Normal Forms questions

All Integrity Constraints and Normal Forms questions →