CS145 Lecture Notes -- Object-Relational SQL
- Most major DBMS vendors 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
- Equality and 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, sizeHS 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 and sizeHS > 1000
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 InfoType AS (GPA float, sizeHS integer)
CREATE TYPE StudentType AS
(ID integer, name char(30), address AddressType, info InfoType)
CREATE TABLE Student OF TYPE 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
Methods
- Signature declared as part of type
- Method body implemented using, e.g., PL/SQL, PSM, or Java
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
- 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 ordered by adjusted GPA
Example: Number of students at each address
Capabilities vary from product to product. No product implements the
standard. Main concepts:
- Strict object equality:
-
Two UDT tuples are equal if and only if they are exactly the same tuple.
- Permits operations =, <>, DISTINCT, GROUP BY
- Structural equality:
- Two UDT tuples are equal if and only if all components are equal (recursively).
- Base case is equality of atomic values
- Permits operations =, <>, DISTINCT, GROUP BY
- Method-defined equality:
- Defined method takes two UDT tuples and returns = or <>
- Permits operations =, <>, DISTINCT, GROUP BY
- Method-defined comparison:
- Defined method takes two UDT tuples and returns =, <, or >
- Permits operations =, <, > <=, >=, <>, ORDER BY, DISTINCT, GROUP BY
- Method-defined mapping:
- Defined method takes UDT tuple and returns real value
- Compare two tuples by comparing their values
- Permits operations =, <, > <=, >=, <>, ORDER BY, DISTINCT, GROUP BY