CODEWITHSUNDEEP
sqldatabaserelationalrelational-algebrarelational-division
rubikskube
rubikskube

Reputation: 402

Relational Algebra - Finding EXACTLY two values

Below is a Table and simple SQL to find the cities that occur in exactly two states

Name     State
----     -----
Paris    MO
Paris    TX
Paris    VA
Austin   MA
Austin   TX
Burling  VT

Result: Only Austin will qualify as it occurs in exactly two state

SQL

SELECT c1.Name FROM City AS c1
JOIN City AS c2 ON c1.Name = c2.Name AND c1.State <> c2.State
WHERE NOT EXISTS (
    SELECT 1 FROM City AS c3
    WHERE c1.Name = c3.Name AND c3.State NOT IN (c1.State, c2.State);

I want to express the above sql in relational algebra. I did the first part where it finds the city that occurs in atleast two state, but cannot figure out how to express the NOT EXISTS (...) part. What I understand is that I would be requiring some relation division in the later parts.

Relational Algebra (for the first part)

P = rho sign to rename, J = Join

X1 --> Pc1(City)
X2 --> Pc2(City)
X3 --> X1 J ( c1.Name = c2.Name AND c1.State <> c2.State ) X2
.....

Looking forward for some help

Upvotes: 3

Views: 5696

Answers (2)

Andrea Nicola
Andrea Nicola

Reputation: 86

i give you a hint:

without aggregate functions you can find:

  • the cities occuring 2 and more than 2 times in a state
  • the cities occuring 3 and more than 3 times in a state

so:

two times in a state can be calculated as:

2 and more than 2 times in a state except 3 and more than 3 times in a state.

so you need to make 2 copies of the table and calculate the towns in more than 2 states and then make 3 copies and calculate the towns in 3 or more states...

be aware of the non transitivity of this operator <> when you do the 3 or more test!!!

i hope you will resolve this exercise! It's really important! Good Luck

Upvotes: 5

duffymo
duffymo

Reputation: 308763

I would think GROUP BY and HAVING first:

select city, count(state) 
from city 
group by city
having count(state) = 2
order by city

Upvotes: -2

Related Questions