Consider the following database schema for relations representing airline-flight data: Flights (fno: integer, cityfrom: string, cityto: string, distance: integer, departs: datetime, arrives: datetime) Airplane (aid: integer, manufacturer: string, code: integer, range: integer) Certification (sid: integer, aid: integer, type: string, ispilot: boolean) Staff(sid: integer, sname: string, salary: integer) The Staff relation describes pilots, mechanics, and other kinds of staff as well; every pilot is certified to fly some aircraft, and every mechanic is certified to maintain some aircraft. (These certifications also imply that individual pilots and mechanics are qualified for their positions). Only pilots and mechanics have certifications. Write the following ten queries using the relational algebra (RA) with operations as described in lectures. Note that some of these queries cannot expressed in the RA, and if this is the case for a query, informally explain why this is so (i.e., a formal proof of inexpressibility is not necessary). 1. Find the sids of pilots certified for some Bombardier aircraft (i.e., an airplane manufactured by Bombardier). 2. Find the names of mechanics certified to maintain some Embraer aircraft. 3. Find the aids of all airplanes that can be used on non-stop flights from Toronto (Canada) to Seoul (South Korea). 4. Identify the flights that can be piloted by every pilot whose salary is more than $150,000. 5. Find the names of mechanics who can maintain airplanes with a range less than 3000 miles but are not certified to work on any Airbus airplane. Find the sids of staff who make the highest salary. 6. 7. Find the sids of staff who make the second-highest salary. 8. Find the sids of staff who are certified for the largest number of aircraft (either pilots or mechanics). 9. Find the sids of staff who are certified to fly exactly three aircraft. 10. Find the total amount paid to staff as salaries. Consider the following database schema for relations representing airline-flight data: Flights (fno: integer, cityfrom: string, cityto: string, distance: integer, departs: datetime, arrives: datetime) Airplane (aid: integer, manufacturer: string, code: integer, range: integer) Certification (sid: integer, aid: integer, type: string, ispilot: boolean) Staff(sid: integer, sname: string, salary: integer) The Staff relation describes pilots, mechanics, and other kinds of staff as well; every pilot is certified to fly some aircraft, and every mechanic is certified to maintain some aircraft. (These certifications also imply that individual pilots and mechanics are qualified for their positions). Only pilots and mechanics have certifications. Write the following ten queries using the relational algebra (RA) with operations as described in lectures. Note that some of these queries cannot expressed in the RA, and if this is the case for a query, informally explain why this is so (i.e., a formal proof of inexpressibility is not necessary). 1. Find the sids of pilots certified for some Bombardier aircraft (i.e., an airplane manufactured by Bombardier). 2. Find the names of mechanics certified to maintain some Embraer aircraft. 3. Find the aids of all airplanes that can be used on non-stop flights from Toronto (Canada) to Seoul (South Korea). 4. Identify the flights that can be piloted by every pilot whose salary is more than $150,000. 5. Find the names of mechanics who can maintain airplanes with a range less than 3000 miles but are not certified to work on any Airbus airplane. Find the sids of staff who make the highest salary. 6. 7. Find the sids of staff who make the second-highest salary. 8. Find the sids of staff who are certified for the largest number of aircraft (either pilots or mechanics). 9. Find the sids of staff who are certified to fly exactly three aircraft. 10. Find the total amount paid to staff as salaries
