Row Types in SQL-3

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) ) Fol
lowing references SELECT president-gtname
FROM Company WHERE president-gtaddress..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 lttype namegt (
• list of attributes and their types
• optional declaration of the comparison
  functions , lt
• 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, ADTs 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 ADTs.

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 -gt 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., theyre 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