Consider the following relations containing airline flight information:
Flights(flno: integer, from: string, to: string,
distance: integer, departs: time, arrives: time)
Aircraft(aid: integer, aname: string, cruisingrange: integer)
Certified(eid: integer, aid: integer)
Employees(eid: integer, ename: string, salary: integer)
Note that the Employees relation describes pilots and other kinds of employees as well;
every pilot is certified for some aircraft (otherwise, he or she would not qualify as a
pilot), and only pilots are certified to fly.
Write the following queries in relational algebra, tuple relational calculus, domain
relational calculus and SQL queries . Note that some of these queries may not be expressible in relational
algebra (and, therefore, also not expressible in tuple and domain relational calculus)!
For such queries, informally explain why they cannot be expressed. (See the exercises
at the end of Chapter 5 for additional queries over the airline schema.)
1. Find the eids of pilots certified for some Boeing aircraft.
2. Find the names of pilots certified for some Boeing aircraft.
3. Find the aids of all aircraft that can be used on non-stop flights from Bonn to
Madras.
4. Identify the flights that can be piloted by every pilot whose salary is more than
$100,000.
5. Find the names of pilots who can operate planes with a range greater than 3,000
miles but are not certified on any Boeing aircraft.
6. Find the eids of employees who make the highest salary.
7. Find the eids of employees who make the second highest salary.
8. Find the eids of employees who are certified for the largest number of aircraft.
9. Find the eids of employees who are certified for exactly three aircraft.
Given the Functional dependencies
I)F = {AB→CD, B→C, BC→D, CD→EF, E→F}. Find minimal cover for this FD set.
ii)F = {A→BC,CD→E, E→C, D→AEH, ABH→BD, DH→BC}. Find minimal cover
Q) Using the following schema
EMP(ENO, ENAME, TITLE)
PROJ (PNO, PNAME, BUDGET)
WORKS(ENO,PNO, RESP, DUR)
PAY(TITLE, SALARY)
and translate the following queries on that database into relational algebra:
(a){ t | exist e (e in Emp and t[Ename] = e[Ename] and
exist w (w inWorks and t[Resp] = w[Resp] and e[Eno] = w[Eno] and w[Dur] > 12) ) }
(b){< p,r> |� exists n,b (<n,p,b> in Proj and exists e,d (<e,n,r,d> in Works and (d > 12 or b > 200000 ) ) ) }
(c) { t | exists p (p in Proj and t[Pname] = p[Pname] and
exists e (e in Emp and t[Ename] = e[Ename] and
exists w (w in Works and w[Pno] = p[Pno] and w[Eno] = e[Eno] and
forall v ( (v in Works and v[Resp] = w[Resp] ) implies v[Dur] less-or-equals w[Dur] ) ) ) ) }
(where the keywords exists, forall, in, and, or, implies, and less-or-equals represent corresponding symbols that are not printable in pure HTML).