Logical Query Languages

1
Logical Query Languages

• Motivation
• Logical rules extend more naturally to recursive
  queries than does relational algebra.
• Used in SQL recursion.
• Logical rules form the basis for many
  information-integration systems and applications.

2
Datalog Example

• Likes(drinker, beer)
• Sells(bar, beer, price)
• Frequents(drinker, bar)
• Happy(d) lt-
• Frequents(d,bar) AND
• Likes(d,beer) AND
• Sells(bar,beer,p)
• Above is a rule.
• Left side head.
• Right side body AND of subgoals.
• Head and subgoals are atoms.
• Atom predicate and arguments.
• Predicate relation name or arithmetic
  predicate, e.g. lt.
• Arguments are variables or constants.
• Subgoals (not head) may optionally be negated by
  NOT.

3
Meaning of Rules

• Head is true of its arguments if there exist
  values for local variables (those in body, not in
  head) that make all of the subgoals true.
• If no negation or arithmetic comparisons, just
  natural join the subgoals and project onto the
  head variables.
• Example
• Above rule equivalent to Happy(d)
  pdrinker(Frequents Likes Sells)

4
Evaluation of Rules

• Two, dual, approaches
• Variable-based Consider all possible
  assignments of values to variables. If all
  subgoals are true, add the head to the result
  relation.
• Tuple-based Consider all assignments of tuples
  to subgoals that make each subgoal true. If the
  variables are assigned consistent values, add the
  head to the result.
• Example Variable-Based Assignment
• S(x,y) lt- R(x,z) AND R(z,y) AND NOT R(x,y)
• R
• A B
• 1 2
• 2 3

5

• Only assignments that make first subgoal true
• x ? 1, z ? 2.
• x ? 2, z ? 3.
• In case (1), y ? 3 makes second subgoal true.
  Since (1,3) is not in R, the third subgoal is
  also true.
• Thus, add (x,y) (1,3) to relation S.
• In case (2), no value of y makes the second
  subgoal true. Thus, S
• A B
• 1 3

6
Example Tuple-Based Assignment

• Trick start with the positive (not negated),
  relational (not arithmetic) subgoals only.
• S(x,y) lt- R(x,z) AND R(z,y) AND NOT R(x,y)
• R A B
• 1 2
• 2 3
• Four assignments of tuples to subgoals
• R(x,z) R(z,y)
• (1,2) (1,2)
• (1,2) (2,3)
• (2,3) (1,2)
• (2,3) (2,3)
• Only the second gives a consistent value to z.
• That assignment also makes NOT R(x,y) true.
• Thus, (1,3) is the only tuple for the head.

7
Safety

• A rule can make no sense if variables appear in
  funny ways.
• Examples
• S(x) lt- R(y)
• S(x) lt- NOT R(x)
• S(x) lt- R(y) AND x lt y
• In each of these cases, the result is infinite,
  even if the relation R is finite.
• To make sense as a database operation, we need to
  require three things of a variable x (
  definition of safety). If x appears in either
• The head,
• A negated subgoal, or
• An arithmetic comparison,
• then x must also appear in a nonnegated,
  ordinary (relational) subgoal of the body.
• We insist that rules be safe, henceforth.

8
Datalog Programs

• A collection of rules is a Datalog program.
• Predicates/relations divide into two classes
• EDB extensional database relation stored in
  DB.
• IDB intensional database relation defined by
  one or more rules.
• A predicate must be IDB or EDB, not both.
• Thus, an IDB predicate can appear in the body or
  head of a rule EDB only in the body.

9
Example

• Convert the following SQL (Find the manufacturers
  of the beers Joe sells)
• Beers(name, manf)
• Sells(bar, beer, price)
• SELECT manf
• FROM Beers
• WHERE name IN(
• SELECT beer
• FROM Sells
• WHERE bar 'Joe''s Bar'
• )
• to a Datalog program.
• JoeSells(b) lt-
• Sells('Joe''s Bar', b, p)
• Answer(m) lt-
• JoeSells(b) AND Beers(b,m)
• Note Beers, Sells EDB JoeSells, Answer IDB.

10
Expressive Power of Datalog

