edited by
17,094 views
85 votes
85 votes

Which of the following query transformations (i.e., replacing the l.h.s. expression by the r.h.s expression) is incorrect? R1 and R2 are relations, C1 and C2 are selection conditions and A1 and A2 are attributes of R1.

  1. $\sigma_{C_1} \left(\sigma_{C_2}\left(R_1\right)\right) \to \sigma_{C_2} \left(\sigma_{C_1}\left(R_1\right)\right)$
  2. $\sigma_{C_1} \left(\pi_{A_1}\left(R_1\right)\right) \to \pi_{A_1} \left(\sigma_{C_1}\left(R_1\right)\right)$
  3. $\sigma_{C_1} \left(R_1 \cup R_2\right) \to  \sigma_{C_1}\left(R_1\right) \cup \sigma_{C_1} \left(R_2\right)$
  4. $\pi_{A_1} \left(\sigma_{C_1}\left(R_1\right)\right) \to \sigma_{C_1} \left(\pi_{A_1}\left(R_1\right)\right)$
edited by

6 Answers

Best answer
51 votes
51 votes
D) if the selection condition is on attribute $A2$,  then we cannot replace it by RHS as there will not be any attribute $A2$ due to projection of $A1$ only.
edited by
14 votes
14 votes
actually in (b) option for validity of L.H.S condition c1 must be applied on attribute a1 and same condition will be applicable  after projection will give  same answer

but in option (d) for validity condition may be any thing because selection is being here first suppose if selection is based on any other attribute other than a1 then in RHS after projection how can we apply same condition because attribute other than a1 are not present there

am i right sir
8 votes
8 votes
a) & c) are correct!  only b) & d) we need to look deep :

Actually, we are assuming here that LHS to be specific and trying to transform the same meaning to RHS. So in b) LHS specifically selects attribute A1 then applies condition C1 so interchanging them in RHS will do no harm.

But in d) we are keeping projections options open by applying first C1 then A1 but in RHS we are restricting by projecting only A1 then C1 so it's not safe.  

For RHS in d) C1 may have been related to other attributes but since they will not be present then the query might return empty but in LHS we are keeping options and not restricting.

Thus answer d
7 votes
7 votes

Let the relation R be

A B C
1 x a
2 y b
3 z c

Let the condition be  πA(σ ("A=2" and "C = b" (R))), it will fetch the result as "2". On the other hand the condition  (σ ("A=2" and "C = b" (πA (R)))), it will fetch nothing.

Therefore, option D is false.

 

Answer:

Related questions

47 votes
47 votes
2 answers
3
Kathleen asked Sep 25, 2014
9,755 views
There are five records in a database.$$\begin{array}{|c|c|c|c|} \hline \textbf {Name} & \textbf {Age} & \textbf {Occupation} & \textbf{Category } \\\hline \text{Rama} & ...