- Set functions:
- Set function
operates on a
set of values
And returns a singleton set of value
Notation:
γfunc(A)( R ) = apply function func on the arrtibute values A in the relation R
For the set function func, you can use:
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 !!!)
- Set function
operates on a
set of values
- Example:
- 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}
- Suppose relation (= table)
employee
contains the following tuples (= data):
- Important note:
- The result of
a set function is
a set (= relation)
(because every Relational Operation returns a set)
- The name of the
attribute is equal to the
name of the
set function
In other words:
- γsum(attr)(R) produces a relation with an attribute name called sum
- γmax(attr)(R) produces a relation with an attribute name called max
- γmin(attr)(R) produces a relation with an attribute name called min
- And so on
- The set result of a set function contains exactly 1 element !!!
- The result of
a set function is
a set (= relation)
- Query:
- Find the highest salary earned by employees in department number 5
Answer:
- The employee relation:
(all employees)
employee: 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
- First find the employees
in department 5:
σdno=5( employee ): fname lname dno salary ------ -------- ----------- --------- John Smith 5 30000.00 Frankl Wong 5 40000.00 Ramesh Narayan 5 38000.00 Joyce English 5 25000.00
- Find the maximum of
the "salary" attribute value in
this set:
γmax(salary)( σdno=5( employee ) ): max <=== Attribute name --------- 40000.00
- Recall that:
- The condition in:
σcondition ( R )can only contain:
- Attribute names of the relation R
- Constants
- The condition in:
- Query:
- Find the employee(s) who earn the highest salary in the company
Answer:
- employee relation:
Employee: 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
- First, we find the
highest salary paid:
M = γmax(salary)( employee )
The relation M is as follows: max <=== This is the attribute name !!! --------- 55000.00 <--- This is a set (= relation) !!!
- The cartesian product
employee × M is
as follows:
Employee × M: fname lname dno salary max ------ -------- ----------- --------- --------- John Smith 5 30000.00 55000.00 Frankl Wong 5 40000.00 55000.00 Alicia Zelaya 4 25000.00 55000.00 Jennif Wallace 4 43000.00 55000.00 Ramesh Narayan 5 38000.00 55000.00 Joyce English 5 25000.00 55000.00 Ahmad Jabbar 4 25000.00 55000.00 James Borg 1 55000.00 55000.00Therefore, the join Employee ⋈salary = max M is equal to:
Employee ⋈salary=max M: fname lname dno salary max ------ -------- ----------- --------- --------- James Borg 1 55000.00 55000.00
- The solution is
therefore:
M = γmax(salary)( employee ) πfname,lname ( Employee ⋈salary=max M )
- Note 1:
- Is the following
solution
correct ???
M = γmax(salary)( employee )
The relation M is as follows: max <=== This is the attribute name !!! --------- 55000.00 <--- This is a set (of 1 elemenet) !!!
πfname,lname ( σsalary=M ( Employee ) ) ????Answer:
- No !!!
- M is a relation name
- The condition can only have: attribute names or constants !!!
You cannot use a relation in the condition of σ !!!!
- No !!!
- Is the following
solution
correct ???
- Note 2:
- Is the following
solution
correct ???
M = γmax(salary)( employee )
The relation M is as follows: max <=== This is the attribute name !!! --------- 55000.00 <--- This is a set !!!
πfname,lname ( σsalary=max ( Employee ) ) correct ????Answer:
- No !!!
- Because max is a not an attribute in the relation Employee !!!
The attribute max is in the relation M
Since the relation M is not used in the query, you cannot use the attribute max in the query !!!
- No !!!
- Is the following
solution
correct ???