This preview shows pages 1–22. Sign up to view the full content.
This preview has intentionally blurred sections. Sign up to view the full version.
View Full DocumentThis preview has intentionally blurred sections. Sign up to view the full version.
View Full DocumentThis preview has intentionally blurred sections. Sign up to view the full version.
View Full DocumentThis preview has intentionally blurred sections. Sign up to view the full version.
View Full DocumentThis preview has intentionally blurred sections. Sign up to view the full version.
View Full DocumentThis preview has intentionally blurred sections. Sign up to view the full version.
View Full DocumentThis preview has intentionally blurred sections. Sign up to view the full version.
View Full DocumentThis preview has intentionally blurred sections. Sign up to view the full version.
View Full DocumentThis preview has intentionally blurred sections. Sign up to view the full version.
View Full DocumentThis preview has intentionally blurred sections. Sign up to view the full version.
View Full DocumentThis preview has intentionally blurred sections. Sign up to view the full version.
View Full Document
Unformatted text preview: 224 DESIGN THEORY FOR RELATIONAL DATABASES 3. For no X—vA in F and proper subset Z ofX is F — {X—vA} U {Z—>A}
equivalent to F.
Intuitively, (2) guarantees that no dependency in F is redundant, and (3)
guarantees that no attribute on any left side is redundant. As each right side
has only one attribute by (1), surely no attribute on the right is redundant. Theorem 7.3: Every set of dependencies F is equivalent to a set F’ that is
minimal. Proof: By Lemma 7.4, assume no right side in F has more than one attribute.
To satisfy condition (2), consider each dependency X —>Y in F, in some order,
and if F — {X —>Y} is equivalent to F, then delete X —>Y from F. Note that
considering dependencies in different orders may result in the elimination of
different sets of dependencies. For example, given the set F: A—rB A—>C
B—rA C—>A
B—>C we can eliminate both B—>A and A—>C, or we can eliminate B—>C, but we
cannot eliminate all three. Having satisﬁed (2), we proceed to satisfy (3) by considering each depen
dency remaining in F, and each attribute in its left side, in some order. If we
can eliminate an attribute from a left side and still have an equivalent set of
attributes, we do so, until no more attributes can be eliminated from any left
side. Again, the order in which attributes are eliminated may affect the result.
For example, given AB—>C
A—>B
B—>A we can eliminate either A or B from AB—rC, but we cannot eliminate them
both. E Example 7.6: Let us consider the dependency set F of Example 7.5. If we use
the algorithm of Lemma 7.4 to split right sides we are left with: AB—rC BE—>C
C—>A CG—rB
BC—>D CG—rD
ACD—rB CE—rA
D—>E CE—>G
D—rG Clearly CE—rA is redundant, since it is implied by C—>A. CG—rB is redundant, since CG—rD, C—>A, and ACD—rB imply CG—rB, as can be checked by
computing (CG)+. Then no more dependencies are redundant. However. U) (U (Pt.50) (D 7.2 FUNCTIONAL DEPENDENCIES 225 AB—>C AB—>C _ C—>A C—>A
BC—>D BC—>D
CD—>B D—>E D—>E D—>G D—>G BE—>C
BE—>C CG—>B
CG—>D CE—>G
CE—>G (a) (b) Fig. 7.2. Two minimal covers. ACD—>B can be replaced by CD—>B, since C—>A is given. Thus one minimal
cover for F is shown in Fig. 7.2(a). Another minimal cover, constructed from F
by eliminating CE—>A, CG—>D, and ACD—>B, is shown in Fig. 7.2(b). Note
that the two minimal covers have different numbers of dependencies. CI 7.3 DECOMPOSITION OF RELATION SCHEMES The decomposition of a relation scheme R = {A1,A2, . . . ,A,” } is its replace
ment by a collection p = {R1, R2, . . . ,Rk } of subsets of R such that R=R1UR2U"‘URk There is no requirement that the Rfs be disjoint. One of the motivations
for performing a decomposition is that it may eliminate some of the problems
mentioned in Section 7.1. In general, it is the responsibility of the person
designing a database (the “database administrator”) to decompose an initial
set of relation schemes when warranted. Example 7.7: Let us reconsider the SUPPLIERS relation scheme introduced
in Example 3.1, but as a shorthand, let the attributes be S (SNAlVIE), A
(SADDRESS), I (ITEM), and P (PRICE). The functional dependencies we shall
assume are S—>A and SI —>P. We mentioned in Section 7.1 that replacement
of the relation scheme SAI P by the two schemes SA and SIP makes certain
problems go away. For example, in SAI P we cannot store the address of a
supplier unless the supplier provides at least one item. In SA, there does not
have to be an item supplied to record an address for the supplier. : One might question whether all is as rosey as it looks, when we replace
SAI P by SA and SIP in Example 7.7. For example, suppose we have a relation
1' as the current value of SAIP. If the database uses SA and SIP instead of
SAI P, we would naturally expect the current relation for these two relation
schemes to be the projection of 1' onto SA and SIP, that is TSA : 7rSA(r) 226 DESIGN THEORY FOR RELATIONAL DATABASES and T5“: = 7rs1p(T). How do we know that TSA and TS“: contain the same
information as T? One way to tell is to check that T can be computed knowing
only TSA and T5113. We claim that the only way to recover T is by taking the
natural join of TSA and Tsjpfl' The reason is that, as we shall prove in the next
lemma, if we let 5 = TSA f><ITsjp, then TrSA(s) : TSA, and 7r51p(.s) : TSIP. If
s # T, then given TSA and 7‘5 11: there is no way to tell whether T or s was the
original relation for scheme SAIP. That is, if the natural join doesn’t recover
the original relation, then there is no way whatsoever to recover it uniquely. Lossless Joins If R is a relation scheme decomposed into schemes R1,R2, . . . ,Rk, and D is a
set of dependencies, we say the decomposition is a lossless join decomposition
(with respect to D) if for every relation T for R satisfying D: T = WR1(T) f><I WR2(T) f><I '   f><17rRk(T) that is, T is the natural join of its projections onto the &’s. From our remarks
above, it is apparent that the lossless join property is a desirable condition for a
decomposition to satisfy, so we shall study the subject of lossless joins in some
detail. Some basic facts about projectjoin mappings follow in Lemma 7.5. First
we introduce some notation. If p = (R1,R2, . . . ,Rk), then Tnp is the mapping
deﬁned by mp(T) = I><I'c 17031.0). That is, mp(T) is the join of the projections
of T onto the relation schemes in p. Thus the lossless join condition with respect
to a set of dependencies D can be expressed as: for all T satisfying D, T = mp(T),
As another useful notational convention, if t is a tuple, we deﬁne t[X], where
X is a set of attributes, to be the components of t for the attributes of X .i
For example, we could express 7rX(T) as I t is in T Lemma 7.5: Let R be a relation scheme, p = (R1, . . . , R1,) a decomposition of
R, T a relation for R, and Ti 2 TR, Then
a) T g TWO“) b) If s '2 mp(T), then 7031(5) = T,.
0) mp(mp(")) .: mp0“)
Proof: a) Let t be in T. Then for each i, t, = is in T1“. By deﬁnition of the
natural join, if is in mp(T), since if agrees with t, on the attributes of R,; for all i.
b) As T g s by (a), it follows that 7rRz.(T) g 7031(5). That is, T,; g 7032(5). To
1' Recall Section 5.2 for a deﬁnition of the natural join. I Recall that t is a mapping from attributes to values, so t[X] is that mapping restricted to
domain X. In practice, we always pick some ordering for the attributes and show tuples, or
restricted tuples such as t[X], as lists of values. ne
09; xt If I he 81‘ DH ks 1e st 18
ns
ct re of 18
31‘ to
or 7.3 DECOMPOSITION OF RELATION SCHENIES 227 show TrR,(s) g Ti, suppose for some particular 2' that t, is in WRILS). Then
there is some tuple t in s such that t[Ri] 2 ti. As t is in 3, there is some
uj in rj for each j such that t[Rj] = Uj. Thus, in particular, t[R,'] is in n.
But t[R,] : ti, so t, is in Ti, and therefore waits) g Ti. We conclude that
Ti = W313). c) If s 2 mph), then by (b), TrR,(s) : n. Thus mp(s) = I><I§=1ri = mph).
Cl Let us observe that if for each 2', Ti is some relation for 13,, and
s = I><I§=1n then 7rRi(s) is not necessarily equal to n. The reason is that r, may contain
“dangling” tuples that do not match with anything when we take the join. For
example, if R1 2 AB, R2 : BC, r1 : {albl }, and r2 : {b161,b262 }, then
s =_ {alblcl} and TFBC(S) = {blcl} 7é r2. However, in general, 7rRi(s) g n,
and if the us are each the projection of some one relation r, then 7rRi(s) : n. The ability to store “dangling” tuples is an advantage of decomposition.
As we mentioned previously, this advantage must be balanced against the
need to compute more joins when we answer queries, if relation schemes are
decomposed, than if they are not. When all things are considered, it is generally
believed that decomposition is desirable when necessary to cure the problems,
such as redundancy, described in Section 7.1, but not otherwise. Testing Lossless Joins It turns out to be fairly easy to tell whether a decomposition has a lossless join
with respect to a set of functional dependencies. Algorithm 7.2: Testing for a Lossless Join.
Input: A relation scheme R = A1.  An, a set of functional dependencies F,
and a decomposition p = (R1, . . . ,Rk).
Output: A decision whether p is a decomposition with a lossless join.
Method: We construct a table with n columns and k rows; column j corresponds
to attribute Aj, and row 2' corresponds to relation scheme Bi. In row i and
column 3' put the symbol aj if AJ is in Bi. If not, put the symbol bij there.
Repeatedly “consider” each of the dependencies X —>Y in F, until no more
changes can be made to the table. Each time we “consider” ,X—>Y, we look
for rows that agree in all the columns for the attributes of X. If we ﬁnd two
such rows, equate the symbols of those rows for the attributes of Y. When we
equate two symbols, if one of them is aj, make the other be aj. If they are bi]
and bgj, make them both bij or bgj, arbitrarily.
If after modifying the rows of the table as above, we discover that some
row has become (11  «1k, then the join is lossless. If not, the join is lossy (not
lossless). El 228 DESIGN THEORY FOR RELATIONAL DATABASES Example 7.8: Let us consider the decomposition of SAI P into SA and SIP as
in Example 7.7. The dependencies are S—>A and SI—>P, and the initial table 18 Since S —+A, and the two rows agree on S, we may equate their symbols for A,
making (322 become (12. The resulting table is Since one row has all a’s, the join is lossless. For a more complicated example, let R = ABC’DE, R1 = AD, R2 2 AB,
R3 : BE, R4 : ODE, and R5 2 AE. Let the functional dependencies be: A—>C’ DE—>C’
B—>C’ CE—>A
C—>D The initial table is shown in Fig. 7.3(a). We can apply A—>C’ to equate b13,
(J23, and b53. Then we use B—rC to equate these symbols with b33; the result is
shown in Fig. 7.3(b), where b13 has been chosen as the representative symbol.
Now use C—>D to equate a4, b24, b34, and b54; the resulting symbol is a4. Then
DE—>C’ enables us to equate b13 with a3, and C’E—>A lets us equate b31, b41,
and al. The result is shown in Fig. 7.3(c). Since the middle row is all a’s, the
decomposition has a lossless join. 3 It is interesting to note that one might assume Algorithm 7.2 could be
simpliﬁed by only equating symbols if one was an a. The above example shows
this is not the case; if we do not begin by equating b13, b23, b33, and b53, we
can never get a row of all a’s. Theorem 7.4: Algorithm 7.2 correctly determines if a decomposition has a
lossless join. Proof: Suppose the ﬁnal table produced by Algorithm 7.2 does not have a row
of all a’s. We may view this table as a relation 7' for scheme R; the rows are
tuples, and the aj’s and bij’s are distinct symbols chosen from the domain of Aj.
Relation 7' satisﬁes the dependencies F, since Algorithm 7.2 modiﬁes the table
whenever a violation of the dependencies is found. We claim that r 75 mp(r).
Clearly r does not contain the tuple a1a2.  an. But for each R1, there is a tuple
t, in 7', namely the tuple that is row i, such that ti[Rz~] consists of all a’s. Thus
the join of the «Ri(r)’s contains the tuple with all a’s, since that tuple agrees
with ti for all i. We conclude that if the ﬁnal table from Algorithm 7.2 does U4 \U Lu 7.3 DECOMPOSITION OF RELATION SCHEMES 229 A B C D E
a 1 b1 2 b1 3 a4 bl 5
a1 (12 (>23 (>24 (>25
b3 1 (12 (>33 534 (15
b4 1 (>42 (13 a4 a5
(11 552 (>53 554 (15
(a)
A B C D E
(11 b1 2 b1 3 (14 b1 5
a1 (12 (>13 524 525
b3 1 (12 b1 3 b34 (15
b4 1 (>42 (13 a4 a5
a1 552 (’13 (’54 (15
(b)
A B C D E
(11 b1 2 a3 (14 b1 5
a1 a2 (13 a4 1’25
(11 (12 a3 a4 a5
(11 (L12 03 a4 (15
1 (11 b52 a3 a4 (15 Fig. 7 .3. Applying Algorithm 7.2. not have a row with all a’s, then the decomposition p does not have a lossless
join; we have found a relation 7' for R such that mp('r) # 7'. Conversely, suppose the ﬁnaltable has a row with all a’s. We can in general
view the table as shorthand for the domain relational calculus expression {0102  ‘an I (3b11)'(3bkn)(R(W1) /\  /\ R(wk))} (71) where w is the ith row of the initial table. Formula (7.1) deﬁnes the function
mp, since mp('r) contains an arbitrary tuple (11  an if and only if for each 2',
7' contains a tuple with (1’s in the attributes of R; and arbitrary values in the
other attributes. Since 'we assume that any relation 7' for scheme R, to which (7.1) could
be applied, satisﬁes the dependencies F, we can infer that (7.1) is equivalent
to a set of similar formulas with some of the a’s and/or b’s identiﬁed. The
modiﬁcations made to the table by Algorithm 7.2 are such that the table is 230 DESIGN THEORY FOR RELATIONAL DATABASES row for R1 . aa  
row for R2 (10,  ‘a, Fig. 7.4. A general two row table. always shorthand for some formula whose value on relation 1‘ is mp(r) whenever
1‘ satisﬁes F, as can be proved by an easy induction on the number of symbols
identiﬁed. Since the ﬁnal table contains a row with all a’s, the domain calculus
expression for the ﬁnal table is of the form. {a1.anR(a1an)A~} I (72) Clearly the value of (7.2) applied to relation r for R, is a subset of r.
However, if 1‘ satisﬁes F, then the value of (7.2) is mp(r), and by Lemma 7.5(a),
r g mp(r). Thus whenever 1‘ satisﬁes F, (7.2) computes r, so 1‘ = mp(r). That
is to say, the decomposition p has a lossless join with respect to F. El Algorithm 7.2 can be applied to decompositions into any number of relation
schemes. However, for decompositions into two schemes we can give a simpler test, the subject of the next theorem. Theorem 7.5: If p = (R1,R2) is a decomposition of R, and F is a set of
functional dependencies, then p has a lossless join with respect to F if and only
if (R1 (7 R2)—>(R1 —R2) or (R1 (7 R2)—>(R2 —R1). Note that these dependencies
need not be in the given set F; it is sufﬁcient that they be in F +. Proof: The initial table used in an application of Algorithm 7.2 is shown in
Fig. 7.4, although we have omitted the subscripts on a and b, which are easily
determined and immaterial anyway. It is easy to show by induction on the
number of symbols identiﬁed by Algorithm 7.2 that if the b in the column for
attribute A is changed to an a, then A is in (R1 {‘1 R2)+. It is also easy to
show by induction on the number of steps needed to prove (R1 {‘1 R2)—>Y by
Armstrong’s axioms, that any b’s in the columns for attributes in Y are changed
to a’s. Thus the row for R1 becomes all a’s if and only if R2 —R1 g (R1 {‘1 R2)+, that is (R1 ['1 R2)—>(R2 — R1), and similarly, the row for R2 becomes all a’s if
and only if (R1 {‘1 R2)—>(R1 — R2). El Example 7.9: Suppose R = ABC and F = {A—rB Then the decomposition
of R into AB and AC hasa lossless join, since AB ['1 AC 2 A, AB—AC = B,T
and A—>B holds. However. if we decompose R into R1 2 AB and R2 2 BC, we
discover that R1 {‘1 R2 = B, and B functionally determines neither R1 — R2 =
A nor R2 ~— R1 = C. Thus the decomposition AB and BC does not have a lossless join with respect to F = {A—rB }, as can be seen by considering the 1' To make sense of equations like these do not forget that A1A2  An stands for the set of
attributes {A1,A2, . . . ,An 7.3 DECOMPOSITION OF RELATION SCHEMES 231 relation 1‘ = {a1b1c1, a2b102} for R. Then 7rAB(r) = {a1b1, 02b1}, 7rBC(r) =
{b1c1, b162 }, and 7rAB(r)I><I7rBC(r)={a1b1c1,a1b102,a2b1c1, a2b1c2 El Decompositions that Preserve Dependencies We have seen that it is desirable for a decomposition to have the lossless join
property, because it guarantees that any relation can be recovered from its
projections. Another important property of a decomposition of relation scheme
R into p —= (R1, . . . ,Rk) is that the set of dependencies F for R be implied by
the projection of F onto the Ri’s. Formally, the projection of F onto a set of
attributes Z, denoted 7rz (F), is the set of dependencies X —>Y in F + such that
XY Q Z. (Note that X—rY need not be in F; it need only be in F+.) We
say decomposition p preserves a set of dependencies F if the union of all the
dependencies in 7rR, (F), for 'i = 1, 2, . . . ,lc logically implies all the dependencies
in F. The reason it is desirable that p preserve F is that the dependencies in
F can be viewed as integrity constraints for the relation R. If the projected
dependencies do not imply F, then should we represent R by p = (R1, . . . , Rk),
we could ﬁnd that the current value of the Ri’s represented a relation R that
did not satisfy F, even .if p had the lossless join property with respect to F.
Alternatively, every updatewtomoneﬂof the Ri’s would require a join to check that the constraints were not violate . , Example 7.10: Let us reconsider the problem of Example 7.3, where we had
attributes CITY, ST, and ZIP, which we here abbreviate C, S, and Z. We ob—
served the dependencies CS—rZ and Z —rC . The decomposition of the relation scheme CS Z into 8 Z and CZ has a lossless join, since
(SZ {7 CZ)—>(CZ — SZ) However, the projection of F = {CS—>2, Z —>C } onto SZ gives only the trivial
dependencies that follow from reﬂexivity, while the projection onto CZ gives
Z —>C and the trivial dependencies. It can be checked that Z —>C and trivial
dependencies do not imply CS—rZ, so the decomposition does not preserve
dependencies. For example, the join of the two relations in Fig. 7.5(a) and (b) is the rela
tion of Fig. 7.5(c). Figure 7.5(a) satisﬁes the trivial dependencies, as any rela
tion must. Figure 7.5(b) satisﬁes the trivial dependencies and the dependency
Z—rC. However, their join in Fig. 7.5(c) violates CS—>Z. El We should note that a decomposition may have a lossless join with respect
to set of dependencies F, yet not preserve F. Example 7.10 gave one such instance. Also, the decomposition could preserve F yet not have a lossless join.
For example, let F = {A—rB, C—rD }, R = ABCD, and p 2 (AB, CD). 232 DESIGN THEORY FOR RELATIONAL DATABASES 545 Tech Sq. Cambridge, Mass.
545 Tech Sq. 02139 Cambridge, Mass. 02139 (a) (b) 545 Tech Sq.
545 Tech Sq. (c) Fig. 7.5. A join violating a functional dependency. Cambridge, Mass.
Cambridge, Mass. Testing Preservation of Dependencies In principle, it is easy to test whether a decomposition p = (R1,...,Rk)
preserves a set of dependencies F. Just compute F + and project it onto all
the Ri’s. Take the union of the resulting sets of dependencies, and test whether this set covers F. However, in practice, just computing F + is a formidable task, since the
number of dependencies it contains will be exponential in the size of F. There—
fore, it is fortunate that there is a way to test preservation without actually
computing F +; this method takes time that is polynomial in the size of F. Algorithm 7.3: Testing Preservation of Dependencies. Input: A decompostion p 2 (R1, . . . , RIC) and a set of functional dependencies
F. Output: A decision whether p preserves F. Method: Define ‘G to be Ui=1 «RAF Note that we do not compute G; we
merely wish to see whether it covers F. To test whether G covers F, we must
consider each X —>Y in F and determine whether X +, computed with respect
to G, contains Y. The trick we use to compute X + without having G available
is to consider repeatedly what the effect is of closing X with respect to the
projections of F onto the various Ri’s. That is, deﬁne an Roperation on set of attributes Z with respect to a
set of dependencies F to be the replacement of Z by Z U ((Z n R)+ n R),
the closure being taken with respect to F. This operation adjoins to Z those
attributes A such that (Z n R)—>A is in 7rR(F Then we compute X + with
respect to G by starting with X, and repeatedly running through the list of
Ri’s, performing the Rioperation for each i in turn. If at some pass, none of
the Rioperations make any change in the current set of attributes, then we are
done; the resulting set is X +. More formally, the algorithm is: (DGErErG' 7.3 DECOMPOSITION OF RELATION SCHEMES 233 Z = X
while changes to Z occur do
for i = 1 to k do ZZZUUZDRNDRL) If Y is a subset of the Z that results from executing the above steps, then X —>Y
is in G. If each X —>Y in F is thus found to be in G, answer “yes,” otherwise
answer “no.” El Example 7.11: Consider set of attributes ABCD with decomposition
{ AB, BC, CD } and set of dependencies F = {A—>B, B—>C, C—>D, D—>A}. That is, in F+,
each attribute functionally determines all the others. We might ﬁrst imagine
that when we project F onto AB, BC, and CD, we fail to get the dependency
D—>A, but that intuition is wrong. When we project F, we really project F+
onto the relation schemes, so projecting onto AB we get not only A—>B, but also
B—>A. Similarly, we get C—>B in NBC(F) and D—>C in «CD(F), and these three
dependencies logically imply D—>A. Thus, we should expect that Algorithm 7.3
will tell us that D—>A follows logically from G : 7IAB(F) U W300?) U WCDU?) We start with Z = Applying the ABoperation does not help,
since {D} U ﬂ {A,B})+ ﬂ {A,B}) is just Similarly, the BC—
operation does not change Z. However, when we apply the CDoperation we
get {D} U(({D}ﬂ{0,D})+ “{C,D})
={D}U({D}+ﬂ{C,D})
{D}U({A,B,C,D}H{C,D}) Similarly, on the next pass, the BC—operation applied to the current Z :
{C,D} produces Z = {B,C,D}, and on the third pass, the ABoperation
sets Z to {A, B, C,D }, whereupon no more changes to Z are possible. Thus, with respect to G, {D }+ = {A, B, C,D }, which contains A, so we
conclude that G I: D—>A. Since it is easy to check that the other members
of F are in G+ (in fact they are in G), we conclude that this decomposition
preserves the set of dependencies F. El Theorem 7.6: Algorithm 7.3 correctly determines if X —>Y is in G+. Proof: Each time we add an attribute to Z, we are using a dependency in G, so
when the algorithm says ﬁres,” it must be correct. Conversely, suppose X —>Y
is in G+. Then there is a sequence of steps whereby, using Algorithm 7.1 to
take the closure of X with respect to G, we eventually include all the attributes 234 DESIGN THEORY FOR RELATIONAL DATABASES of Y. Each of these steps involves the application of a dependency in G, and
therefore it is a dependency in 7rRi(F) for some i. Let one such dependency be
U —>V. An easy induction on the number of dependencies applied in Algorithm
7.1 shows that eventually U becomes a subset of Z, and then on the next pass
the Ri—operation will surely cause all attributes of V to be added to Z if they are not already there. [:1 7.4 NORMAL FORMS FOR RELATION SCHEMES A number of different properties, or “normal forms” for relation schemes with
dependencies have been deﬁned. The most signiﬁcant of these are called “third
normal form”]L and “BoyceCodd normal form.” These normal forms guarantee
that most of the problems of redundancy and anomalies discussed in Section 7.1 do not occur. BoyceCodd Normal Form The strongest of these normal forms is called Boyce—Codd. A relation scheme
R with dependencies F is said to be in Boyce—Codd normal form if whenever
X —>A holds in R, and A is not in X, then X is a superkey for R; that is, X is
or contains a key. Put another way, the only nontrivial dependencies are those
in which a key functionally determines one or more other attributes. Example 7.12: The relation scheme CS Z of Example 7.10, with dependencies
CS—>Z and Z —>C, is not in Boyce—Codd normal form (although we shall see it
is in third normal form). The reason is that Z —>C holds (in fact it is a given I dependency), but Z is not a key of CS Z, nor does it contain a key. ' The MEMBERS and ORDERS relations of Example 3.1 are in Boyce—Codd
normal form, since their keys, NAME and ORDER_NO respectively, are the
left sides of the only dependencies that were given for their respective relations.
We shall see that the SUPPLIERS relation is neither in Boyce—Codd normal form nor third normal form. El Third Normal Form _ It turns out that in some circumstances, BoyceCodd normal form is‘ too strong a condition, in the sense that it is not possible to bring a relation scheme into
that form by_decomposition without losing the ability to preserve dependencies.
Thus third normal form has seen use as a condition that has almost the beneﬁts
of Boyce—Codd normal form, as far as elimination of” anomalies is conCerned,
yet that we can achieve for an arbitrary database scheme without giving up
either dependency preservation or the lossless join property. I _ Before deﬁning thirdnormal form, we need a preliminary deﬁnition. Call T Yes Virginia, there is a ﬁrst normal form and a second normal form. There’s even a fourth
normal form. All in good time 7.4 NORMAL FORMS FOR RELATION SCHEMES 235  .
an attribute A in relation scheme R a prime attribute if A is a member of any
key for R (recall there may be many keys). If A is not a member of any key, the .A is nonprime. k/EX/ainple 7.13: In the relation scheme CSZ of Example 7.10, all attributes are
prime, since given the dependencies CS —>Z and Z —>C’, both CS and 8 Z are
keys. In the relation scheme ABCD with dependencies AB—>C, B—>D, and
BC—gA we can check that AB and BC are the only keys, so A, B, and C
are prime; D is nonprime. El A relation scheme R is in third normal form if whenever X —+A holds in R
and A is not in X, then either X is a superkey for R, or A is prime. Notice
that the deﬁnitions of BoyceCodd and third normal forms are identical except
for the clause “or A is prime” that makes third normal form a weaker condition
than BoyceCodd normal form. If X —>A violates third normal form, then one of two cases can occur. Either I. X is a proper subset of a key, or
2. X is a proper subset of no key.
In the ﬁrst case, we say that X —>A is a partial dependency, and in the second
case we call it a transitive dependency. The term “transitive” comes from the '
fact that if Y is a key, then Y—)X—>A is a nontrivial chain of dependencies. It
is nontrivial because we know that X is not a subset of Y, by (2), A is given
not to be in X, and A cannot be in Y because A is nonprime. If R has no
partial dependencies, although it may have transitive dependencies, we say R
is igsecond normal 1"orm.]L \/E)€ample 7.14: The relation scheme 8 AI P from Example 7.7, with dependencies
' S I aP and SaA violates third normal form, and in fact violates second normal
form. A is a nonprime attribute, since the only key is SI. Then S——>A
violates the third normal form condition, since 8 is not a superkey. Note
that in this case, the violating dependency, S —’A, not only holds, it is even
a given dependency. In general, however, it is sufﬁcient that the violating
dependency follow from the given set of dependencies, even if it is not itself a
given dependency. As another example, the relation scheme CSZ from Example 7.12 is in
third normal form. Since all of its attributes are prime, the conditions for third
normal form hold vacuously. __ For an example of a relation scheme in second normal form but not third,
consider the attributes 8 (Store) I (Item) D (Department number), and M T O.K., we might as well mention “ﬁrst normal form.” That form simply requires that the domain of each attribute consists of indivisible values, not sets or tuples of values from a
more elementary domain or domains. We have not considered setvalued domains and so feel
free to ignore ﬁrst normal form. In effect, “relation” is for us synonymous with “ﬁrst normal
form relation” in some works appearing in the literature. 236 I DESIGN THEORY FOR RELATIONAL DATABASES (Manager). The functional dependencies we assume are SI —*D (each item in
each store is sold by at most one department) and SD—rM (each department
in each store has one manager). The only key is SI. Then SD—’M violates
third normal form, since SD is not a superset of a key. Note the application of
transitivity implied by the chain S I —*S D—’M . However, there are no partial
dependencies, since no proper subset of the key SI functionally determines D
or M. El Motivation Behind Normal Forms We may suppose that the functional dependencies ,X—rY not only represent
an integrity constraint on relations, but also represent a relationship that the
database is intended to store. That is, we regard it important to know, given
an assignment of values to the attributes in X, what value for each of the Y
attributes is associated with this assignment of X —values. If we have a partial
dependency Y—*A, where X is a key and Y a proper subset of X, then in every
tuple used to associate an X value with values for other attributes besides A
and those in X, the same association between Y and A must appear. This
situation is best seen in the running example of the SAI P scheme, where S —*A
is a partial dependency, and the supplier’s address must be repeated once for
each item supplied by the supplier. The third normal form condition eliminates
this possibility and the resultant redundancy and update anomalies. If we have a transitive dependency X —+Y—’A, then we cannot associate a
Y—value with an X —value unless there is an Avalue associated with the Y value.
This situation leads to insertion and deletion anomalies, where we cannot insert
an X to—Y association without a Y.—toA association, and if we delete the A
value associated with a given Yvalue, we may lose track of an X to—Y associa
tion. For example, in the relation scheme SIDM with dependencies SI—*D
and SD—*M, mentioned in Example 7.14, we cannot record the department
selling hats in Bloomingdales if that department has no manager. As we saw from Example 7.14, a relation scheme can be in third normal
form but not BoyceCodd normal form. However, every BoyceCodd normal
form relation scheme is in third normal form. The beneﬁts of BoyceCodd
normal form are the same as for third normal form—freedom from insertion
and deletion anomalies and redundancies. Note how BoyceCodd normal form
eliminates some anomalies not prevented by third normal form. For instance,
in the CS Z example, we cannot record the city to which a zip code belongs
unless we know a street address with that zip code. It is worth mentioning that relations intended to represent an entity set or
a manyone mapping between entity sets will be in BoyceCodd normal form
unless there are unexpected relationships among attributes. It is interesting
to conjecture that all functional dependencies that satisfy third normal form
but violate BoyceCodd normal form are in a sense irrelevant. That is, they 7.4 NORMAL FORMS FOR RELATION SCHEMES 237 tell us something about the structure of the real world that is of no use to the
database designer. For example, Z ——+0 in the above example tells us how cities
are broken into zip codes, but the information is not really useful, since the
apparent application of the CSZ database is not to relate zip codes to cities,
but to store zip codes for addresses. Lossless Join Decomposition into BoyceCodd Normal Form We have now been introduced to the properties we desire for relation schemes:
BoyceCodd normal form or, failing that, third normal form. In the last section
we saw the two most important properties of database schemes as a whole, the
lossless join and dependency preservation properties. Now we must attempt to
put these ideas together, that is, construct database schemes with the properties
we desire for database schemes, and with each individual relation scheme having
the properties we desire for relation schemes. It turns out that any relation scheme has a lossless join decomposition into
BoyceCodd Normal Form, and it has a decomposition into third normal form
that has a lossless join and is also dependency—preserving. However, there may
be no decomposition of a relation scheme into BoyceCodd normal form that is
dependencypreserving. The CS Z relation scheme is the canonical example. It
is not in BoyceCodd normal form because the dependency Z ——+C holds, yet if
we decompose CS Z in any way such that CS Z is not one of the schemes in the
decomposition, then the dependency CS ——+Z is not implied by the projected
dependencies. Before giving the decomposition algorithms, we shall state some
properties of natural joins that we shall need. Lemma 7.6: a) Suppose R is a relation scheme with functional dependencies F. Let p =
(R1, . . . ,Rk) be a decomposition of R with a lossless join with respect to
F. For a particular i, let F, = 7rR,(F), and let a = (81,...,Sm) be
a decomposition of R, whose join is lossless with respect to R. Then
the decomposition ofR into (R1, . . . ,R,~_1, SI, . .. , Sm,R,+1, . . . ,Rk) has a
lossless join with respect to F. b) Suppose R, F and p are as in (a), and let 7' = (R1, . . . ,Rk,Rk+1, ,Rn)
be a decomposition of R into a set of relation schemes that includes those
of p. Then 7' also has a lossless join with respect to F. Proof: Each of these statements follows by algebraic manipulation from the
deﬁnition of a lossless join decomposition. We shall leave formal proofs as
exercises and only give the intuition here. The reason (a) holds is that if we
take relation 7' for R and project it to relations rj for each Rj, and then project
7', to relations 5,, for each Sp, the lossless join property tells us we can join the
sp’s to recover n. Then we can join the 'rj’s to recover 7'. Since the natural join
is an associative operation (another exercise for the reader) the order in which 238 DESIGN THEORY FOR RELATIONAL DATABASES we perform the join doesn’t matter, so we recover 7' no matter in what order
we take the join of the rj’s, for i 7é j, and the sp’s. For part (b), we again appeal to the associativity of the natural join.
Observe that if we project relation r for R onto the Ri’s, i = 1, 2, . . . , n, then
when we take the join of the projections onto R1, . . . ,Rk we recover r. Since
R1, . . . ,Rk include all the attributes of R, further joins can only produce a
subset of what we already have, which is r. But by Lemma 7.5(a), r g mT(r),
so we cannot wind up with less than T. That is, mT(r) : r, and T is a lossless
join decomposition. [1 Algorithm 7.4: Lossless Join Decomposition into BoyceCodd Normal Form.
Input: Relation scheme R and functional dependencies F. Output: A decomposition of R with a lossless join, such that every relation
scheme in the decomposition is in Boyce—Codd normal form with respect to the
projection of F onto that scheme. Method: We iteratively construct a decomposition p for R. At all times, p will
have a lossless join with respect to F. Initially, p consists of R alone. If S is a
relation scheme in p, and S is not in Boyce—Codd normal form, let X —>A be a
dependency that holds in S, where X is not a superkey for S, and A is not in
X. Replace S in p by 81 and 82, where 81 consists of A and the attributes of
X, and 82 consists of all the attributes of S except for A. 82 is surely a proper
subset of S. 81 is also a proper subset, or else X = S — A, so X is a superkey
for S. By Theorem 7.5, the decomposition of S into 81 and 82 has a lossless join
with respect to the set of dependencies projected onto 8, since 81 D 82 = X,
81 — 82 = A, and therefore (81 ﬂ 82)—>(Sl — 82). By Lemma 7.6(a), p with 8
replaced by 81 and 82 has a lossless join, if p does. As 81 and 82 each have
fewer attributes then 8, and any relation scheme with two or fewer attributes
must be in BoyceCodd normal form, we eventually reach a point where each
relation scheme in p is in BoyceCodd normal form. At that time, p still has a
lossless join, since the initial p consisting of R alone does, and each modiﬁcation
of p preserves the lossless join property. [1 Example 7.15: Let us consider the relation scheme CTHRSG, where C =
course, T 2 teacher, H = hour, R = room, 8 = student, and G 2 grade.
The functional dependencies F we assume are C—>T each course has one teacher
H R—rC only one course can meet in a room at one time
H T—rR a teacher can be in only one room at one time
08—>G each student has one grade in each course
H 8 —>R a student can be in only one room at one time The only key for CTHRSG is H8.
To decompose this relation scheme into Boyce—Codd normal form, we might 7.4 NORMAL FORMS FOR RELATION SCHEMES 239 ﬁrst consider the dependency CS —>G, which violates the condition, since CS
does not contain a key. Thus, by Algorithm 7.4, we ﬁrst decompose CTH RS G
into C S G and CTH RS . For further decompositions we must compute F + and
project it onto CS G and CTH RS . Note that this process is in general time consuming, as the size of F +
can be exponential in the size of F. Even in this relatively simple example,
F +, naturally, has all the trivial dependencies that follow by reﬂexivity and,
in addition to those in F, some other nontrivial dependencies like CH —>R,
H S—>C, and H R—>T. Once we have F +, we select those involving only C,
S, and G. This is Wcsg(F This set has a minimal cover consisting of
CS —>G alone; all other dependencies in the set follow from this dependency
by Armstrong’s axioms. We also project F + onto CTH RS . 7rCTHRs(F) has a
minimal cover C —>T TH —>R
H R—>C H S —>R and the only key for CTHRS is HS. It is easy to check that CSG is in BoyceCodd normal form with respect
to its projected dependencies. CTH RS must be decomposed further, and we
might choose the dependency C—>T to break it into CT and CH RS . Minimal
covers for the projected dependencies are C—>T for CT and CH —>R, H S —>R,
and H R—rC for CH RS ; HS is the only key of the latter scheme. Observe that
CH —>R is needed in a cover of CH RS , although in CTH RS it followed from
C—>T and TH —>R. CT is in Boyce—Codd normal form, and one more decomposition of CH RS ,
say using CH —>R, puts the entire database scheme into the desired form. In
Fig. 7.6 we see the tree of decompositions, with the keys and minimal covers
for the sets of projected dependencies also shown. The ﬁnal decomposition of CTHRSG is CSG, ‘CT, CHR, and CHS.
This is not a bad database design, since its four relation schemes tabulate,
respectively, 1. grades for students in courses, 2 the teacher of each course, 3. the hours at which each course meets and the room for each hour, and
4 the schedule of courses and hours for each student. In fairness it should be noted that not every decomposition produces a
database scheme that matches so well our intuition about what information
should be tabulated in the database. For example, if at the last decomposition
step we had used dependency H R—>C instead of CH —>R, we would have scheme
HRS instead of CH S, and HRS represents the room in which a student can
be found at a given hour, rather than the class he is attending. Surely the latter
is more fundamental information than the former. 240 DESIGN THEORY FOR RELATIONAL DATABASES CTHRSG
key=HS
C—>T C’s—>0
HR—>C HS—>
TH—>R CHRS
key=SH
CH—>R HR—>C
HS—>R CHR
keys=CH, HR
CH—>R HR—>C Fig. 7.6. Tree of decomposition. Another problem with the decomposition of Fig. 7.6 is that the dependency
TH —>R is not preserved by the decomposition. That is, the projection of F
onto 05 G, CT, OH R, and CH S , which can be represented by the cover CS—>G HR——)C
C—>T HS——>C
CH——>R found by taking the minimal covers in each of the leaves of Fig. 7.6, does not
imply TH ——>R. For example, the relation for 0TH RS G shown below 0 T H R S G c1 t h 7'1 31 91
C2 t _ h 7'2 82 92 7.4 NORMAL FORMS FOR RELATION SCHEMES 241 does not satisfy TH —>R, yet its projections onto 080, CT, OH R, and OHS
satisfy all the projected dependencies. D We mentioned that the process of projecting dependencies, where we con
struct F + from F and then select out those with a particular set of attributes,
can be exponential in the number of dependencies in F. One might wonder
whether Algorithm 7.4 can be made to run in less than exponential time by using another approach to decomposition. Unfortunately, Beeri and Bernstein' ' [1979] proved that it is NPcomplete]L just to determine whether a relation
scheme is in BoyceCodd normal form. Thus it is extremely unlikely that one
will ﬁnd a substantially better algorithm. Dependency Preserving Decompositions into Third Normal Form We saw from Examples 7.12 and 7.14 that it is not always possible to decompose
a relation scheme into'BoyceCodd normal form and still preserve the depen
dencies. However, we can always ﬁnd a dependency—preserving decomposition
into third normal form, as the next algorithm and theorem show. Algorithm 7.5: DependencyPreserving Decomposition into Third Normal Form. Input: Relation scheme R and set of functional dependencies F, which we
assume without loss of generality to be—‘a minimal covergt? Output: A dependencypreserving decomposition of R such that each relation scheme is in third normal fgurrpwwith respect to the projection of F onto that scheme. Method: If there are any attributes of R not involved in any dependency of F,
either on the left or right, then that attribute can, in principle, form a relation
scheme by itself, and we shall eliminate it from R.I If one of the dependencies
in F involves all the attributes of R, then output R itself. Otherwise, the
decomposition p to be output consists of scheme X A for each dependency X —>A
in F. However, if X —>A1, X ——>A2, . . . ,X —>An are in F, we may use scheme
XAluAn instead of XAz for 1 S 2' S n, and in fact, this substitution is
usually preferable. D Example 7.16: Reconsider the relation scheme CTHRSG of Example 7.15,
whose dependencies have minimal cover T NPcompleteness of a problem almost certainly implies that it is inherently exponential.
See Aho, Hopcroft, and Ullman [1974] or Garey and Johnson [1979] for a description of the
theory. 1 Sometimes it is desirable to have two or more attributes, say A and B, appear together
in a relation scheme, even though there is no functional dependency involving them. There
may simply be a manymany relationship between A and B. An idea of Bernstein [1976] is to
introduce a dummy attribute 0 and functional dependency AB—bﬁ, to force this association.
After completing the design, attribute 0 is eliminated. 242 DESIGN THEORY FOR RELATIONAL DATABASES C—>T CS—>G
HR—>C HS—>R
HT—>R Algorithm 7.5 yields the set of relation schemes CT, OH R, H RT, 008, and
HRS. D Theorem 7.7: Algorithm 7.5 yields a dependencypreserving decomposition into
third normal form. Proof: Since the projected dependencies include a cover for F, the decomposi
tion clearly preserves dependencies. We must showthat the relation scheme
YB, for each functional dependency Y—>B in the minimal cover, is in third
normal form. Suppose X —>A violates third normal form for YB, that is, A is
not in X, X is not a superkey for YB, and A is nonprime. Of course, we also
know that X A g YB, and X —>A follows logically from F. We shall consider
two cases, depending on whether or not A = B.  Case 1: A 2 B. Then since A is not in X, we know X g Y, and since X is not
a superkey for YB, X must be a proper subset of Y. But then X —>B, which
is X —>A, could replace Y—>B in the supposed contradicting the
assumption that Y—>B was part of the given minimal cover. Case 2: A % B. Since Y is a superkey for YB, there must be some Z g Y
that is a key for YB. But A is in Y, since we are assuming A # B, and A
cannot be in Z, because A is nonprime. Thus Z is a proper subset of Y, yet
Z —>B can replace Y—>B in the supposedly minimal cover, again providing a
contradiction. El Decompositions into Third Normal Form with a Lossless Join and
Preservation of Dependencies We have seen that we can decompose any relation scheme R into a set of schemes
p = (R1, . . . ,Rk) such that p has a lossless join and each R, is in BoyceCodd
normal form (and therefore in third normal form). We can also decompose R
into a = ($1, . . . , Sm) such that a preserves the set of dependencies F, and each
Sj is in third normal form. Can we ﬁnd a decomposition into third normal form
that has both the lossless join and dependencypreservation properties? We can,
if we simply adjoin to a a relation scheme X that is a'key for R, as the next theorem shows. Theorem 7.8: Let a be the third normal form decomposig‘on of R constructed by
Algorithm 7.5, and let X be a key for R. Then 7" = a is a decomposition of R with all relation schemes in third norﬂlTﬁﬁwfhE’decomposition preserves
dependencies and has the lossless join property. Proof: It is easy to show that any transitiveor partial dependency in X implies. that a proper subset of X functionally determines X, and therefore R, so X by. 7.4 NORMAL FORMS FOR RELATION SCHEMES 243 would not be a key in that case. Thus X, as well as the members of a, are in
third normal form. Clearly T preserves dependencies, since a does. To show that T has a lossless join, apply the tabular test of Algorithm 7.2.
We\can show that the row for X becomes all a’s, as follows. Consider the order
A1,A2, . . . ,Ak in which the attributes of R —X are added to X + in Algorithm
7.1. Surely all attributes are added eventually, since X is a key. We show by
induction on i that the column corresponding to A, in the row for X is set to
a, in the test of Algorithm 7.2. The basis, 1' : 0, is trivial. Assume the result for i — 1. Then A, is added
to X + because of some given functional dependency Y—>A,, where YgXu{A1,...,A._1} Then YA, is in 0, and the rows for YA, and X agree on Y (they are all a’s) after
the columns of the X—row for A1, . . . ,Ai_1 are made a’s. Thus these rows are
made to agree on A, during the execution of Algorithm 7.2. Since the YAirow
has 0., there, so must the X —r0w. El Obviously, in some cases T is not the smallest set of relation schemes with
the properties of Theorem 7.8. We can throw out relation schemes in T one at
a time as long as the desired properties are preserved. Many different database
schemes may result, depending on the order in which we throw out schemes,
since eliminating one may preclude the elimination of others. Example 7.17: We could take the union of the database scheme produced for
CTH RS G in Example 7.16 with the key SH, to get a decomposition that has
a lossless join and preserves dependencies. It happens that SH is a subset of
HRS, which is one of the relation schemes already selected. Thus, SH may
be eliminated, and the database scheme of Example 7.16, that is CT, OH R,
H RT, CGS, and HRS, sufﬁces. Although some proper subsets of this set of
five relation schemes are lossless join decompositions, we can check that the
projected dependencies for any four of them do not imply the complete set of
dependencies F, last mentioned in Example 7.16. El 7.5 MULTIVALUED DEPENDENCIES In previous sections we have assumed that the only possible kind of data depen—
dency is functional. In fact there are many plausible kinds of dependencies, and
at least one other, the multivalued dependency, appears in the “real world.”
Suppose we are given a relation scheme R, and X and Y are subsets of R.
Intuitively, we say that X —>—>Y, read “X multidetermines Y,” or “there is a
multivalued dependency of Y on X,” if given values for the attributes of X
there is a set of zero or more associated values for the attributes of Y, and
this set of Y—values is not connected in any way to values of the attributes in
R — X — Y. 244 DESIGN THEORY FOR RELATIONAL DATABASES Klunk, A. '
Klunk, A.
Klunk, A. Deadwood, J.
Deadwood, J.
Deadwood, J. C8101
C8101 C8101 Deadwood, J. Zonker, B. C
C8101 Deadwood, J. Zonker, B. C
C8101 Deadwood, J. Zonker, B. C Fig. 7.7. A sample relation for scheme 0TH RS G. Formally, we say X —»—>Y holds in R if whenever 'r is a relation for R, and
t and s are two tuples in r, with t[X] : 3[X] (that is, t and 3 agree on the
attributes of X), then 7 also contains tuples u and v, where
1. : v[X] = t[X] = 8[X],
2. u[Y] = t[Y] and ulR ~—X — Y] = 8[R —X — Y], and
3. v[Y] : 8[Y] and v[R — X — Y] : t[R —X — Y].]L
That is, we can exchange the Y values of t and s to obtain two new tuples that
must also be in 7'. Note we did not assume that X and Y are disjoint in the
above deﬁnition. Example 7.18: Let us reconsider the relation scheme 0TH RS G of the previous
section. In Fig. 7.7 we see a possible relation for this relation scheme. In this
simple case there is only one course with two students, but we see several salient
facts that we would expect to hold in any relation for this relation scheme. A
course can meet for several hours, in different rooms each time. Each student
has a tuple for each class taken and each session of that class. His grade for the
class is repeated for each tuple. Thus we expect that in general the multivalued dependency C—HH R holds,
that is, there is a set of hour—room pairs associated with each course and
disassociated from the other attributes. For example, if in the formal deﬁnition
of a multivalued dependency we let t=CSlOl Deadwood, J. M9 222 Klunk, A. B+
s=C8101 Deadwood, J. W9 333 Zonker, B. C . then we would expect to be able to exchange (M9, 222) from t with (W9, 333)
in 3 to get the two tuples u = C8101 Deadwood, J.
v = C8101 Deadwood, J. Zonker, B. 4 C
Klu‘nk, A. B+ M9 222
W9 333 A glance at Fig. 7.7 afﬁrms that u and v are indeed in T.
It should be emphasized that C—HH R holds not because it held in the 1‘ Note we could have eliminated clause The existence of tuple 2) follows from the existence
of u when we apply the definition with t and s interchanged. \A An )8 1e it
1e 1s
is
1t 1t
1e 16 cc 7.5 MULTIVALUED DEPENDENCIES 245 one relation of Fig. 7.7. It holds because for any course 0, if it meets at hour
hl in room 71, with teacher t1 and student 31 who is getting grade g1, and it
also meets at hour hz in room 7'2 with teacher t2 and student 32 who is getting
grade g2, then we expect from our understanding of the attributes’ meanings
that the course c also meets at hour hl in room r1 With teacher t2 and student
32 with grade g2. Note also that C—HH does not hold, nor does C—HR. In proof, consider
relation r of Fig. 7.7 with tuples t and s as above. If C—HH held, we would
expect to ﬁnd tuple CSIOI Deadwood, J. M9 333 in T, which we do not. There are a number of other multivalued dependencies
that hold, however, such as C—HSG and H R—HSG. There are also trivial
multivalued dependencies like H R—HR. We shall in fact prove that every func
tional dependency X —>Y that holds implies that the multivalued dependency X —»Y holds as well. D Zonker, B. C Axioms for Functional and Multivalued Dependencies We shall now present a sound and complete set of axioms for making inferences
about a set of functional and multivalued dependencies over a set of attributes
U . The ﬁrst three are Armstrong’s axioms for functional dependencies only; we repeat them here. A1: (reﬂexivity for functional dependencies) If Y Q X Q U, then X —+Y. A2: (augmentation for functional dependencies) If X —+Y holds, and Z Q U,
then X Z ——>YZ . A3: (transitivity for functional dependencies) {X—ﬂﬂ Y——>Z } l: X —>Z .
The next three axioms apply to multivalued dependencies. A4: (complementation for multivalued dependencies) {X—HY}l=X—>—>(U—XY) A5: (augmentation for multivalued dependencies) If X —»Y holds, and V Q W,
then WX—HVY.
A6: (transitivity for multivalued dependencies) {X—HY, Y—HZ} =X—H(Z—Y) It is worthwhile comparing A4—A6 with A1—A3. Axiom A4, the com
plementation rule, has no counterpart for functional dependencies. Axiom A1,
reﬂexivity, appears to have no counterpart for multivalued dependencies, but
the fact that X —>—>Y whenever Y Q X follows from A1 and the rule (Axiom
A7, to be given) that if X —+Y then X —>—>Y. A6 is more restrictive than its
counterpart transitivity axiom, A3. The more general statement, that X —>—>Y
and Y—HZ imply X —>—>Z is false. For instance, we saw in Example 7.18 that ...
View
Full
Document
 Fall '08
 Staff

Click to edit the document details