10,886 views

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

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}

@Arjun Sir,
In option A, r has no duplicate also means that r can be empty, which means Cartesian Product is not possible, then option A becomes invalid. Correct me, if I'm wrong, PLEASE!

Why s should be non empty? Else R x S will be empty irrespective of R, so unless R is also it cannot be same as R.

Why r has no duplicates? We have distinct in select. Without distinct in select this condition isn't essential.

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

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.

Explanation:
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 ?
Yes , empty set means nothing is there in the set so there is no chance of having duplicates in the set.(since the set is empty)

@Satbir

How, A) could be ans?

Say r has duplicates and r,s has no duplicates due to distinct keyword, then how will it match?

Say r has duplicates

in A it is written r has NO duplicates.

yes, the word given "provided"

That means if r has no duplicates, then only it will be same as r. right?

Actually here options to complete the sentence.
yes
Hello to all

What if in place of option a) statement, this is written "R is empty"

Then also correct option is a)

Am I correct?
Doesn’t the Definition of Candidate Key itself disallow 2 Tuples to Be Same?

I mean if a Relation Has No Proper Subset of Attributes as  Candidate Key, it will have All attributes combined as Candidate Key and so , 2 tuples can’t be same i guess.
(a)

if s is empty r x s will give empty set, so s cant be empty
by

reshown by
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 !
edited by

yes In SQL, relation can have duplicate tuples

Relation

 A B 1 2 4 9 1 2

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

Option A would have been correct if the only condition was on S that S should be non empty

Even if R has duplicates, it will exexute correctly

1. S is empty
2. S is non empty
by