Test by Bikram | Databases | Test 1 | Question: 2

Question

Under what condition does the selection operation distribute over theta join operation?

Let $R$ and $S$ are two relations then which one of the following statements about the above condition is TRUE?

1. $\sigma C (R \bowtie S) \equiv  (\sigma C1 (R)) \bowtie (\sigma C2 (S))$
2. $\sigma C (R \bowtie S) \equiv (\sigma C (R)) \bowtie S$
3. $\sigma C (R \bowtie S ) \equiv \sigma C2 ( (\sigma C1 (R)) \bowtie S)$
4. $\sigma C (R \bowtie S ) \equiv  \sigma C1 (  R \bowtie (\sigma C2  (S)))$

• A. i, iii
• B. i, ii, iii, iv
• C. ii, iii
• D. i, ii

Comments

@Abhinav93    and @Mainak  

here given condition is the selection operation have to distribute over theta join operation.

Please see the best answer, i hope you will understand the solution.

---

the answer is 1,3 and 4 as correct options

---

see my below answer, why you not consider option 2 as well ?

Answer 1

In this question given condition is : the selection operation have to distribute over theta join operation.

Where ii) says:

If all the attributes in the selection condition C involve only in the attributes of one of the relations being joined say, R

 then two operations can be written as follows:             σ_C( R ⋈ S ) ≡ (σ_C( R ) ) ⋈ R 

so this option says that the selection operation is distribute over theta join operation . Which match with given condition.

Now for rest options :

• If the selection condition C can be written as C1 and C2, where condition C1 involves only the attributes of R and/or condition C2 involves only the attributes of S,  then operations can be written as : either 

i) σ_C (R ⋈ S) ≡  (σ_C1 (R)) ⋈ (σ_C2 (S)) 

or

 iii) σ_C (R ⋈ S ) ≡ σ_C2 ( (σ_C1 (R) ) ⋈ S) 

or 

 iv) σ_C (R ⋈ S ) ≡  σ_C1 (  R ⋈ (σ_C2  (S) ) ) 

all three options also support that given condition - " selection operation distribute over theta join operation " .

hence all Four options are correct .

Comments

i am not able to understand in option (ii) σ_C (R ⋈ S) ≡ (σ_C (R)) ⋈ R  in the RHS how will we retain the attributes of S.

As per my understanding in RHS using σ_C (R) we are selecting tuples from R based on condition C, and then we are taking theta join with R again, so no way we will get the attributes of S. 

While in the LHS we first perform (R ⋈ S) which may result the attributes from R and S both and then we are selecting some tuples from the result based on condition C, therefore resulting in the attributes of S and R both.

So how does  (σ_C (R ⋈ S) ≡ (σ_C (R)) ⋈ R) is true? Please help me to understand. 

---

@Bikram  sir @srestha mam

If all the attributes in the selection condition C involve only in the attributes of one of the relations being joined say, R

I agree with this sir, But why again we are doing the $\Join$ with R ? 

---

yes, shouldnot be R, I think In option B) right side will be S

Answer 2

Please explain this  question which condition are they talking about?

Comments

@abhinav93

This question says there are 4 options , among which support that given condition in the question.

The condition is -   selection operation distribute over theta join operation