{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

08-recursion

# 08-recursion - Announcements(Tue Sep 27 Homework#2 due in...

This preview shows pages 1–3. 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 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

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

View Full Document
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 point (assuming q is monotone) 8 Finding ancestors WITH RECURSIVE Ancestor(anc, desc) AS
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

### What students are saying

• As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

Kiran Temple University Fox School of Business ‘17, Course Hero Intern

• I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

Dana University of Pennsylvania ‘17, Course Hero Intern

• The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

Jill Tulane University ‘16, Course Hero Intern