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
that:
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 hourroom pairs
are independent of the teacherstudentgrade
triples.
3 There will be a unique teacher for the
course, but studentgrade information
should appear in combination with each
hourroom pair, since there is no logical
reason to assign di erent grades for
di erent hours and rooms. Axiomatization of FD's + MVD's
A4:
A5:
A6:
A7:
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
1.
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 hourroom pairs, and a set of
studentgrade pairs. Generalized Dependencies Unify FD's, MVD's, lossless joins, and inferences
about dependencies. A generalized dependency
consists of:
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 tuplegenerating dependency TGD;
if an equality it is an equalitygenerating
dependency EGD.
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
elsewhere.
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
value.
If a full TGD, then the conclusion row, with
the same mapping applied, must also be a row
of R.
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 :
ABCD
a1 b1 c1 d1
a1 b1 c2 d2
c1 = c2
The MVD A! BC :
!
AB
a1 b1
a1 b2
a1 b1 C
c1
c2
c1 D
d1
d2
d2 The join dependency JD . AB; BC; CD,
which says that AB , BC , and CD are a
losslessjoin decomposition of ABCD:
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
relation:
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
equal.
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
counterexample:
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
database schema
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
number. Example
Let F = fA ! B; C ! Dg. Recall the generated
decomposition is fAB; AC; CDg. The JD is:
A
a
a
a3
a B
b
b2
b3
b C
c1
c
c
c D
d1
d2
d
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
unsubscripted version.
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

Click to edit the document details