Relational algebra – solved exercise
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 New Delhi.
σ destination = “New Delhi” (flight)
-------------------------------------------------------------------------------------------
----------
b) Get the details about all flights from Chennai to New
Delhi.
σ src = “Chennai” ^ dest = “New Delhi” (flight)
--------------------------------------------------------------------------------------------
---------
c) Find only the flight numbers for passenger with pid 123
Π fid (σ pid = 123 (booking) ⨝ σ dest = “Chennai” ^ fdate <
for flights to Chennai before 06/11/2020.
06/11/2020 (flight))
d) Find the passenger names for passengers who have
Π pname (passenger ⨝ booking)
bookings on at least one flight.
-------------------------------------------------------------------------------------------
----------
e) Find the passenger names for those who do not have
any bookings in any flights.
Π pid (booking)) ⨝ passenger)
Π pname ((Π pid (passenger) -
f) Find the agency names for agencies that located in the
same city as passenger with passenger id 123.
Π aname (agency ⨝ acity = pcity (σ pid = 123 (passenger)))
g) Get the details of flights that are scheduled on both
dates 01/12/2020 and 02/12/2020 at 16:00 hours.
(σ fdate = 01/12/2020 ^ time = 16:00 (flight)) ∩ (σ fdate =
02/12/2020 ^ time = 16:00 (flight))
[Hint: the requirement is for flight details for both dates in
common. Hence, set intersection is used between
the temporary relations generated from application of
various conditions.]
--------------------------------------------------------------------------------------------
---------
h) Get the details of flights that are scheduled on either of
the dates 01/12/2020 or 02/12/2020 or both at 16:00
(σ fdate = 01/12/2020 ^ time = 16:00 (flight)) ∪ (σ fdate =
hours.
02/12/2020 ^ time = 16:00 (flight))
-------------------------------------------------------------------------------------------
----------
i) Find the agency names for agencies who do not have
Π aname (agency ⨝ (Π aid (agency) – Π aid (σ pid =
any bookings for passenger with id 123.
123 (booking)))
--------------------------------------------------------------------------------------------
---------
j) Find the details of all male passengers who are
associated with Jet agency.
‘Jet’ (passengers ⨝ booking ⨝ agency))
Π passengers.pid, pname, pcity (σ pgender = “Male” ^ aname =
