Unformatted text preview: SQL Recursion
WITH stu that looks like Datalog rules an SQL query about EDB, IDB Rule =
RECURSIVE SQL query R arguments AS 1 Example Find Sally's cousins, using EDB Parchild, parent.
WITH Sibx,y AS SELECT p1.child, p2,child FROM Par p1, Par p2 WHERE p1.parent = p2.parent AND p1.child p2.child, RECURSIVE Cousinx,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 1. De ne monotonicity," a property that generalizes strati cation." 2. Generalize stratum graph to apply to SQL queries instead of Datalog rules. 3 Nonmonotonicity replaces NOT in subgoals. 3. De ne semantically correct SQL recursions in terms of stratum graph. Plan for Describing Legal SQL recursion Monotonicity
P Q P If relation is a function of relation and perhaps other things, we say is monotone in if adding tuples to cannot cause any tuple of to be deleted.
Q P Q 3 Monotonicity Example In addition to certain negations, an aggregation can cause nonmonotonicity.
Sellsbar, beer, price SELECT AVGprice FROM Sells WHERE bar = 'Joe''s Bar'; Adding to Sells a tuple that gives a new beer Joe sells will usually change the average price of beer at Joe's. Thus, the former result, which might be a single tuple like 2 78 becomes another single tuple like 2 81, and the old tuple is lost.
: : 4 Generalizing Stratum Graph to SQL
Node for each relation de ned by a rule." Node for each subquery in the body" of a rule. Arc ! if a is head" of a rule, and is a relation appearing in the FROM list of the rule not in the FROM list of a subquery, as argument of a UNION, etc. b is head of a rule, and is a subquery directly used in that rule not nested within some larger subquery. c is a subquery, and is a relation or subquery used directly within analogous to a and b for rule heads . Label the arc , if is not monotone in . Requirement for legal SQL recursion: nite strata only.
P Q P Q P Q P Q P P Q 5 Example For the Sib Cousin example, there are three nodes: Sib, Cousin, and the second term of the union in the rule for Cousin.
SQ Sib Cousin SQ No nonmonotonicity, hence legal. 6 A Nonmonotonic Example Change the UNION to EXCEPT in the rule for Cousin.
RECURSIVE Cousinx,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 Now, adding to the result of the subquery can delete Cousin facts; i.e., Cousin is nonmonotone in .
SQ Sib Cousin ,
SQ In nite number of ,'s in cycle, so illegal in SQL. 7 Another Example: Nonmonotone NOT Doesn't Mean Leave Cousin as it was, but negate one of the conditions in the where-clause.
RECURSIVE Cousinx,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 You might think that depends negatively on Cousin, but it doesn't. 3 If I add a new tuple to Cousin, all the old tuples still exist and yield whatever tuples in they used to yield. 3 In addition, the new Cousin tuple might combine with old 1 and 2 tuples to yield something new.
SQ SQ p p 8 Object-Oriented DBMS's
ODMG = Object Data Management Group: an OO standard for databases. ODL = Object Description Language: design in the OO style. OQL = Object Query Language: queries an OO database with an ODL schema, in a manner similar to SQL. 9 ODL Overview Class declarations include: 1. Name for the class. 2. Key declarations, which are optional. 3. Extent declaration = name for the set of currently existing objects of a class. 4. Element declarations. An element is an attribute, a relationship, or a method. 10 ODL Class Declarations
class name elements = attributes, relationships, methods Element Declarations
attribute relationship type name ; rangetype name ; Relationships involve objects; attributes usually involve non-object values, e.g., integers.
float gpain string raisesnoGrades Method Example
= return type. in: indicates the argument a student name, presumably is read-only. 3 Other options: out, inout. noGrades is an exception that can be raised by method gpa.
float 11 ODL Relationships
Only binary relations supported. 3 Multiway relationships require a connecting" class, as discussed for E R model. Relationships come in inverse pairs. 3 Example: Sells" between beers and bars is represented by a relationship in bars, giving the beers sold, and a relationship in beers giving the bars that sell it. Many-many relationships have a set type called a collection type in each direction. Many-one relationships have a set type for the one, and a simple class name for the many. One-one relations have classes for both. 12 Beers-Bars-Drinkers Example
class Beers attribute string name; attribute string manf; relationship Set Bars servedAt inverse Bars::serves; relationship Set Drinkers fans inverse Drinkers::likes; An element from another class is indicated by class :: Form a set type 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 types have names and bracketed lists of eld-type pairs. Enumerated types have names and bracketed lists of values. 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 Addr type. 15 ODL Type System
Basic types: int, real oat, string, enumerated types, and classes. Type constructors: Struct for structures and ve collection types : Set, Bag, List, Array, and Dictionary. Relationship types many only be classes or a collection of a class. 16 Many-One Relationships Don't use a collection type for relationship in the many" class. Example: Drinkers Have Favorite 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::favoriteBeer; 17 Example: Multiway Relationship Consider a 3-way relationship bars-beers-prices. We have 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; Inverses for theBar, theBeer must be added to Bars, Beers. Better in this special case: make no Prices class; make price an attribute of BBP. Notice that keys are optional. 3 BBP has no key, yet is not weak." Object identity su ces to distinguish di erent BBP objects. 18 Roles in ODL Names of relationships handle roles." Example: Spouses 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 optional when the inverse is a relationship of the same class. 19 ...
View Full Document
- Fall '07
- Recursion, relationship Set, relationship Set Beers, relationship Set Bars