1 / 6

Exercise 3.6.1

Exercise 3.6.1. R(A,B,C,D) with FD’s AB C , CD , and DA Indicate all the BCNF violations. Do not forget to consider FD’s that are not in the given set, but follow from them.

torresjames
Download Presentation

Exercise 3.6.1

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Exercise 3.6.1 R(A,B,C,D) with FD’s ABC, CD, and DA 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.

  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 (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.

  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 BCNFviolation. • Let’s 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 weren’t 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 aren’t bogus. By the FD BC 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)

  6. What if BC wasn’t 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.”

More Related