Relational Calculus
CS 186, Spring 2007,
Lecture 6
R&G, Chapter 4
Mary Roth
We will occasionally use this
arrow notation unless there
is danger of no confusion.
Ronald Graham
Elements of Ramsey Theory
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Guidelines and tips
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Community
Ask the community
Ask the community for help and clear up your study doubts
University Rankings
Discover the best universities in your country according to Docsity users
Free resources
Our save-the-student-ebooks!
Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors
Relational Calculus: A Formal Foundation for Query-by-Example in CS 186, Summaries of Calculus
A set of lecture notes from a Computer Science course on Relational Calculus. The notes cover the basics of relational calculus, its advantages over relational algebra, and additional operations such as intersection, join, and division. The document also includes examples of relational calculus queries and their optimization.
What you will learn
- What is the difference between relational algebra and relational calculus?
- What are the advantages of using relational calculus over relational algebra?
- How do you perform selection, projection, and join operations in relational calculus?
- How do you optimize relational calculus queries?
- What is the difference between tuple relational calculus and domain relational calculus?
Typology: Summaries
2020/2021
1 / 29
This page cannot be seen from the preview
Don't miss anything!
Related documents
Partial preview of the text
Download Relational Calculus: A Formal Foundation for Query-by-Example in CS 186 and more Summaries Calculus in PDF only on Docsity!
Relational Calculus
CS 186, Spring 2007,
Lecture 6
R&G, Chapter 4
Mary Roth
We will occasionally use this
arrow notation unless there
s danger of no confusion.
Ronald Graham
Elements of Ramsey Theory
Administrivia
Homework 1 due in 1 week
Thursday, Feb 8 10 p.m.
New syllabus on web site
Questions?
Review: Where have we been?
Query Optimization
and Execution
Relational Operators
Files and Access Methods
Buffer Management
Disk Space Management
DB
Practice
Theory
Lecture
Relational Algebra
Relational Model
Lecture
Lectures 3
Review: Where have we been?
Where are we going next?
Query Optimization
and Execution
Relational Operators
Files and Access Methods
Buffer Management
Disk Space Management
DB
Practice
Theory
Lecture
Relational Algebra
Relational Model
Lecture
Lectures 3
Relational Calculus
Today
Review – Why do we need Query
Languages anyway?
Two key advantages
Less work for user asking query
- More opportunities for optimization
Relational Algebra
Theoretical foundation for SQL
Higher level than programming language
- but still must specify steps to get desired result
Relational Calculus
- Formal foundation for Query-by-Example
A first-order logic description of desired result
Only specify desired result, not how to get it
Additional operations:
- Intersection ()
- Join ( )
- Division ( / )
Relational Algebra Review
sid sname rating age
22 dustin 7 45.
31 lubber 8 55.
58 rusty 10 35.
bid bname color
101 Interlake Blue
102 Interlake Red
103 Clipper Green
104 Marine Red
sid bid day
22 101 10/10/
58 103 11/12/
Reserves Sailors Boats
Basic operations:
- Selection ( σ )
- Projection ( π )
- Cross-product ( )
- Set-difference ( — )
- Union ( )
:tuples in both relations.
: like but only keep tuples where common fields are equal.
:tuples from relation 1 with matches in relation 2
: gives a subset of rows.
: deletes unwanted columns.
: combine two relations.
: tuples in relation 1, but not 2
: tuples in relation 1 and 2.
Query Optimization
and Execution
Relational Operators
Files and Access Methods
Buffer Management
Disk Space Management
DB
Prediction: These
relational operators
are going to look
hauntingly familiar
when we get to
them…!
Relational Algebra Review
sid sname rating age
22 dustin 7 45.
31 lubber 8 55.
58 rusty 10 35.
bid bname color
101 Interlake Blue
102 Interlake Red
103 Clipper Green
104 Marine Red
sid bid day
22 101 10/10/
58 103 11/12/
Reserves Sailors Boats
Find names of sailors who’ve reserved a green boat
Given the previous algebra, a query optimizer would replace it with this
σ(
color=‘Green’
Boats)
( Sailors)
sname
( Reserves)
bid
sid
Or better yet:
Intermission
Some algebra exercises for you to practice with
are out on the class web site
Algebra and calculus exercises make for good
exam questions!
Relational Calculus Building
Blocks
Variables
TRC : Variables are bound to tuples.
DRC : Variables are bound to domain elements (= column
values)
Constants
7, “Foo”, 3.14159, etc.
Comparison operators
=, <>, <, >, etc.
Logical connectives
- not
– and
- or
- implies
- is a member of
Quantifiers
X(p(X)): For every X, p(X) must be true
X(p(X)): There exists at least one X such that p(X) is true
Relational Calculus
English example: Find all sailors with a rating above 7
Tuple R.C.:
{S |S Sailors S.rating > 7}
“ From all that is, find me the set of things that are tuples in the
Sailors relation and whose rating field is greater than 7.”
Domain R.C.:
{<S,N,R,A>| <S,N,R,A> Sailors R > 7}
“From all that is, find me column values S, N, R, and A, where S
is an integer, N is a string, R is an integer, A is a floating
point number, such that <S, N, R, A> is a tuple in the Sailors
relation and R is greater than 7.”
sid sname rating age
28 yuppy 9 35.
31 lubber 8 55.
44 guppy 5 35.
58 rusty 10 35.
An atomic formula is one of the following:
R Rel
R.a op S.b
R.a op constant, where
op is one of
A formula can be:
- an atomic formula
- where p and q are formulas
- where variable R is a tuple variable
- where variable R is a tuple variable
TRC Formulas
, , , , ,
p , p q , p q
R ( p ( R ) )
R ( p ( R ) )
Free and Bound Variables
The use of quantifiers X and X in a formula is
said to bind X in the formula.
A variable that is not bound is free.
Important restriction
{ T | p ( T )}
The variable T that appears to the left of `|’ must
be the only free variable in the formula p ( T ).
In other words, all other tuple variables must be
bound using a quantifier.
a b is the same as a
b
If a is true, b must be
true!
If a is true and b is
false, the
expression
evaluates to false.
If a is not true, we
don’t care about b
The expression is
always true.
a
T
F
T F
b
T
T T
F
Quantifier Shortcuts
x ((x Boats) (x.color = “Red”))
“For every x in the Boats relation, the color must be
Red.”
Can also be written as:
x Boats(x.color = “Red”)
x ( (x Boats) (x.color = “Red”))
“There exists a tuple x in the Boats relation
whose color is Red.”
Can also be written as:
x Boats (x.color = “Red”)