SVNIT, Surat
DBMM (MBA- IInd Sem )
Tutorial 2
1) 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.
b) Get the details about all flights from Chennai to New Delhi.
c) Find only the flight numbers for passenger with pid 123 for flights to Chennai
before 06/11/2020.
d) Find the passenger names for passengers who have bookings on at least one flight.
e) Find the passenger names for those who do not have any bookings in any flights.
f) Find the agency names for agencies that located in the same city as passenger
with passenger id 123.
g) Get the details of flights that are scheduled on both dates 01/12/2020 and
02/12/2020 at 16:00 hours.
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 hours.
i) Find the details of all male passengers who are associated with Jet agency.
j) Find the agency names for agencies who do not have any bookings for passenger
with id 123.
2) Suppliers(sID, sName, address)
Parts(pID, pName, colour)
Catalog(sID, pID, price)
Catalog[sID] ⊆ Suppliers[sID]
Catalog[pID] ⊆ Parts[pID]
a) Find the names of all red parts.
b) Find all prices for parts that are red or green. (A part may have different prices from
different manufacturers.)
c) Find the sIDs of all suppliers who supply a part that is red or green.
d) Find the names of all suppliers who supply a part that is red or green.