### Relational Algebra: Set Functions

• Set Functions

• Set functions:

 Set function operates on a set of values And returns a singleton set of value

• To illustrate the various set function, we will apply the set functions on this set of value:

A = {1, 4, 9}

• The set functions in Relational Algebra are:

• sum(A) = returns the sum of all values of A

 sum(A) = {14}

• avg(A) = returns the average of all values in A

 avg(A) = {4.6666}

• max(A) = returns the maximum value of all values in A

 max(A) = {9}

• min(A) = returns the minimum value of all values in A

 min(A) = {1}

• any(A) = returns true if set A is not empty; otherwise, returns false

 any(A) = {TRUE}

• count(A) = returns the cardinality of set A

 count(A) = {3} (= number of elements in set A))

• 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( ... ) produces a relation with an attribute name called sum max( ... ) produces a relation with an attribute name called max min( ... ) produces a relation with an attribute name called min And so on

• The set result of a set function contains exactly 1 element !!!

• Simple query using set functions

• Query:

 Find the highest salary earned (by employees) in the company

• Sample input 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 find the set of salary figures:

 ``` πsalary( employee ): salary --------- 30000.00 40000.00 25000.00 43000.00 38000.00 25000.00 25000.00 55000.00 ```

• Find the maximum value in this set:

 ``` max ( πsalary( employee ) ): max <=== Attribute name --------- 55000.00 ```

• A more complex query using set functions

• Recall that:

• The condition in:

 ``` σcondition ( R ) ```

can only contain:

 Attribute names of the relation R          Constants

• Query:

 Find the employee who earns the highest salary in the company

• Sample input 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.00 ```

Therefore, 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 ) ) ???? ```

• No !!!

 M is a relation name The condition can only have: attribute names or constants !!!

You cannot use a relation in the condition of σ !!!!

• 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 ) ) ???? ```