Question

Consider the following relational database schema consisting of the four relation schemas: passenger ( pid, pname, pgender, pcity) agency ( aid, aname, acity) flight (fid, fdate, time, src, dest) booking (pid, aid, fid, fdate)

Answer the following questions using relational algebra queries
a) Get the complete details of all flights to Italy.
b) Get the details about all flights from paris to italy.
c) Find the passenger names for those who do not have any bookings in any flights.
d) Find the agency names for agencies that located in the same city as pascenger with passenger id 512
e) Get the details of flights that are scheduled on either of the dates 01/1/2023 or 02/1/2023 or both at 15:00 hours.
f) Find the details of all male passengers who are associated with Jet agency.

Answer 1

To answer the given questions using relational algebra queries, we can use operations like SELECT, JOIN, MINUS, and UNION. The queries provided in the answer provide the solutions to each question, giving specific details based on the given relational database schema.

Step-by-step explanation:

a) Get the complete details of all flights to Italy:To get the complete details of all flights to Italy, we can use the SELECT and JOIN operations in relational algebra. Here is the query:π(fid, fdate, time, src, dest) (flight ⨝ dest = 'Italy')(flight)b) Get the details about all flights from Paris to Italy:To get the details about all flights from Paris to Italy, we can modify the previous query by adding a condition on the source city:π(fid, fdate, time, src, dest) (flight ⨝ (src = 'Paris' ∧ dest = 'Italy'))(flight)c) Find the passenger names for those who do not have any bookings in any flights:To find the passenger names for those who do not have any bookings in any flights, we can use the MINUS operation in relational algebra. Here is the query:π(pname) (passenger) - π(pname) (booking)d) Find the agency names for agencies that are located in the same city as passenger with passenger id 512:To find the agency names for agencies that are located in the same city as the passenger with passenger id 512, we can use the SELECT and JOIN operations.

Here is the query:π(aname) (agency ⨝ aid = aid (passenger ⨝ pid = 512 ∧ acity = pcity))e) Get the details of flights that are scheduled on either of the dates 01/1/2023 or 02/1/2023 or both at 15:00 hours:To get the details of flights that are scheduled on either of the dates 01/1/2023 or 02/1/2023 or both at 15:00 hours, we can use the UNION operation in relational algebra. Here is the query:π(fid, fdate, time, src, dest) (flight ⨝ ((fdate = '01/1/2023' ∨ fdate = '02/1/2023') ∧ time = '15:00'))f) Find the details of all male passengers who are associated with Jet agency:To find the details of all male passengers who are associated with Jet agency, we can use the SELECT and JOIN operations. Here is the query:π(pid, pname, pgender, pcity) (passenger ⨝ pgender = 'male')(booking) ⨝ pid = pid (agency ⨝ aname = 'Jet')