CHAPTER_SEVEN

The Relational Algebra and Relational Calculus

 INTRODUCTION
 In this chapter we discuss the two formal languages for the Relational
model: relational algebra and Relational calculus.
 The basic set of operations for the relational model is the relational
algebra.
These operations enable a user to specify basic retrieval requests as
relational algebra expressions.
The result of a retrieval is a new relation, which may have been
formed from one or more relations
The algebra operations thus produce new relations, which can be
further manipulated using operations of the same algebra.
A sequence of relational algebra operations forms a relational
algebra expression, whose result will also be a relation that represents
the result of a database query (or retrieval request).
Cont.
 The relational algebra is very important for several reasons.
First, it provides a formal foundation for relational model
operations.
Second, and perhaps more important, it is used as a basis for
implementing and optimizing queries in the query processing and
optimization modules that are integral parts of relational database
management systems (RDBMSs).
 The algebra defines a set of operations for the relational model
The relational calculus provides a higher-level declarative
language for specifying relational queries. A relational calculus
expression creates a new relation.
Cont..
In a relational calculus expression, there is no order of operations to
specify how to retrieve the query result—only what information the
result should contain.
This is the main distinguishing feature between relational algebra and
relational calculus.
The relational calculus is important because it has a firm basis in
mathematical logic and because the standard query language (SQL)
for RDBMSs has some of its foundations in a variation of relational
calculus known as the tuple relational calculus.
The relational algebra is often considered to be an integral part of the
relational data model.
Six basic operators in relational algebra:
Select σ selects a subset of tuples from reln
project π deletes unwanted columns from reln
Cartesian Product × allows to combine two relations
Set-difference − tuples in reln. 1, but not in reln. 2
Union ∪ tuples in reln 1 plus tuples in reln 2
Rename ρ renames attribute(s) and relation
Cont …..
Its operations can be divided into two groups.
One group includes set operations from mathematical set theory; these
are applicable because each relation is defined to be a set of tuples in
the formal relational model .
Set operations include UNION, INTERSECTION, SET
DIFFERENCE, and CARTESIAN PRODUCT (CROSS PRODUCT).
The other group consists of operations developed specifically for
relational databases—these include SELECT,PROJECT, and JOIN,
among others.
 UNARY RELATIONAL OPERATIONS
1. The SELECT Operation
The SELECT operation is used to choose a subset of the
tuples from a relation that satisfies a selection condition.
 The SELECT operation to restrict the tuples in a relation to
only those tuples that satisfy the condition.
 The SELECT operation can also be visualized as a
horizontal partition of the relation into two sets of tuples—
those tuples that satisfy the condition and are selected, and
those tuples that do not satisfy the condition and are
discarded.
Cont ….
For example, to select the EMPLOYEE tuples whose department is 4,
or those whose salary is greater than $30,000, we can individually
specify each of these two conditions with a SELECT operation as
follows:
σ Dno=4(EMPLOYEE)
σ Salary>30000(EMPLOYEE)
In general, SELECT operation is denoted by
σ<selection condition>(R)
where the symbol σ (sigma) is used to denote the SELECT operator
and the selection condition is a Boolean expression (condition)
specified on the attributes of relation R.
The Boolean expression specified in <selection condition> is made up
of a number of clauses of the form <attribute name> <comparison
op> <constant value> or
<attribute name> <comparison op> <attribute name>
Cont …..
 Clauses can be connected by the standard Boolean operators and, or, and
not to form a general selection condition.
For example, to select the tuples for all employees who either work in
department 4 and make over $25,000 per year, or work in department 5 and
make over $30,000, we can specify the following SELECT operation:
σ(Dno=4 AND Salary>25000) OR (Dno=5 AND
Salary>30000)(EMPLOYEE)
Notice that all the comparison operators in the set {=, <, ≤, >, ≥, ≠} can
apply to attributes whose domains are ordered values, such as numeric or
date domains.
 If the domain of an attribute is a set of unordered values, then only the
comparison operators in the set {=, ≠} can be used. An example of an
unordered domain is the domain Color = { ‘red’, ‘blue’, ‘green’, ‘white’,
‘yellow’, ...}, where no order is specified among the various colors
Cont …
Properties
 The SELECT operator is unary; that is, it is applied to a single
relation.
 is commutative.
The PROJECT Operation
selects certain columns from the table and discards the other columns.
If we are interested in only certain attributes of a relation, we use the
PROJECT operation to project the relation over these attributes only.
Therefore, the result of the PROJECT operation can be visualized as a
vertical partition of the relation into two relations
one has the needed columns (attributes) and contains the result of the
operation, and the other contains the discarded columns.
EXAMPLES
Cont …
For example, to list each employee’s first and last name and
salary, we can use the PROJECT operation as follows
π Lname, Fname, Salary(EMPLOYEE)
 The general form of the PROJECT operation is
π <attribute list>(R)
The PROJECT operation removes any duplicate tuples, so
the result of the PROJECT operation is a set of distinct
tuples, and hence a valid relation.
This is known as duplicate elimination.
 For example, consider the following PROJECT operation:
π Sex, Salary(EMPLOYEE)
 Commutatively does not hold on PROJECT.
Sequences of Operations and the RENAME Operation
we can apply one operation at a time and create intermediate
result relations.
we must give names to the relations that hold the intermediate
results. For example, to retrieve the first name, last name, and
salary of all employees who work in department number 5, we
must apply a SELECT and a PROJECT operation.
We can write a single relational algebra expression, also known
as an in-line expression, as follows:
πFname, Lname, Salary(σDno=5(EMPLOYEE))
 To rename the attributes in a relation, we simply list the new
