1 / 71

CSE 544 Relational Calculus

CSE 544 Relational Calculus. Lecture #2 January 11 th , 2011. Announcements. HW1 is posted First paper review due next week Friday: project groups are due. Announcements. Guest lecture on Tuesday, 1/18, by Bill Howe SQLShare : Smart Services for Ad Hoc Databases

armina
Download Presentation

CSE 544 Relational Calculus

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CSE 544Relational Calculus Lecture #2 January 11th, 2011 Dan Suciu -- 544, Winter 2011

  2. Announcements • HW1 is posted • First paper review due next week • Friday: project groups are due Dan Suciu -- 544, Winter 2011

  3. Announcements • Guest lecture on Tuesday, 1/18, by Bill HoweSQLShare: Smart Services for Ad Hoc Databases • Need to makeup one lecture this/next week. When ? • Friday 1/14, 12:30-2pm ? • Wednesday, 1/19, morning (9…12) ? • Friday, 1/21, late morning (11...1pm) ? Dan Suciu -- 544, Winter 2011

  4. Today’s Agenda Relational calculus: • Domain Relational Calculus • Non-recursive datalog (a reasonable abstraction of SQL) • Relational algebra They are equivalent and why we care Dan Suciu -- 544, Winter 2011

  5. Domain Relational Calculus Given: • A vocabulary: R1, …, Rk • An arity, ar(Ri), for each i=1,…,k • An infinite supply of variables x1, x2, x3, … • Constants: c1, c2, c3, ... Dan Suciu -- 544, Winter 2011

  6. Domain Relational Calculus Terms t and Formulas  are: t ::= x | a /* variable or constant */ • ::= R(t1, ..., tk) | ti = tj | ’ | ’ | ’ | x. | x. A query Q is any formula , usually written as: Q = { x |  } where x = (x1,x2,…) are the free variables in . Dan Suciu -- 544, Winter 2011

  7. Example: Querying a Graph R encodes a graph What do these queries return ? {x | y.R(x,y)} 1 4 2 3 {x | y.z.u.(R(x,y) R(y,z) R(z,u)) R= {(x,y) | z.(R(x,z) R(y,z)}

  8. Example: Querying a Graph R encodes a graph What do these queries return ? {x | y.R(x,y)} 1 4 Nodes that have at least one child: {1,2,3} 2 3 {x | y.z.u.(R(x,y) R(y,z) R(z,u)) R= Nodes that have a great-grand-child: {1,2} {(x,y) | z.(R(x,z) R(y,z)} Every child of x is a child of y:{(1,1),(2,2),(3,1),(3,3),(4,1), (4,2), (4,3)}

  9. Semantics of a Formula • A database instance (or a model) isD = (D, R1D, …, RkD) • D = a set, called domain, or universe • RiD D  D  ...  D, (ar(Ri) times) i = 1,...,k • A valuation is a function s : {x1, x2, ...} D • The semantics of a formula says when D ⊨ [s] holds The domain D is not part of the database,yet we need it to define the semantics. Where ? Dan Suciu -- 544, Winter 2011

  10. Semantics of D ⊨ [s] D ⊨ (R(t1, ..., tn)) [s] If (s(t1), ..., s(tn))  RD If s(t) = s(t’) D ⊨ (t= t’) [s] If D ⊨ [s] and D ⊨ (’) [s] D ⊨ (’) [s] If D ⊨ [s] or D ⊨ ’[s] D ⊨ (’) [s] If it is not the case D ⊨ [s] D ⊨ (’) [s] Dan Suciu -- 544, Winter 2011

  11. Semantics of D ⊨ [s] Notation: sa/x = the valuation s modified to map x to a D ⊨ (x.) [s] If for every constant a in D,D ⊨ [sa/x] If for some constant a in D,D ⊨ [sa/x] D ⊨ (x.) [s] Dan Suciu -- 544, Winter 2011

  12. Semantics of D ⊨ [s] Notation: sa/x = the valuation s modified to map x to a D ⊨ (x.) [s] If for every constant a in D,D ⊨ [sa/x] If for some constant a in D,D ⊨ [sa/x] D ⊨ (x.) [s] Note: here we need the domain D Dan Suciu -- 544, Winter 2011

  13. Semantics of a Query The semantics of a queryQ = { x |  } is Q(D) = { s(x) | for all s such that D ⊨ [s] } Dan Suciu -- 544, Winter 2011

  14. The drinkers-bars-beers example Likes(drinker, beer) Frequents(drinker, bar) Serves(bar, beer) What does this query compute ? x: y. z. Frequents(x, y) Serves(y,z) Likes(x,z)

  15. The drinkers-bars-beers example Likes(drinker, beer) Frequents(drinker, bar) Serves(bar, beer) What does this query compute ? x: y. z. Frequents(x, y) Serves(y,z) Likes(x,z) Find drinkers that frequent some bar that serves some beer they like.

  16. The drinkers-bars-beers example Likes(drinker, beer) Frequents(drinker, bar) Serves(bar, beer) What does this query compute ? x: y. Frequents(x, y) (z. Serves(y,z)Likes(x,z))

  17. The drinkers-bars-beers example Likes(drinker, beer) Frequents(drinker, bar) Serves(bar, beer) What does this query compute ? x: y. Frequents(x, y) (z. Serves(y,z)Likes(x,z)) Find drinkers that frequent only bars that serves some beer they like.

  18. The drinkers-bars-beers example Likes(drinker, beer) Frequents(drinker, bar) Serves(bar, beer) What does this query compute ? x: y. Frequents(x, y)z.(Serves(y,z) Likes(x,z))

  19. The drinkers-bars-beers example Likes(drinker, beer) Frequents(drinker, bar) Serves(bar, beer) What does this query compute ? x: y. Frequents(x, y)z.(Serves(y,z) Likes(x,z)) Find drinkers that frequent some bar that serves only beers they like.

  20. The drinkers-bars-beers example Likes(drinker, beer) Frequents(drinker, bar) Serves(bar, beer) What does this query compute ? x:y.Frequents(x, y)z.(Serves(y,z) Likes(x,z)) Dan Suciu -- 544, Winter 2011

  21. The drinkers-bars-beers example Likes(drinker, beer) Frequents(drinker, bar) Serves(bar, beer) What does this query compute ? x:y.Frequents(x, y)z.(Serves(y,z) Likes(x,z)) Find drinkers that frequent only bars that serves only beer they like. Dan Suciu -- 544, Winter 2011

  22. Summary of Relational Calculus • Same as First Order Logic • See book for the two variants: • Domain relational calculus (what we discussed) • Tuple relational calculus • This is a powerful, concise language Dan Suciu -- 544, Winter 2011

  23. Datalog Rule Body Head Li = a literal, or atom, or subgoal; can be one of: • R(x1, x2, …) = relational atom • u = v, or u ≠ v, where u, v = variables or constants S = a new symbol x = (x1, x2, …) are head variables Q(x) :- L1, L2, …, Lk Example: Q(x) :- Likes(x,y),Likes(x,z),y≠z Dan Suciu -- 544, Winter 2011

  24. Semantics of Datalog Rule Q(x) :- L1, L2, …, Lk Q = {x | y1.y2… L1L2…Lk} y1, y2,… are all non-head variables The semantics is: Q(D) :- { s(x) | s = valuation s.t. D ⊨ L1[s],…,D ⊨ Lk[s] } Note: a datalog rule is also called a conjunctive query Dan Suciu -- 544, Winter 2011

  25. Non-Recursive Datalog S1(x1) :- body1 S2(x2) :- body2 S3(x3) :- body3 . . . Each symbol Sk may appearin bodyk+1, bodyk+2, …,but may not appearin body1,…,bodyk. Example: What does Q compute ? A(x,y) :- R(x,z),R(z,y) B(x,y) :- A(x,z),A(z,y) C(x,y) :- B(x,z),B(z,y) Q(x,y) :- C(x,z),C(z,y) Dan Suciu -- 544, Winter 2011

  26. Non-Recursive Datalog S1(x1) :- body1 S2(x2) :- body2 S3(x3) :- body3 . . . Each symbol Sk may appearin bodyk+1, bodyk+2, …,but may not appearin body1,…,bodyk. Example: What does Q compute ? A(x,y) :- R(x,z),R(z,y) B(x,y) :- A(x,z),A(z,y) C(x,y) :- B(x,z),B(z,y) Q(x,y) :- C(x,z),C(z,y) A: all pairs (x,y) connected bya path of length 16. Dan Suciu -- 544, Winter 2011

  27. Comparison Is non-recursive datalog equivalentto the Relational Calculus ? Dan Suciu -- 544, Winter 2011

  28. Comparison Is non-recursive datalog equivalentto the Relational Calculus ? What about writing this query in non-recursive datalog ? Q = {x | y. Frequents(x,y) Serves(y,’Bud’) z. Frequents(x,z) Serves(z,’Miller’) } Dan Suciu -- 544, Winter 2011

  29. Comparison Is non-recursive datalog equivalentto the Relational Calculus ? What about writing this query in non-recursive datalog ? Q = {x | y. Frequents(x,y) Serves(y,’Bud’) z. Frequents(x,z) Serves(z,’Miller’) } Q(x) :- Frequents(x,y), Serves(y,’Bud’) Q(x) :- Frequents(x,z), Serves(z,’Miller’) Dan Suciu -- 544, Winter 2011

  30. Comparison Is non-recursive datalog equivalentto the Relational Calculus ? Now what about writing this query in non-recursive datalog ? Q = {x | y. Frequents(x,y) z.Likes(x,z)Serves(y,z) Dan Suciu -- 544, Winter 2011

  31. Comparison Is non-recursive datalog equivalentto the Relational Calculus ? Now what about writing this query in non-recursive datalog ? Q = {x | y. Frequents(x,y) z.Likes(x,z)Serves(y,z) Impossible ! Proof on next slides. Dan Suciu -- 544, Winter 2011

  32. Monotone Queries • Given two database instancesD = (D, R1, …, Rk) and D’ = (D’, R’1, …, R’k)we write D⊆ D’ if D⊆D’, R1⊆R’1,…, Rk⊆R’k. • In other words, D’ is obtained by adding tuples to D. • We say that Q is monotoneif whenever D⊆ D’, Q(D)⊆ Q(D’). Dan Suciu -- 544, Winter 2011

  33. Monotone Queries Fact. Every datalog rule is monotone. Why ? Fact. Every datalog program is monotone. Why ? Fact. This query is not monotone:Q = {x | y. Likes(x,y) z.Frequents(x,z)Serves(z,y) Why ? Dan Suciu -- 544, Winter 2011

  34. Monotone Queries Fact. Every datalog rule is monotone. Why ? Recall the semantics: Q(D) = { s(x) | D ⊨ L1[s],…,D ⊨ Lk[s] } If D ⊨ Li[s] then D’ ⊨ Li[s] hence Q(D)⊆ Q(D’). Fact. Every datalog program is monotone. Why ? Fact. This query is not monotone:Q = {x | y. Likes(x,y) z.Frequents(x,z)Serves(z,y) Why ? Dan Suciu -- 544, Winter 2011

  35. Monotone Queries Fact. Every datalog rule is monotone. Why ? Recall the semantics: Q(D) = { s(x) | D ⊨ L1[s],…,D ⊨ Lk[s] } If D ⊨ Li[s] then D’ ⊨ Li[s] hence Q(D)⊆ Q(D’). Fact. Every datalog program is monotone. Why ? By induction on the number of rules Fact. This query is not monotone:Q = {x | y. Likes(x,y) z.Frequents(x,z)Serves(z,y) Why ? Dan Suciu -- 544, Winter 2011

  36. Monotone Queries Fact. Every datalog rule is monotone. Why ? Recall the semantics: Q(D) = { s(x) | D ⊨ L1[s],…,D ⊨ Lk[s] } If D ⊨ Li[s] then D’ ⊨ Li[s] hence Q(D)⊆ Q(D’). Fact. Every datalog program is monotone. Why ? By induction on the number of rules Fact. This query is not monotone:Q = {x | y. Likes(x,y) z.Frequents(x,z)Serves(z,y) Why ? By adding a tuple to Frequents we may remove some answer Dan Suciu -- 544, Winter 2011

  37. Monotone Queries Recall the relational calculus: t ::= x | a /* variable or constant */ • ::= R(t1, ..., tk) | ti = tj | ’ | ’ | ’ | x. | x. What fragment of the relational calculus is monotone ? Dan Suciu -- 544, Winter 2011

  38. Monotone Queries Recall the relational calculus: t ::= x | a /* variable or constant */ • ::= R(t1, ..., tk) | ti = tj | ’ | ’ | ’ | x. | x. What fragment of the relational calculus is monotone ? t ::= x | a /* variable or constant */ • ::= R(t1, ..., tk) | ti = tj | ’ | ’ | x. Dan Suciu -- 544, Winter 2011

  39. Non-Recursive Datalog Li = may also be R(x1, x2, …) • R(x1, x2, …) = relational atom • u = v, or u ≠ v, where u, v = variables or constants Q(x) :- L1, L2, …, Lk Dan Suciu -- 544, Winter 2011

  40. Non-Recursive Datalog Example: Q = {x | y. Likes(x,y) z.Frequents(x,z)Serves(z,y) Dan Suciu -- 544, Winter 2011

  41. Non-Recursive Datalog Example: Q = {x | y. Likes(x,y) z.Frequents(x,z)Serves(z,y) FS(x,y) :- Frequents(x,z),Serves(z,y) Q(x) :- Likes(x,y), FS(x,y) Dan Suciu -- 544, Winter 2011

  42. Rel. Calculus  non-rec. Datalog Theorem. Every query in the Relational Calculuscan be translated to non-recursive Datalog Dan Suciu -- 544, Winter 2011

  43. Rel. Calculus  non-rec. Datalog Proof First remove  by using De Morgan: x. = x. Then, inductively, for each subformulacreate a datalog rule F(x):-bodythat computes Q = { x |  } Dan Suciu -- 544, Winter 2011

  44. Rel. Calculus  non-rec. Datalog R(t1, ..., tk) F(x) :- R(t1, ..., tk) Dan Suciu -- 544, Winter 2011

  45. Rel. Calculus  non-rec. Datalog F1(x1) :- … /* for 1*/ F2(x2) :- … /* for 2*/ 12 ? ? ? Dan Suciu -- 544, Winter 2011

  46. Rel. Calculus  non-rec. Datalog F1(x1) :- … /* for 1*/ F2(x2) :- … /* for 2*/ 12 F(x) :- F1(x1),F2(x2) Dan Suciu -- 544, Winter 2011

  47. Rel. Calculus  non-rec. Datalog F1(x1) :- … /* for 1*/ F2(x2) :- … /* for 2*/ 1 2 ? ? ? Dan Suciu -- 544, Winter 2011

  48. Rel. Calculus  non-rec. Datalog F1(x1) :- … /* for 1*/ F2(x2) :- … /* for 2*/ 1 2 F(x) :- F1(x1) F(x) :- F2(x2) Dan Suciu -- 544, Winter 2011

  49. Rel. Calculus  non-rec. Datalog F1(x) :- … /* for 1*/ x.1 ? ? ? Dan Suciu -- 544, Winter 2011

  50. Rel. Calculus  non-rec. Datalog F1(x) :- … /* for 1*/ x.1 F(y) :- F1(x) where y = x – {x} Dan Suciu -- 544, Winter 2011

More Related