• Nonrecursive Datalog (classical) relational
  algebra.
• See discussion in text.
• Datalog simulates SQL select-from-where without
  aggregation and grouping.
• Recursive Datalog expresses queries that cannot
  be expressed in SQL.
• But none of these languages have full expressive
  power (Turing completeness).

11
Recursion

• IDB predicate P depends on predicate Q if there
  is a rule with P in the head and Q in a subgoal.
• Draw a graph nodes IDB predicates, arc P ? Q
  means P depends on Q.
• Cycles if and only if recursive.
• Recursive Example
• Sib(x,y) lt- Par(x,p) AND Par(y,p)
• AND x ltgt y
• Cousin(x,y) lt- Sib(x,y)
• Cousin(x,y) lt- Par(x,xp)
• AND Par(y,yp)
• AND Cousin(xp,yp)

12
Iterative Fixed-Point Evaluates Recursive Rules
Start IDB ø
Apply rulesto IDB, EDB
Changeto IDB?
done
yes
no
13
Example

• EDB Par
• Note, because of symmetry, Sib and Cousin facts
  appear in pairs, so we shall mention only (x,y)
  when both (x,y) and (y,x) are meant.

a
d
e
c
b
h
g
f
k
j
i
14

• Sib Cousin
• Initial ? ?
• Round 1 (b,c), (c,e) ?
• add (g,h), (j,k)
• Round 2 (b,c), (c,e)
• add (g,h), (j,k)
• Round 3 (f,g), (f,h)
• add (g,i), (h,i)
• (i,k)
• Round 4 (k,k)
• add (i,j)

15
Stratified Negation

• Negation wrapped inside a recursion makes no
  sense.
• Even when negation and recursion are separated,
  there can be ambiguity about what the rules mean,
  and some one meaning must be selected.
• Stratified negation is an additional restraint on
  recursive rules (like safety) that solves both
  problems
• It rules out negation wrapped in recursion.
• When negation is separate from recursion, it
  yields the intuitively correct meaning of rules
  (the stratified model).

16
Problem with Recursive Negation

• Consider
• P(x) lt- Q(x) AND NOT P(x)
• Q EDB 1,2.
• Compute IDB P iteratively?
• Initially, P ?.
• Round 1 P 1,2.
• Round 2 P ?, etc., etc.

17
Strata

• Intuitively stratum of an IDB predicate
  maximum number of negations you can pass through
  on the way to an EDB predicate.
• Must not be ? in stratified rules.
• Define stratum graph
• Nodes IDB predicates.
• Arc P ? Q if Q appears in the body of a rule with
  head P.
• Label that arc if Q is in a negated subgoal.
• Example
• P(x) lt- Q(x) AND NOT P(x)

P

18
Example

• Which target nodes cannot be reached from any
  source node?
• Reach(x) lt- Source(x)
• Reach(x) lt- Reach(y) AND Arc(y,x)
• NoReach(x) lt- Target(x)
• AND NOT Reach(x)

NoReach

Reach
19
Computing Strata

• Stratum of an IDB predicate A maximum number of
  arcs on any path from A in the stratum graph.
• Examples
• For first example, stratum of P is ?.
• For second example, stratum of Reach is 0
  stratum of NoReach is 1.
• Stratified Negation
• A Datalog program is stratified if every IDB
  predicate has a finite stratum.
• Stratified Model
• If a Datalog program is stratified, we can
  compute the relations for the IDB predicates
  lowest-stratum-first.

20
Example

• Reach(x) lt- Source(x)
• Reach(x) lt- Reach(y) AND Arc(y,x)
• NoReach(x) lt- Target(x) AND NOT Reach(x)
• EDB
• Source 1.
• Arc (1,2), (3,4), (4,3).
• Target 2,3.
• First compute Reach 1,2 (stratum 0).
• Next compute NoReach 3.

1
2
3
4
source
target
target
21
Is the Stratified Solution Obvious?

• Not really.
• There is another model that makes the rules true
  no matter what values we substitute for the
  variables.
• Reach 1,2,3,4.
• NoReach ?.
• Remember the only way to make a Datalog rule
  false is to find values for the variables that
  make the body true and the head false.
