0

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?

SQB
  • 3,926
  • 2
  • 28
  • 49
Kendel Ventonda
  • 411
  • 3
  • 9
  • 22
  • I disagree with your first solution, since nothing in the first query justifies the `w ϵ MAKLER`. I think you may have meant `w ϵ IMMOBILIE`. – SQB Dec 05 '16 at 11:22
  • 1
    @SQB Also, `'Preis'<'100000'` is always *false* in the first `SELECT`. – Y.B. Dec 06 '16 at 16:27

2 Answers2

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.

Y.B.
  • 3,526
  • 14
  • 24
  • 1
    You mean `∃i(i ϵ IMMOBILIE ∧ i.Typ = 'Wohnung' ∧ i.angebotenVon = m.MaklerID)`. Given a (logic) assignment (tuple mapping from variables to values) for the non-x free (logic) variables in wff/formula/predicate `...`, `{x|...}` denotes a (sub)set but `∃x(...)` denotes whether `{x|...}<>{}`. PS An unfortunate thing about most versions of TRC is that queries are *not* composed from other queries. However if a query expression is a wff whose value is the relation holding the set of assignments that make the wff true then queries are composed of other queries. – philipxy Jan 12 '17 at 03:00
  • @philipxy Thank you, you are absolutely right. We do not need to create a subset, we just need to verify the condition. `... ∃i(i|i ϵ IMMOBILIE ...` was a bit of SQL thinking where a subquery can be run on it's own. – Y.B. Jan 12 '17 at 11:49
  • "Most of the materials on TRC seems to be in pdf format." ==> Actually most of the materials on mathematics are in pdf format ;-) – Fabian Pijcke Jan 12 '17 at 12:01
1

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') }
Fabian Pijcke
  • 2,920
  • 25
  • 29
  • You're right, I think it makes more sense to put m everywhere though, but let's keep the OP's notations – Fabian Pijcke Jan 12 '17 at 12:34
  • Can you suggest any online materials to look up this notation: `{w | ∃i, w ϵ MAKLER...`, in particular - `∃i,` comma bit? – Y.B. Jan 12 '17 at 13:36
  • 1
    The comma is not part of first order logic, I added it just to make things clearer as I think they advantageously replace parentheses. I learned TRC from my database course. My main source for database theory is the book from Serge Abiteboul freely available at http://webdam.inria.fr/Alice/ (this is an English book, don't worry about the french hosting website). Unfortunately he only uses DRC, which is really close to TRC yet not the same. The notations can still be used (chapter 5). – Fabian Pijcke Jan 12 '17 at 14:05
  • 1
    Thanks for the link. That is exactly what I have found confusing: would not in DRC `∃i, w ϵ MAKLER...` mean "Exist such elements `i` and `w` of `MAKLER` set ..."? – Y.B. Jan 12 '17 at 15:55
  • Oh, I never thought of it that way, but you are perfectly right. I'll stick to parentheses from now on, thank you for your feedback :-) – Fabian Pijcke Jan 12 '17 at 15:57
  • 1
    This is actually very interesting to reflect upon from the DB optimizer perspective: your solution has no assumptions of which set should be analysed first and which only in relation to the other. In a sense, you leave the optimizer full freedom to design execution strategy. My solution technically has exactly the same logic, but carries a suggestion that `IMMOBILIE` is somehow secondary to the `MAKLER`. :-) – Y.B. Jan 12 '17 at 16:21
  • @Y.B. We can take any algebra or calculus expression to specify an execution order, or we can read it as only specifying a result. Since the start of the relational model it has been assumed that a DBMS only cares about the result and knows value-preserving axioms/transformations. Algebra=procedure & calculus=specification has been the prevalent myth. ([Difference between Relational Algebra and Relational calculus](http://stackoverflow.com/a/32841232/3404097)) But here you are thinking that a calculus expression implies a procedure, where traditionally no "suggestion" is seen. – philipxy Jan 31 '17 at 02:13