GATE CSE 2003 | Question: 30

Question

Consider the following SQL query

Select distinct $a_1, a_2, …, a_n$

from $r_1, r_2, …, r_m$

where P

For an arbitrary predicate P, this query is equivalent to which of the following relational algebra expressions?

• A. $\Pi_{a_1, a_2, … a_n}  \sigma_p \left(r_1 \times r_2 \times \dots \times r_m\right)$

• B. $\Pi_{a_1, a_2, … a_n}  \sigma_p \left(r_1 \bowtie r_2 \bowtie \dots \bowtie r_m \right)$

• C. $\Pi_{a_1, a_2, … a_n}  \sigma_p \left(r_1 \cup r_2 \cup \dots \cup r_m \right)$

• D. $\Pi_{a_1, a_2, … a_n}  \sigma_p \left(r_1 \cap r_2 \cap \dots \cap r_m \right)$

Answer 1

select distinct in SQL is equivalent to project and by default relation 1, relation 2 in SQL corresponds to cross-product. So, option A.

Comments

If query doesn't ask for distinct,is there any valid relational algebra expression for it?

---

@prayas No

RA: Uses set

SQL: Uses Multiset

---

Sir can you please explain why option B is not correct?

---

Option B is doing natural join meaning only those tuples having same value for common attribute(s) in both the relations are selected for output.

---

Oh ok.got it.Thank you sir

---

Why not union?? It will combine all tuples and return only those Which satisfy the condition!!

---

@vupadhayayx86

For cartesian product we don't check union compatability, but for performing union operation those tables must be union compatiable.

---

similarly  σ (R) is equivalent to select distinct * from R ?

Answer 2

Join intersection union might give lesser num of tuples cross gives all ,A option.

Comments

I have doubt in option 1 and option 2? here why not option 2?

Answer 3

A. SQL form the cartesian product of the relation named in the form clause, perform a relational algebra selection using the where clause predicate and then projects the result onto the attributes of select clause.

Answer 4

SELECT DISTINCT In SQL  -  Projection (Π) in Relational Algebra

FROM in SQL - Cartesian Product (×) in Relational Algebra

WHERE in SQL - SELECTION(σ) in Relational Algebra

So, the query will be equivalent to 

Πₐ₁,ₐ₂,…ₐₙσₚ₍ᵣ₁×ᵣ₂×⋯×ᵣₘ₎