Relational Algebra and Relational Calculus: Lecture Four

Lecture Four
Relational Algebra and

Relational Calculus
Based on Chapter Four of this book:
Database Systems: A Practical Approach

to Design, Implementation and
Management
International Computer Science S.
Carolyn Begg, Thomas Connolly

Lecture4Objectives
Meaningofthetermrelationalcompleteness.
Howtoformqueriesinrelationalalgebra.
CategoriesofrelationalDML.
2
Introduction
Relationalalgebraandrelationalcalculusareformal
languagesassociatedwiththerelationalmodel.
Informally,relationalalgebraisa(highlevel)
procedurallanguageandrelationalcalculusanon
procedurallanguage.
However,formallybothareequivalenttooneanother.
Alanguagethatcanproduceanyrelationthatcanbe
derivedusingrelationalcalculusisrelationally
complete.
3
RelationalAlgebra
Relationalalgebraoperationsworkononeor
morerelationstodefineanotherrelationwithout
changingtheoriginalrelations.
Bothoperandsandresultsarerelations,sooutput
fromoneoperationcanbecomeinputtoanother
operation.
Allowsexpressionstobenested,justasin
arithmetic.Thispropertyiscalledclosure.
4
RelationalAlgebra
5basicoperationsinrelationalalgebra:Selection,
Projection,Cartesianproduct,Union,andSet
Difference.
Theseperformmostofthedataretrieval
operationsneeded.
AlsohaveJoin,Intersection,andDivision
operations,whichcanbeexpressedintermsof5
basicoperations.
5
Relational Algebra Operations
6
Relational Algebra Operations
7
Selection (or Restriction)
predicate (R)
Works on a single relation R and defines a
relation that contains only those tuples (rows) of
R that satisfy the specified condition (predicate).
8
Example - Selection (or Restriction)
List all staff with a salary greater than 10,000.
salary > 10000 (Staff)
9
Projection
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.
10
Example - Projection
Produce a list of salaries for all staff, showing only
staffNo, fName, lName, and salary details.
staffNo, fName, lName, salary(Staff)
11
Union
RS
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.
12
Example - Union
List all cities where there is either a branch office
or a property for rent.
city(Branch) city(PropertyForRent)
13
Set Difference
RS
Defines a relation consisting of the tuples that
are in relation R, but not in S.
14
Example - Set Difference
List all cities where there is a branch office but no
properties for rent.
15
Intersection
RS
Defines a relation consisting of the set of all
tuples that are in both R and S.
Expressed using basic operations:

R S = R (R S)
16
Example - Intersection
List all cities where there is both a branch office
and at least one property for rent.
17
Cartesian product
RXS
Defines a relation that is the concatenation of
every tuple of relation R with every tuple of
relation S.
18
Example - Cartesian Product
List the names and comments of all clients who have
viewed a property for rent.
( clientNo, fName, lName(Client)) X ( clientNo, propertyNo,comment
(Viewing))
19
Example - Cartesian Product and Selection
Useselectionoperationtoextractthosetupleswhere
Client.clientNo=Viewing.clientNo.
Client.clientNo=viewing.clientNo(( clientNo,fName,lName(Client))
( clientNo,propertyNo,comment(Viewing)))
Cartesian product and Selection can be reduced to a single

operationcalledaJoin.
20
Join Operations
Join is a derivative of Cartesian product.
Equivalent to performing a Selection, using join

predicate as selection formula, over Cartesian
product of the two operand relations.
One of the most difficult operations to implement

efficiently in an RDBMS and one reason why
RDBMSs have intrinsic performance problems.
21
Join Operations
Various forms of join operation
Theta join
Equijoin (a particular type of Theta join)
Natural join
Outer join
Semijoin
22
Theta join (-join)
R FS
Defines a relation that contains tuples

satisfying the predicate F from the Cartesian
product of R and S.
The predicate F is of the form R.ai S.bi
where may be one of the comparison
operators (<, , >, , =, ).
23
Theta join (-join)
Can rewrite Theta join using basic Selection and
Cartesian product operations.
R S = F(R S)
F
Degree of a Theta join is sum of degrees of the

operand relations R and S. If predicate F contains
only equality (=), the term Equijoin is used.
24
Example - Equijoin
List the names and comments of all clients who
have viewed a property for rent.
( clientNo,fName,lName(Client)) Client.clientNo = Viewing.clientNo
( clientNo,propertyNo,comment(Viewing))
25
Natural Join
R S
An Equijoin of the two relations R and S over all
common attributes x. One occurrence of each
common attribute is eliminated from the result.
26
Example - Natural Join
List the names and comments of all clients who
have viewed a property for rent.
( clientNo,fName,lName(Client))
( clientNo,propertyNo,comment(Viewing))
27
Outer join
Todisplayrowsintheresultthatdonothave
matchingvaluesinthejoincolumn,useOuter
join.
R S
(Left) outer join is join in which tuples from
R that do not have matching values in
common columns of S are also included in
result relation.
28
Example - Left Outer join
Produce a status report on property viewings.
propertyNo,street,city(PropertyForRent) Viewing
29
Other Languages
Transform-oriented languages are non-procedural
languages that use relations to transform input
data into required outputs (e.g. SQL).
Graphical languages provide user with picture of

the structure of the relation. User fills in example
of what is wanted and system returns required
data in that format (e.g. QBE).
30
Other Languages
4GLscancreatecompletecustomizedapplication
usinglimitedsetofcommandsinauserfriendly,
oftenmenudrivenenvironment.
Somesystemsacceptaformofnaturallanguage,
sometimescalleda5GL,althoughthis
developmentisstillaanearlystage.
31

Relational Algebra and Relational Calculus: Lecture Four

Uploaded by

Copyright:

Available Formats

Relational Algebra and Relational Calculus: Lecture Four

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Relational Algebra and Relational Calculus: Lecture Four

Uploaded by

Copyright:

Available Formats

Lecture Four

Relational Algebra and

Based on Chapter Four of this book:

Database Systems: A Practical Approach

International Computer Science S.

Carolyn Begg, Thomas Connolly

salary > 10000 (Staff)

staffNo, fName, lName, salary(Staff)

If R and S have I and J tuples, respectively, union

Expressed using basic operations:

Cartesian product and Selection can be reduced to a single

Equivalent to performing a Selection, using join

One of the most difficult operations to implement

Defines a relation that contains tuples

Degree of a Theta join is sum of degrees of the

Graphical languages provide user with picture of

You might also like