Convert sql to tuple relational calculus

Question

I'm trying to convert an sql query into tuple relational calculus, but there is a NOT EXIST that causes me to be stuck.

SQL query:

SELECT num FROM a a1, b b1
WHERE a1.num = b1.no AND a1.name = "Tim"
AND NOT EXIST
    (SELECT * FROM a a2, b b2
     WHERE a2.num = b2.no AND a2.name = "Tim" 
     AND b2.rating > b1.rating)

I already started with:

{ t: num | ∃a1 ∈ a ∃b1 ∈ b [ t(num) = a1(num) ∧ a1.name = "Tim" ∧ ¬∃a2 ∈ a ...

this is where I'm stuck. How do I show that it is NOT EXISTS from both a2 and b2? If I write ¬∃a2 ∈ a ¬∃b2 ∈ b then this is going to be a doubly nested sql query and not the one listed above.

Answer 1

You want there to not exist a2 and b2 per the WHERE.

SQL expression

NOT EXISTS (SELECT * FROM a a2, b b2 WHERE ...)

corresponds to tuple calculus expression

¬(∃ a2 ∈ a ∃ b2 ∈ b [...]) 

In SQL there EXISTS a row in the subquery if and only if there exist a2 and b2 row values per its WHERE to form that row. So NOT EXISTS such an SQL a2 plus b2 pair when it's not the case that there are calculus a2 and b2 where ... .