CS457 Syllabus & Progress

## 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 ```