Let R(A, B, C, D, E, P, G) be a relational schema in which the following…

2025

Let R(A, B, C, D, E, P, G) be a relational schema in which the following functional dependencies are known to hold:

  • AB → CD

  • DE → P

  • C → E

  • P → C

  • B → G

The relation schema R is ______.

  1. A.

    In Boyce-Codd normal form

  2. B.

    In third normal form, but not in Boyce-Codd normal form

  3. C.

    In second normal form, but not in third normal form

  4. D.

    Not in second normal form

Attempted by 85 students.

Show answer & explanation

Correct answer: D

A relation is in Second Normal Form (2NF) when it is already in 1NF and, in addition, every non-prime attribute (one that belongs to no candidate key) is fully functionally dependent on each candidate key as a whole — with no non-prime attribute depending on only a proper subset of a key. Such a subset-only dependency is called a partial dependency, and its presence is exactly what disqualifies a relation from 2NF. Testing this requires first finding every candidate key via attribute-closure computation, then checking whether any proper subset of a key closes (directly via a given FD, or indirectly through the closure) to include a non-prime attribute.

  1. List the FDs: AB → CD, DE → P, C → E, P → C, B → G, over the seven attributes A, B, C, D, E, P, G.

  2. Neither A nor B ever appears on the right-hand side of any FD (the right-hand sides across all five FDs are C, D, P, E, G) — so no combination of the remaining attributes can ever derive A or B on its own; every candidate key of R must therefore contain both A and B.

  3. Compute the closure of {A, B}: AB → CD adds C and D, giving {A,B,C,D}; B → G adds G, giving {A,B,C,D,G}; C → E adds E, giving {A,B,C,D,E,G}; DE → P adds P, giving {A,B,C,D,E,G,P} — every attribute of R.

  4. Since every candidate key must already contain A and B (step 2), and {A,B} alone closes to the full attribute set (step 3), AB is R's unique, minimal candidate key. A and B are therefore the only prime attributes; C, D, E, P, G are all non-prime.

  5. Test every FD's left-hand side against the key AB: B → G has left side B, a proper subset of AB, and right side G, a non-prime attribute — this is a partial dependency of a non-prime attribute on part of the key.

A partial dependency is sufficient by itself to fail 2NF, and since the normal forms nest as BCNF ⊆ 3NF ⊆ 2NF (every BCNF relation is in 3NF, and every 3NF relation is in 2NF), failing 2NF automatically rules out 3NF and BCNF as well. Independently, C → E and P → C confirm the same conclusion from another angle: C and P are non-superkey determinants of non-prime attributes (E and C respectively), so the relation would fail BCNF and 3NF even without considering B → G — but it is specifically the B → G partial dependency that pushes R below 2NF.

Hence R is not in 2NF.

Explore the full course: Amcat Preparation

Loading lesson…