The Gateway to Computer Science Excellence

+56 votes

Which of the following query transformations (i.e., replacing the l.h.s. expression by the r.h.s expression) is incorrect? R_{1} and R_{2} are relations, C_{1} and C_{2 }are selection conditions and A_{1} and A_{2} are attributes of R_{1.}

A. $\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)$

B. $\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)$

C. $\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)$

D. $\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)$

0

y B is correct bcz same reasoning valid for B also wch is for D ten ans shd B& D both incoorect kindly explain

+7

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)

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)

+38 votes

Best answer

+14

In (B), first we select columns and then apply select conditions on them. So, it can be assumed that select conditions are based the selected columns only.

+23

For that LHS of B should be an invalid query. Since the question talks about query transformation and not just query equivalence, we can assume LHS is a valid one.

+28

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

0

I have a questions:

1) for LHS of option B, can C1 be applied on attribute A2 ?

OR

2) for RHS of option D, can C1 be applied on attribute A2 ?

Will the above operations return anything (empty relation) ? or they are invalid operations?

@Arjun Sir

1) for LHS of option B, can C1 be applied on attribute A2 ?

OR

2) for RHS of option D, can C1 be applied on attribute A2 ?

Will the above operations return anything (empty relation) ? or they are invalid operations?

@Arjun Sir

+6

I have tried to explain this way. @Arjun Sir, please check if is right way to do it.

A->B ..if A is true, then B is true to make "A implies B is valid"

Else A is true and B is false, then "A implies B is not valid"

So either of the cases, LHS has to be true. Now we need to check whether RHS is true or false. Here I am not bothered when A is false.

Here true means getting some attribute values or non empty set.

Option B:

____________

LHS: first obtain columns A1, then apply the condition C1. Since LHS is true means some values are there. So we can confirm that C1 is related to A1 and no other attributes, so we got the LHS is true.

Option B RHS: Since it is confirmed from LHS that C1 is related to A1, so RHS will give same number of values.

So option B is valid.

Option D.

__________

If C1 is based on only attribute A1, then both LHS and RHS will give same result.

But in LHS , C1 is not bound to only A1. It may have any attributes from A1, A2,A3,...An.

So let's say C1 is based on attribute A2, then it will select only those tuples where A2 condition satisfy and from those tuples it will select only attribute A1. By doing so we got some attribute values on left side since LHS is true.

Now check the RHS: Select all the attribute A1. On top of that when I apply C1 which is based on A2, then there will be no values. So RHS is empty.

So LHS has some values(true) and RHS is empty set(false). So for option D, LHS->RHS is not valid.

So only option B is true.

A->B ..if A is true, then B is true to make "A implies B is valid"

Else A is true and B is false, then "A implies B is not valid"

So either of the cases, LHS has to be true. Now we need to check whether RHS is true or false. Here I am not bothered when A is false.

Here true means getting some attribute values or non empty set.

Option B:

____________

LHS: first obtain columns A1, then apply the condition C1. Since LHS is true means some values are there. So we can confirm that C1 is related to A1 and no other attributes, so we got the LHS is true.

Option B RHS: Since it is confirmed from LHS that C1 is related to A1, so RHS will give same number of values.

So option B is valid.

Option D.

__________

If C1 is based on only attribute A1, then both LHS and RHS will give same result.

But in LHS , C1 is not bound to only A1. It may have any attributes from A1, A2,A3,...An.

So let's say C1 is based on attribute A2, then it will select only those tuples where A2 condition satisfy and from those tuples it will select only attribute A1. By doing so we got some attribute values on left side since LHS is true.

Now check the RHS: Select all the attribute A1. On top of that when I apply C1 which is based on A2, then there will be no values. So RHS is empty.

So LHS has some values(true) and RHS is empty set(false). So for option D, LHS->RHS is not valid.

So only option B is true.

+7

Question says " replacing the l.h.s. expression by the r.h.s expression", means they are asking us to assume that the L.H.S. expression is true and works fine, then if we replace this LHS expression by RHS one, would it work fine ALWAYS?

0

yeah.. that make sense now..

so, in option D , rhs can be made empty if condition is not on projected attribute so it will result in false though lhs is right.

is it fine ?

0

pls its just a doubt , pls tell me

if i have a table

R

A1 | A2 |
---|---|

1 | 5 |

3 | 8 |

5 | 9 |

now if i apply $\sigma _{A2<10}(\Pi_{A1} (R))$

what will i get???

0

It is asked if LHS can be replaced by RHS , so basically does RHS able to produce same ROWS and COLUMNS that LHS can PRODUCE ?

-> In option (b) , in LHS , we first TAKE OUT a column A1 and then apply condition C1 on THAT column .

So it is guaranteed that condition is applied on A1.

