slides15

slides15 - SQL Recursion WITH stu that looks like Datalog...

Info iconThis preview shows page 1. Sign up to view the full content.

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

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

Ask a homework question - tutors are online