---

Forming groups using multiple attributes

---

• Forming groups using multiple attributes
  • You can form groups using more than one attributes

    In that case, you will have "finer" (more smaller) groups.

    ---

  • Example:

    Find the average salary of males and females for each department

    We must first form groups of employee tuples based on:

    Sex Departmenet (number)

    Then:

    compute the average salary of each group

    Schematically:

    • Group 1 is the male employees in department 4
    • Group 2 is the male employees in department 5
    • Group 3 is the female employees in department 5

    ---

  • The Result SET is:

    DNO sex Avg +--------+-----------+------------+ | 4 | M | 45000 | +--------+-----------+------------+ | 5 | M | 60000 | +--------+-----------+------------+ | 5 | F | 50000 | +--------+-----------+------------+

  ---

  ---

  ---

  ---

• Empty groups
  • Notice that:
    1. The group of female employees in department 4 is empty :

    2. The result set of the operation not equal to:

       DNO sex Avg +--------+-----------+------------+ | 4 | M | 45000 | +--------+-----------+------------+ | 4 | F | 0 | <---- You may expect this result !!! +--------+-----------+------------+ | 5 | M | 60000 | +--------+-----------+------------+ | 5 | F | 50000 | +--------+-----------+------------+

       The correct result set is (from above):

       DNO sex Avg +--------+-----------+------------+ | 4 | M | 45000 | +--------+-----------+------------+ | 5 | M | 60000 | +--------+-----------+------------+ | 5 | F | 50000 | +--------+-----------+------------+ Does not contain a row with 0 !!!

    ---

    ---

    ---

  • Important observation in set operations:

    Group with 0 elements are not represented in the result relation (set) !!!

  • Therefore:

    Do not use these expressions to find empty sets: Avg == 0 or: Count == 0