CPS 430/542 Lecture notes:
Recursion in Datalog and SQL
Coverage: [FCDB] §§10.310.6 (pp. 480502)
Why study multiple query languages? (revisited)
database system implementation  ←  relational algebra  
(query processing   SQL  
(and optimization)   Datalog  → 
AI 




knowledgebased systems, LP (PROLOG) 




data mining, ILP (PROGOL) 
Overview
 why did we study multiple query languages
 SQL is most mainstream language
 relational algebra helps us make connections to
database system implementation (query processing and optimization in
view example)
 Datalog is most expressive; helps us make
connections to knowledgebased systems (through PROLOG)
 there are some queries which cannot be written in SQL or relational
algebra
 extensional (stored) vs. intensional (computed) relations (EDB's vs. IDB's)
 types of recursion
 linear: left or right
 nonlinear, e.g., fib(n) = fib(n1) + fib(n2);
 fixpoints ... remember these?
 least fixpoint, e.g., of f(x) = x^{2} is 0
(the greatest fixpoint is 1)
 ancestor example (the hello world of recursion in Datalog)
 must use caution when mixing negation and recursion
Examples
 ancestor example (the hello world of recursion in Datalog)
 celebrity problem
 flights example (stratified negation)
 evenodd example
 mutual recursion with negation
 2 least fixpoint solutions, and they contradict
each other
 unstratified example
Stratified recursion
 how to determine if negation is stratified?
 boils down to checking for cycles in a graph of IDB relations
Recursion in SQL
 support for recursion based on Datalog
 in SQL99 which is not part of the core SQL
 defining IDB relations in SQL: use WITH RECURSIVE
 only linear recursion permitted
 only stratified negation and aggregations permitted
Deductive vs. inductive systems
 deductive (LP; e.g., PROLOG): rules → data
 inductive (ILP; e.g., PROGOL): data → rules
(inverses of each other akin to differentiation and integration
in calculus)
References
[FCDB] 
J.D. Ullman and J. Widom. A First Course in Database Systems.
Prentice Hall, Upper Saddle River, NJ, Second edition, 2002.

