-
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:
-
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 πfname, lname do not contain salary !!!
- Relation(s) that contain the
necessary information
to answer the query:
-
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 ) )
- Relation(s) 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:
Example that 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:
|