CS145 Lecture Notes (15) -- Object-Relational SQL



SQL-99 Object Support - Major Components

  1. Type definitions separate from table definitions
  2. User-defined simple and complex types
  3. Methods
  4. References (pointers)
  5. Observer, generator, and mutator functions
  6. Equality and ordering relationships

Type Definitions (UDTs)

Original student table, recast:
  CREATE TYPE StudentType
      (ID integer, name char(30), address char(100), GPA float, sizeHS integer)

  CREATE TABLE Student OF StudentType (PRIMARY KEY (ID))
Effect on queries: Example: Find name, address of all students with GPA > 3.8 and sizeHS > 1000








Can use same type for multiple relations:
  CREATE TABLE OldStudent OF 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 InfoType AS (GPA float, sizeHS integer)

  CREATE TYPE StudentType AS
     (ID integer, name char(30), address AddressType, info InfoType)

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








Note: no actual relations with types AddressType or InfoType

Queries with Nesting

Just more dots

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








Distinct Types

  CREATE TYPE SatScore AS integer
  CREATE TYPE NumStudents as integer
  CREATE TABLE Student (name char(30), sat SatScore, sizeHS NumStudents)
Now can't inadvertently compare student SAT scores and a High School sizes.

Methods

Examples:
  CREATE TYPE InfoType AS (GPA float, sizeHS integer)
  METHOD adjustGPA() 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 adjusted GPA for all students





Example: Send "sorry" mailing to all students with adjusted GPA < 3.0. Return ID and confirmation character as result of query.










References

Example: To make students referenceable:
  CREATE TABLE Student OF 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))
When SCOPE is used, can declare referential-integrity-like constraint enforcement, e.g.:
  REFERENCES ARE CHECKED ON DELETE SET NULL

Queries with References

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 UDTs
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 UDTs also

Example: Students ordered by adjusted GPA







Example: Number of students at each address







Capabilities vary from product to product. No product implements the standard ("CREATE ORDERING"). Main concepts:
  1. Strict object equality:

  2. Structural equality:

  3. Method-defined equality:

  4. Method-defined comparison:

  5. Method-defined mapping: