CS 319: Theory of Databases: C3

1 / 50

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

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

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

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

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

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

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