Hello, if you have any need, please feel free to consult us, this is my wechat: wx91due
CS 122A: Introduction to Data Management
Homework 6: Relational Design Theory (75 points)
Submission
All homework assignments should have the student IDs and names of your team members. Remember that all homework assignments should be done in a group. This homework assignment should be submitted on EEE before 11:45 pm on the due date.
Only one student in a group should submit the file. Everybody on the team isrequired to have the finally submitted version. Refer to the following table for the submission guidelines. After the 24hour grace period, no more submission is allowed on EEE. That is, we will not accept assignments after that time. We will publish the solutions at that time for the next assignment. Please get all your work in on time!
Relational Design Theory [75 pts]
1. [20pts] We have a relation R(A,B,C,D) with four attributes. For each of the following sets of FDs, assuming those are the only dependencies that hold for R, do the following: (a) Identify the candidate key(s) for R. (b) Identify the strongest form that R satisfies (1NF, 2NF, 3NF, or BCNF), and point out a single dependency that violates the normal form.
1) C → D, C → A, B → A
2) A → B, B → C, D → A
3) AB → D, D → B
4) A → B, A → C, C → D
5) BC → A, BC → D, A → C, D → B
2. [20pts] Answer the following questions:
(1) Give a set of FDs for the relation schema R(A,B,C,D) with primary key AB under which R is in 1NF but not in 2NF.
(2) Give a set of FDs for the relation schema R(A,B,C,D) with primary key AB under which
R is in 2NF but not in 3NF.
3. [10 pts] Suppose we have a relation R with 6 attributes ABCDEF. Part of its instances is listed below:aa
|
A
|
B
|
C
|
D
|
E
|
F
|
|
1
|
2
|
4
|
6
|
3
|
6
|
|
2
|
1
|
5
|
7
|
9
|
7
|
3
|
2
|
4
|
6
|
3
|
8
|
|
4
|
1
|
4
|
0
|
9
|
9
|
Try to infer all the function dependencies in relations R, and listed as follow.
Write down am inimal set of functional dependencies with at most two attributes on the lefthand side that are not violated by this instance. By “minimal” we mean no FDs in the set can be derived by the other FDs in the set. For instance, the set {X → Y, Y → Z, X → Z} is not minimal since the last FD can be derived by the first two. The set {X>Y, Y> Z} is minimal.
4. [25pts] Consider the attribute set R = ABCDEGH and the FD set F = {AB → CD, AC → B,AD→ E, BD → A, B → C, E → G}.
(a) For each of the following attribute sets, do the following: (i) Compute the set of dependencies that hold over the set (ii) Name the strongest normal form that is not violatedby the relation containing these attributes.
(1) ABC, (2) ABCD, (3) ABCEG, (4) DCEGH, (5) ACEH
(b) Which of the following decompositions of R = ABCDEGH, with the same set of dependencies F, is dependencypreserving? Explain why.
Decomposition 1: {AB, BC, ABDE, EG}
Decomposition 2: {ABC, ACDE, ADG}
Extra Credits
[10pts] Decompose each of the attribute sets in Question 4(a) into a collection of BCNF relations if it is not in BCNF.