---

Forming groups and applying a set function on individual groups

---

• Forming sub-groups using attribute values
  • Forming sub-groups:

    Sub-groups of tuples 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 | +-----------+------------+

    ---

    ---

    ---

    ---

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

  ---

  ---

  ---

  ---

• Notation: forming sub-groups and applying a set function on each sub-group
  • Notation:

    GrAttrs γ func(A)( R ) = (1) First, form subgroups based on common value in attributes GrAttrs (2) Then, apply function func on attribute A in each subgroup

    Examples:

    DNO γ avg(salary)( employee ) will compute the following result: I.e.: DNO Avg +-----------+------------+ | 4 | 45000 | +-----------+------------+ | 5 | 55000 | +-----------+------------+ sex γ avg(salary)( employee ) will compute the following result: I.e.: sex Avg +-----------+------------+ | M | 50000 | +-----------+------------+ | F | 50000 | +-----------+------------+