3

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

rubikskube
  • 372
  • 1
  • 5
  • 22
  • `X4 = X3 J (... c3 ...) AND x4 IS EMPTY` (there must be an operator for is_empty) – wildplasser Aug 24 '14 at 16:31
  • 3
    Your simple join without the not exists tells you cities found in at least 2 states. An additional join along similar lines would give you cities found in at least three states. Then you just need to subtract that from the first set. – Martin Smith Aug 24 '14 at 17:14
  • There is not just one version of relational algebra. Give a link to the one you are to use. – philipxy Sep 02 '14 at 01:48
  • @wildplasser There is no is_empty & no way to express it assuming a standard operator set that can nevertheless express any query. (X, U, -, project, rename & restrict that can only mention attributes of an argument.) Assume table R aliased to R holds rows where R(R.x,...). Then T WHERE EXISTS(U WHERE ...T.a...U.b...) = rows where T(T.a,...) & exists U.b,... [U(U.b,...) & ...T.a...U.b...] = rows where exists U.b,... [T(T.a,...) & U(U.b,...) & ...T.a...U.b...] = project T.a,... (restrict ...T.a...U.b... (T X U)). (Later SELECT R.x AS y,... ... gives rename R.x\y,... (project R.x,... (...)).) – philipxy May 04 '18 at 01:29

2 Answers2

5

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

Andrea Nicola
  • 66
  • 3
-2

I would think GROUP BY and HAVING first:

select city, count(state) 
from city 
group by city
having count(state) = 2
order by city
duffymo
  • 305,152
  • 44
  • 369
  • 561