dr. a.i. cristea acristea/ cs 319: theory of databases: fds

Dr. A.I. Cristea

http://www.dcs.warwick.ac.uk/~acristea/

CS 319: Theory of Databases: FDs

2

… previous

Generalities on Databases

3

… previous

Generalities on DatabasesDefinitions of databasesThe issues databases tried/try to solveThe ingredients of a databaseThe users of a database and their respective roles (look at the later review of the database administrator as well)The data abstraction levels in a databaseThe data models in a databaseThe distinction between instance and schemaData definition versus data manipulation languageData manager program and its functionsOverall database system structure

4

Content1. Generalities DB2. Integrity constraints (FD revisited)3. Relational Algebra (revisited)4. Query optimisation5. Tuple calculus6. Domain calculus7. Query equivalence8. LLJ, DP and applications9. Temporal Data10. The Askew Wall

5

Functional Dependency- A functional dependency (FD) has the form X Y

where 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)also written:(r R, t1, t2 r : : t1[X]=t2[X] :: t1[Y]=t2[Y])basically identical with:(r R, t1, t2 r ::::t1[X]=t2[X] t1[Y]=t2[Y])

6

Notations• r R indicates instance r is a valid instance

for schema R (relation type). • t r indicates t is a tuple of r. • X R (***) indicates X is a subset of the set

of attributes used by R (~ heading). • XY means X Y.

(***) Should actually be X Attr(R) (heading)

7

To prove or not to prove, that is the question.

Given a proposition Q it always holds that Q Q.

For example:

))(::())(::( xPxxPx

))(::())(::( xPxxPx {De Morgan}

ergoProve or give a counter example

8

Proving

• To prove a functional dependency we can use the inference rules (Armstrong) or the definition of functional dependency

• Normally, the choice is optional.

9

Why prove something using definition of FD?

• Usually we prefer “inference rules”.

• However: we must prove that they are correct (hold). – via FD definitions!

10

Ex: Augmentation and Transitivity rules1. Augmentation: Prove (using the definition of fd)

that if X, Y and Z are sets of attributes of a relational schema R, and the fd X Y holds in R, then XZ YZ also holds in R.

2. Transitivity: Prove (using the definition of fd) that if X, Y and Z are sets of attributes of a relational schema R, and the fds X Y and Y Z hold in R, then X Z also holds in R.

11

Augumentation (in short)(r R, t1, t2 r : t1[X]=t2[X] : t1[Y]=t2[Y])

(this is the definition of X Y)(r R, t1, t2 r : t1[Z]=t2[Z] : t1[Z]=t2[Z])

(this is always true) (because ((A B) (C D) (A C) (B D))(r R, t1, t2 r : t1[X]=t2[X] t1[Z]=t2[Z] :

t1[Y]=t2[Y] t1[Z]=t2[Z]) (because for a function t: t[X Z] = t[X] t[Z])(r R, t1, t2 r : t1[XZ]=t2[XZ] : t1[YZ]=t2[YZ])

(this is the definition of XZ YZ)

12

Lemma 1• (((A B) (C D)) (A C) (B D))• <=> (use (X => Y) <=> (~X v Y) (twice) and distribute the negation over the conjunction) ~(A B) v ~(C D) v ~(A C) v (B D)• <=> (use ~(X => Y) <=> (X ~Y), distribute negation over conjunction) (A ~B) v (C ~D) v ~A v ~C v (B D)• <=> (use ((X ~Y) v ~X) <=> (~Y v ~X)) ~A v~B v~C v~D v (B D)• <=> (distribute negation over conjunction) ~A v~(B D) v~C v (B D)• <=> ( (X v~X) <=> true; true/false elimination) true

13

Augumentation (formal -1)(1) (r R, t1, t2 r ::t1[X]=t2[X] t1[Y]=t2[Y]) (this

is the definition of X Y)(2) (r R, t1, t2 r :: t1[Z]=t2[Z] t1[Z]=t2[Z]) (this

is always true)(3) Since both (1) and (2) hold, we can conjugate them:

(r R, t1, t2 r :: t1[X]=t2[X] t1[Y]=t2[Y]) (r R, t1, t2 r :: t1[Z]=t2[Z] t1[Z]=t2[Z])

(domain splitting)(4) (r R, t1, t2 r :: (t1[X]=t2[X] t1[Y]=t2[Y])

t1[Z]=t2[Z] t1[Z]=t2[Z]))

14

Augumentation (formal -2) (domain splitting)(4) (r R, t1, t2 r :: (t1[X]=t2[X] t1[Y]=t2[Y])

t1[Z]=t2[Z] t1[Z]=t2[Z]))5) (because of Lemma 1: ((A B) (C D)) ((A

C) (B D)))(r R, t1, t2 r :: (t1[X]=t2[X] t1[Z]=t2[Z]) (t1[Y]=t2[Y] t1[Z]=t2[Z]))