Thus we get Column A1 and some Rows in answer according to condition C1.

Now in RHS we apply SAME condition C1 on WHOLE TABLE .

Whole Table will definitely contain Column A1.

And then we keep only Column A1,and eliminate Rest , same as lhs.

-> In option (b), it is inverse of option (b).

LHS = First Condition c1 Is Applied on Whole table. They Have Not Mentioned that condition is applied on which column. Lets assume its applies on A2. Thus we get some rows.

Now Projection applied on A1. Thus we get column A1 and rows(which satisfy c1) in OUTPUT.

But In RHS , they are Projecting column A1 and applying same PREVIOUS Condition C1 which we assumed is applied on A2.

But because we projected only A1 from table we cant apply condition on A2 (bcoz it doesnt exist) . ERROR.

ITS JUST THE ORDER THAT MATTERS HERE AND WHETHER RHS CAN PRODUCE SAME OUTPUT AS LHS IN ALL CASES.

-> In option (b) , in LHS , we first TAKE OUT a column A1 and then apply condition C1 on THAT column .

So it is guaranteed that condition is applied on A1.

Thus we get Column A1 and some Rows in answer according to condition C1.

Now in RHS we apply SAME condition C1 on WHOLE TABLE .

Whole Table will definitely contain Column A1.

And then we keep only Column A1,and eliminate Rest , same as lhs.

-> In option (b), it is inverse of option (b).

LHS = First Condition c1 Is Applied on Whole table. They Have Not Mentioned that condition is applied on which column. Lets assume its applies on A2. Thus we get some rows.

Now Projection applied on A1. Thus we get column A1 and rows(which satisfy c1) in OUTPUT.

But In RHS , they are Projecting column A1 and applying same PREVIOUS Condition C1 which we assumed is applied on A2.

But because we projected only A1 from table we cant apply condition on A2 (bcoz it doesnt exist) . ERROR.

ITS JUST THE ORDER THAT MATTERS HERE AND WHETHER RHS CAN PRODUCE SAME OUTPUT AS LHS IN ALL CASES.

+13 votes

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

0

Other way to look at this question is like just check if number of attributes available for select operator is same in both LHS and RHS. If projection is being done before selection, attributes available for selection are less and hence the option d.

why B is correct then? Using above logic in LHS of B suppose select operator getting attribute A,B for comparison then in RHS it is getting superset of attributes which will yield same result.

why B is correct then? Using above logic in LHS of B suppose select operator getting attribute A,B for comparison then in RHS it is getting superset of attributes which will yield same result.

+5 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

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

+1

Other way to look at this question is like just check if number of attributes available for select operator is same in both LHS and RHS. If projection is being done before selection, attributes available for selection are less and hence the option d.

why B is correct then? Using above logic in LHS of B suppose select operator getting attribute A,B for comparison then in RHS it is getting superset of attributes which will yeild same result.

why B is correct then? Using above logic in LHS of B suppose select operator getting attribute A,B for comparison then in RHS it is getting superset of attributes which will yeild same result.

+2 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.

0 votes

This question can also be answered by using the p -> q analogy this implies statement only becomes FALSE when p is TRUE and q is FALSE

so, in option D we can see from the below given examples p is TRUE and q is FALSE but in all other option we we get TRUE p-> q

Please refer p-> q truth table i.e.:

P Q

T T =T

T F=F

F T= T

F F=T

so, in option D we can see from the below given examples p is TRUE and q is FALSE but in all other option we we get TRUE p-> q

Please refer p-> q truth table i.e.:

P Q

T T =T

T F=F

F T= T

F F=T

0 votes

-> In option (b) , in LHS , we first TAKE OUT a column A1 and then apply condition C1 on THAT column .

So it is guaranteed that condition is applied on A1.

Thus we get Column A1 and some Rows in answer according to condition C1.

Now in RHS we apply SAME condition C1 on WHOLE TABLE .

Whole Table will definitely contain Column A1.

And then we keep only Column A1,and eliminate Rest , same as lhs.

-> In option (b), it is inverse of option (b).

LHS = First Condition c1 Is Applied on Whole table. They Have Not Mentioned that condition is applied on which column. Lets assume its applies on A2. Thus we get some rows.

Now Projection applied on A1. Thus we get column A1 and rows(which satisfy c1) in OUTPUT.

But In RHS , they are Projecting column A1 and applying same PREVIOUS Condition C1 which we assumed is applied on A2.

But because we projected only A1 from table we cant apply condition on A2 (bcoz it doesnt exist) . ERROR.

ITS JUST THE ORDER THAT MATTERS HERE AND WHETHER RHS CAN PRODUCE SAME OUTPUT AS LHS IN ALL CASES.

52,315 questions

60,432 answers

201,778 comments

95,257 users