Transcript Title Slide - Computer Science Department @ University of

CS 319: Theory of Databases: C2
Dr. A.I. Cristea
http://www.dcs.warwick.ac.uk/~acristea/
Organisational
•
•
•
•
Office hours
11:00-12:00, Wednesdays
17:00-18:00, Thursdays
Per email is also possible – but
availability depends on demand
• (general questions better to post on
forum)
… 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
(provisionary) Content
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
Generalities DB
Integrity constraints (FD revisited)
Temporal Data
Relational Algebra (revisited)
Query optimisation
Tuple calculus
Domain calculus
Query equivalence
LLJ, DP and applications
The Askew Wall
Datalog
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 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)
X  Y iff any two tuples that agree on X value also agree on Y value
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)
To prove or not to prove, that is
the question.
Given a proposition Q it always holds that Q  Q.
For example:
(x :: P( x))  (x :: P( x))
{De Morgan}
(x :: P( x))  (x :: P( x))
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
1. 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.
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)
(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]))
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)
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 ⇒ B) v ~(B ⇒ C) v (A ⇒ 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
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.
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.
Bogus rules 3
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.
X Y
• Solution:
1
0
• Consider the following relation instance,
0 0
• 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 X
• But not Z  X.
0
1
Y
0
0
Z
0
0
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
0
0
Y
0
1
Z
0
0
Summary
• We have learned how to prove &
disprove FDs based on the definition
… to follow
Functional Dependencies (FDs) applied (2)
Questions?