1 / 29

Conjunctive Queries

Conjunctive Queries. Thursday, February 8, 2001. Outline. Definition of conjunctive queries Query containment Query equivalence Query minimization. Picture So Far. Recursive queries. DATALOG. FO. Non-recursive Datalog. Non-monotone queries. Picture for This Lecture. DATALOG.

njuan
Download Presentation

Conjunctive Queries

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. Conjunctive Queries Thursday, February 8, 2001

  2. Outline • Definition of conjunctive queries • Query containment • Query equivalence • Query minimization

  3. Picture So Far Recursive queries DATALOG FO Non-recursive Datalog Non-monotone queries

  4. Picture for This Lecture DATALOG Conjunctive Queries FO

  5. Conjunctive Queries • Definition A conjunctive query is exactly one Datalog rule or: • Definition A conjunctive query is FO restricted to(missing are )

  6. Examples Employee(x), ManagedBy(x,y), Manager(y) • Find all employees having the same manager as Smith:A(x) :- ManagedBy(“Smith”,y), ManagedBy(x,y)

  7. Examples Employee(x), ManagedBy(x,y), Manager(y) • Find all employees having the same director as Smith:A(x) :- ManagedBy(“Smith”,y), ManagedBy(y,z), ManagedBy(x,u), ManagedBy(u,z)

  8. Equivalent Formulations Relational Algebra: • Conjunctive queries correspond precisely to sC, PA, x (missing: U, -) • A(x) :- ManagedBy(“Smith”,y), ManagedBy(x,y) P$2.name $1.manager=$2.manager sname=“Smith” ManagedBy ManagedBy

  9. Equivalent Formulations SQL: • Conjunctive queries correspond precisely to single select-from-where blocks select m2.namefrom ManagedBy m1, ManagedBy m2where m1.name=“Smith” AND m1.manager=m2.manager

  10. Conjunctive Queries • Most useful class of queries • Also enjoys remarkable, positive properties • Focus of research during 70’s, 80’s • Still focus of research in the 00’s • We discuss the most celebrated property of conjunctive queries: containment is decidable

  11. Query Containment • Definition Given two queries q1, q2, we say that q1 is contained in q2 if for every database D, q1(D)  q2(D). • Notation: q1 q2 • Obviously: if q1 q2 and q2 q1 then q1 = q2.

  12. Examples of Query Containments 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)Then q1 q2 (why ?)

  13. Examples of Query Containments 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) Then q1 q2 (why ?)

  14. Query Containment • Recall: query containment for FO is undecidable • Theorem Query containment for conjunctive queries is decidable and NP-complete.

  15. Query Containment The most interesting part: how we check q1 q2 • The canonical database and the canonical tuple for q1: • Canonical database: Dq1 = (D, R1, …, Rk) where: • D = all variables and constants in q1 • R1, …, Rk = the body of q1 • Canonical tuple: tq1 = the head of q1

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

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

  18. Checking Containment Theorem: q1 q2 iff tq1q1(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

  19. Query Homeomorphisms • How do we evaluate q2 on Dq1 ? • A homeomorphism f : q2 q1 is a function f: var(q2)  var(q1) U const(q1)such that: • f(body(q2))  body(q1) • f(canonicalTuple(q2)) = canonicalTuple(q1)

  20. 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)

  21. Example of Query Homeomorphism var(q1) U 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)

  22. The Homeomorphism Theorem • Theorem q1 q2 iff there exists a homeomorphism from q2 to q1. • TheoremConjunctive query containment is: (1) decidable (why ?) (2) in NP (why ?) (3) NP-hard • Short: it is NP-complete

  23. Query Equivalence Definition Query q1 is equivalent to q2 if for every database D, q1(D) = q2(D) • Obviously, they are equivalent iff:q1 q2 and q2 q1 , hence NP-complete too

  24. Application: Query Minimization Definition A conjunctive query q is minimal if for any other query q’ equivalent to q, q’ has more goals than q Query minimization problem: Given q, find an equivalent q0 that is minimal.

  25. Example of Query Minimization q(x) :- R(x,u), R(u,v), R(v,v), R(x,w), R(v,w) • Minimal query equivalent to q: q0(x) :- R(x,u), R(u,v), R(v,v)

  26. Example of Query Minimization Proof: q  q0 q(x) :- R(x,u), R(u,v), R(v,v), R(x,w), R(v,w) q0(x) :- R(x,u), R(u,v), R(v,v) q0 q q(x) :- R(x,u), R(u,v), R(v,v), R(x,w), R(v,w) q0(x) :- R(x,u), R(u,v), R(v,v)

  27. Query Minimization Method • Given q, find a subset of its goals, q0 s.t. there exists a homeomorphism from q to q0 • More practical: for each subgoal, try to see if it can be eliminated. • Note: all minimal queries equivalent to q are isomorphic (assignment: prove that)

  28. Query Minimization in Practice q(name) :- Employee(name,age), Employee(name2,age) Minimizes to: q0(name) :- Employee(name,age) Try this in SQL server: select distinct e1.name from Employee e1, Employee e2 where e1.age = e2.age

  29. Query Minimization in Practice • Database queries used to be written by programmers • “programmers don’t write stupid queries” • Today: queries are often generated automatically • E.g. in SilkRoute, XML-QL queries are translated to SQL: NEED MINIMIZATION !

More Related