Row Types in SQL3 Row types define types

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

Relations as Row Types CREATE TABLE Person OF TYPE Person. Type; 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%’

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

Abstract Data Types in SQL 3 • 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 SQL 3 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 );

Address ADT CREATE TYPE Address. ADT ( street CHAR(50), city CHAR(20), EQUALS addr. Eq, LESS THAN addr. LT FUNCTION full. Addr (a: Address. ADT) RETURNS CHAR(100); : z CHAR(10); BEGIN : z = find. Zip(: a. street, : a. city); RETURN (…. ) END; DECLARE EXTERNAL find. Zip CHAR(50) CHAR(20) RETURNS CHAR(10) LANGUAGE C; ); Encapsulation is obtained by making methods public/private

Differences Between OODB Approaches • Programming environment: much more closely coupled in OQL/ODL than in SQL 3. • Changes to objects are done via the programming language in OQL, and via SQL statements in SQL 3. • 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 SQL 3, but are supported by ADT’s.

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 c I want to express the query: Find all nodes reachable from a. e

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!

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?

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;