CODEWITHSUNDEEP
mysqlsqltuplestuple-relational-calculus
Kendel Ventonda
Kendel Ventonda

Reputation: 411

From SQL to tuple relational calculus with Join

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?

Upvotes: 0

Views: 4173

Answers (2)

Fabian Pijcke
Fabian Pijcke

Reputation: 3210

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') }

Upvotes: 1

Y.B.
Y.B.

Reputation: 3586

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.

Upvotes: 1

Related Questions