Instructor: Churee Techawut

1
Relational Algebra
Chapter 6

• Instructor Churee Techawut

CS (204)321 Database System I
2
Outlines

• 1) Unary relational operations SELECT and
  PROJECT
• 2) Relational algebra operations from set theory
• 3) Binary relational operations JOIN and
  DIVISION
• 4) Additional relational operations
• 5) Examples of queries in relational algebra

3
Unary relational operations SELECT

• The SELECT operation is used to select a subset
  of the tuples from a relation that satisfy a
  selection condition.

• Sigma s is used to denote the SELECT operator,
  and the selection condition is a Boolean
  expression specified on the attributes of
  relation R.

• The result of relational operation is a relation.

• The degree of the relation resulting from a
  SELECT operation is the same as that of R.

• The number of tuples in the resulting relation
  is always less than or equal to the number of
  tuples in R.

4
Unary relational operations SELECT

• Clauses of selection condition

ltattribute namegtltcomparison opgtltconstant valuegt
ltattribute namegtltcomparison opgtltattribute namegt

• Boolean operators AND, OR, NOT can also be used
  to form a selection condition

5
Unary relational operations SELECT

• EX1 select the EMPLOYEE tuples whose department
  is 4.

• EX2 select the EMPLOYEE tuples whose salary is
  greater than 30,000.

• EX3 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 .

6
Unary relational operations PROJECT

• The PROJECT operation is used to project the
  relation over certain attributes of a relation.

• Pi p is used to present the PROJECT operator.

• The result of the PROJECT operation has only the
  attributes specified in ltattribute listgt and in
  the same order as they appear in the list.

• The degree of the relation that is equal to the
  number of attributes in ltattribute listgt.

• Duplicate elimination if the attribute list
  includes only nonkey attributes of R.

7
Unary relational operations PROJECT

• EX4 list each employees last and first name
  and salary.

• EX5 list each employees sex and salary.

8
Sequences of operations and RENAME operation

• Two ways to write the relational algebra

(1) Write single relational algebra expression by
nesting the operations, or
(2) Apply one operation at a time and create
intermediate result relations.

• EX6 to retrieve the first name, last name, and
  salary of all employees who work in department
  number 5.

9
Sequences of operations and RENAME operation

• RENAME operation can be used to rename the
  attributes in the intermediate and result
  relations.

• EX7 from EX1 with RENAME operation

10
Sequences of operations and RENAME operation

• Rho ? is used to present the RENAME operator.

-------- (1)
-------- (2)
-------- (3)
(1) Rename both resulting relation name and
attribute names
(2) Rename only resulting relation name
(3) Rename only attribute names
11
Relational algebra operations from set theory

• Use standard mathematical on sets and on two
  union-compatible relations R and S

• UNION

Combine tuples in R and S and then eliminate
duplicate tuples.

• INTERSECTION

Include all tuples that are in both R and S.

• MINUS or DIFFERENCE

Include all tuples that are in R but not in S.
12
Relational algebra operations from set theory

• Rules for using these operators

• Commutative operations

• Associative operations

• Note DIFFERENCE operation is not commutative

13
Relational algebra operations from set theory

• Another set theoretic operation is CARTESIAN
  PRODUCT or CROSS PRODUCT.

• Relations on which it is applied do not have to
  be union compatible.

• If R has nR tuples and S has nS tuples, then RxS
  will have nR nS tuples.

14
Relational algebra operations from set theory

• The operation is useful when followed by a
  selection that matches values of attributes
  coming from the component relations.

• EX8 to retrieve for each female employee a list
  of the names of her dependents

15
Binary relational operations JOIN

• The JOIN operation, denoted by

,is used to combine
related tuples from two relations into single
tuples.

• The general form of a JOIN operation on two
  relations is

• A general join condition is in the form

16
Binary relational operations JOIN

• The main difference between CARTESIAN PRODUCT
  and JOIN

17
Binary relational operations JOIN

• Three kinds of JOIN operations

18
Binary relational operations DIVISION

• The DIVISION operation is denoted as following

19
Binary relational operations DIVISION
A a1 a2 a3 a4 a1 a3 a2 a3 a4 a1 a2 a3
R
B b1 b1 b1 b1 b2 b2 b3 b3 b3 b4 b4 b4
S
A a1 a2 a3
20
Additional relational operations AGGREGATE
FUNCTION and GROUPING

• Cannot be perform with the basic relational
  algebra operations previously described.

• Two ways of applications

(2) Use GROUPING to group tuples in a relation by
the value of some of their attributes and then
applying an AGGREGATE FUNCTIONS independently to
each group.
21
Additional relational operations AGGREGATE
FUNCTION and GROUPING

• Script F, , is used to present
  AGGREGATE FUNCTION operation.

is a list of attributes of the relation specified
in R.
is a list of
pairs.
are such as SUM, AVERAGE, MAXIMUM, MINIMUM,COUNT.
is an attribute of the relation specified in R.

• The resulting relation has the grouping
  attributes plus one attribute for each element in
  the function list.

22
Additional relational operations AGGREGATE
FUNCTION and GROUPING

• EX10 group employee tuples by DNO, and list the
  number of employees in the department, and their
  average salary.

23
Examples of queries in relational algebra

• Query1 Retrieve the name and address of all
  employees who work for the Research department.

Note The operation order is SELECT JOIN -
PROJECT

• Query2 Find the names of employees who work on
  all the projects controlled by department number
  5.

• Query3 List the names of employees who have at
  least one dependent.