[PDF] - SQL Recursion WITH stu that lo oks lik e Datalog rules an PDF Document

SLIDE 1 SQL Recursion WITH stu that lo

ks

lik e Datalog rules an SQL query ab

ut

EDB, IDB

Rule

= [RECURSIVE] R (<argumen ts>) AS SQL query 1

SLIDE 2 Example Find Sally's cousins, using EDB Par(child, parent). WITH Sib(x,y) AS SELECT p1.child, p2,child FROM Par p1, Par p2 WHERE p1.parent = p2.parent AND p1.child <> p2.child, RECURSIVE Cousin(x,y) AS Sib UNION (SELECT p1.child, p2.child FROM Par p1, Par p2, Cousin WHERE p1.parent = Cousin.x AND p2.parent = Cousin.y ) SELECT y FROM Cousin WHERE x = 'Sally'; 2

SLIDE 3 Plan for Describing Legal SQL recursion 1. Dene \monotonicit y ," a prop ert y that generalizes \straticati

n."

2. Generalize stratum graph to apply to SQL queries instead

f

Datalog rules.

✦

(Non)monotonicit y replaces NOT in subgoals. 3. Dene seman tically correct SQL recursions in terms

f

stratum graph. Monotonicit y If relation P is a function

f

relation Q (and p erhaps

ther

things), w e sa y P is monotone in Q if adding tuples to Q cannot cause an y tuple

f

P to b e deleted. 3

SLIDE 4 Monotonicit y Example In addition to certain negations, an aggregation can cause nonmonotonicit y . Sells(bar , beer , price) SELECT AVG(price) FROM Sells WHERE bar = 'Joe''s Bar';

Adding

to Sells a tuple that giv es a new b eer Jo e sells will usually c hange the a v erage price

f

b eer at Jo e's.

Th

us, the former result, whic h migh t b e a single tuple lik e (2:78) b ecomes another single tuple lik e (2:81), and the

ld

tuple is lost. 4

SLIDE 5 Generalizing Stratum Graph to SQL

No

de for eac h relation dened b y a \rule."

No

de for eac h sub query in the \b

dy"
f

a rule.

Arc

P ! Q if a) P is \head"

f

a rule, and Q is a relation app earing in the FROM list

f

the rule (not in the FROM list

f

a sub query), as argumen t

f

a UNION, etc. b) P is head

f

a rule, and Q is a sub query directly used in that rule (not nested within some larger sub query). c) P is a sub query , and Q is a relation

r

sub query used directly within P [analogous to (a) and (b) for rule heads].

Lab

el the arc

if

P is not monotone in Q.

Requiremen

t for legal SQL recursion: nite strata

nly

. 5

SLIDE 6 Example F

r

the Sib/Cousin example, there are three no des: Sib, Cousin, and S Q (the second term

f

the union in the rule for Cousin). Sib Cousin S Q

No

nonmonotonicit y , hence legal. 6

SLIDE 7 A Nonmonotonic Example Change the UNION to EXCEPT in the rule for Cousin. RECURSIVE Cousin(x,y) AS Sib EXCEPT (SELECT p1.child, p2.child FROM Par p1, Par p2, Cousin WHERE p1.parent = Cousin.x AND p2.parent = Cousin.y )

No

w, adding to the result

f

the sub query can delete Cousin facts; i.e., Cousin is nonmonotone in S Q. Sib Cousin S Q

Innite

n um b er

f

's in cycle, so illegal in SQL. 7

SLIDE 8 Another Example: NOT Do esn't Mean Nonmonotone Lea v e Cousin as it w as, but negate

ne
f

the conditions in the where-clause. RECURSIVE Cousin(x,y) AS Sib UNION (SELECT p1.child, p2.child FROM Par p1, Par p2, Cousin WHERE p1.parent = Cousin.x AND NOT (p2.parent = Cousin.y) )

Y
u

migh t think that S Q dep ends negativ ely

n

Cousin, but it do esn't.

✦

If I add a new tuple to Cousin, all the

ld

tuples still exist and yield whatev er tuples in S Q they used to yield.

✦

In addition, the new Cousin tuple migh t com bine with

ld

p1 and p2 tuples to yield something new. 8

SLIDE 9 Ob ject-Orien ted DBMS's

ODMG

= Ob ject Data Managemen t Group: an OO standard for databases.

ODL

= Ob ject Description Language: design in the OO st yle.

OQL

= Ob ject Query Language: queries an OO database with an ODL sc hema, in a manner similar to SQL. 9

SLIDE 10 ODL Ov erview Class declarations include: 1. Name for the class. 2. Key declaration(s), whic h are

