Relational Algebra Exercises and Solutions
Uploaded by
ishitaag2003Relational Algebra Exercises and Solutions
Uploaded by
ishitaag2003Common questions
Powered by AITo determine agencies located in the same city as a particular passenger using relational algebra, use a natural join between the agency relation and the result of a selection operation on the passenger relation. The process involves matching the city field of agencies with the city of the specific passenger. The relational algebra query is: Π aname (agency ⨝ acity = pcity (σ pid = 123 (passenger))). This extracts agency names where the agency's city matches the city of the passenger with ID 123.
To identify all flights to a specific destination using relational algebra, a selection operation is performed on the flight relation with the condition set to match the desired destination. For flights destined for New Delhi, the query is: σ destination = “New Delhi” (flight). This filters the set of all flights to retrieve only those where the destination attribute is 'New Delhi'.
The expression to find flights from a specific source to a specific destination using relational algebra involves using the selection operation to filter the flight relation based on both source and destination criteria. For example, to find flights from Chennai to New Delhi, the query used is: σ src = “Chennai” ^ dest = “New Delhi” (flight). This applies a selection on the flight details where the source is 'Chennai' and the destination is 'New Delhi'.
To retrieve flight details scheduled on either or both of two specific dates at a given time using relational algebra, the set union operator is used. This combines flights for both dates into a single result set. The expression is: (σ fdate = 01/12/2020 ^ time = 16:00 (flight)) ∪ (σ fdate = 02/12/2020 ^ time = 16:00 (flight)). This gives all flights scheduled at 16:00 hours either on 01/12/2020, 02/12/2020, or both.
To find flights that operate on both of two specific dates at a certain time using relational algebra, the set intersection operator is used. This logic is implemented by first selecting flights that operate on each of the required dates and times, creating two separate sets, and then intersecting these sets to find common elements. The query is: (σ fdate = 01/12/2020 ^ time = 16:00 (flight)) ∩ (σ fdate = 02/12/2020 ^ time = 16:00 (flight)). This selects flights scheduled at 16:00 hours on both 01/12/2020 and 02/12/2020.
To determine male passengers associated with the 'Jet' agency, relational algebra can be used to filter and join multiple tables. First, select male passengers, then join this with booking and agency tables, and apply selection for the agency 'Jet'. The query is: Π passengers.pid, pname, pcity (σ pgender = “Male” ^ aname = ‘Jet’ (passengers ⨝ booking ⨝ agency)). This first filters male passengers, joins with bookings and agency, and further filters results to include only those associated with the agency named 'Jet'.
To find passengers who do not have any bookings, you can use the difference operator in relational algebra to identify passengers from the passenger relation who do not appear in the booking relation. The relational algebra query is: Π pname ((Π pid (passenger) - Π pid (booking)) ⨝ passenger). This subtracts the set of passenger IDs in the booking table from the passenger table and joins it back with the passenger table to get the corresponding names.
To find agencies without bookings for a specific passenger using relational algebra, perform a difference operation between all agencies and those that have bookings for the given passenger. Specifically, derive the agency IDs that have bookings for the passenger, and subtract these from all agency IDs. The query is: Π aname (agency ⨝ (Π aid (agency) – Π aid (σ pid = 123 (booking)))). This yields agency names that are not in the set obtained from bookings by passenger ID 123.
To find flight numbers for a passenger with a specific ID traveling to a destination before a given date using relational algebra, you would need to perform a natural join between the booking and flight relations and apply the necessary selection conditions. In the given example, this is achieved by: Π fid (σ pid = 123 (booking) ⨝ σ dest = “Chennai” ^ fdate < 06/11/2020 (flight)). This query first selects bookings for passenger ID 123, joins them with flights where the destination is Chennai, and where the flight date is before 06/11/2020.
To extract passenger names with bookings using relational algebra, you need to perform a natural join between the passenger and booking tables and then project on the passenger name attribute. The query for this operation is: Π pname (passenger ⨝ booking). This results in a list of passenger names who have made at least one booking.