CS 319: Theory of Databases: C3

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 • 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 & counter-example

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  Band A  C, then A  BC F5. Decomposition if A BC, then A  B and A  C • Prove them! A B C B A 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} closure of F • F* = {fd | F |- fd} cover of F

Exercises • Show that Armstrong’s inference rules for FDs (F1-3) are not redundant. • 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  Ywhere 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)

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 C I P S A

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: • 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 }

F* • Usually F* is much too large even to enumerate! • Example (3 attributes, 2 FD’s 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

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 • Formal Definition F+(the closure 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 }

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  Band A  C, then A  BC • Decomposition if A BC, then A  B and A  C A 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 N O

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) ≡ (Bv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 (rR) in which all the dependencies in F are true, but X ® Y does not hold X ® Y  F* enough: 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 Counter example: L ® O cannot be deduced (so not in F+) but also does not hold (not in F*) R=LMNO X=L F={L ® M , M ® N, O ® N} then X+ = LMN L M N O

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 (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 R=LMNO X=L then X+ = L+ L ® O cannot be deduced L+ = LMN

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

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

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) so by F1 S1 S2 S3 if then by F2 so by F1 so by F3 if then by F3 so by F2

Solution (completeness) so {S1} Y®Y, {S2}YZ®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}

Ø A B AB Suppose 1 1 1 1 Apply above rules exhaustively 1 1 1 Ø A B AB obtained from F1-3but not from R1-3 1 1 1 1 Hence: answer is NO Completeness of the Armstrong’s 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

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

CS 319: Theory of Databases: C3