Coursehero >> Ohio >> Case Western Reserve University >> EECS 341

Course Hero has millions of student submitted documents similar to the one below including study guides, homework solutions, papers, exam answer keys and textbook solutions.

341 eecs (spring 2005) homework 4 solution Murat Ta an s 1 functional dependency manipulation (20 pts) Recall that Armstrong s Axioms are sound and complete. This means that any other rules on functional dependencies can be derived from Armstrong s Axioms. Using only those three axioms (re exivity, augmentation, and transitivity), the following rules can be derived: union 1. (given) 2. (given) 3. (augmentation of to step 2) 4. (augmentation of to step 1) 5. (transitivity on steps 3 and 4) decomposition 1. (given) 2. (re exivity) 3. (re exivity) 4. (transitivity on steps 1 and 2) 5. (transitivity on steps 1 and 3) pseudotransitivity ( ) ( ) 1. (given) 2. (given) 3. (augmentation of to step 1) 4. (transitivity on steps 2 and 3) 1 2 attribute set closure (20 pts) The lines in question are: . . . for all F do + if F then + F + F end if end for . . . Using the current + : F 1. + (given by the de nition, and can be assumed as the hypothesis F in an inductive proof) 2. (given in algorithm) 3. + + (re exivity) F F 4. (transitivity on steps 1 and 3) 5. (transitivity on steps 2 and 4) 6. + (union on steps 1 and 5) F Thus, the current + can be grown by adding . F 3 canonical covers (20 pts) Given a relation schema R = {a, b, c, d, e} with functional dependency set F = {{a} {b, c}, {c, d} {e}, {b} {d}, {e} {a}}, a canonical cover FC can be found by testing for extraneous attributes: 1. test if b is extraneous in {a} {b, c}: F = (F \ {{a} {b, c}}) {{a} {c}} {a}+ = {a, c} F {b, c} + {a}F , thus b is not extraneous in {a} {b, c} 2. test if c is extraneous in {a} {b, c}: F = (F \ {{a} {b, c}}) {{a} {b}} {a}+ = {a, b, d} F {b, c} {a}+ , thus c is not extraneous in {a} {b, c} F 3. test if d is extraneous in {c, d} {e}: {c}+ = {c} F 2 {e} {c}+ , thus d is not extraneous in {c, d} {e}: F 4. test if c is extraneous in {c, d} {e}: {d}+ = {d} F {e} {d}+ , thus c is not extraneous in {c, d} {e}: F 5. test if e is extraneous in {c, d} {e}: F = F \ {{c, d} {e}} {c, d}+ = {c, d} F {e} {c, d}+ , thus e is not extraneous in {c, d} {e} F 6. test if d is extraneous in {b} {d}: F = F \ {{b} {d}} {b}+ = {b} F {d} {b}+ , thus d is not extraneous in {b} {d} F 7. test if a is extraneous in {e} {a}: F = F \ {{e} {a}} {e}+ = {e} F {a} {e}+ , thus a is not extraneous in {e} {a} F Since all tests for extraneous elements have resulted in no extraneous elements, FC = F (i.e. F is already in canonical cover form). There are no more canonical covers (computable via the algorithm) for F (although note that if one used the + algorithm on F + , it may be possible to nd another canonical cover of FC that is equivalent to F ). 4 BCNF (20 pts) Given a relation schema R = {a, b, c, d, e} with functional dependency F set = {{a} {b, c}, {c, d} {e}, {b} {d}, {e} {a}}, a BCNF form of this schema can be found: 1. is R in BCNF with respect to F ? test {a} {b, c} F ; {a}+ = R thus this test passes F + test {c, d} {e} F ; {c, d}F = R this this test passes test {b} {d} F ; {b}+ = {b, d} thus this test fails, and {b} {d} F can be my witness 2. split R into R1 and R2 about {d} R1 = R \ {d} = {a, b, c, e} 3 R2 = {b} {d} = {b, d} 3. is R1 in BCNF with respect to F ? test {a} R1 ; {a}+ = R R1 thus this test passes F test {b} R1 ; {b}+ = {b, d} is not a key for R1 ; but {b}+ (R1 \ F F {b}) = {} so this test passes test {c} R1 ; {c}+ = {c} is not a key for R1 ; but {c}+ (R1 \{c}) = F F {} so this test passes test {e} R1 ; {e}+ = R R1 thus this test passes F obviously, any subset with a or e will result in a key for R1 , so now test only those combinations without a and e test {b, c} R1 ; {b, c}+ = R R1 thus this test passes F no other tests left, thus R1 is in BCNF with respect to F 4. is R2 in BCNF with respect to F ? test {b} R2 ; {b}+ = R2 thus this test passes F test {d} R2 ; {d}+ = {d} is not a key for R2 ; but {d}+ (R2 \{d}) = F F {} so this test passes no other tests left, thus R2 is in BCNF with respect to F 5. this, the BCNF decomposition is R1 = {a, b, c, e} and R2 {b, d} Given some relation R not in BCNF and functional dependency set F , a BCNF decomposition to R1 and R2 can be shown to be a lossless-join decomposition: 1. let the failing non-trivial dependency be F 2. let the witness then be {x} where x 3. since {x} is non trivial, it is true that {x} = {} (they are disjoint) 4. R1 (only {x} has been removed from R to obtain R1 , and from step 3 it is true that and {x} are disjoint) 5. R2 (since R2 = {x}) 6. R1 R2 (from steps 4 and 5) 7. R2 (from augmentation of on {x}) 8. R1 R2 (re exivity on step 6) 9. R1 R2 R2 (transitivity on steps 7 and 8), thus it is a lossless-join decomposition (recall the de nition of lossless-join decompositions) 4 5 3NF (20 pts) Given a relation schema R = {a, b, c, d, e} with functional dependency set F = {{a} {b, c}, {c, d} {e}, {b} {d}, {e} {a}}, a BCNF decomposition is R1 = {a, b, c, e} and R2 {b, d}; a test to see if this decomposition is dependency preserving is as follows: 1. dependencies {a} {b, c}, {b} {d}, {e} {a} can all be tested using a single relation in the decomposition (i.e. they pass the simple test) 2. dependency {c, d} {e} must be tested using the algorithmic test: at the end of the algorithm, result = {c, d}, thus {e} indicating that this dependency is not preserved result, 3. since at least one dependency has been found ({c, d} {e}) that is not preserved, this is not a dependency preserving decomposition Using the 3NF synthesis algorithm, a 3NF decomposition of R that is dependency preserving is: R1 = {a, b, c} R2 = {c, d, e} R3 = {b, d} R4 = {e, a} Note that the last part of the synthesis algorithm (creating a relation of a candidate key s attributes) is not needed because both e and a are candidate keys, and each appear in at least one relation in the decomposition already. 5

Find millions of documents here - Study Guides, Homework Solutions, Papers, Exam Answer Keys and more. Course Hero has millions of course related materials that will enable you to learn better, faster and get an A in all your courses.
Below is a small sample set of documents: