Forming sub-groups
based on common attribute values
- Forming sub-groups:
- Sub-groups
(subsets)
of tuples
can be formed based on
common values in
one or more
attributes
|
- Example:
form sub-groups
of employee tuples
based on their
dno attribute:
|
Applying set functions
on sub-groups
- Query:
- Find the average salary
for each department
in the company
|
-
Sample
input and
result:
|
Applying set functions
on sub-groups
- Query:
- Find the average salary
for each department
in the company
|
- Step 1:
form groups
of employee tuples based on
their
DNO attribute values
|
Applying set functions
on sub-groups
- Query:
- Find the average salary
for each department
in the company
|
- Step 2:
compute
the average
of the Salary values
in each group
|
Applying set functions
on sub-groups
- Query:
- Find the average salary
for each department
in the company
|
- The result
relation of the
query
is:
DNO Avg
+-----------+------------+
| 4 | 45000 |
+-----------+------------+
| 5 | 55000 |
+-----------+------------+
|
- We will now
learn:
- The
notation
used to
express
set functions on
sub-groups
|
|
Notation: applying set functions
on sub-groups
- Notation:
GrpAttrs γ func(A)(R) = (1) First, form subgroups based on common value
in attributes GrpAttrs
(2) Then, apply function func on attribute A
in each subgroup
|
- The
output relation
of
GrpAttrs γ
func(A)(R)
is:
+------------+---------------+
| GrpAttrs | func |
+------------+---------------+
|
I.e.:
- The attributes
in the output relation
are (1) the attributes
used to form groups and
(2) the
function values
- The names
of the attributes
are (1) the names
of the attributes
used to form groups
and (2) the function names
|
|
Notation: applying set functions
on sub-groups
DNO γ avg(salary)( employee ) ---> DNO Avg
+-----------+------------+
| 4 | 45000 |
+-----------+------------+
| 5 | 55000 |
+-----------+------------+
|
|
❮
❯