Reference no: EM13509377

1.JDs are motivated by the fact that sometimes a relation that cannot be decomposed into two smaller relations in a lossless-join manner can be so decom-posed into three or more relations. An example is a relation with attributes supplier, part, and project, denoted SPJ, with no FDs or MVDs. The JD ;: {SP, PJ, JS} holds.

From the JD, the set of relation schemes SP, PJ, and JS is a lossless-join decomposition of SPJ. Construct an instance of SPJ to illustrate that no two of these schemes su?ce.

2.Suppose that we have the following four tuples in a relation S with three attributes ABC: (1,2,3), (4,2,3), (5,3,3), (5,3,4). Which of the following functional (→) and multivalued (→→) dependencies can you infer does not hold over relation S?

1. A → B

2. A →→ B

3. BC → A

4. BC →→ A

5. B → C

6. B →→ C

3.Suppose that B → C. Is the decomposition of R into R1 and R2 lossless-join? Reconcile your answer with the observation that neither of the FDs R1 ∩ R2 → R1 nor R1 ∩ R2 → R2 hold, in light of the simple test o?ering a necessary and su?cient condition for lossless-join decomposition into two relations in Section 15.6.1.

4.Suppose you are given a relation R(A,B,C,D). For each of the fol-lowing sets of FDs, assuming they are the only dependencies that hold for R, do the following: (a) Identify the candidate key(s) for R. (b) State whether or not the pro-posed decomposition of R into smaller relations is a good decomposition and brie?y explain why or why not.

1. B → C, D → A; decompose into BC and AD.

2. AB → C, C → A, C → D; decompose into ACD and BC.

3. A → BC, C → AD; decompose into ABC and AD.

4. A → B, B → C, C → D; decompose into AB and ACD.

5. A → B, B → C, C → D; decompose into AB, AD and CD.

5.Consider the attribute set R = ABCDEGH and the FD set F = {AB → C, AC → B, AD → E, B → D, BC → A, E → G}.

a. For each of the following attribute sets, do the following: (i) Compute the set of dependencies that hold over the set and write down a minimal cover. (ii) Name the strongest normal form that is not violated by the relation containing these attributes. (iii) Decompose it into a collection of BCNF relations if it is not in BCNF.

(a) ABC, (b) ABCD, (c) ABCEG, (d) DCEGH, (e) ACEH

b. Which of the following decompositions of R = ABCDEG, with the same set of dependencies F, is (a) dependency-preserving? (b) lossless-join?

(a) {AB, BC, ABDE, EG }

(b) {ABC, ACDE, ADG }