cs 319 theory of databases c3 l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
CS 319: Theory of Databases: C3 PowerPoint Presentation
Download Presentation
CS 319: Theory of Databases: C3

Loading in 2 Seconds...

play fullscreen
1 / 50

CS 319: Theory of Databases: C3 - PowerPoint PPT Presentation


  • 173 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'CS 319: Theory of Databases: C3' - ancelin


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
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
Exam preparation
  • Perform example problems by yourself, then check results;
  • If different, try to understand why;
  • Search also for alternative solutions.
provisionary content
(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
slide4

… previous

( HD: Temporal Data;)

FD revisited; proofs with FD with definition & counter-example

fd part 2 proving with fds
FD Part 2: Proving with FDs:
  • Proving with Armstrong axioms
  • (non)Redundancy of FDs
armstrong s axioms
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 axioms7
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
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
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
Rules hold vs redundant?
  • Armstrong Rules hold – but are they all necessary?
  • Can we leave some out?
    • How do we check this?
redundancy
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}.

slide12
F+
  • F+ = {fd | F |= fd} closure of F
  • F* = {fd | F |- fd} cover of F
exercises
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
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
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
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
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
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
How do we show something is redundant (superfluous)?
  • Show that it is inferable from the other axioms
f4 is superfluous
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
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
FD Part 3
  • Soundness and Completeness of Armstrong’s axioms
armstrong s axioms sound complete
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
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
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 dependency26
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
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 }

slide28
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 axioms29
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

slide30
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 axioms31
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 axioms32
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
armstrong s axioms33

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
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 axioms35
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
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 (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 axioms38
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
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 axioms40
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 axioms41
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 axioms42
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 axioms43
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 axioms44
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 axioms45
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
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
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}

completeness of the armstrong s axioms48

Ø 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

summary49
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
slide50

… to follow

RA revisited