CS457 Syllabus & Progress

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

1. The result (of an operator in Relational Algenra) is always a RELATION (i.e., a set of tuples !!!)

2. 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 | +-----------+------------+ ```