relational algebra wrap-up and relational calculus zachary g. ives university of pennsylvania cis...
Post on 22-Dec-2015
214 views
TRANSCRIPT
Relational Algebra Wrap-up and Relational Calculus
Zachary G. IvesUniversity of Pennsylvania
CIS 550 – Database & Information Systems
September 11, 2003
Some slide content courtesy of Susan Davidson & Raghu Ramakrishnan
2
Relational Algebra Relational algebra operations operate on relations and
produce relations (“closure”)f: Relation -> Relation f: Relation x Relation ->
Relation Six basic operations:
Projection (R) Selection (R) Union R1 [ R2
Difference R1 – R2
Product R1 £ R2
(Rename) (R) And some other useful ones:
Join R1 ⋈ R2
Semijoin R1 ⊲ R2
Intersection R1 Å R2 Division R1 ¥ R2
3
Example Data Instance
sid name
1 Jill
2 Qun
3 Nitin
4 Marty
fid name
1 Ives
2 Saul
8 Roth
sid exp-grade
cid
1 A 550-0103
1 A 700-1003
3 A 700-1003
3 C 500-0103
4 C 500-0103
cid subj sem
550-0103 DB F03
700-1003 AI S03
501-0103 Arch F03
fid cid
1 550-0103
2 700-1003
8 501-0103
STUDENT Takes COURSE
PROFESSOR Teaches
4
Natural Join and Intersection
Natural join: special case of join where is implicit – attributes with same name must be equal:
STUDENT ⋈ Takes ´ STUDENT ⋈STUDENT.sid = Takes.sid Takes
Intersection: as with set operations, derivable from difference
A-B B-A
A B
A Å B≡ (A [ B) – (A – B) – (B – A)≡ (A - B) – (B - A)
5
Division
A somewhat messy operation that can be expressed in terms of the operations we have already defined
Used to express queries such as “The fid's of faculty who have taught all subjects”
Paraphrased: “The fid’s of professors for which there does not exist a subject that they haven’t taught”
6
Division Using Our Existing Operators
All possible teaching assignments: Allpairs:
NotTaught, all (fid,subj) pairs for which professor fid has not taught subj:
Answer is all faculty not in NotTaught:
fid,subj (PROFESSOR £ subj(COURSE))
Allpairs - fid,subj(Teaches COURSE)⋈fid(PROFESSOR) - fid(NotTaught)
´ fid(PROFESSOR) - fid(fid,subj (PROFESSOR £ subj(COURSE)) -fid,subj(Teaches COURSE))⋈
7
Division: R1 R2
Requirement: schema(R1) ¾ schema(R2) Result schema: schema(R1) – schema(R2) “Professors who have taught all courses”:
What about “Courses that have been taught by all faculty”?
fid (fid,subj(Teaches ⋈ COURSE) subj(COURSE))
8
The Big Picture: SQL to Algebra toQuery Plan to Web Page
SELECT * FROM STUDENT, Takes, COURSE
WHERE STUDENT.sid = Takes.sID AND Takes.cID = cid
STUDENT
Takes COURSE
Merge
Hash
by cid by cidOptimizer
ExecutionEngine
StorageSubsystem
Web Server / UI / etc
Query Plan – anoperator tree
9
Hint of Future Things: OptimizationIs Based on Algebraic Equivalences
Relational algebra has laws of commutativity, associativity, etc. that imply certain expressions are equivalent in semantics
They may be different in cost of evaluation!
c Ç d(R) ´ c(R) [ d(R)
c (R1 £ R2) ´ R1 ⋈c R2
c Ç d (R) ´ c (d (R))
Query optimization finds the most efficient representation to evaluate (or one that’s not bad)
10
Relational Calculus: A Logical Way ofExpressing Query Operations
First-order logic (FOL) can also be thought of as a query language, and can be used in two ways: Tuple relational calculus Domain relational calculus Difference is the level at which variables are
used: for attributes (domains) or for tuples The calculus is non-procedural (declarative)
as compared to the algebra More like what we’ll see in SQL More convenient to express certain things
11
Domain Relational Calculus
Queries have form:
{<x1,x2, …, xn>| p}
Predicate: boolean expression over x1,x2, …, xn Precise operations depend on the domain and
query language – may include special functions, etc.
Assume the following at minimum:<xi,xj,…> R X op Y X op const const op X
where op is , , , , , xi,xj,… are domain variables
domain variables
predicate
12
More Complex Predicates
Starting with these atomic predicates, build up new predicates by the following rules: Logical connectives: If p and q are predicates,
then so are pq, pq, p, and pq (x>2) (x<4) (x>2) (x>0)
Existential quantification: If p is a predicate, then so is x.p
x. (x>2) (x<4)
Universal quantification: If p is a predicate, then so is x.p
x.x>2 x. y.y>x
13
Some Examples
Faculty ids Course names for courses with students
expecting a “C” Courses taken by Jill
14
Logical Equivalences
There are two logical equivalences that will be heavily used: pq p q
(Whenever p is true, q must also be true.) x. p(x) x. p(x)
(p is true for all x)
The second can be a lot easier to check!
15
Free and Bound Variables A variable v is bound in a predicate p
when p is of the form v… or v… A variable occurs free in p if it occurs in
a position where it is not bound by an enclosing or
Examples: x is free in x>2 x is bound in x.x>y
16
Can Rename Bound Variables Only
When a variable is bound one can replace it with some other variable without altering the meaning of the expression, providing there are no name clashes
Example: x.x>2 is equivalent to y.y>2
Otherwise, the variable is defined outside our “scope”…
17
Safety Pitfall in what we have done so far – how do we
interpret: {<sid,name>| <sid,name> STUDENT}
Set of all binary tuples that are not students: an infinite set (and unsafe query)
A query is safe if no matter how we instantiate the relations, it always produces a finite answer Domain independent: answer is the same regardless
of the domain in which it is evaluated Unfortunately, both this definition of safety and
domain independence are semantic conditions, and are undecidable
18
Safety and Termination Guarantees
There are syntactic conditions that are used to guarantee “safe” formulas The definition is complicated, and we won’t discuss
it; you can find it in Ullman’s Principles of Database and Knowledge-Base Systems
The formulas that are expressible in real query languages based on relational calculus are all “safe”
Many DB languages include additional features, like recursion, that must be restricted in certain ways to guarantee termination and consistent answers
19
Mini-Quiz
How do you write: Which students have taken more than one
course from the same professor?
What is the highest course number offered?
20
Translating from RA to DRC
Core of relational algebra: , , , x, - We need to work our way through the
structure of an RA expression, translating each possible form. Let TR[e] be the translation of RA expression e
into DRC.
Relation names: For the RA expression R, the DRC expression is {<x1,x2, …, xn>| <x1,x2, …, xn> R}
21
Selection: TR[ R]
Suppose we have (e’), where e’ is another RA expression that translates as:
TR[e’]= {<x1,x2, …, xn>| p} Then the translation of c(e’) is
{<x1,x2, …, xn>| p’}where ’ is obtained from by replacing each attribute with the corresponding variable
Example: TR[#1=#2 #4>2.5R] (if R has arity 4) is
{<x1,x2, x3, x4>|< x1,x2, x3, x4> R x1=x2 x4>2.5}
22
Projection: TR[i1,…,im(e)]
If TR[e]= {<x1,x2, …, xn>| p} then TR[i1,i2,…,im
(e)]=
{<x i1,x i2
, …, x im >| xj1,xj2
, …, xjk.p},
where xj1,xj2
, …, xjk are variables in x1,x2, …, xn
that are not in x i1,x i2
, …, x im
Example: With R as before,#1,#3 (R)={<x1,x3>| x2,x4. <x1,x2, x3,x4> R}
23
Union: TR[R1 R2] R1 and R2 must have the same arity For e1 e2, where e1, e2 are algebra
expressionsTR[e1]={<x1,…,xn>|p} and TR[e2]={<y1,…yn>|q}
Relabel the variables in the second:TR[e2]={< x1,…,xn>|q’}
This may involve relabeling bound variables in q to avoid clashesTR[e1e2]={<x1,…,xn>|pq’}.
Example: TR[R1 R2] = {< x1,x2, x3,x4>| <x1,x2, x3,x4>R1 <x1,x2, x3,x4>R2
24
Other Binary Operators
Difference: The same conditions hold as for unionIf TR[e1]={<x1,…,xn>|p} and TR[e2]={< x1,…,xn>|q}
Then TR[e1- e2]= {<x1,…,xn>|pq}
Product: If TR[e1]={<x1,…,xn>|p} and TR[e2]={< y1,…,ym>|q}
Then TR[e1 e2]= {<x1,…,xn, y1,…,ym >| pq}
Example: TR[RS]= {<x1,…,xn, y1,…,ym >|
<x1,…,xn> R <y1,…,ym > S }
25
Summary
Can translate relational algebra into (domain) relational calculus.
Given syntactic restrictions that guarantee safety of DRC query, can translate back to relational algebra
These are the principles behind initial development of relational databases SQL is close to calculus; query plan is close to
algebra Great example of theory leading to practice!
26
Limitations of the Relational Algebra / Calculus
Can’t do: Aggregate operations Recursive queries Complex (non-tabular) structures
Most of these are expressible in SQL, OQL, XQuery – using other special operators
Sometimes we even need the power of a Turing-complete programming language