IAE Academy

GATE 2020 CS Q.36: solution and answer

GATE 2020 · CSQ.36 · 2 marks · MCQ

Consider a relational table that is in 3NF, but not in BCNF. Which one of the following statements is TRUE?

  1. (A)

    has a nontrivial functional dependency , where is not a superkey and is a prime attribute.

  2. (B)

    has a nontrivial functional dependency , where is not a superkey and is a non-prime attribute and is not a proper subset of any key.

  3. (C)

    has a nontrivial functional dependency , where is not a superkey and is a non-prime attribute and is a proper subset of some key.

  4. (D)

    A cell in holds a set instead of an atomic value.

See the question in the paper

Answer and solution

ANSWER

Option (A) is true: the FD that breaks BCNF must end in a prime attribute.

Official answer: ASource: Official answer key, GATE 2020 CS (IIT Delhi)

  1. is not in BCNF, so some nontrivial FD holds in with not a superkey.

  2. is in 3NF, so every nontrivial FD must satisfy at least one of: is a superkey, or is a prime attribute.

  3. For the FD in step 1, is not a superkey, so the second condition must hold: is a prime attribute. This is exactly option (A), so (A) is true.

  4. Options (B) and (C) both claim a nontrivial FD with not a superkey and non-prime. That is the case 3NF forbids, so both are false, whatever is a subset of.

  5. Option (D) is false. 3NF includes 1NF, so every cell holds an atomic value.

KEY IDEA

3NF allows an FD when is a superkey or is prime. BCNF allows only the first. A table in 3NF but not BCNF must therefore have an FD whose determinant is not a superkey and whose right side is prime.

WATCH OUT FOR

Picking (C) because partial dependencies sound like a normal-form violation. A non-prime attribute depending on part of a key breaks 2NF, so a table in 3NF cannot have one. Another slip is choosing (D) by confusing 3NF with the 1NF requirement.

From the GATE 2020 CS paper. Every question of this paper · Download the full paper · All 19 Normal forms questions, with notes

More Normal forms questions