- (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)
- Relation(s) that contain the
necessary information
to answer the query:
- (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 !!!
- Relation(s) that contain the
necessary information
to answer the query:
- (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) )
- Relation(s) that contain the
necessary information
to answer the query:
- 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 DepartmentNow 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:
- Relation(s) that contain
the necessary information
to answer the query:
- 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 )
- Find John Smith's info:
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
- Relations that contain the
necessary information
to answer the query:
- Find fname and lname of John Smith's supervisor
- Find fname and lname of all employees that have dependents
- 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 who
can be matched with
some dependent that
us not his/her own
- 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")
- This query will
find:
Database Model:
|
- Do not try
to memorize how to
do queries
Rather:
- Understand the process to find the tuples that is asked for