ptional.

3. Extent declaration = name for the set

f

curren tly existing

b

jects

f

a class. 4. Element declarations. An elemen t is an attribute, a relationship,

r

a metho d. 10

SLIDE 11 ODL Class Declarations class <name> { elemen ts = attributes, relationships, metho ds } Elemen t Declarations attribute <t yp e> <name>; relationship <ranget yp e> <name>;

Relationshi

ps in v

lv

e

b

jects; attributes (usually) in v

lv

e non-ob ject v alues, e.g., in tegers. Metho d Example float gpa(in string) raises(noGrades)

float

= return t yp e.

in:

indicates the argumen t (a studen t name, presumably) is read-only .

✦

Other

ptions:
ut,

inout.

noGrades

is an exception that can b e raised b y metho d gpa. 11

SLIDE 12 ODL Relationships

Only

binary relations supp

rted.

✦

Multiw a y relationships require a \connecting" class, as discussed for E/R mo del.

Relationshi

ps come in in v erse pairs.

✦

Example: \Sells" b et w een b eers and bars is represen ted b y a relationship in bars, giving the b eers sold, and a relationship in b eers giving the bars that sell it.

Man

y-man y relationships ha v e a set t yp e (called a c

l

le ction typ e) in eac h direction.

Man

y-one relationships ha v e a set t yp e for the

ne,

and a simple class name for the man y .

One-one

relations ha v e classes for b

th.

12

SLIDE 13 Beers-Bars-Drink ers Example class Beers { attribute string name; attribute string manf; relationship Set<Bars> servedAt inverse Bars::serves; relationship Set<Drinkers> fans inverse Drinkers::likes; }

An

elemen t from another class is indicated b y <class>::

F
rm

a set t yp e with Set<type>. 13

SLIDE 14 class Bars { attribute string name; attribute Struct Addr {string street, string city, int zip} address; attribute Enum Lic {full, beer, none} licenseType; relationship Set<Drinkers> customers inverse Drinkers::frequents; relationship Set<Beers> serves inverse Beers::servedAt; }

Structured

t yp es ha v e names and brac k eted lists

f

eld-t yp e pairs.

En

umerated t yp es ha v e names and brac k eted lists

f

v alues. 14

SLIDE 15 class Drinkers { attribute string name; attribute Struct Bars::Addr address; relationship Set<Beers> likes inverse Beers::fans; relationship Set<Bars> frequents inverse Bars::customers; }

Note

reuse

f

Addr t yp e. 15

SLIDE 16 ODL T yp e System

Basic

t yp es: in t, real/oat, string, en umerated t yp es, and classes.

T

yp e constructors: Struct for structures and v e c

l

le ction typ es : Set, Bag, List, Array, and Dictionary.

Relationshi

p t yp es man y

nly

b e classes

r

a collecti

n
f

a class. 16

SLIDE 17 Man y-One Relationships Don't use a collecti

n

t yp e for relationship in the \man y" class. Example: Drink ers Ha v e F a v

rite

Beers class Drinkers { attribute string name; attribute Struct Bars::Addr address; relationship Set<Beers> likes inverse Beers::fans; relationship Beers favoriteBeer inverse Beers::realFans; relationship Set<Bars> frequents inverse Bars::customers; }

Also

add to Beers: relationship Set<Drinkers> realFans inverse Drinkers::favoriteBe er; 17

SLIDE 18 Example: Multiw a y Relationship Consider a 3-w a y relationship bars-b eers-prices. W e ha v e to create a connecting class BBP. class Prices { attribute real price; relationship Set<BBP> toBBP inverse BBP::thePrice; } class BBP { relationship Bars theBar inverse ... relationship Beers theBeer inverse ... relationship Prices thePrice inverse Prices::toBBP; }

In

v erses for theBar, theBeer m ust b e added to Bars, Beers.

Better

in this sp ecial case: mak e no Prices class; mak e price an attribute

f

BBP.

Notice

that k eys are

ptional.

✦

BBP has no k ey , y et is not \w eak." Ob ject iden tit y suces to distinguish dieren t BBP

b

jects. 18

SLIDE 19 Roles in ODL Names

f

relationships handle \roles." Example: Sp

uses

and Drinking Buddies class Drinkers { attribute string name; attribute Struct Bars::Addr address; relationship Set<Beers> likes inverse Beers::fans; relationship Set<Bars> frequents inverse Bars::customers; relationship Drinkers husband inverse wife; relationship Drinkers wife inverse husband; relationship Set<Drinkers> buddies inverse buddies; }

Notice

that Drinkers:: is

ptional

when the in v erse is a relationship

f

the same class. 19