CSE 444
Homework 5 Solutions
15.8
P
AB(r) JN P BC(r) = r, or R1 JN R2 = R(see page 413)
B is definitely not a key. In R2, we see the B value 1 occurring with C values 8 and 9. This shows B cannot be a key.
15.9
The decomposition is BCNF. However, we cannot relate AD and BC after the decomposition, so it is lossy.
The relations are BCNF. It is a lossless decomposition since the join attribute, e.g. C, determines ACD. The decomposition is not dependency preserving, e.g. AB->C.
The relation was already in BCNF, so the decomposition was not needed. The resulting relations are still BCNF and is was a lossless decomposition since the join attribute A determines ABC.
It is not BCNF since C->D and A is the key for ACD. It is not dependency preserving, e.g. B->C. It was a lossless decomposition since the join attribute A determines both relations.
The relations are BCNF. The decomposition is lossy since the join attribute for AD and CD is D and D does not determine either relation. Further, the decomposition is not dependency preserving, e.g. B->C.
15.11
1.
|
Violate BC->D |
Violate BC->->D |
a. |
No |
No |
b. |
No, as long as a !=2 |
No |
c. |
No, as long as a != 2 |
No, unless a = 2 |
d. |
Yes |
No, unless a = 2 |
e. |
No, as long as a != 2 |
No, unless a = 2 |
f. |
Yes |
Yes |
g. |
Yes |
No |
2. We cannot say if A->B holds for sure, but it is not violated.
17.2
T3 ¬ T1
¯T2
17.3
|
serial |
conflict serial |
strict |
recoverable |
1. |
No, unless T1 or T2 is aborted. |
No. |
No, T2 writes X before T1 commits. |
Yes. No changes are read. |
2. |
Yes. Running T1 to completion and then running T2 yields the same results. |
Yes. |
No, T2 reads X before T1 commits. |
Only if T1 commits before T2. |
3. |
Yes, T1 T3 T2. |
Yes. |
No. T3 writes X before T1 commits. |
Only if T3 commits before T2. |
4. |
No, because of the T2 reads of Y around T3's write of Y. |
No. |
No. T2 reads Y before T3 commits. |
Only if T3 commits before T2. |
5. |
Yes. T1 |
Yes. |
No. T1 writes X before T2 aborts. |
Yes. |