=========================================================================
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
----------------------------------------

(1) Type definitions separate from table definitions

(2) Nested structures

(3) Methods

(4) References (pointers)

(5) Observer, generator, and mutator functions

(6) 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"


Ex: 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))


** Constraints are part of table declaration, not type declaration


Nested Structures
-----------------

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


Ex: Use StudentType in student phone list:

   CREATE TABLE Phone (student StudentType, phone char(15))

But might better be done with references (see below)


Ex: 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, ScoresType


Queries with Nesting
--------------------

Just more dots

Ex: 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 or PSM


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

Ex: 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
--------------------

Ex: Return ID and composite score for all students






Ex: 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


Ex: To make students referenceable:

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


Ex: Student phone list:

CREATE TABLE Phone (student REF(StudentType), phone 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).


Ex: Student phone list without old students:

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


Queries with References
-----------------------

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


Ex: Phone numbers of students living in Mountain View







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







- References eliminate joins

- Looks like ODL classes and relationships

- But still no set-valued attributes or relationships


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 it for UDT's also.


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






"CREATE ORDERING FOR <type> <ordering>" statement


<ordering>: EQUALS ONLY BY STATE

  - Two tuples are equal if all components are equal

  - If component is itself UDT, need ORDERING for that UDT
    (and so on, recursively)

  - Permits operations =, <>, DISTINCT, GROUP BY


<ordering>: ORDERING FULL BY RELATIVE WITH <function>

  - <function> must take two tuples, (logically) return =, <, or >

  - Permits operations =, <, > <=, >=, <>, ORDER BY, DISTINCT, GROUP BY


<ordering>: EQUALS ONLY BY RELATIVE WITH <function>

  - <function> must take two tuples, (logically) returns = or <>

  - Permits operations =, <>, DISTINCT, GROUP BY


** Without CREATE ORDERING, cannot do any of =, <, > <=, >=, <>,
** ORDER BY, DISTINCT, GROUP BY
  

Translating an ODL Design to Object-Relational
----------------------------------------------

1. Class with atomic attributes and no relationships: translates
   directly to conventional relation

2. Struct attributes: no need to "flatten," create TYPE for them
   instead

3. Sets, Bags, Lists, Arrays: Same issues as in ODL-to-relational
   translation (assuming no "nested relations")

4. Relationships: Similar issues as ODL-to-relational translation
   except can use references (with SCOPE) instead of keys

5. Subclasses: Similar issues as ODL-to-relational translation


Q: Not much has changed.  S what's the advantage of object-relational
   over relational?

A: