CS430/630 FDs, Normalization Lab NAME: ______________
1. Given the relation T1:
a. Find all the FDs consistent with this relation instance that have one
attribute on the left hand side and one on the right hand side and are not
trivial (A->A is trivial, for example)
b. Find all FDs that have two attributes on the left hand side and one on
the right and are not trivial. For example, AB -> A is trivial, but
AB->C is not trivial even though it is implied by A->C (from part
2. Suppose a company has an employees relation as follows, showing the
department and parking lot number for each employee
a. Find the nontrivial FDs consistent with this relation instance.
b. Find the single-attribute key for this relation instance. Can you also
find a two-attribute key?
c. Explain the redundancy found in this table (what data is repeated).
d. Propose a decomposition that removes the redundancy.
e. Prove that your decomposition is lossless. Use the Lossless Decomposition
Theorem that says for losslessness, the attributes common to the two tables
(the join attributes) must be a superkey of one or other of the tables.
3. Given the FDs for a relation R:
(1) A -> B, (2) C-> A, (3) D -> AC
Here is an example attribute closure computation as a model
A+ = A (look for A in LHS's, add RHS: find A->B, add B)
A+ = AB by (1)
(No more FDs have A or B or both on left hand side, so done. --not necessary
to write this)
a. Compute C+
b. Find a single-attribute key for R (compute K+ to prove your key K)
c. Is R in BCNF? If not, determine the FDs (in the numbered list) that
violate BCNF, that is, FDs that are not of the form key -> non-key