Transcript Title Slide - Computer Science Department @ University of

CS 319: Theory of Databases: C3
Dr. Alexandra I. Cristea
http://www.dcs.warwick.ac.uk/~acristea/
Exam preparation
• Perform example problems by yourself,
then check results;
• If different, try to understand why;
• Search also for alternative solutions.
(provisionary) Content
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
Generalities DB
Temporal Data
Integrity constraints (FD revisited)
Relational Algebra (revisited)
Query optimisation
Tuple calculus
Domain calculus
Query equivalence
LLJ, DP and applications
The Askew Wall
Datalog
… previous
( HD: Temporal Data;)
FD revisited; proofs with FD with definition & counterexample
FD Part 2: Proving with FDs:
• Proving with Armstrong axioms
• (non)Redundancy of FDs
Armstrong’s Axioms
• Axioms for reasoning about FD’s
F1: reflexivity
if Y  X then X  Y
F2: augmentation
if X  Y then XZ  YZ
F3: transitivity
if X  Y and Y  Z then X  Z
Armstrong’s Axioms ++
• Additional rules derived from axioms:
F4. Union
if A  B and A  C, then A  BC
A
F5. Decomposition
if A BC, then A  B and A  C
B
C
B
A
• Prove them!
C
Union Rule
if A  B and A  C, then A  BC
• Let A  B and A  C
• A  B, augument (F2) with A: A  AB
• A  C, augument (F2) with B: AB  BC
• A  AB and AB  BC, apply transitivity
(F3): A  BC q.e.d.
Decomposition Rule
if A  BC, then A  B and A  C
• Let A  BC
• BBC, apply reflexivity (F1) : BC  B
• A  BC and BC  B, apply
transitivity (F3): A  B
• Idem for A  C q.e.d.
Rules hold vs redundant?
• Armstrong Rules hold – but are they all
necessary?
• Can we leave some out?
– How do we check this?
Redundancy
DEF: An inference rule inf in a set of
inference rules Rules for a certain type
of constraint C
is redundant (superfluous)
when for all sets F of constraints of type
C it holds that:
F+{Rules –{inf}} = F+{Rules}.
F+
• F+ = {fd | F |= fd}
• F* = {fd | F |- fd}
closure of F
cover of F
Exercises
1. Show that Armstrong’s inference rules
for FDs (F1-3) are not redundant.
2. Show that Rules = {F1, F2, F3, F4} is
redundant.
Hint (Ex. 1)
• Show with the help of an example that, if one
of the three axioms is omitted, the remaining
set of functional dependencies is not
complete.
• Take therefore an appropriate set of
constraints and compute with the help of R –
{r} all possible consequences. Show then that
there is another consequence to be
computed with the help of r.
Solution
• We start from a relation scheme R and an arbitrary
legal instance r(R). Let ,  and  be sets of attributes
(headers), so that Attr(R), Attr(R) and Attr(R).
We have the following axioms:
• F1: (Reflexivity) Let  be valid (holds). Then we
also have →.
• F2: (Augmentation) Let → be valid. Then we also
have →.
• F3: (Transitivity) Let → and → be valid. Then
we also have →.
• Now we omit in turn one of the axioms.
– Why in turn?
– Why not just one?
Case 1: F1 is not superfluous:
• Let Attr(R) = {X} and F = . Because F is
empty, neither F2 nor F3 can be used to
deduce new fds. Therefore, F+ = F = .
• From F1 we could however deduce that X
 X is valid, which is not present in the
above set.
Case 3: F2 is not superfluous:
• Let R = {X, Y} and F = {X  Y}.
• With the help of F1 and F3 we deduce:
• F+ = {   , X  X, Y  Y, X  ,
Y  , XY  XY, XY  Y, XY  X, XY
 }
