Exercise 3.6.1

1
Exercise 3.6.1

• R(A,B,C,D) with FDs AB?C, C?D, and D?A
• Indicate all the BCNF violations. Do not forget
  to consider FDs that are not in the given set,
  but follow from them.
• Indicate all the BCNF violations. Do not forget
  to consider FDs that are not in the given set,
  but follow from them.

2
Babies

• Exercise 2.2.5 At a birth, there is one baby
  (twins would be represented by two births), one
  mother, any number of nurses, and any number of
  doctors. Suppose, therefore, that we have entity
  sets Babies, Mothers, Nurses, and Doctors.
• Suppose we also use a relationship Births, which
  connects these four entity sets. Note that a
  tuple of the relationship set for Births has the
  form
• (baby, mother, nurse, doctor)

3
Babies (Conted)

• There are certain assumptions that we might wish
  to incorporate into our design. For each, tell
  how to add arrows or other elements to the E/R
  diagram in order to express the assumption.
• a) For every baby, there is a unique mother.
• c) For every combination of a baby and a mother
  there is a unique doctor.

4
Recovering Info from a Decomposition

• Why a decomposition based on an FD preserves the
  information of the original relation?
• Because
• The projections of the original tuples can be
  joined again to produce all and only the
  original tuples.
• Example
• Consider R(A, B, C) and FD B?C, which suppose is
  a BCNF violation.
• Lets decompose based on B?C R1(A,B) and
  R2(B,C).
• Let (a,b,c) be a tuple of R, that projects as
  (a,b) for R1, and as (b,c) for R2.
• It is possible to join a tuple from R1 with a
  tuple from R2, when they agree on the B
  component.
• In particular, (a,b) joins with (b,c) to give us
  the original tuple (a,b,c) back again.
• Getting back those tuples we started with is not
  enough.
• Do we also get false tuples, i.e. that werent in
  the original relation?

5
Example continued

• What might happen if there were two tuples of R,
  say (a,b,c) and (d,b,e)?
• We get
• (a,b) and (d,b) in R1
• (b,c) and (b,e) in R2
• Now if we join R1 with R2 we get
• (a,b,c)
• (d,b,e)
• (a,b,e) (is it bogus?)
• (d,b,c) (is it bogus?)
• They arent bogus. By the FD B?C we know that if
  two tuples agree on B, they must agree on C as
  well. Hence ce and we have
• (a,b,c)
• (d,b,e)
• (a,b,e) (a,b,c)
• (d,b,c) (d,b,e)

6
What if B?C wasnt a true FD?

• Suppose R consists of two tuples
• A B C
• 1 2 3
• 4 2 5
• The projection of R onto the relations with
  schemas R1(A,B) and R2(B,C) are
• A B and B C
• 1 2 2 3
• 4 2 2 5

• Since all four tuples share the same B-value, 2,
  each tuple of one relation joins with both tuples
  of the other relation.
• Thus, when we try to reconstruct R by joining, we
  get
• A B C
• 1 2 3
• 1 2 5
• 4 2 3
• 4 2 5
• That is, we get too much.