Example of Queries on Relational Algebra

---

---

Relational Algebra provides the theoretical foundation of querying. It is used for understanding and manipulating data in relational databases. It is used for a variety of operations to query and transform datasets effectively. By understanding these operations, database users can perform complex queries while maintaining clarity and precision.

In this chapter, we will see several query examples from relational algebra. Each example is based on a specific scenario and demonstrates the practical application of relational algebra operations such as selection, projection, join, union, and division.

Let us first take a look at the required tables and their data to express the query in true sense.

EMPLOYEE Table

Here's the EMPLOYEE table that collects the data relevant to all employees –

DEPARTMENT Table

There are three departments as highlighted in the following DEPARTMENT table –

PROJECT Table

The PROJECT table contains the relevant data of all the projects –

WORKS_ON Table

The WORKS_ON table gathers which employee is working on which project for how many hours –

DEPENDENT Table

The DEPENDENT table is as follows –

Query Examples on Relational Algebra

Let us now check some queries that extracts data from the inputs supplied in the above tables –

Retrieve the Name and Address of Employees in the Research Department

This query is to identify employees working in the "Research" department and retrieve their names and addresses.

Steps − Use a selection operation to filter the "Research" department from the DEPARTMENT relation –

$$\mathrm{RESEARCH_DEPT \: ←\: σ_{Dname \:=\: 'Research'}\:(DEPARTMENT)}$$

Join the resulting relation with the EMPLOYEE relation using the department number (Dnumber and Dno) –

$$\mathrm{RESEARCH_EMPS \:←\: RESEARCH_DEPT\: ⋈_{Dnumber\:=\:Dno}\: EMPLOYEE}$$

Finally, apply a projection operation to retrieve the desired attributes (first name, last name, and address).

$$\mathrm{RESULT \:←\: π_{Fname,\: Lname,\: Address}\:(RESEARCH_EMPS)}$$

In-Line Expression −

$$\mathrm{π_{Fname,\: Lname,\: Address}\: (σ_{Dname\:=\:'Research'}\:(DEPARTMENT\: ⋈_{Dnumber\:=\:Dno}\: EMPLOYEE))}$$

List Project Details for Stafford

For projects located in "Stafford," we are going to retrieve the project number, the controlling department, and the manager's details (last name, address, and birth date).

Steps − Filter projects located in "Stafford" using selection.

$$\mathrm{\text{STAFFORD_PROJS}\: ← \:σ_{Plocation\:=\:'Stafford'}\:(PROJECT)}$$

Join these projects with their controlling departments based on the department number –

$$\mathrm{\text{CONTR_DEPTS} \:←\: \text{STAFFORD_PROJS}\: \bowtie_{Dnum\:=\:Dnumber}\: DEPARTMENT}$$

Join the result with the EMPLOYEE relation to retrieve manager details (Mgr_ssn = Ssn) –

$$\mathrm{\text{PROJ_DEPT_MGRS} \:←\: \text{CONTR_DEPTS}\: \bowtie_{\text{Mgr_ssn}\:=\:Ssn}\: EMPLOYEE}$$

Finally, use projection to extract the desired attributes.

$$\mathrm{RESULT \:←\: π_{Pnumber,\: Dnum,\: Lname,\: Address,\: Bdate}\:(\text{PROJ_DEPT_MGRS})}$$

Employees Working on All Projects Controlled by Department 5

This query identifies employees assigned to every project under department number 5.

Steps − Create a relation with project numbers of all projects controlled by department 5 –

$$\mathrm{\text{DEPT5_PROJS}\: ←\: ρ(Pno)\: (π_{Pnumber}\:(σ_{Dnum=5} \:(PROJECT)))}$$

Build a relation of employees and their assigned projects (Ssn and Pno).

$$\mathrm{\text{EMP_PROJ} \:← \:ρ(Ssn,\: Pno)\:(π_{Essn,\: Pno}\:(\text{WORKS_ON}))}$$

Apply the division operation to find employees associated with all DEPT5_PROJS.

$$\mathrm{\text{RESULT_EMP_SSNS}\: ←\: \text{EMP_PROJ}\: ÷ \:\text{DEPT5_PROJS}}$$

Join with the EMPLOYEE relation to retrieve names.

$$\mathrm{RESULT\: ←\: π_{Lname,\: Fname}\: (\text{RESULT_EMP_SSNS}\: *\: EMPLOYEE)}$$

