CS457 Syllabus & Progress

## Systematic query formulation in Relational Algebra

• Systematic treatment of query formulation

• I have compiled a number of types of queries that you encounter often in database queries:

• 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

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: