Relational algebra in database management systems solved exercise

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;

Solution:

Relational algebra operators: σ – selection with conditions (It selects all tuples that satisfies the conditions. Shows entire table with respect to the structure) Π – projection operator (It selects the attributes which are listed here) ⨝ - natural join operator (Binary operator that join two relations on common attributes’ values) -, ∪, and ∩ - set operators (difference, union and intersection)

Most of the following queries can be written in many different ways.

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 for flights to Chennai before 06/11/2020.

Π _fid (σ _{pid = 123} (booking) ⨝ σ _{dest = “Chennai” ^ fdate < 06/11/2020} (flight))

[Hint: Given conditions are pid, dest, and fdate. To get the flight id for a passenger given a pid, we have two tables flight and booking to be joined with necessary conditions. From the result, the flight id can be projected]

 -----------------------------------------------------------------------------------------------------

d) Find the passenger names for passengers who have bookings on at least one flight.

Π _pname (passenger ⨝ booking)

 -----------------------------------------------------------------------------------------------------

e) Find the passenger names for those who do not have any bookings in any flights.

Π _pname ((Π _pid (passenger) - Π _pid (booking)) ⨝ passenger)

[Hint: here applied a set difference operation. The set difference operation returns only pids that have no booking. The result is joined with passenger table to get the passenger names.]

-----------------------------------------------------------------------------------------------------

 

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)))

[Hint: we performed a theta join on equality conditions (equi join) here. This is done between details of passenger 123 and the agency table to get the valid records where the city values are same. From the results, aname is projected.]

 -----------------------------------------------------------------------------------------------------

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 hours.

(σ _{fdate = 01/12/2020 ^ time = 16:00} (flight)) ∪ (σ _{fdate = 02/12/2020 ^ time = 16:00} (flight))

 -----------------------------------------------------------------------------------------------------

i) Find the agency names for agencies who do not have any bookings for passenger with id 123.

Π _aname (agency ⨝ (Π _aid (agency) – Π _aid (σ _{pid = 123} (booking)))

-----------------------------------------------------------------------------------------------------

j) Find the details of all male passengers who are associated with Jet agency.

Π _{passengers.pid, pname, pcity} (σ _{pgender = “Male” ^ aname = ‘Jet’} (passengers ⨝ booking ⨝ agency))

[Hint: To get the link between passengers and agency, we need to join all three tables passengers, booking, and agency with necessary condition. Here, agency links both passengers and agency. As we have performed natural join operation between all three tables, the degree of the result will consist of all attributes from all the three tables. Hence, we project only passengers details as these are mentioned as required.]

*************

Related links:

• Go to DBMS - solved exercises page 

• Go to DBMS - Multiple Choice Questions (MCQs) page

solved exercises in dbms

solved exercises in relational algebra

solved exercises in SQL

SQL and relational algebra short answers

SQL and relational algebra short exercises

Relational algebra exercises with answers explained