Scribd
Upload
199 views3 pages

Relational Algebra Query

Uploaded by

ishitaag2003
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
199 views3 pages

Relational Algebra Query

Uploaded by

ishitaag2003
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

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 =

You might also like