Selecting DISTINCT rows in relational algebra

Question

There is a DISTINCT operator in SQL. However, I have an assignment in which I need to get some distinct values from a table, and I can only use relational algebra. Is there a way?

Marquez

Maybe you can use the division operator Rel1 ÷ Rel1 ! – Insidious

Answer 1

Relational operators always return distinct tuples so DISTINCT is never needed. Duplicate tuples are not permitted in the RA - that being one major difference between the relational model and the SQL model.

Rna

I know this is an old question but I have to object, there is a distinct like operator in relational algebra, in my edition of Ullmans Database Systems The Complete Book it is in the following chapter: ,,5.2.1 Duplicate Elimination". Yes, sure , duplicates are not allowed in normal sets, but there are multisets, which allow them. – Rebane

Hi DDavid, I think you may be missing the point made by Molina,Ullman,Widom (excellent book by the way and one I often refer to). Quote from section 5.2: "When DBMS's that used the relational model were first developed, their query languages largely implemented the relational algebra. However ... these systems regarded relations as bags, not sets."(my emphasis) They go on to point out that bag operations are different to the set operations supported by the RA. SQL's DISTINCT "operator" takes a tuple bag and eliminates duplicates. RA deals exclusively with sets of tuples. – Rna

Answer 2

Relational Algebra is based on set theory. SQL is an implementation of multi-set theory. Set always has distinct elements. So tuples are distinct by default in Relational Algebra.

Moor

Answer 3

The projection in relational algebra is equivalent to select distinct. In select statement of sql, duplicates are allowed as it is based on multiset theory but in projection of relational algebra, which is based on set theory, duplicates are not allowed.

Hence, relational algebra equivalent of select distinct is projection.

Millsaps