---

Systematic query formulation in Relational Algebra

---

• Systematic treatment of query formulation
  • I have compiled a number of types of queries that we have encountered previously.

    I reformulated them under one common theme:

    Employees working on project(s) that are controlled by the the Research department

    ---

    ---

  • The variations of queries on this theme are:

    Find things that has a certain property Example: Find employees who work on a project controlled by the Research department Find things that has multiple occurences of a certain property Example: Find employees who work on 2 or more projects controlled by the Research department Find things that has all occurences of a certain property Example: Find employees who work on all projects controlled by the Research department Find things that has no occurences of a certain property Example: Find employees who do not work on any projects controlled by the Research department Find things that only has a certain property Example: Find employees who only work on projects controlled by the Research department

  I don't want to redo these queries in class.

  (You have seen these types of queries before !!! It will be boring to repeat them)

  I put them in the webpage below for your convenience.

1. Find fname, lname of all employees who work on a project controlled by the Research department

   Relations needed: Employee (fname, lname, ssn) Works_on ((essn, pno) --- projects worked on by employee) Project (which department controls the project)            Department (dname) Query plan: Find the Research department Find the projects controlled by the Research department Find the SSN of employees who work of these projects Find the fname and lname of these employees

   Solution:

   ResearchDept = σdname='Research' ( Department ) ResearchProjs = Project ⋈dnum=dnumber ResearchDept EmpResProjs = πessn ( Works_on ⋈pno=pnumber ResearchProjs ) Answer = πfname, lname ( Employee ⋈ssn=essn EmpResProjs )

   ---

   ---

   ---

   ---

2. Find fname and lname of all employees who work on 2 or more projects controlled by the "Research" department.

   Relations needed: Employee (fname, lname, ssn) Works_on ((essn, pno) --- projects worked on by employee) Project (which department controls the project)            Department (dname) Query plan: Find the projects controlled by the Research department Find the number of Research projects worked on by each employee Find employees who work on ≥ 2 research projects Find the fname and lname of these employees

   Solution:

   ---

   ---

   ---

   ---

3. Find fname and lname of all employees who work on all projects controlled by the "Research" department.

   Relations needed: Employee (fname, lname, ssn) Works_on ((essn, pno) --- projects worked on by employee) Project (which department controls the project)            Department (dname) Query plan: use a division operation !!! Find the projects controlled by the Research department Form the relation (essn, pno) for the division Divide by the set of projects controlled by the Research department Find the fname and lname of these employees

   Solution:

   ---

   ---

   ---

   ---

4. Find fname and lname of all employees that do not work on any projects controlled by the "Research" department.

   Hint:

   Fact: If an employee works on a project controlled by the Research department, then this employee must not be selected !!! First, find this set of disqualified employees: Disqualified = {employees (SSN) who work on a project controlled by the "Research" department} Solution set (employees) = All employees − Disqualified set       (i.e.: set difference !!)

   Solution procedure:

   Relations needed: Employee (fname, lname, ssn) Works_on ((essn, pno) --- projects worked on by employee) Project (which department controls the project)            Department (dname) Query plan: Find projects controlled by the Research department Find employees who work on a project controlled by the Research department Remove these employees and obtain the qualifying employees Find the fname and lname of the qualifying employees

   Solution:

   ---

   ---

   ---

   ---

5. Find fname and lname of all employees that only work on projects controlled by the "Research" department.

   Hint:

   Fact: If an employee works on a project that is not controlled by the Research department, then this employee does not only work on projects controlled by the Research department First, find this set of disqualified employees: Disqualified = {employees (SSN) who work on a project that is not controlled by the "Research" department} These employees do not only work on Research projects !!! Solution set (employees) = All employees − Disqualified set       (i.e.: set difference !!)

   Solution procedure:

   Relations needed: Employee (fname, lname, ssn) Works_on ((essn, pno) --- projects worked on by employee) Project (which department controls the project)            Department (dname) Query plan: Find projects that ate not controlled by the Research department Find employees who work on a project that is not controlled by the Research department Remove these employees (the remaining employees will only work on projects controlled by the Research department) Find the fname and lname of the qualifying employees

   Solution: