08-recursion - 1 SQL: Recursion CPS 116 Introduction to...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the 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.

Page1 / 4

08-recursion - 1 SQL: Recursion CPS 116 Introduction to...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online