6) (because for a function t: t[X Z] = t[X] t[Z])(r R, t1, t2 r :: (t1[XZ]=t2[XZ] t1[YZ]=t2[YZ])(this is the definition of XZ YZ)

15

Transitivity (1)• (1) (r R, t1, t2 r:: (t1[X]=t2[X]) (t1[Y]=t2[Y])) (definition of X Y)• (2) (r R, t1, t2 r:: (t1[Y]=t2[Y]) (t1[Z]=t2[Z])) (definition of Y Z)• Since both (1) and (2) hold, we can conjugate them: (r R, t1, t2 r :: (t1[X]=t2[X]) (t1[Y]=t2[Y])) (r R, t1, t2 r :: (t1[Y]=t2[Y]) (t1[Z]=t2[Z])) (domain splitting) (r R, t1, t2 r ::( t1[X]=t2[X] t1[Y]=t2[Y])

(t1[Y]=t2[Y] t1[Z]=t2[Z]))

16

Transitivity (2) (domain splitting) (r R, t1, t2 r ::( t1[X]=t2[X] t1[Y]=t2[Y]) (t1[Y]=t2[Y]

t1[Z]=t2[Z])) (because of Lemma 2: ((A B) (B C)) (A C)) • (r R, t1, t2 r :: t1[X]=t2[X] t1[Z]=t2[Z]) (this is the definition of X Z)

17

Lemma 2• ((A B) (B C)) (A C) (use (X Y) (~X v Y) and distribute the negation over the conjunction) ~(A v B) v ~(B v C) v (~A v C) (use ~(X Y) (X ~Y), distribute negation over conjunction) (A ~B) v (B ~C) v (~A v C) (use ((X ~Y) v ~X) (~Y v ~X)) ~A v ~B v B v C ( (X v ~X) true; true/false elimination) true

18

Disproving

• to show a rule does not hold you must find (using your imagination) at least one instance in which the given fds hold and the “supposedly implied” fds do not hold.

19

Bogus rules3. Disprove that if X and Y are sets of attributes of a

relational schema R, and the fd X Y holds in R, then Y X also holds in R.

4. Disprove that if X, Y and Z are sets of attributes of a relational schema R, and the fds X Y and Y Z hold in R, then Z X also holds in R.

5. Disprove that if X, Y and Z are sets of attributes of a relational schema R, and the fd XY Z holds in R, then X YZ also holds in R.

20

Bogus rules 33. Disprove that if X and Y are sets of

attributes of a relational schema R, and the fd X Y holds in R, then Y X also holds in R.

• Solution:• Consider the following relation instance,• where we use singletons for X and Y:• We see that X Y holds, but not Y X

X Y

1 0

0 0

21

Bogus rules 4• Disprove that if X, Y and Z are sets of

attributes of a relational schema R, and the fds X Y and Y Z hold in R, then Z X also holds in R.

• Solution:• Consider the following relation instance,• where we use singletons for X, Y, and Z:• We see that both X Y and Y Z hold• But not Z X.

X Y Z

0 0 0

1 0 0

22

Bogus rules 5• Disprove that if X, Y and Z are sets of

attributes of a relational schema R, and the fd XY Z holds in R, then X YZ also holds in R.

• Solution:• Consider the following relation instance• where we use singletons for X, Y, and Z:• We see that XY Z holds, but not X

YZ.X Y Z

0 0 0

0 1 0

23

Summary

• We have learned how to prove & disprove FDs based on the definition

24

… to follow

Functional Dependencies (FDs) applied (2)

25

Questions?