Relational Algebra

• The relational algebra is a procedural query

language
• It consists of a set of operations that take one or
two relations as input and produce a new relation
as their result.
• These operations enable a user to specify basic
retrieval requests (or queries)
Cont.
• The fundamental operations in the relational
algebra are select, project, union, set difference,
Cartesian product, and rename

• The select, project, and rename operations are

called unary operations, because they operate on
one relation

• The other three operations operate on pairs of

relations and are, therefore, called binary
operations
Unary Relational Operations
 SELECT (symbol:  (sigma))
 Selects a subset of rows from relation

 PROJECT (symbol:  (pi))

 Selects columns from relation

 RENAME (symbol:  (rho))

SELECTION
 Selection
S2
sid sname rating age
28 yuppy 9 35.0  rating  (S2)
31 lubber 8 55.5 8
44 guppy 5 35.0
58 rusty 10 35.0

sid sname rating age

28 yuppy 9 35.0
58 rusty 10 35.0
 EXAMPLE
• Select the EMPLOYEE tuples whose department number is
4:
 DNO = 4 (EMPLOYEE)
• Select the employee tuples whose salary is greater than
$30,000:
 SALARY > 30,000 (EMPLOYEE)

• Select the instructors in Physics with a salary greater

than $90,000, (and (∧), or (∨), and not (￢))

 dept name =“Physics” ∧ salary>90000 (instructor)

PROJECTION
 EXAMPLE
• Example: To list each employee’s first and last
name and salary, the following is used:
LNAME, FNAME,SALARY(EMPLOYEE)
Composition of Relational Operations
 Example
• “Find the name of all instructors in the
Physics department.
 name ( dept name =“Physics”
(instructor))
 Rename
• Name conflict can arise in some
situations
• It is convenient to be able to give names to
the fields of a relation instance defined by a
relational algebra expression.
(R(F),E)
•Take arbitrary relational expression E
• Returns an instance of a new relation R
• R is the result of E except that some fields are renamed
• Renaming list has the form (oldname  newname or position  newname)
 RENAME OPERATION
• The general RENAME operation  can be
expressed by any of the following forms:
– S(R) changes:
• the relation name only to S
– (B1, B2, …, Bn )(R) changes:
• the column (attribute) names only to B1, B1,
…..Bn
– S (B1, B2, …, Bn )(R) changes both:
• the relation name to S, and
• the column (attribute) names to B1, B1, …..Bn
 Rename Example
(C(1sid1,5sid2),S1R1)
(sid) s n a m e rating age (sid) bid da y
22 dustin 7 45.0 22 101 1 0 /1 0/9 6
22 dustin 7 45.0 58 103 1 1 /1 2/9 6
31 lubber 8 55.5 22 101 1 0 /1 0/9 6
31 lubber 8 55.5 58 103 1 1 /1 2/9 6
58 rusty 10 35.0 22 101 1 0 /1 0/9 6
58 rusty 10 35.0 58 103 1 1 /1 2/9 6

sid1 sname rating age sid2 bid day

22 dustin 7 45.0 22 101 10/10/96
22 dustin 7 45.0 58 103 11/12/96
31 lubber 8 55.5 22 101 10/10/96
31 lubber 8 55.5 58 103 11/12/96
58 rusty 10 35.0 22 101 10/10/96
58 rusty 10 35.0 58 103 11/12/96
 Relational Algebra Operations
Set Theory
• Union
• Intersection
• Set Difference / Minus
• Cartesian Product
 UNION
• It is a Binary operation, denoted by 

– The result of 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.

 UNION
– The two operand relations R and S mustbe
“type compatible” (or UNION compatible)
• Two relations are union compatible if
– Relation R and S should have same arity, Both
have same number of columns
– Names of attributes and the domain type are
the same in both
– corresponding fields, taken in order from left
to right, have the same domains
 Example
Tables:
Person (SSN, Name, Address, Hobby)
Professor (Id, Name, Office, Phone)
are not union compatible. However

 Name (Person) and  Name (Professor)

are union compatible and

15
 INTERSECTION
• INTERSECTION is denoted by 

• The result of the operation R  S, is a relation

that includes all tuples that are in both R and
S
– The attribute names in the result will be the
same as the attribute names in R

• The two operand relations R and S must be

“type compatible
• Consider a query to find the set of all courses
taught in the Fall 2009 semester, the Spring
2010 semester, or both

• To find the set of all courses taught in the

