Consider the following relational database schema consisting of the four relation schemas.
- passenger (pid, pname, pgender, pcity)
- agency (aid, aname, acity)
- bus (bid, bdate, time, src, dest)
- booking (pid, aid, bid, bdate)
Answer the following question using relational algebra queries:
- Give the details of all buses from Dhangadhi to Kathmandu.
- Find the name of passengers who booked at least one bus.
- Find the bus number for the passenger with pid p04 for bus to butwal before 20/05/2021.
- Find the name of passenger who has not booked any bus.
- Find the details of all male passengers associated with Ugratara Agency.
A: Relational Algebra for Relations passenger, agency, bus and booking: 67
a. Give the details of all buses from Dhangadhi to Kathmandu.
σ src = 'Dhangadhi' ^ dest = 'Kathmandu' ( bus )
b. Find the name of passengers who booked at least one bus.
π passenger.pname ( passenger ⨝ booking )
Explanation:
We are performing Natural Join here. Thus, only those 'pid's will be listed which are present in 'booking' relation. Being present in 'booking' relation means the passenger ('pid') has done at least one booking.
c. Find the bus number for the passenger with pid p04 for bus to butwal before 20/05/2021.
π bid ( σ booking.pid = 'p04' ^ bus.dest = 'butwal' ^ date(booking.bdate) < '20/05/2021' ( bus ⨝ booking ) )
Explanation:
The attribute to find: bus number (ie; bid) from 'bus' schema.
Given Conditions:
1. Passenger's pid = 'p04'. From 'booking' schema.
2. Bus to butwal ie; dest = 'butwal' . From 'bus' schema.
3. Before '20/05/2021' ie; bdate < 'booking'. From either 'bus' or 'booking' schema.
Hence, join is needed. And for comparing date, we are using 'date()' function.
d. Find the name of passengers who have not booked any bus.
Method #1: Using Left Outer Join ( ⟕ )
π pname ( σ bid = NULL ( passenger ⟕ booking ) )
Explanation:
Performing a Left Outer Join will list all passengers and bookings even if pid is not present in 'booking' relation.
For those passengers (pids), the value of bid will be NULL.
Method #2: Using Natural Join ( ⨝ ) and Difference ( - )
Temp1 ← π pname ( passenger ⨝ booking )
π pname ( passenger ) - Temp1
Explanation:
Temp1 will have pname of passengers who have done at least one booking.
The 'difference' of 'all passengers' with Temp1 will give us a list of passengers who have not done any booking.
e. Find the details of all male passengers associated with Ugratara Agency.
π pid, pname, pgender, pcity ( σ pgender = 'male' ^ aname = 'Ugratara Agency' ( passenger ⨝ booking ⨝ agency ) )