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 !