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 –

Fname	Minit	Lname	Ssn	Bdate	Address	Sex	Salary	Super_ssn	Dno
John	B	Smith	123456789	1965-01-09	731 Fondren, Houston, TX	M	30000	333445555	5
Franklin	T	Wong	333445555	1955-12-08	638 Voss, Houston, TX	M	40000	888665555	5
Alicia	J	Zelaya	999887777	1968-01-19	3321 Castle, Spring, TX	F	25000	987654321	4
Jennifer	S	Wallace	987654321	1941-06-20	291 Berry, Bellaire, TX	F	43000	888665555	4
Ramesh	K	Narayan	666884444	1962-09-15	975 Fire Oak, Humble, TX	M	38000	333445555	5
Joyce	A	English	453453453	1972-07-31	5631 Rice, Houston, TX	F	25000	333445555	5
Ahmad	V	Jabbar	987987987	1969-03-29	980 Dallas, Houston, TX	M	25000	987654321	4
James	E	Borg	888665555	1937-11-10	450 Stone, Houston, TX	M	55000	NULL	1

DEPARTMENT Table

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

Dname	Dnumber	Mgr_ssn	Mgr_start_date
Research	5	333445555	1988-05-22
Administration	4	987654321	1995-01-01
Headquarters	1	888665555	1981-06-19

PROJECT Table

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

Pname	Pnumber	Plocation	Dnum
ProductX	1	Bellaire	5
ProductY	2	Sugarland	5
ProductZ	3	Houston	5
Computerization	10	Stafford	4
Reorganization	20	Houston	1
Newbenefits	30	Stafford	4

WORKS_ON Table

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

Essn	Pno	Hours
123456789	1	32.5
123456789	2	7.5
666884444	3	40.0
453453453	1	20.0
453453453	2	20.0
333445555	2	10.0
333445555	3	10.0
333445555	10	10.0
333445555	20	10.0
999887777	30	30.0
999887777	10	10.0
987987987	10	35.0
987987987	30	5.0
987654321	30	20.0
987654321	20	15.0
888665555	20	NULL

DEPENDENT Table

The DEPENDENT table is as follows –

Essn	Dependent_name	Sex	Bdate	Relationship
333445555	Alice	F	1986-04-05	Daughter
333445555	Theodore	M	1983-10-25	Son
333445555	Joy	F	1958-05-03	Spouse
987654321	Abner	M	1942-02-28	Spouse
123456789	Michael	M	1988-01-04	Son
123456789	Alice	F	1988-12-30	Daughter
123456789	Elizabeth	F	1967-05-05	Spouse

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 –

RESEARCHDEPT←σDname=′Research′(DEPARTMENT)

Dname	Dnumber	Mgr_ssn	Mgr_start_date
Research	5	333445555	1988-05-22

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

RESEARCHEMPS←RESEARCHDEPT⋈Dnumber=DnoEMPLOYEE

Dname	Dnumber	Mgr_ssn	Mgr_start_date	Fname	Minit	Lname	Ssn	Bdate	Address	Sex	Salary	Super_ssn	Dno
Research	5	333445555	1988-05-22	John	B	Smith	123456789	1965-01-09	731 Fondren, Houston, TX	M	30000	333445555	5
Research	5	333445555	1988-05-22	Franklin	T	Wong	333445555	1955-12-08	638 Voss, Houston, TX	M	40000	888665555	5
Research	5	333445555	1988-05-22	Ramesh	K	Narayan	666884444	1962-09-15	975 Fire Oak, Humble, TX	M	38000	333445555	5
Research	5	333445555	1988-05-22	Joyce	A	English	453453453	1972-07-31	5631 Rice, Houston, TX	F	25000	333445555	5

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

RESULT←πFname,Lname,Address(RESEARCHEMPS)

Fname	Lname	Address
John	Smith	731 Fondren, Houston, TX
Franklin	Wong	638 Voss, Houston, TX
Ramesh	Narayan	975 Fire Oak, Humble, TX
Joyce	English	5631 Rice, Houston, TX

In-Line Expression −

πFname,Lname,Address(σDname=′Research′(DEPARTMENT⋈Dnumber=DnoEMPLOYEE))

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.

STAFFORD_PROJS←σPlocation=′Stafford′(PROJECT)

Pname	Pnumber	Plocation	Dnum
Computerization	10	Stafford	4
Newbenefits	30	Stafford	4

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

