Programming Portion OODB

1. SQL Extensions for Object-Relational Features

Structured Types (Composite Attributes):

Define a named composite type:

-- Type for a name
create type Name as
    (firstname varchar(20),
     lastname varchar(20))
final; -- Cannot have subtypes
 
-- Type for an address
create type Address as
    (street varchar(20),
     city varchar(20),
     zipcode varchar(9))
not final; -- Can have subtypes

Use the type in a table:

create table person (
    name Name,
    address Address,
    dateOfBirth date
);

Access components: name.lastname, address.city

select name.lastname, address.city
from person;

Alternative: Use unnamed row types:

create table person_r (
    name row (firstname varchar(20), lastname varchar(20)),
    address row (street varchar(20), city varchar(20), zipcode varchar(9)),
    dateOfBirth date
);

Create a table where rows are the type:

create type PersonType as ( ... ) not final;
create table person of PersonType;

Methods on Structured Types:

Declare method signature in type definition:

create type PersonType as (
    ...
    method ageOnDate(onDate date) returns interval year
);

Define method body separately:

create instance method ageOnDate (onDate date)
returns interval year
for PersonType
begin
   return onDate - self.dateOfBirth;
end;

Invoke method:

select name.lastname, ageOnDate(current_date)
from person; -- Assumes person table is of PersonType

Constructors:

Define a constructor function (same name as type):

create function Name (firstname varchar(20), lastname varchar(20))
returns Name
begin
   set self.firstname = firstname;
   set self.lastname = lastname;
   return self; -- Implicitly returns self
end;

Use constructor in INSERT:

insert into Person
values (
    new Name('John', 'Smith'),
    new Address('20 Main St', 'New York', '11001'),
    date '1960-8-22'
);

Type Inheritance:

Define subtypes using UNDER:

create type Person (...); -- Supertype
 
create type Student
under Person -- Inherits Person's attributes
    (degree varchar(20), department varchar(20));
 
create type Teacher
under Person
    (salary integer, department varchar(20));

Multiple inheritance (conceptual, not standard SQL): create type TA under Student, Teacher;
Renaming conflicting attributes (conceptual): under Student with (department as student_dept), ...

Table Inheritance:
- Create table from supertype: create table people of Person;
- Create subtables using UNDER:
```
create table students of Student under people;
create table teachers of Teacher under people;
```
- Querying people includes students and teachers.
- Query only supertype instances: select * from only people;
- DELETE FROM people affects subtables; DELETE FROM only people does not.

Collection Types (Arrays and Multisets):

Define in types/tables:

create type Book as (
    ...
    author_array varchar(20) array [10], -- Ordered array, max 10 elements
    keyword_set varchar(20) multiset     -- Unordered multiset
);
create table books of Book;

Create collection values:

array['Smith', 'Jones']
multiset['parsing', 'analysis']

Insert collection values:

insert into books values (
    'Compilers',
    array['Smith', 'Jones'],
    new Publisher('McGraw-Hill', 'New York'),
    multiset['parsing', 'analysis']
);

Access array elements: author_array[1]

Querying Collections:

Use unnest to treat collection elements like rows:

-- Check for membership in a multiset
where 'database' in (unnest(keyword_set));
 
-- Flatten array into rows (title, author)
select B.title, A.author
from books as B, unnest(B.author_array) as A(author);
 
-- Flatten array with position (title, author, position)
select title, A.author, A.position
from books as B, unnest(B.author_array) with ordinality as A(author, position);

Nesting (re-creating collections from flat data):

Using collect aggregate:

select title, author, Publisher(pub_name, pub_branch) as publisher,
       collect(keyword) as keyword_set -- Creates a multiset
from flat_books
group by title, author, publisher;

Using subqueries with array() or multiset():

select title,
       array( select author from authors A where A.title = B.title order by A.position ) as author_array,
       multiset( select keyword from keywords K where K.title = B.title ) as keyword_set
from books4 as B;

Reference Types (Object Identity):

Define ref attribute, scoped to a table:

create type Department (
    name varchar(20),
    head ref(Person) scope people -- 'head' refers to a tuple in 'people' table
);
create table departments of Department;

Define table’s self-referential ID (Object ID - OID):

-- System generated OID
create table people of Person
ref is person_id system generated;
 
-- User generated OID (must provide value on insert)
create type Person (...) ref using varchar(20);
create table people of Person
ref is person_id user generated;
 
-- OID derived from primary key (e.g., 'name')
create type Person (name varchar(20) primary key, ...) ref from(name);
create table people of Person
ref is person_id derived;

Dereference using -> or deref():

-- Path expression using ->
select head->name, head->address
from departments;
 
-- Using deref() function
select deref(head).name
from departments;

Initialize references (if system generated):

-- 1. Insert with null ref
insert into departments values ('CS', null);
-- 2. Update with OID selected from referenced table
update departments
set head = (select p.person_id from people as p where p.name = 'John')
where name = 'CS';

2. Persistent Programming Languages

General Concepts:
- Integrate database persistence directly into the language (e.g., C++, Java).
- Shared type system between language and DB.
- Transparent data fetching/storing (programmer doesn’t write explicit load/save).
- Persistence approaches: By class, by creation (new), by marking, by reachability (common in Java/JDO).

Quartz 4

Explorer

Programming Portion OODB

Graph View

Backlinks