# CPS 430/542 Lecture notes: Recursion in Datalog and SQL

Coverage: [FCDB] §§10.3-10.6 (pp. 480-502)

## Why study multiple query languages? (revisited)

 database system implementation ← relational algebra (query processing SQL (and optimization) Datalog → AI knowledge-based 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 knowledge-based 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
• non-linear, e.g., fib(n) = fib(n-1) + fib(n-2);
• fixpoints ... remember these?
• least fixpoint, e.g., of f(x) = x2 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)
• even-odd 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.