From SQL to tuple relational calculus with Join

Question

I have two SQL-queries I need to convert into tuple relational calculus. The first query

SELECT immobilie.*
FROM immobilie
WHERE 'Preis'<'100000'

seems to be pretty obvious (if I understood it right):

{w|w ϵ MAKLER ∧ w.Preis < `100000‘} 

But the second one:

SELECT makler.*
FROM makler
JOIN immobilie
     ON makler.MaklerID = immobilie.angebotenVon
WHERE immobilie.Typ = 'Wohnung'

has a join and I couldn't find a good example how I would need to convert it. Could anyone help me with an explanation?

Answer 1

Most of the materials on TRC seems to be in pdf format. That might have something to do with the symbols heavily used. According to this presentation on Berkeley CS 106 this should work:

{m|m ϵ MAKLER ∧ ∃i(i ϵ IMMOBILIE ∧ i.Typ = `Wohnung‘ ∧ i.angebotenVon = m.MaklerID)}

Basically, the condition is that an element should exist in another query: for each tuple taken from MAKLER ensure there ∃xists tuple in IMMOBILIE that have Typ equal to 'Wohnung' and angebotenVon equal to MaklerID of the tuple in consideration.

Unfortunately I have no way to test it at the moment.

Answer 2

You just have to transform the JOIN into a CROSS JOIN and move the condition in the WHERE clause. Then it is easy to get the TRC translation:

{ w | ∃i (w ϵ MAKLER ∧ i ϵ IMMOBILIE ∧ w.MaklerID = i.angebotenVon ∧ i.Typ = 'Wohnung') }