• However, with X  Y and with the help
of F2 we can infer that X  XY is valid,
which is not present in the above set.
Case 3: F3 is not superfluous:
• Let R = {X, Y, Z} and F = {X  Y, Y  Z }.
F+ = {
XYZ  XYZ, XY  XY,
YZ  YZ,
X  Y,
Y  Z,
XYZ  XY, XY  X,
YZ  Y,
X  XY,
Y  YZ,
XYZ  XZ, XY  Y,
YZ  Z,
XY  Y,
XY  XZ,
XYZ  YZ, XY  ,
YZ  ,
XZ  YZ,
YZ  Z,
XYZ  X, XZ  XZ,
X  X, X  , Y Y, Y  , Z  Z, Z  
XYZ  Y,
XYZ  Z, XYZ  ,
XZ  }
• With the help of F3 we can also infer X  Z, which is not in F+.
How do we show something is
redundant (superfluous)?
• Show that it is inferable from the other
axioms
F4 is superfluous:
• F4 (union rule) : Let → and  → be valid.
• Then →  is also valid.
• We show now that F = {F1, F2, F3, F4} is redundant is by, e.g., inferring F4
from the other three.
• By using augumentation, from → we deduce that also → is valid
(augmentation with ).
• By using augumentation, from → we deduce that also → is valid
(augmentation with ).
• By using transitivity, from → and →, we deduce that also → is
valid.
• Note that to prove that a set of rules (axioms) is redundant we can use
normal calculus; however, to prove that a set of rules is not redundant, we
need to know the meaning of the rules.
Summary
• We have learned how to prove fds based
on the Armstrong axioms
– and also why & when it’s ok to do so
• We have learned how to prove that a set
of axioms is redundant or not
• We have learned that the Armstrong
axioms are not redundant
FD Part 3
• Soundness and Completeness of
Armstrong’s axioms
Armstrong’s Axioms:
Sound & Complete
Ingredients:
• Functional Dependency (reminder)
– Definition
– Inference rules
– Closure of F : F*
Set of all FDs obtained by applying inferences rules on a basic set of
FDs
– Issues and resolutions
• Armstrong’s Axioms (reminder)
– 3 inferences rules for obtaining the closure of F
– Properties of the Armstrong’s Axioms
They are a sound an complete set of inference rules
• Proof of the completeness
Functional Dependency
- A functional dependency (FD) has the form X  Y
where X and Y are sets of attributes in a relation R
X  Y if and only if:
for any instance r of R
for any tuples t1 and t2 of r
t1(X) = t2(X)  t1(Y) = t2(Y)
X  Y iff any two tuples that agree on X value also agree on Y value
Inference of Functional
Dependencies
• Suppose R is a relation scheme, and F is a set of functional
dependencies for R
• If X, Y are subsets of attributes of Attr(R) and if all instances r of
R which satisfy the FDs in F also satisfy X  Y, then we say that
F logically implies X  Y, written F |- X  Y
• In other words:
there is no instance r of R that does not satisfy X  Y
• Example
if F = { A  B, B  C } then F |- A  C
if F = { S  A, SI  P } then
F |- S I  AP, F |- SP  SAP etc.
A
B
I
S
P
A
C
Functional Dependency
• Issue:
– How to represent set of ALL FD’s for a relation R?
• Solution
– Find a basic set of FD’s ((canonical) cover)
– Use axioms for inferring
– Represent the set of all FD’s as the set of FD’s that can be
inferred from a basic set of FD’s
• Axioms
– they must be a sound and complete set of inference rules
Set of Functional Dependencies F*
• Formal Definition of F*, the cover of F:
if F is a set of FD’s, then F* { X  Y  F ├ X  Y }
• Informal Definitions
– F* is the set of all FD’s logically implied by F
(entailed)
• ... usually F* is much too large even to
enumerate!
F*
• Usually F* is much too large even to
enumerate!
• Example (3 attributes, 2 FD’s and 43 entailed
dependencies)
A
Attr(R)=ABC and F ={ A  B, B  C } then F* is
A  S for all [subset of ABC]
B  BC, B  B, B  C, B  
C  C, C  ,   
AB  S for all subsets S of ABC
AC  S for all subsets S of ABC
BC  BC, BC  B, BC  C, BC  
ABC  S for all subsets S of ABC
B
8 FDs
4 FDs
3 FDs
8 FDs
8 FDs
4 FDs
8 FDs
C
Armstrong’s Axioms
• Axioms for reasoning about FD’s
F1: reflexivity
if Y  X then X  Y
F2: augmentation
if X  Y then XZ  YZ
F3: transitivity
if X  Y and Y  Z then X  Z
+
F
• Informal Definition
F + (the closure of F) is the set of dependencies which can
• Formal
Definition
be deduced
from F by applying Armstrong’s axioms
if F is a set of FD’s, then F+ { X  Y F |= X  Y }
Armstrong’s Axioms
Theorem:
Armstrong’s axioms are a sound and complete
set of inference rules
– Sound: all Armstrong axioms are valid (correct / hold)
– Complete: all fds that are entailed can be deduced with
the help of the Armstrong axioms
• How to:
– Prove the soundness?
Armstrong’s Axioms
• Theorem: Armstrong’s axioms are a sound
and complete set of inference rules
– Sound: the Armstrong’s rules generate only FDs in
F*
F+  F*
– Complete: the Armstrong’s rules generate all FDs
in F*
F*  F +
– If complete and sound then F+ = F*
• Here
– Proof of the completeness
For the proof, we can use:
Armstrong’s Axioms ++
• Additional rules derived from axioms
– Union
if A  B and A  C, then A  BC
A
– Decomposition
if A BC, then A  B and A  C
B
C
B
A
C
Completeness of the
Armstrong’s Axioms
• Proving that Armstrong’s axioms are a
complete set of inference rules
Armstrong’s rules generate all FDs in F*
Completeness of the
Armstrong’s Axioms
• First define X+, the closure of X with respect to F:
X+ is the set of attributes A such that X  A can be deduced
from F with Armstrong’s axioms
• Note that we can deduce that X  Y for some set Y by
applying Armstrong’s axioms if and only if Y  X+
Example:
Attr(R)=LMNO
X=L
F={L  M , M  N, O  N}
then X+ = L+ = LMN
L
M
O
N
XY
+
F <=> Y  X+
• Proof: We can deduce that X  Y for some set Y by applying
Armstrong’s axioms if and only if Y  X+
Y  X+  X  Y  F+
• Y  X+ and suppose that A Y then X  A  F+ (definition of X+)
•  A Y: X  A  F+
• X  Y  F+ (union rule)
X  Y  F+  Y  X+
• X  Y  F+ and suppose that A Y then X  A  F+
(decomposition rule)
• A  X+ (definition of X+)
•  A Y: A  X+
• Y  X+
Completeness (F*  F +) of the
Armstrong’s Axioms
•
Completeness:
(R,  X,Y Attr(R), F true in R : : X  Y  F* => X  Y  F +)
•
Idea: (A => B) ≡ (A v B) ≡ (B v A) ≡ (B =>A)
To establish completeness, it is sufficient to show:
if X  Y cannot be deduced from F using Armstrong’s axioms then also X  Y is not
logically implied by F:
(R,  X,Y Attr(R), F true in R : : X  Y  F+ => X  Y  F*)
X  Y  F* enough:
(In other words) there is a relational instance r in R (rR) in which all the
dependencies in F are true, but X  Y does not hold
Counter example!!
Completeness of the
Armstrong’s Axioms
• Example for the proof idea for a given R, F, X:
If X  Y cannot be deduced using Armstrong’s axioms: then there
is a relational instance for R in which all the dependencies in F are
true, but X  Y does not hold
R=LMNO
X=L
F={L  M , M  N, O  N}
then X+ = LMN
Counter example:
L
M
O
N
L  O cannot be
deduced (so not in F+)
but also does not hold
(not in F*)
What we want to prove thus:
(R,  X,Y Attr(R), F true in R : : X  Y  F+ => X  Y  F*)
(In other words) Counterexample
– by construction:
– there is a relational instance r in R (rR)
– in which all the dependencies in F are true
( F true in R ),
– but X  Y does not hold
Completeness of the
Armstrong’s Axioms
• Suppose one can not deduce X  Y from Armstrong’s
axioms for an arbitrary R, F, X,Y; construct counter-ex.
• Consider the instance r0 for R with 2 tuples
Relational instance r0 for R with 2 tuples
Attributes of
X+
Other
Attributes
11…1
1 1 …. 1
Example:
L+ = LMN
L
M
N
O
11…1
1
1
1
1
00…0
1
1
1
0
R=LMNO
X=L
then X+ = L+
L  O cannot
be deduced
(assuming Boolean attributes, or more generally that the two
tuples agree on X+ but disagree elsewhere)
Completeness of the
Armstrong’s Axioms
• Check that all the dependencies in F are true in R:
– Suppose that V  W is a dependency in F
• If V is not a subset of X+, the dependency holds in r0
• If V is a subset of X+, then both X  V, and then X  W can be
deduced by Armstrong’s axioms. This means that W is a subset
of X+, and thus V  W holds in r0
Relational instance r0 for R with 2 tuples
Attributes of X+
Other Attributes
11…1
11…1
1 1 …. 1
00…0
Completeness of the
Armstrong’s Axioms
• Check that all the dependencies in F are true
– Extended Example (more tuples)
• O  N is a dependency in F but O is not a subset of X+,
the dependency holds in r0
• M  N is a dependency in F and M is a subset of X+,
then both L  M, and L  N can be deduced by
Armstrong’s axioms. This means that N is a subset of
X+, and thus M  N holds in r0
R=LMNO
X=L
F={L  M , M  N, O  N}
then X+ = LMN
Completeness of the
Armstrong’s Axioms
• Proof that X  Y does not hold in r0:
– Recall that we can deduce that X  Y for some set Y by
applying Armstrong’s axioms if and only if Y  X+
– By assumption, we can’t deduce that X  Y holds in r0
– Hence Y contains (at least) an attribute not in the subset X+,
confirming that X  Y does not hold in r0
Relational instance r0 for R with 2 tuples
Attributes of
X+
Other
Attributes
11…1
11…1
1 1 …. 1
00…0
Completeness of the
Armstrong’s Axioms
• We have proved the correctness (last colstruction)
and here, the completeness of Armstrong’s
Axioms:
– How can we prove the completeness of another
set of rules?
• Repeat the proof for this set
• Deduce the Armstrong’s Axioms from this set
– How can we disprove the completeness of another
set of rules?
• By showing (via a counterexample) that some
consequence of Armstrong's rules cannot be deduced
from them (see the proof technique for non-redundancy)
Completeness of the
Armstrong’s Axioms
• Exercise
– Are the following set of rules a sound and complete set of
inference rules? (X, Y, Z, W  R)
S1: X  X
S2: if X  Y then XZ  Y
S3: if X  Y, Y  Z then XW  ZW
(This is a typical exam question )
• Extra Question:
– Can we have a sound and complete set of inference rules
consisting of only 2 rules?
– What about 1 rule?
Solution (soundness)
XX
if X  Y
Y  YZ
so by F1
then by F2
S1
X Y
XZ  Y
S2
X  Y ,Y  Z
XW  ZW
S3
XZ  YZ
so by F1
YZ  Y
XZ  YZ , YZ  Y so by F3
XZ  Y
if X  Y , Y  Z then by F3
so by F2
XX
X Z
XW  ZW
Solution (completeness)
if Y  X then
Z :: YZ  X so {S1} Y  Y,
{S2}YZ  Y
if X  Y then {S1} X  X, X  Y, so
{S3} XZ  YZ
if X  Y , Y  Z
  Z 0

then {S3} X  0
hence X  Y
X Y
XZ  YZ
A2
X  Y ,Y  Z
X Z
A3
hence
hence
A1
Completeness of the
Armstrong’s Axioms
• Exercises
– Are the following set of rules a complete set of inference rules?
R1: X  X
R2: X  Y then XZ  Y
R3: X  Y, Y  Z then X  Z
Ø
A
Ø A
1
1
1
1
B AB
1
1
1
1
1
1
Suppose
Att( R)  {A, B}, F  {0  A}
Apply above rules exhaustively
B  AB obtained from F1-3
but not from R1-3
Hence: answer is NO
B
AB
1
Summary
• We have learned how to prove that the
Armstrong axiom set is complete (and
we already knew it is sound)
• We can now prove the soundness and
completeness of any other set of
axioms
… to follow
RA revisited