Unformatted text preview: Multivalued Dependencies
In relation R, we say MVD X ! Y holds if
! whenever there are tuples s and t in R such that
X s = X t, then there is a tuple r in r such
1. XY r = XY s.
2. R,Y X r = R,Y X t.
I.e., r agrees with s on the attributes
mentioned, and with t on X and all the
attributes not mentioned. Example Consider CTHRSG. In addition to the FD's
C ! T ; HR ! C ; HT ! R; HS ! R; CS ! G;
CH ! R, we might reasonably expect the MVD
C ! HR.
That is, given a course, the hour-room pairs
are independent of the teacher-student-grade
3 There will be a unique teacher for the
course, but student-grade information
should appear in combination with each
hour-room pair, since there is no logical
reason to assign di erent grades for
di erent hours and rooms. Axiomatization of FD's + MVD's
A8: Start with Armstrong's Axioms. Then add:
Complementation. If X ! Y holds in R, then
X ! R , X , Y .
MVD augmentation. If X ! Y , then
XZ ! Y Z for any set of attributes Z .
MVD transitivity. If X ! Y and Y ! Z , then
X ! Z , Y .
Promotion. If X ! Y , then X ! Y .
If X ! Y and U ! V , where:
1. U is disjoint from Y , and
2. V Y ,
then X ! V .
3 Example: AB ! CDE and FG ! C
imply AB ! C .
1 Decomposition Rules
For FD's, we know that if X ! A1 An ,
then we can decompose" into
X ! A1 ; : : :; X ! An .
MVD's do not always allow right sides of size
However, X ! R , X always holds by A1
trivial FD's, A7, and A4. We can break
R , X into some disjoint partition, say X ! Y1 Y2 Yk
such that X ! Z i Z is a union of some of
the Yi's. Example For CTHRSG, we have C ! T HR SG
i.e., a course has a teacher only one because of the
FD C ! T , a set of hour-room pairs, and a set of
student-grade pairs. Generalized Dependencies Unify FD's, MVD's, lossless joins, and inferences
about dependencies. A generalized dependency
1. One or more hypothesis rows consisting of
abstract symbols, one for each attribute of the
relation in question.
2. A conclusion that is either another row or an
equality between two symbols.
3 If the conclusion is a row, then the GD
is a tuple-generating dependency TGD;
if an equality it is an equality-generating
We shall usually talk about typed
dependencies, which means that a symbol may
not appear in two di erent columns, and we
may not equate in the conclusion symbols
from di erent columns.
Usually, we talk about full dependencies,
meaning that the conclusion row of a TGD
uses only symbols that have appeared
2 But sometimes we like to have partial
dependencies, where the conclusion introduces
new symbols. Semantics of GD's If there is a mapping from the symbols of the
hypothesis rows that turns each hypothesis into
a tuple of R, then the conclusion must hold.
If it is an EGD, then R must be such that
the equated symbols are mapped to the same
If a full TGD, then the conclusion row, with
the same mapping applied, must also be a row
If a partial TGD, then there is a way to map
the new symbols in the conclusion so the
conclusion row becomes a tuple of R. Examples
In R = ABCD the FD AB ! C :
a1 b1 c1 d1
a1 b1 c2 d2
c1 = c2
The MVD A! BC :
a1 b1 C
d2 The join dependency JD . AB; BC; CD,
which says that AB , BC , and CD are a
lossless-join decomposition of ABCD:
a b c1 d1
a2 b c d2
a3 b3 c d
abcd The Chase Infers Full GD's
Given a set of full GD's G , does another GD G
follow? I.e., does every relation instance that
satis es G also satisfy G?
3 Start with a relation R consisting of the
hypothesis rows of G.
Repeatedly apply GD's H of G to the current
a If H is a TGD, map its hypothesis rows to
tuples of R in any way, and insert the mapped
conclusion row of H into R.
b If H is an EGD, map its hypothesis rows
to tuples of R in any way and equate the
symbols of R that H 's conclusion says are
3 Remember to equate all occurrences of
the symbols that the EGD says are equal.
3 Subtle point: if one or both of the
equated symbols appear in the conclusion,
change these occurrences as well; i.e., the
desired conclusion must change as well as
the tuples in the constructed relation.
Since no new symbols are ever generated
because of the full" assumption, eventually
this process stalls.
At that time, if the conclusion row of G has
been added to R in the case G is a TGD or
the symbols that G says must be equal have
been equated in case G is an EGD, then
conclude that G follows from G. If not, then
not. Proof the Chase Works
Soundness: Each inference made is sound,
since it is a direct application of a given GD.
Completeness: Suppose the conclusion of
G is not obtained. Then the nal R is a
3 It satis es every GD in G , but does not
satisfy G. Proof That a Key Plus Relations From a
Minimal Cover Have a Lossless Join
Suppose we have a minimal cover F consisting of
FD's Xi ! Ai for i = 1; 2; : : :; n, and we choose
fX1 A1; : : :; Xn An; K g
where K is a key for the entire relation R.
4 We need to infer from F written as EGD's, if
you like the JD that says the join is lossless.
This JD has:
1. A conclusion row that has
unsubscripted" symbols corresponding
to each attribute.
2. For each relation schema, a hypothesis
row that has the unsubscripted symbol
for an attribute if that attribute is in the
schema, and a unique symbol bj if b is the
symbol for the attribute and j is the row
Let F = fA ! B; C ! Dg. Recall the generated
decomposition is fAB; AC; CDg. The JD is:
d Proof That This JD Follows From the FD's
We know K + = R.
An induction on the order in which attributes
are added to K + says that in the row for
K , each subscripted symbol is equated to its
3 Key idea: if we use X ! A to add A to
K + , then in the row for K , all symbols
for the attributes in X have lost their
subscripts. Therefore, we may apply
X ! A to the rows for K and XA to
infer that aj = a, where j is the row
number for XA.
As a result, the row for K eventually loses all
its subscripts, and becomes the conclusion row
of the JD. 5 ...
View Full Document
- Spring '09
- Logic, Relational model, Tuple, MVD, conclusion row