particular year, we
• We can find all the courses taught in the Fall
2009 semester but not in Spring 2010
semester by writing:
 Cartesian-Product Operation
• Denoted by x
• Also known as product or cross-join
• Notation r x s
• Combines tuples members of one relation to
other relation
• R (a1, a2 … an) x S(b1, b2 … bn)
– T (a1, a2 … an, b1, b2 … bn)
• If we need the all borrowers and loan holder
in chennai
•  branch = “chennai” (borrower x loan)
Cartesian-Product Operation
 Relations r, s:

 r x s:
JOIN
 The JOIN operation is denoted by the R|X|S symbol
and is used to compound similar tuples from two
Relations into single longer tuples.
 Join operation is generally the cross product of two
relation.
 The notation used is

 R JOIN join condition S

TYPES OF JOIN

 Equi Join
 Condition Join

 Natural Join

 Outer Join
EQUI JOIN
 For whatever JOIN type (INNER, OUTER, etc), if
we use ONLY the equality operator (=), then we say
that the JOIN is an EQUI JOIN
EQUI JOIN Example
 Condition Join
 This is same as EQUI JOIN but it allows all other
R c S   c (R S)
operators like >, <, >= etc.

Example: S1  S1.sid  R1.sid R1

(sid) s n a m e rating age (sid) bid da y
22 dustin 7 45.0 22 101 1 0 /1 0/9 6
22 dustin 7 45.0 58 103 1 1 /1 2/9 6
31 lubber 8 55.5 22 101 1 0 /1 0/9 6
31 lubber 8 55.5 58 103 1 1 /1 2/9 6
58 rusty 10 35.0 22 101 1 0 /1 0/9 6
58 rusty 10 35.0 58 103 1 1 /1 2/9 6

(sid) sname rating age (sid) bid day

22 dustin 7 45.0 58 103 11/12/96
31 lubber 8 55.5 58 103 11/12/96
NATURAL JOIN
 The JOIN involves an equality test, and thus is
often described as an equi-join.
 Such joins result in two attributes in the resulting
relation having exactly the same value.
 A natural join will remove the duplicate attributes.
EXAMPLE
 OUTER JOIN
There are three forms of the outer join, depending on
which data is to be kept.
 LEFT OUTER JOIN - keep data from the left-hand
table and if there are no columns matching in the
right table, it returns NULL values.
 RIGHT OUTER JOIN - keep data from the right- hand
table and If there are no columns matching in the left
table, it returns NULL values.
 FULL OUTER JOIN - keep data from both tables
and it returns row from either table when the
conditions are met and returns NULL value when
there is no match.
Select * from Customers C LEFT
OUTER JOIN Orders O ON
O.CustomerId = C.CustomerId
Select * from Customers C RIGHT
OUTER JOIN Orders O ON
O.CustomerId = C.CustomerId
Select * from Customers C FULL
OUTER JOIN Orders O ON
O.CustomerId = C.CustomerId
 Division

• Suppose A has two groups of fields <x,y>

• y fields are same fields in terms of
domain as B
• A/B = <x> such as for every y value in a
tuple of B there is <x,y> in A.
 Division
sno pno
s1 p1 pno pno pno
s1 p2 p2 p2 p1
s1 p3 B1 p4 p2
s1 p4 B2 p4
s2 p1
sno B3
s2 p2 s1
s3 p2 s2 sno
s4 p2 s3 s1 sno
s4 p4 s4 s4 s1
A/B2 A/B3
A/B1
 Examples
Sailors Reserves Boats
sid snam e ratin age

Q1. Find the names of sailors who have reserved boat 103

Solution 1:  sname(( bid 103 Reserves) Sailors)

Solution 2:  sname( bid  (Reserves Sailors))

103
 Examples
Sailors Reserves Boats
sid snam e ratin age

Q2: Find the names of sailors who have reserved a red boat.
Sol1:  sname(( color ' red ' Boats)  Reserves Sailors)

Sol2:  sname( sid(( bid  color ' red ' Boats)  Res) Sailors)
 Examples
Sailors Reserves Boats
sid snam e ratin age

Q3: Find the colors of boats reserved by Lubber.

 color((sname'Lubber'Sailors) ReservesBoats)
 Examples
Sailors Reserves Boats
sid snam e ratin age

Q5. Fine the names of sailors who reserved a red or a green boat.
 (Tempboats, ( color ' red '  color ' green ' Boats))

 sname(Tempboats Reserves Sailors)