19,269 views
81 votes
81 votes

SQL allows duplicate tuples in relations, and correspondingly defines the multiplicity of tuples in the result of joins. Which one of the following queries always gives the same answer as the nested query shown below: 

select * from R where a in (select S.a from S)
  1. select R.* from R, S where R.a=S.a
  2. select distinct R.* from R,S where R.a=S.a
  3. select R.* from R,(select distinct a from S) as S1 where R.a=S1.a
  4. select R.* from R,S where R.a=S.a and is unique R

6 Answers

104 votes
104 votes

C)

Consider the following instances of $R$ and $S$

$$\overset{R}{\begin{array}{|l|l|}\hline \text{A} & \text{B} & \text{C} \\\hline  \text{1} & \text{2} &\text{3} \\\hline   \text{1} & \text{2} &\text{3} \\\hline   \text{7} & \text{8} &\text{9} \\\hline   \text{7} & \text{8} &\text{9} \\\hline  \end{array}} \qquad \overset{S}{\begin{array}{|l|l|}\hline \text{A} & \text{X} & \text{Z} \\\hline  \text{1} & \text{2} &\text{3} \\\hline   \text{3} & \text{5} &\text{7} \\\hline   \text{7} & \text{6} &\text{5} \\\hline   \text{7} & \text{6} &\text{5} \\\hline  \end{array}}$$
Now output of given query 

select * from R where a in (select S.a from S)
$$\begin{array}{|l|l|}\hline \text{A} & \text{B} & \text{C} \\\hline  \text{1} & \text{2} &\text{3} \\\hline   \text{1} & \text{2} &\text{3} \\\hline   \text{7} & \text{8} &\text{9} \\\hline   \text{7} & \text{8} &\text{9} \\\hline  \end{array}$$

For Option,

A) since multiplicity of tuples is disturbed

select R.* from R, S where R.a=S.a 

∴ Output will be 

$$\begin{array}{|l|l|}\hline \text{A} & \text{B} & \text{C} \\\hline  \text{1} & \text{2} &\text{3} \\\hline   \text{1} & \text{2} &\text{3} \\\hline   \text{7} & \text{8} &\text{9} \\\hline   \text{7} & \text{8} &\text{9} \\\hline  \text{7} & \text{8} &\text{9} \\\hline   \text{7} & \text{8} &\text{9} \\\hline  \end{array}$$
B)

select distinct R.* from R,S where R.a=S.a 
∵ only Distinct R will be chosen in the end so, output will be
$$\begin{array}{|l|l|}\hline \text{A} & \text{B} & \text{C} \\\hline  \text{1} & \text{2} &\text{3}\\\hline   \text{7} & \text{8} &\text{9} \\\hline \end{array}$$
C) ANSWER
select R.* from R,(select distinct a from S) as S1 where R.a=S1.a

Multiplicity of tuples is maintained. ∵ Multiplicity of duplicate tuples will be distributed when there is a match between R.a and S.a and for that match S.a’s value is repeated.

So, Output will be 
$$\begin{array}{|l|l|}\hline \text{A} & \text{B} & \text{C} \\\hline  \text{1} & \text{2} &\text{3} \\\hline   \text{1} & \text{2} &\text{3} \\\hline   \text{7} & \text{8} &\text{9} \\\hline   \text{7} & \text{8} &\text{9} \\\hline  \end{array}$$

edited by
6 votes
6 votes

It must be C bcoz it is the only option in which lossless decomposition condition can be safisfied.

and on joining the two tables..we'll not get any extra row.

For lossless decomposition : from the joining table's....one of the table's column must be key(or we can say unique or distinct).

for example R(a,b,c) and S(x,b,z) are two tables.So,for lossless decomposition the common attribute (i.e., 'b' )  for one of the table must be unique.

select R.* from R,(select distinct a from S) as S1 where R.a=S1.a

Here we are simply making the column 'a' of table 'S' distinct.Therefore satisfying the above condition.

Correct me if I am wrong

0 votes
0 votes

Multiplicity of duplicate tuples will be distributed when there is a match between R.a and S.a; and for that match, S.a's value is repeated in each cases except the third case.

So, the output of query given in the question matches with the output of (C).

Answer:

Related questions

45 votes
45 votes
6 answers
1
go_editor asked Sep 28, 2014
20,614 views
Consider a join (relation algebra) between relations $r(R)$ and $s(S)$ using the nested loop method. There are $3$ buffers each of size equal to disk block size, out of w...
61 votes
61 votes
3 answers
2
33 votes
33 votes
3 answers
4
go_editor asked Sep 28, 2014
7,205 views
Given an instance of the STUDENTS relation as shown as below$$\begin{array}{|c|c|c|c|c|} \hline \textbf {StudentID} & \textbf{StudentName} & \textbf{StudentEmail} & \text...