CS145 Lecture Notes -- Object-Relational SQL

Most major DBMS vendors now call their products "object-relational."
There is great variation in OO functionality among current products.
We'll cover a portion of the SQL-99 standard, as in the textbook.
Oracle 9i has object support that does not adhere exactly to the standard. (See our Oracle help document if interested.)

SQL-99 Object Support - Major Components

Type definitions separate from table definitions
Nested structures
Methods
References (pointers)
Observer, generator, and mutator functions
Ordering relationships

Type Definitions (UDT's)

Separate from table definitions
Can use for multiple tables
Constraints on per-table basis
Can use for attribute types (nested structures)

Original student table, recast:

  CREATE TYPE StudentType
      (ID integer, name char(30), address char(100), GPA float, SAT integer)

  CREATE TABLE Student OF TYPE StudentType (PRIMARY KEY (ID))

Main difference (so far):

Attribute references in queries must use ()
Really invoking "observer methods"

Example: Find name, address of all students with GPA > 3.8 or SAT > 1450

Can use same type for multiple relations:

  CREATE TABLE OldStudent OF TYPE StudentType PRIMARY KEY (name,address))

Note: Constraints are part of table declaration, not type declaration

Nested Structures

An attribute of a table can have a User-Defined Type (UDT)

Example: Use StudentType in student phone list:

   CRATE TABLE Phone (student StudentType, number char(15))

But might better be done with references (see below)

Example: More structure in Student definition:

  CREATE TYPE AddressType AS (street char(50), city char(50), zip integer)

  CREATE TYPE ScoresType AS (GPA float, SAT integer)

  CREATE TYPE StudentType AS
     (ID integer, name char(30), address AddressType, scores ScoresType)

  CREATE TABLE Student OF TYPE StudentType (PRIMARY KEY (ID))

(picture)

Note: no actual relations with types AddressType or ScoresType

Queries with Nesting

Just more dots

Example: Find name, street of all students from Palo Alto with GPA < 3.5

Methods

Signature declared as part of type
Method body implemented using, e.g., PL/SQL, PSM, or Java

Examples:

  CREATE TYPE ScoresType AS (GPA float, SAT integer)
  METHOD composite() RETURNS float

  CREATE TYPE AddressType AS (street char(50), city char(50), zip integer)
  METHOD sendto(name char(20), msg char(50)) RETURNS char(1)

Queries with Methods

Example: Return ID and composite score for all students

Example: Send "sorry" mailing to all students with composite score < 50. Return ID and confirmation character as result of query.

References

To be referenced, tuples must have an explicitly declared "reference column"
Reference column can be derived from primary key or purely system-generated

Example: To make students referenceable:

  CREATE TABLE Student OF TYPE StudentType
     (PRIMARY KEY (ID),
      REF IS SRef DERIVED) // alternative is SYSTEM GENERATED

Example: Student phone list:

  CREATE TABLE Phone (student REF(StudentType), number char(15))

Phone tuples can reference any tuples of StudentType, e.g., in Student table and/or in OldStudent table (if it contains "REF IS" clause).

Example: Student phone list without old students:

  CREATE TABLE Phone
   (student REF(StudentType) SCOPE Student, number char(15))

Queries with References

Dereferencing operator DEREF
Dereferencing and attribute selection operator "->"

Example: Phone numbers of students living in Mountain View

Example: Students with phone numbers containing "(408)"

Main effect of references is to eliminate joins.

Generator and Mutator Functions

For creating and updating values of UDT's
See book

Ordering Relationships

SQL uses equality and ordering of values in lots of places:

  =, <, >, <=, >=, <>
  ORDER BY
  DISTINCT, GROUP BY

Would be nice to have these capabilities for UDT's also

Example: Students with phone numbers containing "(408)", ordered by score

Statement:

  CREATE ORDERING FOR <type> <ordering>

Details and defaults seem to be in flux. Basic idea:

If <ordering> specifies equals only:

Two tuples are equal if and only if all components are equal
If component is itself UDT, need ORDERING for that UDT (and so on, recursively)
Permits operations =, <>, DISTINCT, GROUP BY

If <ordering> specifies method-defined, all comparisons:

Defined method takes two tuples and returns =, <, or >
Permits operations =, <, > <=, >=, <>, ORDER BY, DISTINCT, GROUP BY

If <ordering> specifies method-defined, equals only:

Defined method takes two tuples and returns = or <>
Permits operations =, <>, DISTINCT, GROUP BY