250 likes | 517 Views
CS 319: Theory of Databases: FDs. Dr. A.I. Cristea http://www.dcs.warwick.ac.uk/~acristea/. … previous Generalities on Databases. … previous Generalities on Databases Definitions of databases The issues databases tried/try to solve The ingredients of a database
E N D
CS 319: Theory of Databases: FDs Dr. A.I. Cristea http://www.dcs.warwick.ac.uk/~acristea/
… previous Generalities on Databases
… previous Generalities on Databases Definitions of databases The issues databases tried/try to solve The ingredients of a database The 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 database The data models in a database The distinction between instance and schema Data definition versus data manipulation language Data manager program and its functions Overall database system structure
Content • Generalities DB • Integrity constraints (FD revisited) • Relational Algebra (revisited) • Query optimisation • Tuple calculus • Domain calculus • Query equivalence • LLJ, DP and applications • Temporal Data • The Askew Wall
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) 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])
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). • XYmeans X Y. (***) Should actually be X Attr(R) (heading)
To prove or not to prove, that is the question. Given a proposition Q it always holds that Q Q. For example: {De Morgan} ergo Prove or give a counter example
Proving • To prove a functional dependency we can use the inference rules (Armstrong) or the definition of functional dependency • Normally, the choice is optional.
Why prove something using definition of FD? • Usually we prefer “inference rules”. • However: we must prove that they are correct (hold). • via FD definitions!
Ex: Augmentation and Transitivity rules • 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. • 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.
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)
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
Augumentation (formal -1) • (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) • 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) • (r R, t1, t2 r :: (t1[X]=t2[X] t1[Y]=t2[Y]) Ù (t1[Z]=t2[Z] t1[Z]=t2[Z]))
Augumentation (formal -2) • (domain splitting) • (r R, t1, t2 r :: (t1[X]=t2[X] t1[Y]=t2[Y]) Ù (t1[Z]=t2[Z] t1[Z]=t2[Z])) • (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])) • (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)
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]))
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)
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
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.
Bogus rules • 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. • 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. • 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.
Bogus rules 3 • 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
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.
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.
Summary • We have learned how to prove & disprove FDs based on the definition
… to follow Functional Dependencies (FDs) applied (2)