The Gateway to Computer Science Excellence
First time here? Checkout the FAQ!
+32 votes

Given relations r(w, x) and s(y, z) the result of

select distinct w, x 
from r, s 

is guaranteed to be same as r, provided.

  1. r has no duplicates and s is non-empty
  2. r and s have no duplicates
  3. s has no duplicates and r is non-empty
  4. r and s have the same number of tuples
asked in Databases by Veteran (59.8k points)
edited by | 4.2k views
in general it is written that
select distinct w, x 

then distinct constraint is applied on only w or on both w, s  ??

distinct constraint apply to both w, x {select distinct w,x IS SAME AS distinct(w,x) } Also Select distinct w, distinct x {INVALID}

2 Answers

+43 votes
Best answer

This question is about SQL, in SQL Relations are MULTISET, not SET. So, $R$ or $S$ can have duplicated.

Answer: A.

A. If $R$ has duplicates, in that case, due to distinct keyword those duplicates will be eliminated in final result. So, $R$ can not have duplicates. If $S$ is empty $RXS$ becomes empty, so $S$ must be non empty. This is true.

B. Here, assume that S is empty. (No duplicates.) Then R X S will be empty. SO this is false.

C. Same argument as B.

D. Assume that $R$ has duplicates. Then Distinct keyword will remove duplicates. So, result of query $!= R$, so This is false.

answered by Boss (43.6k points)
edited by

Answer: (a)

The query selects all attributes of r. Since we have distinct in query, result can be equal to r only if r doesn’t have duplicates.

If we do not give any attribute on which we want to join two tables, then the queries like above become equivalent to Cartesian product. Cartisian product of two sets will be empty if any of the two sets is empty. So, s should have atleast one record to get all rows of r.

is distinct only on w or both w and x ?
@Gate Ranker18

It means distinct pair of (w,x)
@Arjun Sir

I think this question is faulty

because it is not join operation. It is cartesian product. After doing cartesian product on 2 tables, we cannot get back 1st table again

plz check it

@srestha  mam 

no need for join operation i think

but how can you consider "empty set" as "no duplicates" ?

can we do that ?
+11 votes

if s is empty r x s will give empty set, so s cant be empty
answered by Active (3.3k points)
yes. but "r has no duplicates" doesn't make sense rt? A relation cannot have duplicates as it is a set of tuples.
Indexing is not strictly required. But whats the connection between indexing and CK?
A relation is defined to be a "SET" of tuples. So, it cannot have same tuples.

Indexing is used for retrieving data from a table. CK can be used for indexing.
:O You are from Kerala? :)
@Arjun Suresh: Agree with you. r has no duplicates doesn't make sense here.
@Arjun, This questions is about SQL. In SQL we allow relations to have duplicates. Check my answer here !

yes In SQL, relation can have duplicate tuples


           A           B
          1           2
          4           9
          1           2

the above relation, it is valid in SQL but not in Relational Algebra.


Related questions

Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true
50,115 questions
53,224 answers
70,473 users