Formal notation, giving name to set function results and
set function on single group
Formal Notation for Grouping and Set functions
The operation where:
a set of tuples (i.e., a relation)
is first separated into different groups based on one or more attributes
and then applying one or more set functions on each group
is denoted as follows:
The
output
of the set function is a
relation
containing the following attributes:
The content
(i.e., tuples )
of the relation consists of:
all
distinct values
of the
grouping attributes attr_{1},
attr_{2}, ...,
attr_{N}.
and the
function values f_{1}(a_{1}),
f_{2}(a_{2}), ...,
f_{M}(a_{M}) on
the corresponding group.
Example:
Notice that the output is indeed a set of tuples -
i.e., a relation.
Notice that there is NO tuple with
attribute values (DNO=4, Sex='F')
when this group (set) is empty !!!
The attribute name for the function values is named
after the function name.
Giving attributes a "proper name" in the result relation
Often -- for clarity reasons -- one or more
attribute
in the result relation
are
renamed
The attribute
in the result relation
are given new names
by
sepcifying explicit names
for the attributes when the
result is computed
Example:
Applying (set) functions without any grouping attributes
When there are
no grouping attributes specified
in a set function,
then the set function is
applied on ONE group that consists of all tuples of the relation
(i.e., all tuples in the relation are in one single group).
The most important fact to remember
in this case is:
Although the
result set
will consist of
ONE single tuple (row) ,
the output of the set function
is
still a relation
It is a
relation
with
one single tuple
in it !!!
Example:
determent the average salary of all employees
in the company and the total number of employees in the company:
Enough Theory, show me the applications....
That's all the theory on relational algebra.
We will next do some examples to help you develop skills
to formulate queries in relation algebra -
these skills will help you formulate queries in SQL.