SQL Recursion WITH stu that lo oks lik e Datalog rules an - PDF document

SQL Recursion WITH stu� that lo oks lik e Datalog rules an SQL query ab out EDB, IDB Rule = � ( < argumen ts > ) [RECURSIVE] R AS SQL query 1

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

Plan for Describing Legal SQL recursion 1. De�ne \monotonicit y ," a prop ert y that generalizes \strati�cati on." 2. Generalize stratum graph to apply to SQL queries instead of Datalog rules. ✦ (Non)monotonicit y replaces in NOT subgoals. 3. De�ne seman tically correct SQL recursions in terms of stratum graph. Monotonicit y If relation is a function of relation (and P Q p erhaps other things), w e sa y is in P monotone if adding tuples to cannot cause an y tuple of Q Q to b e deleted. P 3

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 a tuple that giv es a new b eer Sells � Jo e sells will usually c hange the a v erage price of 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 old tuple is lost. 4

Generalizing Stratum Graph to SQL No de for eac h relation de�ned b y a \rule." � No de for eac h sub query in the \b o dy" of a � rule. Arc if P Q � ! a) is \head" of a rule, and is a relation P Q app earing in the list of the rule FROM (not in the list of a sub query), as FROM argumen t of a UNION , etc. b) is head of a rule, and is a sub query P Q directly used in that rule (not nested within some larger sub query). c) is a sub query , and is a relation P Q or sub query used directly within P [analogous to (a) and (b) for rule heads]. Lab el the arc if is monotone in Q . P not � � Requiremen t for legal SQL recursion: �nite � strata only . 5

Example F or the Sib/Cousin example, there are three no des: Sib , Cousin , and (the second term of the S Q union in the rule for Cousin ). Sib Cousin S Q No nonmonotonicit y , hence legal. � 6

A Nonmonotonic Example Change the to in the rule for UNION EXCEPT 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 of the sub query � can delete facts; i.e., is Cousin Cousin nonmonotone in Q . S Sib Cousin � S Q In�nite n um b er of � 's in cycle, so illegal in � SQL. 7

Another Example: NOT Do esn't Mean Nonmonotone Lea v e as it w as, but negate one of the Cousin 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 ou migh t think that dep ends negativ ely S Q � on Cousin , but it do esn't. ✦ If I add a new tuple to Cousin , all the old tuples still exist and yield whatev er tuples in they used to yield. S Q ✦ In addition, the new tuple migh t Cousin com bine with old p 1 and p 2 tuples to yield something new. 8

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

ODL Ov erview Class declarations include: 1. Name for the class. 2. Key declaration(s), whic h are optional. 3. declaration = name for the set of Extent curren tly existing ob jects of a class. 4. declarations. An elemen t is an Element attribute, a relationship, or a metho d. 10

ODL Class Declarations < name > class { elemen ts = attributes, relationships, metho ds } Elemen t Declarations < t yp e > < name > ; attribute < ranget yp e > < name > ; relationship Relationshi ps in v olv e ob jects; attributes � (usually) in v olv e non-ob ject v alues, e.g., in tegers. Metho d Example float gpa(in string) raises(noGrades) = return t yp e. float � indicates the argumen t (a studen t name, in: � presumably) is read-only . ✦ Other options: out , inout . is an exception that can b e raised noGrades � b y metho d gpa . 11

ODL Relationships Only binary relations supp orted. � ✦ 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, a relationship and in b eers giving the bars that sell it. Man y-man y relationships ha v e a set t yp e � (called a e ) in eac h direction. c ol le ction typ Man y-one relationships ha v e a set t yp e for the � one, and a simple class name for the man y . One-one relations ha v e classes for b oth. � 12

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 orm a set t yp e with Set<type> . � 13

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 of �eld-t yp e pairs. En umerated t yp es ha v e names and brac k eted � lists of v alues. 14

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 of t yp e. Addr � 15

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: for structures and Struct � �v e : Set , Bag , List , Array , c ol le ction typ es and Dictionary . Relationshi p t yp es man y only b e classes or a � collecti on of a class. 16

Man y-One Relationships Don't use a collecti on t yp e for relationship in the \man y" class. Example: Drink ers Ha v e F a v orite 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

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 , m ust b e added theBeer � to Bars , Beers . Better in this sp ecial case: mak e no Prices � class; mak e an attribute of BBP . price Notice that k eys are optional. � ✦ has no k ey , y et is not \w eak." Ob ject BBP iden tit y su�ces to distinguish di�eren t ob jects. BBP 18

Roles in ODL Names of relationships handle \roles." Example: Sp ouses 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 is optional when the Drinkers:: � in v erse is a relationship of the same class. 19