Quiz 2

1
Quiz 2

• Let R(A,B,C,D) be a relation schema with
• the FDs A?BCD and B?CD and
• the MVD A ??B.
• Answer the following
• Find all possible keys of R.
• Is the relation in 4NF? If not, propose a
  decomposition of R into 4NF relations.
• (Answer only what is asked? - Remember to justify
  your answer.)

2
Answer

• There is no FD that has A in the right hand side,
  so any key of R must contain A. Since
  AA,B,C,D we conclude that A (or A) is the
  only key of R.
• To figure out whether the relation is in 4NF or
  not, we need to look for MVDs that will
  potentially violate the 4NF condition. Since A is
  a key of R, we only need to consider MVDs whose
  left hand side is not A. In this case, B??CD is a
  candidate (because each FD is a MVD). This is a
  nontrivial MVD. Furthermore B is not a superkey.
  Therefore, B??CD violates the 4NF condition.
  This implies that R is not in 4NF.
• This suggests a decomposition B,C,D, B,A
  (using B??CD). The FDs of these relations are B
  ?CD (and its derived FDs B?C, B?D) and A ?B,
  respectively. These imply that the newly defined
  relations only have trivial MVDs or MVDs with the
  left hand side is a key (B??C, B??D for the first
  one) . In other words, the decomposition yields
  4NF relations. Hence, we can stop here.