Examples of Queries in Relational Algebra

Examples of Queries in Relational Algebra

The following are additional examples to illustrate the use of the relational algebra operations. All examples refer to the database in Figure 3.6. In general, the same query can be stated in numerous ways using the various operations. We will state each query in one way and leave it to the reader to come up with equivalent formu-lations.

Query 1. Retrieve the name and address of all employees who work for the ‘Research’ department.

RESEARCH_DEPT ← σ_{Dname=‘Research’}(DEPARTMENT)

RESEARCH_EMPS ← (RESEARCH_DEPT _Dnumber=DnoEMPLOYEE)

RESULT ← π_{Fname, Lname, Address}(RESEARCH_EMPS)

As a single in-line expression, this query becomes:

^πFname, Lname, Address ^(σDname=‘Research’^(DEPARTMENT Dnumber=Dno^(EMPLOYEE))

This query could be specified in other ways; for example, the order of the JOIN and SELECT operations could be reversed, or the JOIN could be replaced by a NATURAL JOIN after renaming one of the join attributes to match the other join attribute name.

Query 2. For every project located in ‘Stafford’, list the project number, the controlling department number, and the department manager’s last name, address, and birth date.

STAFFORD_PROJS ← σ_{Plocation=‘Stafford’}(PROJECT)

CONTR_DEPTS ← (STAFFORD_PROJS _Dnum=DnumberDEPARTMENT)

PROJ_DEPT_MGRS ← (CONTR_DEPTS _{Mgr_ssn=Ssn}EMPLOYEE)

^{RESULT ← π}Pnumber, Dnum, Lname, Address, Bdate^{(PROJ_DEPT_MGRS)}

In this example, we first select the projects located in Stafford, then join them with their controlling departments, and then join the result with the department man-agers. Finally, we apply a project operation on the desired attributes.

Query 3. Find the names of employees who work on all the projects controlled by department number 5.

DEPT5_PROJS ← ρ_(Pno)(π_Pnumber(σ_Dnum=5(PROJECT)))

EMP_PROJ ← ρ_{(Ssn, Pno)}(π_{Essn, Pno}(WORKS_ON))

RESULT_EMP_SSNS ← EMP_PROJ ÷ DEPT5_PROJS

RESULT ← π_{Lname, Fname}(RESULT_EMP_SSNS _* EMPLOYEE)

In this query, we first create a table DEPT5_PROJS that contains the project numbers of all projects controlled by department 5. Then we create a table EMP_PROJ that holds (Ssn, Pno) tuples, and apply the division operation. Notice that we renamed the attributes so that they will be correctly used in the division operation. Finally, we join the result of the division, which holds only Ssn values, with the EMPLOYEE table to retrieve the desired attributes from EMPLOYEE.

Query 4. Make a list of project numbers for projects that involve an employee whose last name is ‘Smith’, either as a worker or as a manager of the department that controls the project.

SMITHS(Essn) ← π_Ssn (σ_{Lname=‘Smith’}(EMPLOYEE))

SMITH_WORKER_PROJS ← π_Pno(WORKS_ON _* SMITHS)

MGRS ← π_{Lname, Dnumber}(EMPLOYEE _{Ssn=Mgr_ssn}DEPARTMENT)

SMITH_MANAGED_DEPTS(Dnum) ← π_Dnumber (σ_{Lname=‘Smith’}(MGRS))

SMITH_MGR_PROJS(Pno) ← π_Pnumber(SMITH_MANAGED_DEPTS _* PROJECT)

RESULT ← (SMITH_WORKER_PROJS ∪ SMITH_MGR_PROJS)

In this query, we retrieved the project numbers for projects that involve an employee named Smith as a worker in SMITH_WORKER_PROJS. Then we retrieved the project numbers for projects that involve an employee named Smith as manager of the department that controls the project in SMITH_MGR_PROJS. Finally, we applied the UNION operation on SMITH_WORKER_PROJS and SMITH_MGR_PROJS. As a single in-line expression, this query becomes:

^πPno ^(WORKS_ON   Essn=Ssn^(πSsn ^(σLname=‘Smith’^{(EMPLOYEE))) ∪ π}Pno

^((πDnumber ^(σLname=‘Smith’^(πLname, Dnumber^(EMPLOYEE)))

Ssn=Mgr_ssnDEPARTMENT))  Dnumber=DnumPROJECT)

Query 5. List the names of all employees with two or more dependents.

Strictly speaking, this query cannot be done in the basic (original) relational algebra. We have to use the AGGREGATE FUNCTION operation with the COUNT aggregate function. We assume that dependents of the same employee have distinct Dependent_name values.

T1(Ssn, No_of_dependents)← _Essn ℑ _{COUNT Dependent_name}(DEPENDENT)

^{T2 ← σ}No_of_dependents>2^(T1)

^{RESULT ← π}_{Lname, Fname}^(T2 * ^EMPLOYEE)

Query 6. Retrieve the names of employees who have no dependents.

This is an example of the type of query that uses the MINUS (SET DIFFERENCE) operation.

ALL_EMPS ← π_Ssn(EMPLOYEE)

EMPS_WITH_DEPS(Ssn) ← π_Essn(DEPENDENT)

EMPS_WITHOUT_DEPS ← (ALL_EMPS – EMPS_WITH_DEPS)

RESULT ← π_{Lname, Fname}(EMPS_WITHOUT_DEPS _* EMPLOYEE)

We first retrieve a relation with all employee Ssns in ALL_EMPS. Then we create a table with the Ssns of employees who have at least one dependent in EMPS_WITH_DEPS. Then we apply the SET DIFFERENCE operation to retrieve employees Ssns with no dependents in EMPS_WITHOUT_DEPS, and finally join this with EMPLOYEE to retrieve the desired attributes. As a single in-line expression, this query becomes:

^π_{Lname, Fname}^((π_Ssn^{(EMPLOYEE) – ρ}_Ssn^(π_Essn^{(DEPENDENT)))} * ^EMPLOYEE)

Query 7. List the names of managers who have at least one dependent.

MGRS(Ssn) ← π_{Mgr_ssn}(DEPARTMENT)

EMPS_WITH_DEPS(Ssn) ← π_Essn(DEPENDENT)

MGRS_WITH_DEPS ← (MGRS ∩ EMPS_WITH_DEPS)

RESULT ← π_{Lname, Fname}(MGRS_WITH_DEPS _* EMPLOYEE)

In this query, we retrieve the Ssns of managers in MGRS, and the Ssns of employees with at least one dependent in EMPS_WITH_DEPS, then we apply the SET INTERSECTION operation to get the Ssns of managers who have at least one dependent.

As we mentioned earlier, the same query can be specified in many different ways in relational algebra. In particular, the operations can often be applied in various orders. In addition, some operations can be used to replace others; for example, the INTERSECTION operation in Q7 can be replaced by a NATURAL JOIN. As an exercise, try to do each of these sample queries using different operations.¹² We showed how to write queries as single relational algebra expressions for queries Q1, Q4, and Q6. Try to write the remaining queries as single expressions. In Chapters 4 and 5 and in Sections 6.6 and 6.7, we show how these queries are written in other relational languages.