{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

HW5Sol[1] - Sample Solution for HW#5 Q 1 Answer Given...

Info iconThis preview shows pages 1–5. Sign up to view the full content.

View Full Document Right Arrow Icon
Page 1 of 11 Sample Solution for HW#5 Q 1) Answer Given: R (A,B,C,D,E) F: (Functional Dependencies in R): A BC C A E A B D a)…………………… Attribute closure: (A) + (ABC) (ABCD) : not a candidate key (C) + (CA) (CAB) (CABD) : not a candidate key (E) + (EA) (EABC) (EABCD): candidate key (B) + (BD) : not a candidate key Answer : E is the only candidate key b)…………………….. The relation is not in BCNF because violation is caused by the following FDs: A BC C A B D We decompose the relation as follows: Step 1: Decompose R(A,B,C,D,E) into R1(A,B,C,E) and R2(B,D) Due to B->D violates BCNF New functional dependencies: F1 (Functional Dependencies in R1): A BC C A E A F2 (Functional Dependencies in R2): B D There is no lost dependency because: (F1 F2) + = F +
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Page 2 of 11 Step 2:decompose R1(A,B,C,E) into R3(A,B,C) and R4(E,A). A->BC violates BCNF in R1 We see that R2(B,D) is in BCNF because B is the candidate key. R1(A,B,C,E) is NOT in BCNF because violation caused by relations: A BC C A Therefore, we perform this decomposition. New functional dependencies: F3 (in R3): A BC C A F4 (in R4): E A We see that R3 is in BCNF because no functional dependency in F3 violates BCNF. Also, R4 is in BCNF because F4 does not violate BCNF. This decomposition is also dependency preserving, since (F2 F3 F4) + = F + Answer : the desired schema is: R2(B,D), R3(A,B,C), R4(E,A) Derivation tree: R(A,B,C,D,E) F: {E A, A BC, C A, B D} /\ / \ / \ / \ R1(A,B,C,E) R2(B,D) F1: {E A, A BC, C A} F2: {B D} (dependency preserving) /\ / \ / \ / \ R3(A,B,C) R4(E,A) F3: {A BC, C A} F4: {E A} (dependency preserving)
Background image of page 2
Page 3 of 11 Q 2) Answer Given: R(A,B,C,D,E) F: A C BC E a)………………………….. Decomposition of R: R1(A,E) R2(A,B,C,D) F1: {} F2: A C Candidate keys: R1: AE R2: ABD Answer: Since the common attribute, A, is not a candidate key in either R1 or R2, we say that this decomposition is NOT lossless join decomposition. Counter example: Let there be an instance in R as follows: A B C D E a 1 b 1 c 1 d 1 e 1 a 1 b 2 c 1 d 2 e 2 After decomposition, the instances would be: In R1: A E a 1 e 1 a 1 e 2 In R2: A B C D a 1 b 1 c 1 d 1 a 1 b 2 c 1 d 2
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Page 4 of 11 Now, let us join R1 and R2. The resultant instance of the natural join is: E A B C D e 1 a 1 b 1 c 1 d 1 e 1 a 1 b 2 c 1 d 2 e 2 a 1 b 1 c 1 d 1 e 2 a 1 b 2 c 1 d 2 So, we see that there are four tuples in the joined relation, while there are only two in the original relation R. So, this decomposition is lossy join. b)………………………….. The decomposition is: R3(A,C) and R4(A,B,D,E) We see that this decomposition is lossless , because the join attribute A is a candidate key in R3. In order to prove that it is dependency preserving, we have to prove that (F3 F4) + = F + where, F: A C BC E F3: A C F4: NULL Now, F3 F4: {A C} So, (F3 F4) + F + Answer : this decomposition is NOT dependency preserving c)…………………………..
Background image of page 4
Image of page 5
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}