Description
Problem 1 (10 points)
Consider the relation R(M, N, O, P, Q) and the FD set F={M→N, O→Q, OP→M}.
-
Compute (MO)+.
-
Is R in 3NF?
-
Is R in BCNF?
Problem 2 (30 points)
Consider the relation R(P, Q, S, T, U, V,W) and the FD set F={PQ→S, PS→Q, PT→U, Q→T, QS→P, U→V}. For each of the following relations, do the following:
-
List the set of dependencies that hold over the relation and compute a minimal cover.
-
Name the strongest normal form that is not violated by the relation containing these attributes.
-
Decompose it into a collection of BCNF relations if it is not in BCNF.
(a)R1(P, Q, S)
(b)R2(P, Q, T)
(c)R3(P, T, U, V)
(d)R4(P, Q, T, U)
(e)R5(P, S, U, V, W)
Problem 3 (20 points)
Decide whether each of the following decomposition of R(P, Q, S, T, U, V), with the FD set
-
={PQ→S, PS→Q, PT→U, Q→T, QS→P, U→V}, is (i) dependency-preserving. (ii) lossless-join.
-
R1(P, Q) R2(P, S) R3(P, Q, U) R4(U, V)
-
R1(P, Q, S) R2(P, S, T, V) R3(P, T, U)
NOTE: Perform all the operations on the original tree provided above. Do not carry forward the changes in each question to the next.
a. In the above index, which nodes need to be accessed to find all the data entries which are greater than 180?
b. After 203 is inserted, how the structure will be changed?
c. After removing 144 from the original index, how the structure will be changed assuming to carry out redistribution from the right sibling?
d. After inserting 181, how the structure will be changed? Show the new index. e. What is the height of T1, T2, and T3? What are the entries in each of them?
