This preview shows pages 1–3. Sign up to view the full content.
This preview has intentionally blurred sections. Sign up to view the full version.View Full Document
Unformatted text preview: 1 SQL: Recursion CPS 116 Introduction to Database Systems 2 Announcements (Tue. Sep. 27) Homework #2 due in one week Start now, if you havent already Homework #1 sample solution was handed out Only available in hard copies Midterm next Thursday in class Open-book, open-notes Sample midterm (from last offering of 116) available 3 A motivating example Example: find Barts ancestors Ancestor has a recursive definition X is Y s ancestor if X is Y s parent, or X is Z s ancestor and Z is Y s ancestor Parent ( parent , child ) Bart Lisa Marge Homer Abe Ape parent child Homer Bart Homer Lisa Marge Bart Marge Lisa Abe Homer Ape Abe 4 Recursion in SQL SQL2 had no recursion You can find Barts parents, grandparents, great grandparents, etc. SELECT p1.parent AS grandparent FROM Parent p1, Parent p2 WHERE p1.child = p2.parent AND p2.child = Bart; But you cannot find all his ancestors with a single query SQL3 introduces recursion WITH clause Implemented in PostgreSQL (common table expressions) 5 Ancestor query in SQL3 WITH RECURSIVE Ancestor(anc, desc) AS ((SELECT parent, child FROM Parent) UNION (SELECT a1.anc, a2.desc FROM Ancestor a1, Ancestor a2 WHERE a1.desc = a2.anc)) SELECT anc FROM Ancestor WHERE desc = Bart; Query using the relation defined in WITH clause Define a a relation recursively base case recursion step How do we compute such a recursive query? 6 Fixed point of a function If f : T T is a function from a type T to itself, a fixed point of f is a value x such that f ( x ) = x Example: What is the fixed point of f ( x ) = x / 2? 0, because f (0) = 0 / 2 = 0 To compute a fixed point of f Start with a seed: x x Compute f ( x ) If f ( x ) = x , stop; x is fixed point of f Otherwise, x f ( x ); repeat Example: compute the fixed point of f ( x ) = x / 2 With seed 1: 1, 1/2, 1/4, 1/8, 1/16, 2 7 Fixed point of a query A query q is just a function that maps an input table to an output table, so a fixed point of q is a table T such that q ( T ) = T To compute fixed point of q Start with an empty table: T Evaluate q over T If the result is identical to T , stop; T is a fixed point Otherwise, let T be the new result; repeat ) Starting from produces the unique minimal fixed...
View Full Document
This document was uploaded on 01/17/2012.
- Spring '09