20,928 views

Let $R$ and $S$ be relational schemes such that $R=\{a,b,c\}$ and $S=\{c\}.$ Now consider the following queries on the database:

1. $\pi_{R-S}(r) - \pi_{R-S} \left (\pi_{R-S} (r) \times s - \pi_{R-S,S}(r)\right )$
2. $\left\{t \mid t \in \pi_{R-S} (r) \wedge \forall u \in s \left(\exists v \in r \left(u = v[S] \wedge t = v\left[R-S\right]\right )\right )\right\}$
3. $\left\{t \mid t \in \pi_{R-S} (r) \wedge \forall v \in r \left(\exists u \in s \left(u = v[S] \wedge t = v\left[R-S\right]\right )\right ) \right\}$
4. Select R.a,R.b
From R,S
Where R.c = S.c

Which of the above queries are equivalent?

1. $1$ and $2$
2. $1$ and $3$
3. $2$ and $4$
4. $3$ and $4$

1st one is R divide S

refer: http://users.abo.fi/soini/divisionEnglish.pdf page number 3

@Arjun Thank you sir for all the links provided it is very useful in understanding the concepts. Have you put up any blog which contains standard resources and links for all concepts/subjects?

Query 2 –

for all tuples in s ( there exist a tuple in r such that s=r.c & r.a,r.b = our required tuple)

inner loop will run for every tuple or element in table s

1. $\pi_{R-S}(r) - \pi_{R-S} \left (\pi_{R-S} (r) \times s - \pi_{R-S,S}(r)\right )$
$\quad= \pi_{a,b}(r) - \pi_{a,b} \left (\pi_{a,b} (r) \times s - \pi_{R}(r) \right)$
$\quad=(r/s)$
2. Expanding logically the statement means to select $t (a,b)$ from $r$ such that for all tuples $u$ in $s,$ there is a tuple $v$ in $r,$ such that $u = v[S]$ and $t = v[R-S].$ This is just equivalent to $(r / s)$
3. Expanding logically the statement means that select $t (a,b)$ from $r$ such that for all tuples $v$ in $r,$ there is a tuple $u$ in $s,$ such that $u = v[S]$ and $t = v[R-S].$ This is equivalent to saying to select $(a,b)$ values from $r,$ where the $c$ value is in some tuple of $s.$
4. This selects $(a,b)$ from all tuples from $r$ which has an equivalent $c$ value in $s.$

So, $1$ and $2$ are equivalent.
$$\overset{r}{\begin{array}{|c|c|c|} \hline \textbf{a} & \textbf {b} & \textbf {c} \\\hline \text{Arj }& \text{TY} & 12 \\\hline \text{Arj }& \text{TY} & 14 \\\hline \text{Cell }& \text{TR} & 13\\\hline \text{Tom }& \text{TW} & 12\\\hline \text{Ben }& \text{TE} & 14\\\hline \end{array}} \qquad \overset{s}{\begin{array}{|c|c|c|} \hline \textbf {c} \\\hline 12 \\\hline 14\\\hline \end{array}}$$

1. will give $\langle Arj, TY \rangle.$
2. will give $\langle Arj, TY \rangle.$
3. will not return any tuple as the $c$ value $13,$ is not in $s.$
4. will give $\langle Arj, TY\rangle, \langle Arj, TY \rangle, \langle Tom, TW \rangle, \langle Ben, TE \rangle.$

http://pages.cs.wisc.edu/~dbbook/openAccess/firstEdition/slides/pdfslides/mod3l1.pdf

Correct Answer: $A$

by

Sir , in option second (u = v[S] ^ t=v[R-S]) ..... How we are executing it please explain with the help of table Sir ...

This question is just using different interpretations of “division” operator which is well explained in most DBMS textbooks (like Connolly for example). This link also has a good explanation: https://stackoverflow.com/questions/34978533/how-to-understand-u-r%C3%B7s-the-division-operator-in-relational-algebra