---

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 tuples:

    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 | +--------+---------+------+----------+-----+

    ---

    ---

  • The set functions in Relational Algebra are:

    ℱsum(attr)(R) = returns the sum of all values of the attribute "attr" in the relation R Example: ℱsum(salary)(employee) = {140000}        Note: the result is a relation (= set !!) ℱavg(attr)(R) = returns the average of all values of the attribute "attr" in the relation R Example: ℱavg(salary)(employee) = {35000}        ℱmax(attr)(R) = returns the maximum (highest) of all values of the attribute "attr" in the relation R Example: ℱmax(salary)(employee) = {50000}        ℱmin(attr)(R) = returns the minimum (lowest) of all values of the attribute "attr" in the relation R Example: ℱmin(salary)(employee) = {20000}        ℱcount(*)(R) = returns the cardinality (= # elements) of the relation R                            (duplicate attribute values will be counted multiple times) Example: ℱcount(*)(employee) = {4}        ℱany(*)(R) = returns true if there is at least one tuple in relation R                          otherwise, return false Example: ℱany(*)(employee) = {true}

     

    ---

    ---

  • 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 !!!

  ---

  ---

  ---

  ---

• Simple query using set functions
  • 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

  ---

  ---

  ---

  ---

• Using the result of set functions in a query
  • Recall that:

    The condition in: σcondition ( R ) can only contain: Attribute names of the relation R          Constants

    ---

    ---

  • 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.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 ) ) ???? 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 σ !!!!

    ---

    ---

  • 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 !!!