Dr' Alexandra I' Cristea

1
CS 319 Theory of Databases C3

• Dr. Alexandra I. Cristea
• http//www.dcs.warwick.ac.uk/acristea/

2
(provisionary) Content

• Generalities DB
• Integrity constraints (FD revisited)
• Relational Algebra (revisited)
• Query optimisation
• Tuple calculus
• Domain calculus
• Query equivalence
• LLJ, DP and applications
• Temporal Data
• The Askew Wall

3
previous Proving with Armstrong
axioms (non)Redundancy of FDs
4
FD Part 3

• Soundness and Completeness of Armstrongs axioms

5
Armstrongs 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
• Armstrongs Axioms (reminder)
• 3 inferences rules for obtaining the closure of F
• Properties of the Armstrongs Axioms
• They are a sound an complete set of inference
  rules
• Proof of the completeness

6
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
R for any tuples t1 and t2 of r t1(X) t2(X)
? t1(Y) t2(Y)
7
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
• 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
C
P
I
S
A
8
Functional Dependency

• Issue
• How to represent set of ALL FDs for a relation
  R?
• Solution
• Find a basic set of FDs ((canonical) cover)
• Use axioms for inferring
• Represent the set of all FDs as the set of FDs
  that can be inferred from a basic set of FDs
• Axioms
• they must be a sound and complete set of
  inference rules

9
Set of Functional Dependencies F

• Formal Definition of F, the cover of F
• Informal Definitions
• F is the set of all FDs logically implied by F
• (entailed)
• ... usually F is much too large even to
  enumerate!

if F is a set of FDs, then F? X Y ½ F
X Y
10
F

• Usually F is much too large even to enumerate!
• Example (3 attributes, 2 FDs and 43 entailed
  dependencies)

A
B
C
Attr(R)ABC and F A B, B C then F is A
S for all subset of ABC 8 FDs B BC, B
B, B C, B ? 4 FDs C C, C ?, ? ?
3 FDs AB S for all subsets S of ABC 8
FDs AC S for all subsets S of ABC 8 FDs BC
BC, BC B, BC C, BC ? 4 FDs ABC S for
all subsets S of ABC 8 FDs
11
Armstrongs Axioms

• Axioms for reasoning about FDs

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
12
F

• Informal Definition
• Formal Definition

F (the closure of F) is the set of dependencies
which can be deduced from F by applying
Armstrongs axioms
if F is a set of FDs, then F? X Y ½ F
X Y
13
Armstrongs Axioms

• Theorem
• Armstrongs 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?

14
Armstrongs Axioms

• Theorem Armstrongs axioms are a sound and
  complete set of inference rules
• Sound the Armstrongs rules generate only FDs in
  F
• F ? F
• Complete the Armstrongs rules generate all FDs
  in F
• F ? F
• If complete and sound then F F
• Here
• Proof of the completeness

15
Armstrongs Theorems
For the proof, we can use

• Additional rules derived from axioms
• Union
• if A ? B and A ? C, then A ? BC
• Decomposition
• if A? BC, then A ? B and A ? C

A
B
C
B
A
C
16
Completeness of the Armstrongs Axioms

• Proving that Armstrongs axioms are a complete
  set of inference rules
• Armstrongs axioms generate all FDs in F

F ? F
17
Completeness of the Armstrongs 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 Armstrongs axioms
• Note that we can deduce that X Y for some set Y
  by applying Armstrongs axioms if and only if Y ?
  X

Example
Attr(R)LMNO XL FL M , M N, O N then
X L LMN
L
M
N
O
18
X Y ? F ltgt Y ? X

• Proof We can deduce that X Y for some set Y by
  applying Armstrongs 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

19
Completeness (F ? F ) of the Armstrongs Axioms

• Completeness
• (?R, ? X,Y ?Attr(R), ?F true in R X Y ? F
  gt X Y ? F )
• Idea (A gt B) (?A v B) (B v ?A) (?B gt?A)
• To establish completeness, it is sufficient to
  show
• if X Y cannot be deduced from F using
  Armstrongs axioms then also X Y is not
  logically implied by F
• (?R, ? X,Y ?Attr(R), ?F true in R X Y ? F
  gt X Y ? F)
• (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

X Y ? F enough
Counter example!!
20
Completeness of the Armstrongs Axioms

• Example for the proof idea for a given R, F, X
• If X Y cannot be deduced using Armstrongs
  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

L O cannot be deduced (so not in F) but also
does not hold (not in F)
RLMNO XL FL M , M N, O N then X LMN
L
M
N
O
21
What we want to prove thus

• (?R, ? X,Y ?Attr(R), ?F true in R X Y ? F
  gt X Y ? F)
• (In other words) Counterexample by
  construction
• for any R, any X, Y, any F true in R,
• with X-gtY not inferable from the axioms
• 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

22
Completeness of the Armstrongs Axioms

• Suppose one can not deduce X Y from Armstrongs
  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)

Example
Relational instance r0 for R with 2 tuples
RLMNO XL then X L L O cannot be deduced
L LMN
23
Completeness of the Armstrongs 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 Armstrongs 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
24
Completeness of the Armstrongs 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
  Armstrongs axioms. This means that N is a subset
  of X, and thus M N holds in r0

RLMNO XL FL M , M N, O N then X LMN
25
Completeness of the Armstrongs Axioms
(?R, ? X,Y ?Attr(R), ?F true in R X -gt Y ?
F gt X -gt Y ? F)

• Proof that X Y does not hold in r0
• Recall that we can deduce that X Y for some set
  Y by applying Armstrongs axioms if and only if Y
  ? X
• By assumption, we cant 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
26
Completeness of the Armstrongs Axioms

• We have proved the correctness (last module) and
  here, the completeness of Armstrongs Axioms
• How can we prove the completeness of another set
  of rules?
• Repeat the proof for this set
• Deduce the Armstrongs 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)

27
Completeness of the Armstrongs 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 )

28
Solution (soundness)
so by F1
S1 S2 S3
then by F2
so by F1
so by F3
if
then by F3
so by F2
29
Solution (completeness)
so S1 Y Y, S2YZ Y
if
then
hence X Y
A1 A2 A3
S1 X X, X Y, so S3 XZ YZ
if
then
hence
if
hence
then S3
30
Completeness of the Armstrongs Axioms

• Exercises
• Are the following set of rules a complete set of
  inference rules?

R1 X XR2 X Y then XZ YR3 X Y, Y Z
then X Z
31
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

32

• Extra Question
• Can we have a sound and complete set of inference
  rules consisting of only 2 rules?
• What about 1 rule?

33
to follow Dependency preserving, Lossless
join, Normal forms algorithms