---

Using groups and set functions in Relational Algebra queries

---

1. For each department, list the department name and the highest salary paid to an employee in that department

   Relations needed: Employee (salary, fname, lname, dno) Department (dname, dnumber)            Query plan: Combine the employee and department information Form groups based on common dname value Find the maximum salary value in each group

   Solution:

   ---

   ---

   ---

   ---

2. Find fname and lname of all employees who work on more projects than 'John Smith'.

   Relations needed: Employee (salary, fname, lname, ssn) Works_on ((essn, pno) --- projects worked on by employee)            Query plan: Find the number of projects worked on by John Smith Find the number of projects worked on by each employee Find SSN of employees who work on more projects than John Smith Find the fname and lname of these employees

   Solution:

   ---

   ---

   ---

   ---

3. Find fname and lname of the employees who have the most number of dependents.

   Relations needed: Employee Dependent            Query plan: Find the number of dependents for each employee Find the maximum of the number of dependents over all employees Find the SSN of employee(s) who has the maximum dependents Find the fname and lname of these employees

   Solution:

   Note: the following construct is illegal in Relational Algebra

   H1 = essn ℱ count(dep_name) (dependent) // H1 = ( essn, #dependents-of-this-essn) H2 = ℱ max(count) (H1) // H2 = (max) Note that: H2 is a relation This is illegal: H3 = σ H1.count = H2 (H1) Because: H1.count is an attribute (i.e., a single value) H2 is a relation (i.e., a set of values) You can compare an attribute value against another attribute value or a constant - in both cases, they are one single value. You cannot compare an attribute with a relation.