Project Numbers Involving Smith (As Worker or Manager)

We want to identify all projects where an employee named "Smith" is involved. This is either as a worker or as a department manager.

Steps − Retrieve the Social Security Numbers (SSNs) of employees named "Smith" –

$$\mathrm{SMITHS(Essn) \:←\: π_{Ssn}\:(σ_{Lname='Smith'}\:(EMPLOYEE))}$$

Use the Cartesian product to find projects where "Smith" works.

$$\mathrm{\text{SMITH_WORKER_PROJS}\: ←\: π_{Pno}\:(\text{WORKS_ON}\: * \:SMITHS)}$$

Retrieve SSNs of department managers from the DEPARTMENT relation –

$$\mathrm{MGRS \: ←\: π_{Lname,\: Dnumber}\:(EMPLOYEE\: \bowtie_{Ssn \:=\: \text{Mgr_ssn}}\: DEPARTMENT)}$$

Find the departments managed by "Smith" and join them with PROJECT to identify projects –

$$\mathrm{\text{SMITH_MANAGED_DEPTS}(Dnum)\: ←\: π_{Dnumber}\:(σ_{Lname = 'Smith'}\:(MGRS))}$$

$$\mathrm{\text{SMITH_MGR_PROJS}(Pno)\: ←\: π_{Pnumber}\:(\text{SMITH_MANAGED_DEPTS} \:*\: PROJECT)}$$

Combine the two project lists using union –

$$\mathrm{RESULT \:←\: (\text{SMITH_WORKER_PROJS}\: ∪\: \text{SMITH_MGR_PROJS})}$$

Employees with Two or More Dependents

The goal is to list employees who have at least two dependents. This requires using aggregate functions, which go beyond basic relational algebra.

Steps − Count the number of dependents for each employee using COUNT –

$$\mathrm{T1(Ssn,\: \text{No_of_dependents}) \:← \:Essn\: ℑ\: COUNT_{\text{Dependent_name}}\:(DEPENDENT)}$$

Filter employees with more than two dependents –

$$\mathrm{T2\: ←\: σ_{\text{No_of_dependents}\: >\: 2}\:(T1)}$$

Join with EMPLOYEE to get their names –

$$\mathrm{RESULT \:←\: π_{Lname,\: Fname}\: (T2 \:*\: EMPLOYEE)}$$

Employees without Dependents

This query retrieves employees who have no dependents.

Steps − Create a relation with all employee SSNs.

$$\mathrm{\text{ALL_EMPS} \:←\: π_{Ssn}\:(EMPLOYEE)}$$

Create a relation of employees with dependents.

$$\mathrm{\text{EMPS_WITH_DEPS}(Ssn)\: ←\: π_{Essn}\:(DEPENDENT)}$$

Use the set difference to find employees with no dependents.

$$\mathrm{\text{EMPS_WITHOUT_DEPS} \:←\: (ALL_EMPS \:–\: \text{EMPS_WITH_DEPS})}$$

Join with EMPLOYEE to retrieve names.

$$\mathrm{RESULT \: ←\: π_{Lname,\: Fname}\:(\text{EMPS_WITHOUT_DEPS} \:*\: EMPLOYEE)}$$

Managers with Dependents

This query lists managers who have at least one dependent.

Steps − Extract SSNs of department managers –

$$\mathrm{MGRS(Ssn)\: ←\: π_{Mgr_ssn}\:(DEPARTMENT)}$$

Extract SSNs of employees with dependents –

$$\mathrm{\text{EMPS_WITH_DEPS}(Ssn)\: ←\: π_{Essn}\: (DEPENDENT)}$$

Use intersection to find managers with dependents –

$$\mathrm{\text{MGRS_WITH_DEPS}\: ←\: (MGRS\: ∩ \:\text{EMPS_WITH_DEPS})}$$

Retrieve their names –

$$\mathrm{RESULT \:←\: π_{Lname,\: Fname}\:(\text{MGRS_WITH_DEPS} \:*\: EMPLOYEE)}$$

Conclusion

In this chapter, we reviewed various query examples in relational algebra. We started with basic queries such as retrieving employee details and progressed to more complex ones. These included operations such as division and aggregate functions. Along the way, we explored techniques such as join, union, set difference, and intersection. We also demonstrated how they can be combined to solve practical database problems.