ASSIGNMENT 4 last date of submission 16 Oct 2015

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

• • 1. exist w (w inWorks and t[Resp] = w[Resp] and e[Eno] = w[Eno] and w[Dur] > 12) ) }

• 1. (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 ) ) ) }

  2. (c) { t | exists p (p in Proj and t[Pname] = p[Pname] and

  3. exists e (e in Emp and t[Ename] = e[Ename] and

  4. exists w (w in Works and w[Pno] = p[Pno] and w[Eno] = e[Eno] and

  5. 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).