{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

08-recursion-notes

# 08-recursion-notes - SQL Recursion CPS 116 Introduction to...

This preview shows pages 1–4. Sign up to view the full content.

1 SQL: Recursion CPS 116 Introduction to Database Systems 2 Announcements (Tue. Sep. 27) Homework #2 due in one week Start now, if you haven’t 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 Bart’s 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

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

View Full Document
2 4 Recursion in SQL SQL2 had no recursion You can find Bart’s 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 0 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, … 0
3 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 point (assuming q is monotone) 8

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

View Full Document
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

### Page1 / 8

08-recursion-notes - SQL Recursion CPS 116 Introduction to...

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

View Full Document
Ask a homework question - tutors are online