edited by
24,499 views
90 votes
90 votes

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$
edited by

1 Answer

Best answer
73 votes
73 votes
  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$

edited by
Answer:

Related questions

61 votes
61 votes
6 answers
2