Dr. Alexandra I. Cristea

1
CS 319 Theory of Databases

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

2
Exam preparation

• Perform example problems by yourself, then check
  results
• If different, try to understand why
• Search also for alternative solutions.

3
(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

4
previous FD revisited proofs with FD with
definition counter-example
5
FD Part 2 Proving with FDs

• Proving with Armstrong axioms
• (non)Redundancy of FDs

6
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
7
Theorems

• Additional rules derived from axioms
• F4. Union
• if A ? B and A ? C, then A ? BC
• F5. Decomposition
• if A? BC, then A ? B and A ? C
• Prove them!

A
B
C
B
A
C
8
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.

9
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.

10
Rules hold vs redundant?

• Armstrong Rules hold but are they all
  necessary?
• Can we leave some out?
• How do we check this?

11
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
• FRules inf FRules.

12
F, F

• F fd F fd closure of F
• F fd F - fd cover of F

13
Exercises

• Show that Armstrongs inference rules for FDs
  (F1-3) are not redundant.
• Show that Rules F1, F2, F3, F4 is redundant.

14
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 Rules inf all
  possible consequences. Show then that there is
  another consequence to be computed with the help
  of inf.

15
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?

16
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.

17
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.

18
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.

19
How do we show something is redundant
(superfluous)?

• Show that it is inferable from the other axioms

20
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.

21
Summary

• We have learned how to prove fds based on the
  Armstrong axioms
• and also why when its 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

22
to follow Constrains revisited Soundness and
Completeness of Armstrong Axioms