1 / 10

Row Types in SQL-3

Row Types in SQL-3. Row types define types for tuples, and they can be nested. CREATE ROW TYPE AddressType{ street CHAR(50), city CHAR(25), zipcode CHAR(10) } CREATE ROW TYPE PersonType{ name CHAR(30), address AddressType,

borna
Download Presentation

Row Types in SQL-3

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Row Types in SQL-3 Row types define types for tuples, and they can be nested. CREATE ROW TYPE AddressType{ street CHAR(50), city CHAR(25), zipcode CHAR(10) } CREATE ROW TYPE PersonType{ name CHAR(30), address AddressType, phone phoneNumberType }

  2. Relations as Row Types CREATE TABLE Person OF TYPE PersonType; Recall: row types can be nested! Accessing components of a row type: (double dots) SELECT Person.name, Person.address..city FROM Person WHERE Person.address..street LIKE ‘%Mountain%’

  3. References We can define attributes of a row type to reference objects of other row types: CREATE ROW TYPE Company( name char(30), address addressType, president REF(PersonType) ); Following references: SELECT president->name FROM Company WHERE president->address..city=“Seattle”

  4. Abstract Data Types in SQL3 • Row types provide a lot of the functionality of objects: • allow us to modify objects (unlike OQL), but • do not provide encapsulation. • We can modify objects arbitrarily using SQL3 commands. • In OQL: we can query, but not modify only via methods. • Abstract data types: are used as components of tuples. • CREATE TYPE <type name> ( • list of attributes and their types • optional declaration of the comparison functions: =, < • declaration of methods for the type • );

  5. Address ADT CREATE TYPE AddressADT ( street CHAR(50), city CHAR(20), EQUALS addrEq, LESS THAN addrLT FUNCTION fullAddr (a: AddressADT) RETURNS CHAR(100); :z CHAR(10); BEGIN :z = findZip(:a.street, :a.city); RETURN (….) END; DECLARE EXTERNAL findZip CHAR(50) CHAR(20) RETURNS CHAR(10) LANGUAGE C; ); Encapsulation is obtained by making methods public/private

  6. Differences Between OODB Approaches • Programming environment: much more closely coupled in • OQL/ODL than in SQL3. • Changes to objects are done via the programming language in • OQL, and via SQL statements in SQL3. • Role of relations: still prominent in SQL 3 • Row types are really tuples, ADT’s describe attributes. • In OQL: sets, bags and structures are fundamental. • Encapsulation: exists in OQL; not really supported by row types • in SQL3, but are supported by ADT’s.

  7. Transitive Closure Suppose we are representing a graph by a relation Edge(X,Y): Edge(a,b), Edge (a,c), Edge(b,d), Edge(c,d), Edge(d,e) b a d e c I want to express the query: Find all nodes reachable from a.

  8. Recursion in Datalog Path( X, Y ) :- Edge( X, Y ) Path( X, Y ) :- Path( X, Z ), Path( Z, Y ). Semantics: evaluate the rules until a fixedpoint: Iteration #0: Edge: {(a,b), (a,c), (b,d), (c,d), (d,e)} Path: {} Iteration #1: Path: {(a,b), (a,c), (b,d), (c,d), (d,e)} Iteration #2: Path gets the new tuples: (a,d), (b,e), (c,e) Iteration #3: Path gets the new tuple: (a,e) Iteration #4: Nothing changes -> We stop. Note: number of iterations depends on the data. Cannot be anticipated by only looking at the query!

  9. Deductive Databases • We distinguish two types of relations in our database: • Extensional relations (EDB): their extent is stored in the • database just like in ordinary relational databases. • Intentional relations (IDB): their extension is defined by • a set of possibly recursive datalog rules. • Intentional relations can either be materialized or computed • on demand. • Note: a query and a definition of an intentional predicate look • exactly the same (I.e., they’re both datalog programs). • Hard problem: how do we optimize queries in the presence of • recursion. • Harder problem: do we really need recursion?

  10. Recursion in SQL-3 Limited forms of recursion are considered important. Linear recursion: only 1 occurrence of a recursive predicate in the body Path( X, Y ) :- Edge( X, Y ) Path( X, Y ) :- Edge( X, Z ), Path( Z, Y ). WITH Pairs AS SELECT origin, dest FROM EDGE RECURSIVE Path(origin, dest) AS Pairs UNION (SELECT Pairs.origin, Path.to FROM Pairs, Path WHERE Pairs.to = Path.origin) SELECT * FROM Path;

More Related