Transcript 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,
phone phoneNumberType
}
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%’
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”
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
);
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
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.
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;