cs319 course 4 armstrongcompletesound
Post on 21-Jul-2016
18 Views
Preview:
DESCRIPTION
TRANSCRIPT
Dr. Alexandra I. Cristeahttp://www.dcs.warwick.ac.uk/~acristea/
CS 319: Theory of Databases: C3
(provisionary) Content1. Generalities DB2. Integrity constraints (FD revisited)3. Relational Algebra (revisited)4. Query optimisation5. Tuple calculus6. Domain calculus7. Query equivalence8. LLJ, DP and applications9. Temporal Data10. The Askew Wall
2
… previous
Proving with Armstrong axioms(non)Redundancy of FDs
3
FD Part 3• Soundness and Completeness of
Armstrong’s axioms
4
Armstrong’s Axioms: Sound & Complete
Ingredients:• Functional Dependency (reminder)
– Definition– Inference rules– Closure of F : 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 5
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 iff any two tuples that agree on X value also agree on Y value
X Y if and only if:
for any instance r of Rfor any tuples t1 and t2 of r
t1(X) = t2(X) t1(Y) = t2(Y)
6
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 entails (logically implies) X Y, written F X Y
• In other words:there is no instance r of R that does not satisfy X Y
• Exampleif F = { A B, B C } then F A C
if F = { S A, SI P } thenF S I AP, F SP SAP etc.
A B C
S API
7
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
8
Set of Functional Dependencies F*• Formal Definition of F*, the covercover of F:
• Informal Definitions– F* is the set of all FD’s logically implied by F
(entailed)
• ... usually F* is much too large even to enumerate!
if F is a set of FD’s, then F* { X Y F ├ X Y }
9
F*• Usually F* is much too large even to
enumerate!• Example (3 attributes, 2 FD’s and 43 entailed
dependencies)
Attr(R)=ABC and F ={ A B, B C } then F* isA S for all [subset of ABC] 8 FDsB BC, B B, B C, B 4 FDsC C, C , 3 FDsAB S for all subsets S of ABC 8 FDsAC S for all subsets S of ABC 8 FDsBC BC, BC B, BC C, BC 4 FDsABC S for all subsets S of ABC 8 FDs
A B C
10
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
11
F+
• Informal Definition
• Formal Definition
F + (the closureclosure of F) is the set of dependencies which can be deduced from F by applying Armstrong’s axioms
if F is a set of FD’s, then F+ { X Y F |= X Y }
12
Armstrong’s AxiomsTheorem: 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?
13
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
14
Armstrong’s Theorems• Additional rules derived from axioms
– Unionif A B and A C, then A BC
– Decompositionif A BC, then A B and A C
A B
C
AB
C
For the proof, we can use:
15
Completeness of the Armstrong’s Axioms• Proving that Armstrong’s axioms are a
complete set of inference rules
Armstrong’s axioms generate all FDs in F*
F* F +
16
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+
Attr(R)=LMNO X=LF={L M , M N, O N}
then X+ = L+ = LMN
L M N
O
Example:
17
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+
18
Completeness (F* F +) of the Armstrong’s Axioms
• Completeness:
(R, X,Y AttrR), 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*)
(In other words) there is a relational instance r in R (rR) in which all the dependencies in F are true, but X Y does not hold
X X Y Y F*F* enough: enough:
Counter example!!Counter example!! 19
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
Counter example:
R=LMNO X=LF={L M , M N, O N}
then X+ = LMN
L M N
O
L O cannot be deduced (so not in F+)but also does not hold(not in F*)
20
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: – for any R, any X, Y, any F true in R, – with X->Y not inferable from the axioms– there is a relational instance r in R (rR) – in which all the dependencies in F are true
(F true in R ), – but X Y does not hold 21
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
(assuming Boolean attributes, or more generally, that the two tuples agree on X+ but disagree elsewhere)
Attributes of X+ Other Attributes
1 1 … 1 1 1 … 1
1 1 …. 1 0 0 … 0
Relational instance r0 for R with 2 tuplesL+ = LMNL M N O1 1 1 11 1 1 0
R=LMNO X=L
then X+ = L+ L O cannot be deduced
Example:
22
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
Attributes of X+ Other Attributes
1 1 … 1 1 1 … 11 1 …. 1 0 0 … 0
Relational instance r0 for R with 2 tuples
23
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=LF={L M , M N, O N}
then X+ = LMN
24
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
Attributes of X+ Other Attributes
1 1 … 1 1 1 … 1
1 1 …. 1 0 0 … 0
Relational instance r0 for R with 2 tuples
(R, X,Y Attr(R), F true in R : : X -> Y F+ => X -> Y F*)
25
Completeness of the Armstrong’s Axioms• We have proved the correctness (last module)
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)
26
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 XS2: if X Y then XZ Y S3: if X Y, Y Z then XW ZW
(This is a typical exam question )
27
Solution (soundness)
XX so by F1
YX if then by F2
XX
YZXZ so by F1 YYZ YZY
so by F3 YXZ YYZYZXZ , YXZYX
ZYYX ,if then by F3 ZX so by F2 ZWXW ZWXW
ZYYX
,
S1
S2
S3
28
Solution (completeness)
XY so {S1} Y Y, {S2}YZ Y
if XYZZ ::then hence X Y
YX {S1} X X, X Y, so{S3} XZ YZ
if then henceYZXZYX
A1
A2
A3ZYYX ,ifthen {S3}
henceZX
ZYYX
,00 ZX
29
Completeness of the Armstrong’s Axioms
• Exercises– Are the following set of rules a complete set of inference rules?
1111111
1111
Ø A B AB Ø
A B
AB
}0{},,{)( AFBARAtt Suppose
Apply above rules exhaustively
ABB
Hence: answer is NO
obtained from F1-3but not from R1-3
R1: X XR2: X Y then XZ YR3: X Y, Y Z then X Z
30
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
31
• Extra Question:– Can we have a sound and complete set of
inference rules consisting of only 2 rules?– What about 1 rule?
32
… to follow
Dependency preserving, Lossless join, Normal forms algorithms
33
top related