• For this model, the heads of the rules for Reach
  are true for all values, and in the rule for
  NoReach the subgoal NOT Reach(x) assures that the
  body cannot be true.

22
SQL Recursion

• WITH
• stuff that looks like Datalog rules
• an SQL query about EDB, IDB
• Rule
• RECURSIVE R(ltargumentsgt) AS
• SQL query

23
Example

• Find Sallys cousins, using EDB Par(child,
  parent).
• WITH
• Sib(x,y) AS
• SELECT p1.child, p2,child
• FROM Par p1, Par p2
• WHERE p1.parent p2.parent
• AND p1.child ltgt p2.child,
• RECURSIVE Cousin(x,y) AS
• Sib
• UNION
• (SELECT p1.child, p2.child
• FROM Par p1, Par p2, Cousin
• WHERE p1.parent Cousin.x
• AND p2.parent Cousin.y
• )
• SELECT y
• FROM Cousin
• WHERE x 'Sally'

24
Plan for Describing Legal SQL Recursion

• Define monotonicity, a property that
  generalizes stratification.
• Generalize stratum graph to apply to SQL queries
  instead of Datalog rules.
• (Non)monotonicity replaces NOT in subgoals.
• Define semantically correct SQL recursions in
  terms of stratum graph.
• Monotonicity
• If relation P is a function of relation Q (and
  perhaps other things), we say P is monotone in Q
  if adding tuples to Q cannot cause any tuple of P
  to be deleted.

25
Monotonicity Example

• In addition to certain negations, an aggregation
  can cause nonmonotonicity.
• Sells(bar, beer, price)
• SELECT AVG(price)
• FROM Sells
• WHERE bar 'Joe''s Bar'
• Adding to Sells a tuple that gives a new beer Joe
  sells will usually change the average price of
  beer at Joes.
• Thus, the former result, which might be a single
  tuple like (2.78) becomes another single tuple
  like (2.81), and the old tuple is lost.

26
Generalizing Stratum Graph to SQL

• Node for each relation defined by a rule.
• Node for each subquery in the body of a rule.
• Arc P ? Q if
• P is head of a rule, and Q is a relation
  appearing in the FROM list of the rule (not in
  the FROM list of a subquery), as argument of a
  UNION, etc.
• P is head of a rule, and Q is a subquery
  directly used in that rule (not nested within
  some larger subquery).
• P is a subquery, and Q is a relation or subquery
  used directly within P analogous to (a) and (b)
  for rule heads.
• Label the arc if P is not monotone in Q.
• Requirement for legal SQL recursion finite
  strata only.

27
Example

• For the Sib/Cousin example, there are three
  nodes Sib, Cousin, and SQ (the second term of
  the union in the rule for Cousin).
• No nonmonotonicity, hence legal.

Sib
Cousin
SQ
28
A Nonmonotonic Example

• Change the UNION to EXCEPT in the rule for
  Cousin.
• RECURSIVE Cousin(x,y) AS
• Sib
• EXCEPT
• (SELECT p1.child, p2.child
• FROM Par p1, Par p2, Cousin
• WHERE p1.parent Cousin.x
• AND p2.parent Cousin.y
• )
• Now, adding to the result of the subquery
  candelete Cousin facts i.e., Cousin is
  nonmonotone in SQ.
• Infinite number of s in cycle, so illegal in
  SQL.

Sib
Cousin
SQ
29
Another ExampleNOT Doesnt Mean Nonmonotone

• Leave Cousin as it was, but negate one of the
  conditions in thewhere-clause.
• RECURSIVE Cousin(x,y) AS
• Sib
• UNION
• (SELECT p1.child, p2.child
• FROM Par p1, Par p2, Cousin
• WHERE p1.parent Cousin.x
• AND NOT (p2.parent Cousin.y)
• )
• You might think that SQ depends negatively on
  Cousin, but it doesnt.
• If I add a new tuple to Cousin, all the old
  tuples still exist and yield whatever tuples in
  SQ they used to yield.
• In addition, the new Cousin tuple might combine
  with old p1 and p2 tuples to yield something new.