---

Forming groups and applying a set function on individual groups

---

• Forming groups using attribute values
  • Set functions must be applied to a group/set of value

    (The set may contain zero or one value, it is nevertheless a set )

    ---

  • The SAME (set) function is usually applied to multiple sets/groups :

  • For each group , the set function will produce EXACTLY one output value

    (Therefore, since there are multiple groups , you will get multiple values or a group/set of values )

    ---

    ---

  • Forming groups:

    Groups are formed based on common values in one or more attributes

  • Example: form groups of employee tuples based on their dno attribute:

    Tuples with the same DNO value is put in the same group

  ---

  ---

  ---

  ---

• Applying set functions on groups (formed based on common attribute values)
  • We saw that tuples in a relation can be grouped based on one or more attribute values

  • One or more set functions can now be applied to these groups

    ---

    ---

    ---

  • Example 1:

    Find the average salary for each department

    Solution procedure:

    We first form groups of employee tuples based on their DNO attribute values ("grouped by" department number) Then, we compute the average of the Salary values in each group

    Example:

    ---

  • The Result relation (= set of tuples) is:

    DNO Avg +-----------+------------+ | 4 | 45000 | +-----------+------------+ | 5 | 55000 | +-----------+------------+

    ---

    ---

  • NOTE:

    The result (of an operator in Relational Algenra) is always a RELATION (i.e., a set of tuples !!!) The attributes of the result relation consist of: The grouping attributes !!! The set function Example: DNO Avg +-----------+------------+ | 4 | 45000 | +-----------+------------+ | 5 | 55000 | +-----------+------------+ Note: The grouping attributes (i.e. DNO=4 and DNO=5) are necessary in the result Without them, you cannot tell to which group the average values belong: Avg +------------+ | 45000 | +------------+ | 55000 | +------------+ You cannot tell which of the average salary belongs to dept 4 and which one belongs to dept 5 !!!

    ---

    ---

    ---

    ---

  • Example 2:

    Find the average salary for male employees and female employees

    Solution procedure:

    We first form groups of employee tuples based on their "sex" attribute Then compute the average Salary for each group

    Example:

    ---

  • The Result relation is:

    sex Avg +-----------+------------+ | M | 50000 | +-----------+------------+ | F | 50000 | +-----------+------------+