Examples of Relational Algebra queries
Simple query examples in Relational Algebra....
Database Model:
1. (Seen before, for review only) Find the fname and lname of all employees
Relation(s) that contain the necessary information to answer the query:
Empoyee
Solution:
πfname,lname (employee)
2. (Seen before, for review only) Find the fname and lname of employees that earn > 50000
Relation(s) that contain the necessary information to answer the query:
Empoyee
Solution:
πfname,lname ( σsalary > 50000 (employee) )
Important note: this is a wrong solution:
σsalary > 50000 ( πfname,lname (employee) )
because:
σsalary > 50000 uses attribute salary and the output of σsalary > 50000 do not
contain salary !!!
3. (Seen before, for review only) Find the fname and lname of employees in department 4 that earn > 50000
Relation(s) that contain the necessary information to answer the query:
Empoyee
Solution:
πfname,lname ( σdno = 4 ∧ salary > 50000 (employee) )
4. Find fname and lname of all employees working in the "Research" department that earn more than $50,000
Relation(s) that contain the necessary information to answer the query:
Employee (provides fname, lname, salary and dno info)
Department (provides dnumber and dname (= 'Research') info)
We need to combine the information from 2 relations using a join (⋈ ) operation:
Employee ⋈ dno=dnumber Department
Now we can apply the selection conditions:
σdname='Research' ∧ salary > 50000 ( Employee ⋈ dno=dnumber Department )
Finally, we project out the attributes that we need:
πfn,ln ( σdname='Res' ∧ sal > 50000 ( Emp ⋈ dno=dnumber Dept ) )
Alternative solution:
5. Find project name for all projects worked on by John Smith
Relations that contain the necessary information to answer the query:
Empoyee (provides: SSN, FName = 'John' and LName = 'Smith')
Works_On (provides information on who works on what project)
Project (provides the project name)
Solution:
Find John Smith's info:
JS = σ Fname='John' && LName='Smith' (employee)
Find the project (pnum's) that John Smith works on using his SSN:
JS-Proj = JS ⋈ SSN=ESSN works_on
Find the project name for projects that John Smith works on using the Project relation:
Answer = π pname ( JS-Proj ⋈ pnum=pnumber project )
Note:
The notation:
JS = σ Fname='John' && LName='Smith' (employee)
will create a temporary relation named JS which is the output (result) of:
σ Fname='John' && LName='Smith' (employee)
You may use JS as a relation in subsequent Relatinal Algenra operations
6. Find fname and lname of John Smith's supervisor
7. Find fname and lname of all employees that have dependents
8. Find fname and lname of all employees that do not have any dependents
This solution is wrong:
The following diagram shows how the query works on an example database, which illustrates why the query is wrong:
Notice that:
This query will find:
Employees who can be matched with some dependent that us not his/her own
(i.e., "the kid is not mine !!!)
Employees with dependents will also be selected
(because there is a dependent that does not belong to him/her !!!)
The correct solution is:
H1 = set of SSN of employees with dependents
H2 = set of SSN of employees without any dependents (attribute name is renamed to "essn")
A comment...
Do not try to memorize how to do queries
Rather:
Understand the process to find the tuples that is asked for