GATE CSE 1992 | Question 13b | Relational Algebra

GATE Question (GATE CSE 1992 | Question: 13b)

Suppose we have a database consisting of the following three relations:

• FREQUENTS(Customer, Hotel): Indicates the hotels each customer visits.
• SERVES(Hotel, Snack): Tells which snacks each hotel serves.
• LIKES(Customer, Snack): Indicates which snacks are liked by each customer.

Query: Print the hotels that serve the snack that customer Rama likes.

Detailed Solution

In relational algebra, to retrieve the hotels that serve the snack that Rama likes, we need to join the appropriate relations and filter them based on the snack preference of Rama. Here’s the solution:

Step-by-Step Process:

1. Select the snacks that Rama likes from the LIKES relation:

   σcustomer="Rama"(LIKES)

2. Join this result with the SERVES relation to find the hotels that serve the snacks Rama likes:

   σcustomer="Rama"(LIKES) ⨝ SERVES

3. Project the hotel names from the result:

   πHotel(σcustomer="Rama"(LIKES) ⨝ SERVES)

4. Join with the FREQUENTS relation to ensure Rama has visited these hotels:

   πHotel((σcustomer="Rama"(LIKES) ⨝ SERVES) ⨝ σcustomer="Rama"(FREQUENTS))

Final Relational Algebra Expression:

The final expression can be written as:

πHotel((σcustomer="Rama"(LIKES) ⨝ SERVES) ⨝ σcustomer="Rama"(FREQUENTS))

Summary

In this query, we aim to find the hotels that serve the snacks Rama likes. First, we identify the snacks Rama likes using a selection on the LIKES relation. We then join this result with the SERVES relation to determine which hotels serve these snacks. Finally, we filter these results to only include hotels that Rama has visited by performing a natural join with the FREQUENTS relation. This ensures that the hotels listed both serve the desired snack and have been visited by the customer. The relational algebra expression captures the process in three steps: select the desired snacks, perform joins to connect hotels and snacks, and finally project the hotel names.

Potential Student Doubts and Clarifications

• Why do we need to use a natural join?
  Natural joins automatically combine tables based on common attributes. Here, we use natural joins because we are connecting the SERVES and LIKES relations on the Snack attribute, and the FREQUENTS relation on the Hotel attribute. This simplifies the query by removing unnecessary Cartesian products and selections.
• What happens if Rama has visited a hotel but doesn’t like any of its snacks?
  The query ensures that only hotels serving a snack liked by Rama are considered. If no such hotel exists, the result will simply be empty.
• Is there an alternative way to express this query?
  Yes, the query can be expressed using equivalent relational operations like JOIN followed by selections. However, the natural join simplifies the process by automatically matching common attributes.

Tutorial on Natural Joins and Relational Algebra (500 words)

In relational database theory, natural joins are a fundamental operation used to combine two relations (tables) based on their common attributes. Unlike a Cartesian product, which pairs every tuple from one relation with every tuple from another, a natural join automatically pairs only those tuples that have matching values in their common attributes. This leads to more meaningful and less redundant results.

How Natural Join Works:

Let’s consider two relations: R1(A, B) and R2(B, C). These two relations share a common attribute, B. When we perform a natural join between R1 and R2, the result will consist of tuples where the values of B in both relations are equal. The result will include all attributes from both relations, but B will only appear once.

The syntax for a natural join is:

R1 ⨝ R2

Advantages of Natural Join:

• Simplicity: Natural joins eliminate the need to explicitly specify join conditions.
• Efficiency: Reduces redundancy in the result by ensuring that common attributes are not duplicated.

Example:

Consider the following two tables:

Hotels:

The natural join of these two tables on the Snack attribute would result in the following:

In summary, natural joins are a core component of relational algebra, streamlining queries that require combining multiple relations. Their simplicity and ability to reduce redundancy make them ideal for database operations where relations share common attributes.