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 Openbook, opennotes 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

Click to edit the document details