Formulate the following queries in relational algebra, tuple relational calculus, and domain relational calculus (the answers to these queries in SQL are given in the next section).

Question

The following tables form part of a database held in a Relational Database Management System:
Employee (empNo, eName, salary, position)
Aircraft (aircraftNo, aName, aModel, flyingRange)
Flight (flightNo, from, to, flightDistance, departTime, arriveTime)
Certified (empNo, aircraftNo) where Employee contains details of all employees (pilots and non-pilots) and empNo is the key. AirCraft contains details of aircraft and aircraftNo is the key. Flight contains details of the flights and flightNo is the key. and Certified contains details of the staff who are certified to fly an aircraft, and empNo/aircraftNo form the key.

(1) List all Boeing aircraft.
(2) List all Boeing 737 aircraft.
(3) List the employee numbers of pilots certified for Boeing aircraft.
(4) List the names of pilots certified for Boeing aircraft.
(5) List the aircraft that can fly nonstop from Glasgow to New York (flyingRange > flightDistance).
(6) List the employee numbers of employees who have the highest salary.
(7) List the employee numbers of employees who have the second highest salary.
(8) List the employee numbers of employees who are certified for exactly three aircraft.

Answer 1

(1) RA: ? aName = ‘Boeing’ (Aircraft)
TRC: {A | Aircraft(A) ? (A.aName = ‘Boeing’)}
DRC: {aircraftNo, aName, aModel, flyingRange |
Aircraft(aircraftNo, aName, aModel, flyingRange) ? (aName = ‘Boeing’)}
(2) RA: ? aName = ‘Boeing’ ? aModel= ‘737’ (Aircraft)
TRC: {A | Aircraft(A) ? (A.aName = ‘Boeing’) ? (A.aModel = ‘737’)}
DRC: {aircraftNo, aName, aModel, flyingRange |
Aircraft(aircraftNo, aName, aModel, flyingRange) ?
(aName = ‘Boeing’) ? (aModel = ‘737’)}
(3) RA: ? empNo (? aName = ‘Boeing’ (Aircraft) 3 aircraftNo Certified)
TRC: {C.empNo |Certified(C) ? (?A)(Aircraft(A) ?(A.aircraftNo=C.aircraftNo) ?
A.aName = ‘Boeing’)}
DRC: {empNo | (?aNo)(Certified(empNo, aNo) ?
(?aNo1, aName)Aircraft(aNo1, aName, aT, fR) ? (aNo = aNo1) ?
(aName = ‘Boeing’)}
(4) RA: ? eName ((? aName = ‘Boeing’ (Aircraft) 3 aircraftNo Certified) 3 empNo Employee)
TRC: {E.eName | Employee(E) ? ((?C) Certified(C) ? (E.empNo = C.empNo)
? (?A)(Aircraft(A) ? (A.aircraftNo = C.aircraftNo) ?
A.aName = ‘Boeing’))}
DRC: {eName | (?eNo)(Employee(eNo, eName, sal, posn) ?
(?empNo1, aNo)(Certified(empNo1, aNo) ? (empNo = empNo1) ?
(?aNo1, aName)Aircraft(aNo1, aName, aT, fR) ? (aNo = aNo1) ?
(aName = ‘Boeing’)}
(5) RA: ? aircraftNo (? flyingRange > flightDistance (? from=‘Glasgow’ ? to=‘New York’ (Flight) X Aircraft))
TRC: {A.aircraftNo | Aircraft(A) ? ((?F) Flight(F) ? (F.from = ‘Glasgow’ ?
F.to = ‘New York’) ? (A.flyingRange > F.flightDistance))}
DRC: {aircraftNo | (?flyingRange)(Aircraft(aircraftNo, aN, aM, flyingRange)) ?
(?from, to, flightDistance)(Flight(fNo, from, to, flightDistance, dT, aT) ?
(from = ‘Glasgow’ ? to = ‘New York’) ? (flyingRange > flightDistance))}
(6) RA: To answer this query, we first find all employees who do not have the
highest salary and then subtract these from the original list of employees to
give the list of highest employees.
? E1 (Employee)
? E2 (Employee)
? E3 (? E2.empNo (E1 3 E1.salary > E2.salary E2)
? empNo (E1) – E3
TRC: {E1.empNo | Employee(E1) ? (~(?E2)Employee(E2) ?
(E2.salary > E1.salary))}
DRC: {empNo | (?sal) (Employee(empNo, eN, sal, posn)) ?
(~(?sal1)(Employee(empNo1, eN1, sal1, posn1) ?
(sal1 > sal))}
(7) RA: To answer this query, we proceed as above and first find all employees who do not have the highest salary and then subtract these from the original list
of employees to give the list of highest employees. We next remove the list
of highest paid employees from the original list leaving the second highest
paid employees together with the rest of the employees. We can then simply
remove the rest of the employees to give the second highest paid employees.
? E1 (Employee)
? E2 (Employee)
? E3 (? E2.empNo (E1 3 E1.salary > E2.salary E2)
? E4 (E2 3 E3)
? E5 (E2 3 E3)
? E6 (? E5.empNo (E4 3 E1.salary > E5.salary E5)
? empNo (E3) – E6
TRC: {E1.empNo | Employee(E1) ? Employee(E2) ? (E2.salary > E1.salary)
? (~(?E3)Employee(E3) ? (E3.salary > E2.salary))}
DRC: {empNo | (?sal) (Employee(empNo, eN, sal, posn)) ?
(?sal1) (Employee(empNo1, eN1, sal1, posn1)) ? (sal1 > sal)
(~(?sal2)(Employee(empNo2, eN2, sal2, posn2) ? (sal2 > sal1))}
(8) RA: To answer this query, we first find the employees who are certified for at
least three aircraft, then find the employees who are certified for at least four
aircraft. The difference provides the employees who are certified for exactly
three aircraft.
? C1 (Certified)
? C2 (Certified)
? C3 (Certified)
? C4 (Certified)
? C5 (? empNo ((? ?C1.empNo=C2.empNo=C3.empNo)??C1.aircraftNo<>C2.aircraftNo<>C3.aircraftNo) (C1 X C2 X C3)))
? C6 (? empNo ((? ?C1.empNo=C2.empNo=C3.empNo=C4.empNo)??C1.aircraftNo<>C2.aircraftNo<>C3.aircraftNo<>C4.aircraftNo) (C1 X C2 X C3 X
C4)))
C5 – C6
TRC: {C1.empNo | Certified(C1) ? (?C2)(Certified(C2) ? (?C3)(Certified(C3) ?
(C1.empNo = C2.empNo) ? (C2.empNo = C3.empNo) ?
(C1.aircraftNo <> C2.aircraftNo) ? (C2.aircraftNo <> C3.aircraftNo) ?
(C3.aircraftNo <> C1.aircraftNo)) ? (~(?C4)(Certified(C4) ?
(C3.empNo = C4.empNo) ? (C1.aircraftNo <> C4.aircraftNo) ?
(C2.aircraftNo <> C4aircraftNo) ? (C3aircraftNo <> C4aircraftNo))}
DRC: {C1empNo | (?C1aircraftNo)Certified(C1empNo, C1aircraftNo)) ?
(?C2empNo, C2aircraftNo)Certified(C2empNo, C2aircraftNo)) ?
(?C3empNo, C3aircraftNo) (Certified(C3empNo, C3aircraftNo)) ?
(C1empNo = C2empNo) ? (C2empNo = C3empNo) ?
(C1aircraftNo <> C2aircraftNo) ? (C2aircraftNo <> C3aircraftNo) ?
(C3aircraftNo <> C1aircraftNo) ?
(~(?C4empNo, C4aircraftNo)(Certified(C4empNo, C4aircraftNo) ?
(C3.empNo = C4.empNo) ? (C1.aircraftNo <> C4.aircraftNo) ?
(C2.aircraftNo <> C4aircraftNo) ? (C3aircraftNo <> C4aircraftNo))}