Set (or aggregate) functions
- Set functions:
- Notation of
set function:
γfunc(A)(R) ≡ apply function func on the attr values A in the relation R
|
|
The Set Functions
in Relational Algebra
- Relational Algebra
has the following
set functions:
sum = returns the sum of a set of value
avg = returns the average of a set of value
max = returns the maximum of a set of values
min = returns the maximum of a set of values
count = returns the cardinality of a set (= # elements)
any = returns false if the set is empty, otherwise returns true
|
-
Important note:
- The
return value of a
set function is
always a
set !!!
Recall:
- Every operation in
Relational Algebra operates on
sets and
returns a
set as
result !!!
|
|
|
Examples of Set Functions
- Suppose relation (= table)
employee
contains the following tuples (= data):
employee relation:
+--------+---------+------+----------+-----+
| fname | lname | sex | salary | dno |
+--------+---------+------+----------+-----+
| John | Smith | M | 30000.00 | 5 |
| Frankl | Wong | M | 40000.00 | 5 |
| Alicia | Zelaya | F | 20000.00 | 4 |
| Jennif | Wallace | F | 50000.00 | 4 |
+--------+---------+------+----------+-----+
|
Then:
- γsum(salary)(employee)
=
{140000}
- γavg(salary)(employee)
=
{35000}
- γmax(salary)(employee)
=
{50000}
- γmin(salary)(employee)
=
{20000}
- γcount( * )(employee)
=
{4}
( *
means: a tuple)
- γany( * )(employee)
=
{true}
|
|
How to use set functions
- Query:
- Find the
highest salary earned
by employees in
department number 5
|
Solution:
Sample employee relation: (to make solution concrete)
fname lname dno salary
------ -------- ----------- ---------
John Smith 5 30000.00
Frankl Wong 5 40000.00
Alicia Zelaya 4 25000.00
Jennif Wallace 4 43000.00
Ramesh Narayan 5 38000.00
Joyce English 5 25000.00
Ahmad Jabbar 4 25000.00
James Borg 1 55000.00
|
|
How to
use set functions
in a relational algebra
query
- Query:
- Find the employee(s) who
earn the
highest salary in the
company
|
Solution:
Sample employee relation: (to make solution concrete)
fname lname dno salary
------ -------- ----------- ---------
John Smith 5 30000.00
Frankl Wong 5 40000.00
Alicia Zelaya 4 25000.00
Jennif Wallace 4 43000.00
Ramesh Narayan 5 38000.00
Joyce English 5 25000.00
Ahmad Jabbar 4 25000.00
James Borg 1 55000.00
|
|
How to
use set functions
in a relational algebra
query
How to
use set functions
in a relational algebra
query
❮
❯
