Exercise 3.6.1 R(A,B,C,D) with FD’s ABC, CD, and DA

Indicate all the BCNF violations. Do not forget to consider FD’s that are not in the given set, but follow from them.

Indicate all the BCNF violations. Do not forget to consider FD’s that are not in the given set, but follow from them.

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)

Babies (Cont’ed)

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.

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 BC, which suppose is a BCNF violation.
- Let’s decompose based on BC: 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 weren’t in the original relation?

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

- NowifwejoinR1 withR2 weget:
- (a,b,c)
- (d,b,e)
- (a,b,e) (is it bogus?)
- (d,b,c) (is it bogus?)

- They aren’t bogus. By the FD BC we know that if two tuples agree on B, they must agree on C as well. Hence c=e and we have:
- (a,b,c)
- (d,b,e)
- (a,b,e) = (a,b,c)
- (d,b,c) = (d,b,e)

What if BC wasn’t a true FD?

• Suppose R consists of two tuples: ABC

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 1223 4225

• 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:

ABC 123 125 423 425

That is, we get “too much.”