COMP 101 Lecture Notes - Lecture 5: Relational Calculus, Relational Algebra, Procedural Programming
Chapter 5
Relational Algebra and
Relational Calculus
Chapter 5 - Objectives
Meaning of the term relational completeness.
How to form queries in relational algebra.
How to form queries in tuple relational calculus.
How to form queries in domain relational calculus.
Categories of relational DML.
Introduction
Relational algebra and relational calculus are formal languages associated with the relational
model.
Informally, relational algebra is a (high-level) procedural language and relational calculus a non-
procedural language.
However, formally both are equivalent to one another.
A language that produces a relation that can be derived using relational algebra/calculus is
relationally complete.
Relational Algebra
Relational algebra operations work on one or more relations to define another relation without
changing the original relations.
Both operands and results are relations, so output from one operation can become input to another
operation.
Allows expressions to be nested, just as in arithmetic. This property is called closure.
Relational Algebra
Five basic operations in relational algebra: Selection, Projection, Cartesian product, Union, and Set
Difference.
These perform most of the data retrieval operations needed.
Also have Join, Intersection, and Division operations, which can be expressed in terms of 5 basic
operations.
Relational Algebra Operations
R
e
l
a
t
i
o
n
a
l
A
l
g
e
b
r
a
O
p
e
r
a
t
i
o
n
s
P
r
o
j
e
c
t
i
o
n
col1, . . . , coln(R)
–Works on a single relation R and defines a relation that contains a vertical subset of R, extracting
the values of specified attributes and eliminating duplicates.
Example - Projection
Produce a list of salaries for all staff, showing only staffNo, fName, lName, and salary details.
staffNo, fName, lName, salary(Staff)
Union
R S
–Union of two relations R and S defines a relation that contains all the tuples of R, or S, or both R
and S, duplicate tuples being eliminated.
–R and S must be union-compatible.
If R and S have I and J tuples, respectively, union is obtained by concatenating them into one
relation with a maximum of (I + J) tuples.
Example - Union
List all cities where there is either a branch office or a property for rent.
city(Branch) city(PropertyForRent)
Set Difference
R – S
–Defines a relation consisting of the tuples that are in relation R, but not in S.
–R and S must be union-compatible.
Example - Set Difference
List all cities where there is a branch office but no properties for rent.
city(Branch) – city(PropertyForRent)
Intersection
R S
–Defines a relation consisting of the set of all tuples that are in both R and S.
–R and S must be union-compatible.
Expressed using basic operations:
R S = R – (R – S)
Example - Intersection
L
i
s
t
a
l
l
c
i
t
i
e
s
w
h
e
r
e
t
h
e
r
e
i
s
b
o
t
h
a
b
r
a
n
c
h
o
f
f
i
c
e
a
n
d
a
t
l
e
a
s
t
o
n
e
p
r
o
p
e
r
t
y
f
o
r
r
e
n
t
.
Document Summary
How to form queries in relational algebra. How to form queries in tuple relational calculus. How to form queries in domain relational calculus. Relational algebra and relational calculus are formal languages associated with the relational model. Informally, relational algebra is a (high-level) procedural language and relational calculus a non- procedural language. However, formally both are equivalent to one another. A language that produces a relation that can be derived using relational algebra/calculus is relationally complete. Relational algebra operations work on one or more relations to define another relation without changing the original relations. Both operands and results are relations, so output from one operation can become input to another operation. Allows expressions to be nested, just as in arithmetic. Five basic operations in relational algebra: selection, projection, cartesian product, union, and set. These perform most of the data retrieval operations needed. Also have join, intersection, and division operations, which can be expressed in terms of 5 basic operations.
