This preview shows pages 1–3. Sign up to view the full content.
CHAPTER 11: RELATIONAL DATABASE DESIGN ALGORITHMS AND FURTHER
DEPENDENCIES
Answers to Selected Exercises
11.15 Show that the relation schemas produced by Algorithm 11.2 are in 3NF.
For completeness we repeat here
Algorithm 11.2: Relational Synthesis into 3NF with
Dependency Preservation.
Input: A universal relation R and a set of functional dependencies F on the attributes of R
1.
Find the minimal cover G of F
2.
For each lefthand side X take all the A
1
,..., A
n
such that X
→
A
1
, .
.., X
→
A
n
. Take the
attributes of X and A
1
,..., A
n
together into one relation X
∪
A
1
∪
...
∪
A
n
, with X as
key.
3.
Place all remaining attributes (that do not occur in any FD) together in a single
relation.
Answer:
By construction for each R
i
that is constructed in step 2, there is an FD X
→
{ A
1
,..., A
n
}
(actually, a set of FD’s X
→
A
1
,..., X
→
A
n
), there are no other FD’s for this R
i.
Hence, the FD X
→
{ A
1
,..., A
n
} is certainly not a partial FD, because X is the key by
construction. Furthermore, it is not transitive either, which would require that X has no
attributes in common with the key.
Hence, the only one FD of each R
i
is not partial and not transitive, and thus R
i
is in 3NF, and
thus the complete schema is in 3NF.
This material has been taken from draft PrePublication Material, Copyright AWL2004
1
This preview has intentionally blurred sections. Sign up to view the full version.
View Full Document 11.16 Show that if the matrix S resulting from Algorithm 11.1 does not have a row that
is all "a" symbols, then projecting S on the decomposition and joining it back will
always produce at least one spurious tuple.
Answer:
The matrix S initially has one row for each relation R
i
in the decomposition, with "a" symbols
under the columns for the attributes in R
i
. Since we never change an "a" symbol into a "b"
symbol during the application of the algorithm, projecting S on each R
i
at the end of applying
the algorithm will produce one row consisting of all "a" symbols in each S(R
i
). Joining these
back together again will produce at least one row of all "a" symbols (resulting from joining the
all "a" rows in each projection S(R
i
)).
Hence, if after applying the algorithm, S does not have a row that is all "a", projecting S over
the R
i
's and joining will result in at least one all "a" row, which will be a spurious tuple (since
it did not exist in S but will exist after projecting and joining over the R
i
's).
If this line of reasoning is not immediate clear, take R(A,B,C) without any FD’s and its
decomposition R
1
(A,B) and R
2
(B,C). Then the initial matrix S will be
A
B
C
R
1
a
1
a
2
b
12
R
2
b
21
a
2
a
3
The algorithm will stop immediate, as there are no FD’s at all.
Let us take an extension of R (a concrete set of tuples), that corresponds with the initial
This is the end of the preview. Sign up
to
access the rest of the document.
This note was uploaded on 09/07/2011 for the course MIS unknown taught by Professor Unknown during the Fall '09 term at New York Institute of TechnologyWestbury.
 Fall '09
 Unknown
 Algorithms, Relational Database

Click to edit the document details