We completed normalization, seeing 3NF (Section 3.5). Section 3.4 also contains some general tools (such as the chase test). We then started talking about constraints and triggers (Chapter 7). Next week (Wednesday, Monday is holidy), we will complete triggers (7.4), talk about views (Chapter 8), and then move on to database programming (Chapter 9) & indices (Chapter 8).
1. (Reading Assignment) Read Sections 3.4.1, 3.4.2, and 3.5 of the textbook (Ullman, Widom, A First Course in Database Systems).
2. (3NF, 12pt) In the following problems you are given canonical covers of some sets of FDs; determine the 3NF-decomposition.
a) [4pt] You are given a relation R1(A,B,C,D,E,F) with canonical cover F = {A→E, B→DE, E→A, F→AC}. Find the 3NF of R.
b) [2pt] Is the original relation R1 from a) in 3NF? To determine this, you need to first find all prime attributes of R1, and then check for each FD whether it is a violation.
c) [4pt] You are given a relation R2(A,B,C,D,E,F) with canonical cover F = {B→D, BC→F, D→AB, E→F}. Find the 3NF of R.
d) [2pt] Is the original relation R2 from b) in 3NF? To determine this, you need to first find all prime attribtues of R2, and then check for each FD whether it is a violation.
3. (Lossless join chase test, 13pt) You have a relation R(A,B,C,D,E,F) with functional dependencies {AC→B, B→DE, C→F, D→EF, DF→A}. A friend gave you the following decomposition of R into three tables: P(A,B,C), Q(C,D,E), S(A,D,F).
[4pt] Is this decomposition in BCNF? (Argue your case.)
[9pt] Use the chase test to determine whether the decomposition is a lossless join decomposition. Note: Apply one rule of the chase test in each step, and show the intermediate tables you get.
4. (3NF, 15pt) Decompose the following relations into 3NF. Use the algorithm from class (so you need to compute the keys and minimal cover of each relation). Include the details of determining the Canonical Cover.
R(A,B,C,D) with functional dependencies {A→BC, AD→B, CD→AB}.
R(A,B,C,D,E) with functional dependencies {AD→BC, D→CE, C→E}.
R(A,B,C,D,E) with functional dependencies {AD→B, B→D, C→EA}