4.2k 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
edited | 4.2k views
0
in general it is written that
select distinct w, x


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

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

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.

edited
+8

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.

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

It means distinct pair of (w,x)
0
@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
+1
0

@srestha  mam

no need for join operation i think

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

can we do that ?
(a)

if s is empty r x s will give empty set, so s cant be empty
+8
yes. but "r has no duplicates" doesn't make sense rt? A relation cannot have duplicates as it is a set of tuples.
0
Indexing is not strictly required. But whats the connection between indexing and CK?
0
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.
0
:O You are from Kerala? :)
0
@Arjun Suresh: Agree with you. r has no duplicates doesn't make sense here.
+6
@Arjun, This questions is about SQL. In SQL we allow relations to have duplicates. Check my answer here !
+7

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.

1
+1 vote
2