CONTR_DEPTS←STAFFORD_PROJS⋈Dnum=DnumberDEPARTMENT

Pname	Pnumber	Plocation	Dnum	Dname	Dnumber	Mgr_ssn	Mgr_start_date
Computerization	10	Stafford	4	Administration	4	987654321	1995-01-01
Newbenefits	30	Stafford	4	Administration	4	987654321	1995-01-01

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

PROJ_DEPT_MGRS←CONTR_DEPTS⋈Mgr_ssn=SsnEMPLOYEE

Pname	Pnumber	Plocation	Dnum	Dname	Dnumber	Mgr_ssn	Mgr_start_date	Fname	Minit	Lname	Ssn	…
Computerization	10	Stafford	4	Administration	4	987654321	1995-01-01	Jennifer	S	Wallace	987654321	…
Newbenefits	30	Stafford	4	Administration	4	987654321	1995-01-01	Jennifer	S	Wallace	987654321	…

Finally, use projection to extract the desired attributes.

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

Pnumber	Dnum	Lname	Address	Bdate
10	4	Wallace	291 Berry, Bellaire, TX	1941-06-20
30	4	Wallace	291 Berry, Bellaire, TX	1941-06-20

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 –

DEPT5_PROJS←ρ(Pno)(πPnumber(σDnum=5(PROJECT)))

Pno
1
2
3

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

EMP_PROJ←ρ(Ssn,Pno)(πEssn,Pno(WORKS_ON))

Ssn	Pno
123456789	1
123456789	2
666884444	3
453453453	1
453453453	2
333445555	2
333445555	3
333445555	10
333445555	20
999887777	30
999887777	10
987987987	10
987987987	30
987654321	30
987654321	20
888665555	20

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

RESULT_EMP_SSNS←EMP_PROJ÷DEPT5_PROJS

Join with the EMPLOYEE relation to retrieve names.

RESULT←πLname,Fname(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” –

SMITHS(Essn)←πSsn(σLname=′Smith′(EMPLOYEE))

Essn
123456789

Use the Cartesian product to find projects where “Smith” works.

SMITH_WORKER_PROJS←πPno(WORKS_ON∗SMITHS)

Essn	Pno	Hours
123456789	1	32.5
123456789	2	7.5

Retrieve SSNs of department managers from the DEPARTMENT relation –

MGRS←πLname,Dnumber(EMPLOYEE⋈Ssn=Mgr_ssnDEPARTMENT)

Lname	Dno
Wong	5
Wallace	4
Borg	1

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

SMITH_MANAGED_DEPTS(Dnum)←πDnumber(σLname=′Smith′(MGRS))

SMITH_MGR_PROJS(Pno)←πPnumber(SMITH_MANAGED_DEPTS∗PROJECT)

Combine the two project lists using union –

RESULT←(SMITH_WORKER_PROJS∪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 –

T1(Ssn,No_of_dependents)←EssnℑCOUNTDependent_name(DEPENDENT)

Filter employees with more than two dependents –

T2←σNo_of_dependents>2(T1)

Join with EMPLOYEE to get their names –

RESULT←πLname,Fname(T2∗EMPLOYEE)

Employees without Dependents

This query retrieves employees who have no dependents.

Steps − Create a relation with all employee SSNs.

ALL_EMPS←πSsn(EMPLOYEE)

Create a relation of employees with dependents.

EMPS_WITH_DEPS(Ssn)←πEssn(DEPENDENT)

Use the set difference to find employees with no dependents.

EMPS_WITHOUT_DEPS←(ALLEMPS–EMPS_WITH_DEPS)

Join with EMPLOYEE to retrieve names.

RESULT←πLname,Fname(EMPS_WITHOUT_DEPS∗EMPLOYEE)

Managers with Dependents

This query lists managers who have at least one dependent.

Steps − Extract SSNs of department managers –

MGRS(Ssn)←πMgrssn(DEPARTMENT)

Extract SSNs of employees with dependents –

EMPS_WITH_DEPS(Ssn)←πEssn(DEPENDENT)

Use intersection to find managers with dependents –

MGRS_WITH_DEPS←(MGRS∩EMPS_WITH_DEPS)

Retrieve their names –

RESULT←πLname,Fname(MGRS_WITH_DEPS∗EMPLOYEE)