CS457 Syllabus & Progress

## 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 attr1, attr2, ..., attrN.

• and the function values f1(a1), f2(a2), ..., fM(aM) 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.