GATE CSE 2014 Set 2 | Question: 54

Question

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)

• A. select R.* from R, S where R.a=S.a
• B. select distinct R.* from R,S where R.a=S.a
• C. select R.* from R,(select distinct a from S) as S1 where R.a=S1.a
• D. select R.* from R,S where R.a=S.a and is unique R

Comments

Take an example table of R having duplicate rows the evaluate the given query in question, you'll see duplicate tuples will be present in the output but not in b) and d) since "distinct" is used thus both are out.

Now take another table having all unique elements under attribute 'a' of table R and table S has duplicate values of some values of 'a' attribute which will produce output with only unique elements in 'a' since R.a has all unique values. Now when you evaluate the same with option a) you'll find duplicate tuples come into the result. So a) also out.

Thus c) is the answer.

In c) if duplicates are there in R.a they will be preserved and if all elements are unique in R.a, even then also c)  produces output with only unique values of R.a.

---

Rajesh Pradhan

good observation. qsn is solved within 2 seconds

---

someone plz explain option 4. why it cant be answer ??

Answer 1

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}$$

Comments

hey you have taken two same tuples in relations ,is that allowed in sql??

---

In theory, for a relation, duplicate tuples are not allowed, as a relation is basically a set, and set cannot contain duplicates. But in SQL it is allowed.

---

I got confusion with option C)

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

Here S1 return 1,3,7
R and S1 natural join is performed . Which produces same result as of option A ?

Where did I went wrong ? Could someple pls correct me ?
 

---

@akhilnadhpc : i think you got error in the "where a in " part.  where a in (..), it will only match inside the bracket,it doesnt matter whether there are repeating values inside the bracket,so acc to this example it will return 1177

---

@resilientknight Thanks. Got it :)
 According to my understanding ,

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

Distinct a from s => 1,3,7
Natural Join R with S =>(1,1,7,7) * (1,3,7) => (1,1,7,7)

---

Very well explained. Thanks a lot

---

Exact two same tuple is not allowed in any relation and you did this mistake in the relation R

correct me if i am wrong

---

What will be output of option D

---

select * 
from R 
where a  in(select S.a 
             from S)

Here maximum tupple will be all tupple of R. i.e. Tupple cannot exceed number of tupple in R [ duplicate of s.a doesn't matter]

---

select R.* from R, S where R.a=S.a  = number of tupple can be more than R b/c of duplicate in s.a

---

select distinct R.* from R,S where R.a=S.a = here maximum tupple always subset of original R.

---

select R.* from R,(select distinct a from S) as S1 where R.a=S1.a. exactly what question says.

---

select R.* from R,S where R.a=S.a and is unique R = is unique R says tuples of R must be unique

---

What does the query in the question mean?

---

Is the query in option D even correct? Is "is unique R" part of actual query? I am getting syntax error while running "select R.* from R,S where R.a=S.a and is unique R"

https://www.jdoodle.com/a/MQZ

---

The answer has been clearly understood but in option D considering "is unique", is it actually allowed in SQL?

---

Bro there is no natural join there is cross product between R and S1, think more you will get through this question.

---

YOUR ANSWER IS CORRECT BUT YOUR EXPLANATION IS TOTALLY WRONG

---

yes, duplicates are allowed in sql.

---

@Abhrajyoti00

“multiplicity” means the cardinality or set of tuples?

---

@Pranavpurkar Yes, it is the cardinality or the number of times a tuple appears in the result of a query.

---

@Abhrajyoti00 Thanks :)

Answer 2

So, option c is correct.

Comments

What will be output of option D

Answer 3

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

Answer 4

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).