### Examples of Relational Algebra queries

• Simple query examples in Relational Algebra....

Database Model:

1. 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. 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. 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:

 host Employee with depedents are also 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...

• Clearly, I can easily formulate a gad-sillion different queries in the next day or so, so:

DO NOT prepare for tests with memorizing facts - you gotta understand what's going on and do the queries "from scratch"....

• And for those students that have never had me before: I have a reputation to challenge student's grey cells to work overtime.... so you should expect some very interesting queries.... and plenty of opportunity to practice your skill (in the homeworks and SQL projects).