attribute names in parentheses, as in the following example:
TEMP ← σDno=5(EMPLOYEE)
R(First_name, Last_name, Salary) ← πFname, Lname, Salary(TEMP).
Cont….
The general RENAME operation when applied to a relation R of
degree n is denoted by any of the following three forms:
ρS(B1, B2, ..., Bn)(R) or ρS(R) or ρ(B1, B2, ..., Bn)(R)
where the symbol ρ (rho) is used to denote the RENAME operator, S
is the new relation name, and B1, B2, ..., Bn are the new attribute
names.
 In SQL, a single query typically represents a complex relational
algebra expression.
Renaming in SQL is accomplished by aliasing using AS, as in the
following example:
SELECT E.Fname AS First_name, E.Lname AS Last_name,
E.Salary AS Salary
FROM EMPLOYEE AS E
WHERE E.Dno=5,
RELATIONAL ALGEBRA OPERATION FROM SET THEORY
The UNION, INTERSECTION, and MINUS Operations
algebra operations are the standard mathematical operations on sets.
 These are binary operations; that is each is applied to two sets
(of tuples).
 Two relations R(A1, A2, ..., An) and S(B1, B2, ..., Bn) are said to be union
compatible (or type compatible) if they have the same degree n and if
dom(Ai) = dom(Bi) for 1 f i f n.
This means that the two relations have the same number of attributes and
each corresponding pair of attributes has the same domain.
UNION: The result of this operation, denoted by R ∪ S, is a relation that
includes all tuples that are either in R or in S or in both R and S.
Duplicate tuples are eliminated
INTERSECTION: The result of this operation, denoted by R ∩ S, is a
relation that includes all tuples that are in both R and S.
SET DIFFERENCE (or MINUS): The result of this operation, denoted
by R – S, is a relation that includes all tuples that are in R but not in S.
Notice that both UNION and INTERSECTION are commutative
operations; that is,
R ∪ S = S ∪ R and R ∩ S = S ∩ R
 Both UNION and INTERSECTION can be treated as n-ary operations
applicable to any number of relations because both are also associative
operations; that is,
R ∪ (S ∪ T ) = (R ∪ S) ∪ T and (R ∩ S ) ∩ T = R ∩ (S ∩ T )
 The MINUS operation is not commutative; that is, in general,
R − S ≠ S − R.
 example consider the following student and teacher table
Example 1. from the table student and instructor?
(a) STUDENT table b) Instructor table

Fn Lname fn lname

Susan yao john smiz

Ramesh shah josy kun
Johnny kohler albert sun
Barbara jones www kkkk
Amy ford
Jimmy wang
Ernest Gilibert
con,…

WHAT WILL BE THE RESULT???

1. STUDENT ∪ INSTRUCTOR
2. STUDENT ꓵ INSTRUCTOR
3. STUDENT − INSTRUCTOR
4. INSTRUCTOR − STUDENT.
Example 2. from the relation r and s
The CARTESIAN PRODUCT (CROSS PRODUCT) Operation
 is also a binary set operation, but the relations on which it is applied
do not have to be union compatible.
 In its binary form, this set operation produces a new element by
combining every member (tuple) from one relation (set) with every
member (tuple) from the other relation (set).
In general, the result of R(A1, A2, ..., An) × S(B1, B2, ..., Bm) is a
relation Q with degree n + m attributes Q(A1, A2, ..., An, B1, B2, ...,
Bm), in that order.
The resulting relation Q has one tuple for each combination of
tuples—one from R and one from S.
 if R has n R tuples (denoted as |R| = nR), and S has nS tuples,
then R × S will have nR * nS tuples.
Cont …
The CARTESIAN PRODUCT creates tuples with the combined
attributes of two relations. We can SELECT related tuples only
from the two relations by specifying an appropriate selection
condition after the Cartesian product.
 In SQL, CARTESIAN PRODUCT can be realized by using the
CROSS JOIN option in joined tables . Alternatively, if there are
two tables in the WHERE clause and there is no corresponding
join condition in the query, the result will also be the
CARTESIAN PRODUCT of the two tables.
Example 1.
For example, suppose that we want to retrieve a list of names
of each female employee’s dependents. We can do this as
follows:

FEMALE_EMPS ← σSex=‘F’(EMPLOYEE)
EMPNAMES ← π Fname, Lname, Ssn(FEMALE_EMPS)
EMP_DEPENDENTS ← EMPNAMES × DEPENDENT
ACTUAL_DEPENDENTS ←
σSsn=Essn(EMP_DEPENDENTS)
RESULT ← πFname , Lname,
Dependent_name(ACTUAL_DEPENDENTS)
Cont.
The EMP_DEPENDENTS relation is the result of applying
the CARTESIAN PRODUCT operation to EMPNAMES .
 In EMP_DEPENDENTS, every tuple from EMPNAMES is
combined with every tuple from DEPENDENT, giving a
result that is not very meaningful (every dependent is
combined with every female employee).
We want to combine a female employee tuple only with her
particular dependents—namely, the DEPENDENT tuples
whose Essn value match the Ssn value of the EMPLOYEE
tuple.
The ACTUAL_DEPENDENTS relation accomplishes this.
Example 2. given the relations r and s
END OF CLASS DAY

THANK U ALL !!!