GATE CSE 1992 | Question: 13b

Question

Suppose we have a database consisting of the following three relations:
$$\begin{array}{|c|c|} \hline \text {FREQUENTS} & \text {(CUSTOMER, HOTEL)} \\\hline \text {SERVES} &  \text{(HOTEL, SNACKS)}\\\hline \text{LIKES} & \text{(CUSTOMER, SNACKS)}\\\hline \end{array}$$
The first indicates the hotels each customer visits, the second tells which snacks each hotel serves and last indicates which snacks are liked by each customer. Express the following query in relational algebra:

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

Answer 1

OPTIMIZED ANSWER

$\Pi_{hotel} \Big( ( \sigma _{customer = ``Rama"}{(LIKES)} )  \bowtie{SERVES} \Big)$

Comments

This one is a better answer as it selects the tuples from the LIKES table before performing the join. Hence the number of comparison required in the join are reduced.

---

Isn't we should also take natural join of FREQUENTS with (σcustomer=‘‘Rama"(LIKES))⋈SERVES) as there might be a case where a hotel serves the snacks liked by a customer but that customer doesn't visit that hotel...

---

Why not

$\Pi_{hotel} \Big( ( \sigma _{customer = ``Rama"}{(LIKES)} ) \times {SERVES} \Big)$

Moreover if we do join then Frequents also need to be included in join condition

---

is the given join is natural???

---

@nishant_magarde yes

---

But I think when no clause is given to the join, then it it simple cartesian product

---

ya, when no common attribute it is simple cross product but here they have common attribute SNACKS

---

got it.

---

@Gurdeep Saini

then check this query https://gateoverflow.in/19838/gate1997-76-a

why here used cross product?

---

@pallaviamu FREQUENTS table is not to be joined as the question doesn't mention the clause of printing only those hotels that Rama visits.

---

I think we have to consider only those Hotels where Rama visits. As the question states – “Print the hotels the serve the snack that customer Rama likes.” She should be called customer of only those hotels where she visits.

---

yes the answer should be :-

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

this should be the answer i think .

---

@jatinmittal199510  The thing is here we will select the customer with name rama so after the natural join  of seves and likes if there is no customer with name rama in likes then he will not be printed  and also if he likes the same item from different they also will be printed

Answer 2

π_Hotel(σ_{customer = "RAMA"}(LIKES (Natural Join)Serves))

Comments

see my comment this is not correct i think

Answer 3

$\Pi_{Hotel} (\;Serves\;\bowtie\;(\sigma_{Customer=Rama}\; Likes)\;)$