- 172 Views
- Uploaded on

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

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

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
- BBC, 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

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) ≡ (BvA) ≡ (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 (rR) 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 (rR)
- 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 (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}

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

RA revisited

Download Presentation

Connecting to Server..