Temple University CIS Dept CIS 616 Principles of

Temple University – CIS Dept. CIS 616– Principles of Data Management V. Megalooikonomou Object-Oriented and Object-Relational DBMSs (based on notes by Silberchatz, Korth, and Sudarshan and notes by C. Faloutsos at CMU)

Detailed outline n n OO DBMSs OR DBMS n n n complex data types inheritance UDFs ORACLE-specific extensions Conclusions

Why more than RDBMSs? n n RDBMS: tuples, of numbers + strings What apps need only those?

Why more than RDBMSs? n n RDBMS: tuples, of numbers + strings What apps need only those? n n n Banks Airlines Retailer stores. . . Q: Other apps, with more req’s?

Why more than RDBMS’s n n Q: Other apps, with more req’s? A: n n n text multimedia; financial apps/forecasting Geographic Inf. Sys. CAD/CAM Network management

Their specs? n n n complex objects (sets/vectors) inheritance new data types (image, video, . . . ) and user defined functions (UDFs)

Two solutions: n n Object Oriented DBMSs Object Relational DBMSs

OO DBMS n n roughly, ‘C++’ with persistence commercial systems: n n O 2; Object. Store; Objectivity Object Database Management Group (ODMG): defined standards BUT: OODBMS have small market share Hence: OR-DBMSs

OR DBMSs traditional DBMS with attempts to provide n enriched data types n user defined data types n support for large / complex objects n inheritance

SQL-3 proposed extensions n n n complex types (sets, lists, multisets) inheritance (IS-A hierarchies) User Defined Functions (UDFs)

Complex types e. g. , create type My. Date ( day decimal(2), month char(3), year decimal (4) );

Complex types e. g. , Row Types: create row type Doc ( callnum varchar 2(10), title char(20), authors list (varchar 2(20)) ); create table document of type Doc;

Complex types DML - insertions: insert into document values (‘QA 123. 45’, ‘DB systems’, set(‘Smith’, ‘Johnson’) );

Inheritance single inheritance: create type Person ( ssn varchar 2(10), name char(20)); create type Student (major varchar 2(5)) under Person;

Inheritance multiple inheritance: create type Teacher ( salary integer) under Person; create type TA under Student, Teacher;

Inheritance multiple inheritance: constraints: one TA record corresponds to exactly one ‘Teacher’ and ‘Student’ record insertions/deletions/updates: appropriately propagated.

Object Ids and references can define ‘object ids’ for each object, and use them, effectively as pointers.

Query language extensions find titles, (co-)authored by ‘Smith’ (recall: ) create row type Doc ( callnum varchar 2(10), title char(20), authors list (varchar 2(20)) ); create table document of type Doc;

Query language extensions select title from document where ‘Smith’ in authors;

SQL-3 proposed extensions overview n n n complex types (sets, lists, multisets) inheritance (IS-A hierarchies) User Defined Functions (UDFs)

UDFs create function author-count (adoc document) returns integer as select count (authors) from adoc; select title from document d where author-count(d) > 1

UDFs: stay within the DBMS, for everybody to use!

Detailed outline n n OO DBMSs OR DBMS n n n complex data types inheritance UDFs ORACLE-specific extensions Conclusions

ORACLE-specific n n Large objects PL/SQL and UDFs

ORACLE-specific n n Large objects, e. g. , video, images, 3 d. MRI scans new data types:

ORACLE-specific n n Large objects, e. g. , video, images, 3 d. MRI scans new data types: LOB (=Large OBject) n n BLOB: (up to 4 Gb; binary: jpeg, mpeg, . . . ) CLOB: (up to 2 Gb; character: english text) NCLOB: (. . . ; multi-byte characters) (LONG: similar, for backwards compatibility)

ORACLE-specific n n stored procedures PL/SQL: a ‘C’-like language n n too large to describe here… example of a stored procedure:

ORACLE-specific SQL> create or replace procedure del-st-rec (s-id number) as begin delete from student where s-id = ssn; end del-st-rec; SQL> execute del-st-rec ( 123 );

Illustra Informix IBM’s Informix Dynamic Server Illustra n Informix Dynamic Server (Universal Data Option) n IBM DB 2 Universal Database – Informix Product family n

Illustra Informix IBM’s Informix Dynamic Server n Datablades technology - extensions for specific data domains n n n n Image Text Geodetic Spatial Time series Video Web

Informix - Sample SQL queries n n n n COMPUTE VOLUME OF A GIVEN STRUCTURE return volume((select unique image from structures where side='Left' and atlas='Brodmann' and name='17')) ; DISPLAY GIF OF ALL LESIONS SUMMED UP insert into temp_image_1 values(permanent(map_image(sum_images(( select image from patient_images where image. description='All Lesions')), 'redgreenscale'))) ; select TS. Slice. No, slice(TS. Slice. No, overlay. image): : GIF as Lesion. Density from Talairach. Slices TS, temp_image_1 overlay order by Slice. No ;

Detailed outline n n OO DBMSs OR DBMS n n n complex data types inheritance UDFs ORACLE-specific extensions Informix Conclusions

Conclusions n OO and OR DBMS strive for n n complex data types inheritance UDFs OR DBMSs: overwhelming market share (why? )

Conclusions n OR DBMSs: overwhelming market share (why? ) n n n SQL is more standardized than OO query languages legacy data are in SQL more SQL programmers are available