1 / 78

Understanding Conjunctive Queries in Database Management

Learn about the definition, examples, and equivalence of conjunctive queries in databases, including practical applications and relational algebra translations.

stoltz
Download Presentation

Understanding Conjunctive Queries in Database Management

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. Konjunktív lekérdezések, lekérdezések tartalmazási problémája

  2. Konjunktív lekérdezések (CQ) • Az első rendű logikai (FO)lekérdezések (relációs kalkulusok) részosztálya. • Megfelel aSELECT-DISTINCT-FROM-WHERE SQL lekérdezéseknek. • A gyakorlatban előforduló legtöbb lekérdezés ilyen. • Az optimalizáló algoritmusok leggyakrabban konjunktív lekérdezésekre használhatók, ezért bontsuk fel a lekérdezéseket CQ részlekérdezésekre. • CQ lekérdezések jól meghatározható, elméletileg bizonyítható tulajdonságokkal rendelkeznek.

  3. Konjunktív lekérdezések definíciója • Definíció:a konjunktív lekérdezésekhez tartozó DRC formulákat rekurzívan definiáljuk: • ahol  és ’ konjunktív lekérdezéshez tartozó formula. • Vegyük észre, hogy nem szerepel:, , • Lekérdezésen időnként a lekérdezéshez tartozó formulát értjük. • Vegyük észre, hogy CQ  FO • ::= R(t1, ..., tar(R)) | ti = tj |   ’ | x.

  4. Példák konjunktív lekérdezésekre • Konjunktív lekérdezések (van-e valami kapcsolat köztük?) : • Nem konjunktív lekérdezések (miért?): q(x,y) = z.(R(x,z)  u.(R(z,u)  R(u,y))) q(x) = z.u.(R(x,z)  R(z,u)  R(u,y)) q(x,y) = z.(R(x,z)  R(y,z))q(x) = T(x) z.S(x,z)

  5. Konjunktív lekérdezések • Tetszőleges CQ lekérdezés ekvivalens (átírható) ilyen alakú CQ lekérdezéssel: (Vagyis az összes kvantor a formula elején szerepel.) • Ugyanez Datalogjelöléssel: q(x1,...,xn) = y1.y2...yp.(R1(t11,...,t1m) ... Rk(tk1,...,tkm)) törzs (test) Datalog szabály q(x1,...,xn) :- R1(t11,...,t1m), ... , Rk(tk1,...,tkm)) fej

  6. Példák a Datalog jelölésre Employee(x), ManagedBy(x,y), Manager(y) (Az adatbázisban dolgozókat, fönököket, és a „kinek ki a főnöke” relációt tároljuk.) • Keressük meg az összes dolgozót, akinek ugyanaz a főnöke, mint “Smith”-nek: A(x) :- ManagedBy(“Smith”,y), ManagedBy(x,y)

  7. Példák a Datalog jelölésre Employee(x), ManagedBy(x,y), Manager(y) • Az igazgató a fönök fönöke legyen. • Keressük meg az összes dolgozót, akinek ugyanaz az igazgatója, mint “Smith”-nek: A(x) :- ManagedBy(“Smith”,y), ManagedBy(y,z), ManagedBy(x,u), ManagedBy(u,z)

  8. A CQ és az SQL közti viszony CQ: A(x) :- ManagedBy(“Smith”,y), ManagedBy(x,y) Fontos a “distinct”! SQL: selectdistinct m2.namefrom ManagedBy m1, ManagedBy m2where m1.name=“Smith” AND m1.manager=m2.manager

  9. A CQ és az SQL közti viszony • Véletlen volt, hogy át tudtuk írni, vagya CQ lekérdezések pontosan a SELECT-DISTINCT-FROM-WHERE lekérdezéseknek felelnek meg?

  10. P$2.name $1.manager=$2.manager sname=“Smith” ManagedBy ManagedBy CQ and RA Relational Algebra: • CQ correspond precisely to sC, PA,  (missing: , –) A(x) :- ManagedBy(“Smith”,y), ManagedBy(x,y)

  11. Extensions of CQ CQ Find managers that manage at least 2 employees A(y) :- ManagedBy(x,y), ManagedBy(z,y), xz

  12. Extensions of CQ CQ< Find employees earning more than their manager: A(y) :- ManagedBy(x,y), Salary(x,u), Salary(y,v), u>v)

  13. Extensions of CQ Find people sharing the same office with Alice, butnot the same manager: CQ A(y) :- Office(“Alice”,u), Office(y,u), ManagedBy(“Alice”,x), ManagedBy(x,y)

  14. Extensions of CQ Union of conjuctive queries UCQ Datalog: A(name) :- Employee(name, dept, age, salary), age > 50A(name) :- RetiredEmployee(name, address) Datalog notation is very convenient at expressing unions(no need for  )

  15. Extensions of CQ • If we extend too much, we capture FO • Theoreticians need to be careful: small extensions may make a huge difference on certain theoretical properties of CQ

  16. Query Equivalence and Containment • Justified by optimization needs • Intensively studied since 1977

  17. Query Equivalence • Queries q1 and q2 are equivalent if for every database D, q1(D) = q2(D). • Notation: q1 q2

  18. Query Equivalence SELECT DISTINCT x.name, x.managerFROM Employee x, Employee yWHERE x.dept = ‘Sales’ and x.office = y.office and x.floor = 5 and y.dept = ‘Sales’ Hmmmm…. Is there a simple way to write that ?

  19. Query Containment • Query q1 is contained in q2 if for every database D, q1(D)  q2(D). • q1 and q2 are equivalent if for every database D, q1(D) = q2(D) • Notation: q1 q2, q1 q2 • Obviously: q1 q2 and q2 q1 iff q1 q2 • Conversely: q1 q2 q1 iff q1 q2 We will study the containment problem only.

  20. Examples of Query Containments Is q1 q2 ? q1(x) :- R(x,u), R(u,v), R(v,w) q2(x) :- R(x,u), R(u,v)

  21. Examples of Query Containments Is q1 q2 ? q1(x) :- R(x,u), R(u,v), R(v,x) q2(x) :- R(x,u), R(u,x)

  22. Examples of Query Containments Is q1 q2 ? q1(x) :- R(x,u), R(u,u) q2(x) :- R(x,u), R(u,v), R(v,w)

  23. Examples of Query Containments Is q1 q2 ? q1(x) :- R(x,u), R(u,”Smith”) q2(x) :- R(x,u), R(u,v)

  24. Query Containment • Theorem Query containment for FO is undecidable • Theorem Query containment for CQ is decidable and NP-complete.

  25. Query Containment Algorithm How to check q1 q2 • Canonical database for q1 is:Dq1 = (D, R1D, …, RkD) • D = all variables and constants in q1 • R1D, …, RkD = the body of q1 • Canonical tuple for q1 is: tq1 (the head of q1)

  26. Examples of Canonical Databases • Canonical database: Dq1 = (D, RD) • D={x,y,u,v} • RD = • Canonical tuple: tq1 = (x,y) q1(x,y) :- R(x,u),R(v,u),R(v,y)

  27. Examples of Canonical Databases • Dq1 = (D, R) • D={x,u,”Smith”,”Fred”} • R = • tq1 = (x) q1(x) :- R(x,u), R(u,”Smith”), R(u,”Fred”), R(u, u)

  28. Checking Containment Theorem: q1 q2 iff tq1q2(Dq1). Example: q1(x,y) :- R(x,u),R(v,u),R(v,y)q2(x,y) :- R(x,u),R(v,u),R(v,w),R(t,w),R(t,y) • D={x,y,u,v} • R = tq1 = (x,y) • Yes, q1 q2

  29. Query Homomorphisms • A homomorphism f : q2 q1 is a function f: var(q2)  var(q1)  const(q1)such that: • f(body(q2))  body(q1) • f(tq1) = tq2 The Homomorphism Theoremq1 q2 iff there exists a homomorphism f : q2 q1

  30. Example of Query Homeomorphism var(q1) = {x, u, v, y} var(q2) = {x, u, v, w, t, y} q1(x,y) :- R(x,u),R(v,u),R(v,y)q2(x,y) :- R(x,u),R(v,u),R(v,w),R(t,w),R(t,y) Therefore q1 q2

  31. Example of Query Homeomorphism var(q1)  const(q1) = {x,u, “Smith”} var(q2) = {x,u,v,w} q1(x) :- R(x,u), R(u,”Smith”), R(u,”Fred”), R(u, u) q2(x) :- R(x,u), R(u,v), R(u,”Smith”), R(w,u) Therefore q1 q2

  32. The Homeomorphism Theorem • TheoremConjunctive query containment is: (1) decidable (why ?) (2) in NP (why ?) (3) NP-hard • Short: it is NP-complete

  33. Query Containment for UCQ q1  q2  q3  . . . .  q1’ q2’ q3’ . . . . Notice: q1  q2  q3  . . . .  q iff q1  q and q2  q and q3  q and …. Theorem q  q1’ q2’ q3’ . . . . Iff there exists some k such that q  qk’ It follows that containment for UCQ is decidable, NP-complete.

  34. Query Containment for CQ< q1() :- R(x,y), R(y,x) q2() :- R(x,y), x <= y q1 q2 although there is no homomorphism ! • To check containment do this: • Consider all possible orderings of variables in q1 • For each of them check containment of q1 in q2 • If all hold, then q1 q2 Still decidable, but harder than NP: now in p2

  35. Query Minimization Definition A conjunctive query q is minimal if for every other conjunctive query q’ s.t. q  q’, q’ has at least as many predicates (‘subgoals’) as q Are these queries minimal ? q(x) :- R(x,y), R(y,z), R(x,x) q(x) :- R(x,y), R(y,z), R(x,’Alice’)

  36. Query Minimization • Query minimization algorithm Choose a subgoal g of q Remove g: let q’ be the new query We already know q  q’ (why ?) If q’  q then permanently remove g • Notice: the order in which we inspect subgoals doesn’t matter

  37. Query Minimization In Practice • No database system today performs minimization !!! • Reason: • It’s hard (NP-complete) • Users don’t write non-minimal queries • However, non-minimal queries arise when using views intensively

  38. Query Minimization for Views CREATE VIEW HappyBoaters SELECT DISTINCT E1.name, E1.manager FROM Employee E1, Employee E2 WHERE E1.manager = E2.name and E1.boater=‘YES’ and E2.boater=‘YES’ This query is minimal

  39. Query Minimization for Views Now compute the Very-Happy-Boaters SELECT DISTINCT H1.nameFROM HappyBoaters H1, HappyBoaters H2WHERE H1.manager = H2.name This query is also minimal What happens in SQL when we run a query ona view ?

  40. Query Minimization for Views View Expansion SELECT DISTINCT E1.nameFROM Employee E1, Employee E2, Employee E3, Empolyee E4WHERE E1.manager = E2.name and E1.boater = ‘YES’ and E2.boater = ‘YES’ and E3.manager = E4.name and E3.boater = ‘YES’ and E4.boater = ‘YES’ and E1.manager = E3.name This query is no longer minimal ! E4 E3 E1 E2 is redundant E2

  41. Monotone Queries Definition A query q is monotone if:For every two databases D, D’if D  D’ then q(D)  q(D’) Which queries below are monotone ?   x.R(x,x)   x.y.z.u.(R(x,y)  R(y,z)  R(z,u))   x.y.R(x,y)

  42. Monotone Queries • Theorem. Every conjunctive query is monotone • Stronger: every UCQ query is monotone

  43. How To Impress Your Students Or Your Boss • Find all drinkers that like some beer that is not served by the bar “Black Cat” • Can you write as a simple SELECT-FROM-WHERE (I.e. without a subquery) ? SELECT L.drinkerFROM Likes LWHERE L.beer not in (SELECT S.beer FROM Serves S WHERE S.bar = ‘Black Cat’)

  44. Expressive Power of FO • The following queries cannot be expressed in FO: • Transitive closure: • x.y. there exists x1, ..., xn s.t.R(x,x1)  R(x1,x2)  ...  R(xn-1,xn)  R(xn,y) • Parity: the number of edges in R is even

  45. Datalog • Adds recursion, so we can compute transitive closure • A datalog program (query) consists of several datalog rules: P1(t1) :- body1P2(t2) :- body2 .. . . Pn(tn) :- bodyn

  46. Datalog Terminology: • EDB = extensional database predicates • The database predicates • IDB = intentional database predicates • The new predicates constructed by the program

  47. Employee(x), ManagedBy(x,y), Manager(y) Datalog EDBs All higher level managers that are employees: HMngr(x) :- Manager(x), ManagedBy(y,x), ManagedBy(z,y) Answer(x) :- HMngr(x), Employee(x) IDBs

  48. Employee(x), ManagedBy(x,y), Manager(y) Datalog All persons: Person(x) :- Manager(x) Person(x) :- Employee(x) Manger  Employee

  49. Unfolding non-recursive rules Graph: R(x,y) P(x,y) :- R(x,u), R(u,v), R(v,y) A(x,y) :- P(x,u), P(u,y) Can “unfold” it into: A(x,y) :- R(x,u), R(u,v), R(v,w), R(w,m), R(m,n), R(n,y)

  50. Unfolding non-recursive rules Graph: R(x,y) P(x,y) :- R(x,y) P(x,y) :- R(x,u), R(u,y) A(x,y) :- P(x,y) Now the unfolding has a union: A(x,y) :- R(x,y)  u(R(x,u) R(u,y))

More Related