We also found out that the keys are AB, AC and AD. FDs where the left side is not a superkey or
the attributes on the right are not part of some key are 3NF violations. The 3NF violations are
Exercise 3.5.2a
The usual procedure to find the keys would be to take the closure of all 63 nonempty subsets.
Exercise 3.5.2b
The first step to verify that the given FDs are their own minimal basis is to check to see if any of
the FDs can be removed. However, if we remove any one of the five FDs, the remaining four
Exercise 3.5.2c
Since the only key is HS, the given set of FDs has some dependencies that violate 3NF. We also
Exercise 3.5.3
The usual procedure to find the keys would be to take the closure of all 63 nonempty subsets.
However, if we notice that none of the right sides of the FDs contains attributes I and S. Thus we
know that attributes I and S must be part of any key. We eventually will find out that IS is the
Exercise 3.5.4
This is the initial tableau:
A
B
C
D
E
a
b
c
d1
e1
a
b2
c2
d
e2
a
b
c3
d3
e
A
B
C
D
E
a
b
c
d
e1
a
b2
c2
d
e2
a
b
c
d
e
Exercise 3.5.5
Suppose that our relation relates to the work environment and has three attributes, Name,
RoomNumber and ComputerID. Suppose also that only the following FDs hold:
Name→RoomNumber
ComputerID→RoomNumber
would contain the tuple:
{‘John Doe’, 1, ‘A’}
If we follow the decomposition, we would expect the tuple above to be broken into two smaller
tuples:
{‘John Doe’, 1}
Exercise 3.6.1
Since A→→B, and all the tuples have the same value for attribute A, we can pair the B-value
Exercise 3.6.2a
First, people have unique Social Security numbers and unique birthdates. Thus, we expect the
functional dependencies ssNo→name and ssNo→birthdate hold. The same applies to children, so
we expect childSSNo→childname and childSSNo→childBirthdate. Finally, an automobile has a
unique brand, so we expect autoSerialNo→autoMake.
Exercise 3.6.2b
We suggest the relation schemas:
{ssNo, name, birthdate}
Exercise 3.6.3a
Since there are no functional dependencies, the only key is all four attributes, ABCD. Thus, each
Exercise 3.6.3b
Since there are no functional dependencies, the only key is all four attributes, ABCD. Thus each
Exercise 3.6.3c
From the FD B→D, we can deduce that the only key is ABC. The MVD AB→→C and the
Exercise 3.6.3d
From the FDs A→D and AB→E, we can deduce that the only key is ABC. The MVDs A→→B,
Exercise 3.6.4
We would not expect name to be functionally determined by the other four attributes because
there could be more than one person living at the same address who starred in the same movie.
For example, a husband and wife could star in a romance movie.
Exercise 3.7.1a
Our starting tableau is:
A
B
C
D
E
a
b1
c1
d1
e1
a
b2
c2
d2
e2
Applying MVD A→→BC we get:
A
B
C
D
E
a
b1
c1
d1
e1
a
b2
c2
d2
e2
a
b1
c1
d2
e2
a
b2
c2
d1
e1
Applying FD B→D we get:
A
B
C
D
E
a
b1
c1
d1
e1
a
b1
c1
d1
e2
a
b2
c2
d1
e1
Applying MVD C→→E we get:
A
B
C
D
E
a
b1
c1
d1
e1
Exercise 3.7.1b
Our starting tableau is:
A
B
C
D
E
a
b1
c1
d
e1
a
b
c
d2
e
Applying MVD A→→BC we get:
A
B
C
D
E
a
b1
c1
d
e1
a
b
c
d2
e
a
b
c
d
e1
a
b1
c1
d2
e
Applying FD B→D we get:
A
B
C
D
E
a
b1
c1
d
e1
a
b
c
d
e
a
b
c
d
e1
a
b1
c1
d
e
A
B
C
D
E
a
b1
c1
d1
e2
a
b2
c2
d1
e1
a
b1
c1
d1
e2
a
b2
c2
d1
e1
a
b1
c1
d1
e1
a
b2
c2
d1
e2
a
b
c
d
e1
a
b1
c1
d
e
Exercise 3.7.1c
Our starting tableau is:
A
B
C
D
E
a
b1
c1
d1
e1
a
b2
c2
d2
e2
Applying MVD A→→BC we get:
A
B
C
D
E
a
b1
c1
d1
e1
a
b2
c2
d2
e2
a
b1
c1
d2
e2
a
b2
c2
d1
e1
Applying FD B→D we get:
A
B
C
D
E
a
b1
c1
d1
e1
a
b2
c2
d1
e2
a
b1
c1
d1
e2
a
b2
c2
d1
e1
A
B
C
D
E
a
b1
c1
d1
e1
a
b1
c1
d
e
a
b
c
d
e1
a
b
c
d
e
a
b1
c1
d
e1
Exercise 3.7.1d
Our starting tableau is:
A
B
C
D
E
a
b1
c1
d1
e
a
b
c
d
e2
Applying MVD A→→BC we get:
A
B
C
D
E
a
b1
c1
d1
e
a
b
c
d
e2
a
b1
c1
d
e2
a
b
c
d1
e
A
B
C
D
E
a
b1
c1
d
e
a
b
c
d
e2
a
b1
c1
d
e2
a
b
c
d
e
A
B
C
D
E
a
b1
c1
d
e
a
b
c
d
e2
a
b1
c1
d1
e2
a
b2
c2
d1
e1
a
b1
c1
d1
e1
a
b2
c2
d1
e2
a
b1
c1
d
e2
a
b
c
d
e
Exercise 3.7.2
Using the list of simplifications on pg. 120, we can narrow the list of possible FDs down to
For the FDs A→C, A→E the initial tableau looks like:
A
B
C
D
E
a
b1
c1
d1
e1
a
b2
c2
d2
e2
The final tableau looks like:
A
B
C
D
E
a
b1
c1
d1
e1
a
b2
c2
d1
e2
a
b1
c1
d1
e2
a
b2
c2
d1
e1
a
b1
c1
d
e
a
b
c
d
e2
A
B
C
D
E
a1
b1
c
d1
e1
We conclude that neither C→A nor C→E hold in relation S.
For the FD AC→E the initial tableau looks like:
A
B
C
D
E
a
b1
c
d1
e1
a
b2
c
d2
e2
The final tableau looks like:
A
B
C
D
E
a
b1
c
d1
e1
a
b2
c
d1
e2
a
b1
c
d1
e2
a
b2
c
d1
e1
We conclude that AC→E does not hold in relation S.
A
B
C
D
E
a
b1
c1
d1
e
a
b2
c2
d1
e
a
b1
c1
d1
e
a
b2
c2
d1
e
a2
b2
c
d2
e2
a1
b1
c
d1
e2
a2
b2
c
d2
e1
For the FD CE→A the initial tableau looks like:
A
B
C
D
E
a1
b1
c
d1
e
a2
b2
c
d2
e
The final tableau looks like:
For the MVD A→→C the initial tableau looks like:
A
B
C
D
E
a
b1
c
d1
e1
a
b
c2
d
e
The final tableau looks like:
A
B
C
D
E
a
b1
c
d
e1
a
b
c2
d
e
a
b1
c
d
e
a
b
c2
d
e1
For the MVD C→→A the initial tableau looks like:
A
B
C
D
E
a
b1
C
d1
e1
a2
b
d
e
A
B
C
D
E
a1
b1
c
d1
e
a2
b2
c
d2
e
a
b1
C
d1
e1
Exercise 3.7.3a
Let W be the set of attributes not in X, Y, or Z. Consider two tuples xyzw and xy’z’w’ in the
Exercise 3.7.3b
Let W be the set of attributes not in X, Y, or Z, V be the set of attributes that Y and Z have in
common, Y1 be the set of attributes of Y not in V and Z1 be the set of attributes of Z not in V.
Exercise 3.7.3c
Let W be the set of attributes not in X, Y, or Z, V be the set of attributes that Y and Z have in
common, Y1 be the set of attributes of Y not in V and Z1 be the set of attributes of Z not in V.
Exercise 3.7.3d
Let W be the set of attributes not in X or Y, V be the set of attributes that X and Y have in
Exercise 3.7.4a
If we want to perform the chase test for A→→B, then an example of an initial tableau is:
A
B
C
D
a
b
c1
d1
a
b2
c
d
A
B
C
D
a
b
c1
d1
a
b2
c
d
a
b2
c
d1
a
b
c1
d
Exercise 3.7.4b
If we want to perform the chase test for A→B, then an example of an initial tableau is:
A
B
C
D
a
b1
c1
d1
a
b2
c2
d2
A
B
C
D
Exercise 3.7.4c
If we want to perform the chase test for A→→C, then an example of an initial tableau is:
A
B
C
D
a
b1
c
d1
a
b
c2
d
A
B
C
D
a
b1
c
d1
a
b
c2
d
a
b1
c1
d1
a
b2
c2
d2
a
b2
c1
d1
a
b1
c2
d2