Transcript OO & OR DBMSs - Temple University

Temple University – CIS Dept.
CIS331– Principles of
Database Systems
V. Megalooikonomou
Object-Oriented and Object-Relational
DBMSs
(based on notes by Silberchatz,Korth, and Sudarshan and notes by C.
Faloutsos at CMU)
General Overview - rel. model






Relational model - SQL
Functional Dependencies & Normalization
Physical Design; Indexing
Query optimization
Transaction processing
Advanced topics


Distributed Databases
OO- and OR-DBMSs
Detailed outline


OO DBMSs
OR DBMS





complex data types
inheritance
UDFs
ORACLE-specific extensions
Conclusions
Why more than RDBMSs?


RDBMS: tuples, of numbers + strings
What apps need only those?
Why more than RDBMSs?


RDBMS: tuples, of numbers + strings
What apps need only those?





Banks
Airlines
Retailer stores
...
Q: Other apps, with more req’s?
Why more than RDBMS’s


Q: Other apps, with more req’s?
A:





text
multimedia; financial apps/forecasting
Geographic Inf. Sys.
CAD/CAM
Network management
Their specs?



complex objects (sets/vectors)
inheritance
new data types (image, video, ...) and
user defined functions (UDFs)
Two solutions:


Object Oriented DBMSs
Object Relational DBMSs
OO DBMS


roughly, ‘C++’ with persistence
commercial systems:




O2; ObjectStore; 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
 enriched data types
 user defined data types
 support for large / complex objects
 inheritance
SQL-3 proposed extensions



complex types (sets, lists, multisets)
inheritance (IS-A hierarchies)
User Defined Functions (UDFs)
Complex types
e.g.,
create type MyDate (
day decimal(2),
month char(3),
year decimal (4)
);
Complex types
e.g., Row Types:
create row type Doc (
callnum varchar2(10),
title char(20),
authors list (varchar2(20))
);
create table document of type Doc;
Complex types
DML - insertions:
insert into document values
(‘QA123.45’, ‘DB systems’, set(‘Smith’,
‘Johnson’) );
Inheritance
single inheritance:
create type Person (
ssn varchar2(10),
name char(20));
create type Student (major
varchar2(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 varchar2(10),
title char(20),
authors list (varchar2(20))
);
create table document of type
Doc;
Query language extensions
select title
from document
where ‘Smith’ in authors;
SQL-3 proposed extensions overview



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
UDFs: stay within the DBMS, for everybody
to use!
Detailed outline


OO DBMSs
OR DBMS





complex data types
inheritance
UDFs
ORACLE-specific extensions
Conclusions
ORACLE-specific


Large objects
PL/SQL and UDFs
ORACLE-specific


Large objects, e.g., video, images, 3dMRI scans
new data types:
ORACLE-specific


Large objects, e.g., video, images, 3dMRI scans
new data types: LOB (=Large OBject)




BLOB: (up to 4Gb; binary: jpeg, mpeg, ...)
CLOB: (up to 2Gb; character: english text)
NCLOB:(..............; multi-byte characters)
(LONG: similar, for backwards
compatibility)
ORACLE-specific


stored procedures
PL/SQL: a ‘C’-like language


too large to describe here (see book on
reserve)
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
Informix Dynamic Server (Universal
Data Option)
IBM DB2 Universal Database –
Informix Product family
IllustraInformixIBM’s
Informix Dynamic Server

Datablades technology - extensions for
specific data domains







Image
Text
Geodetic
Spatial
Time series
Video
Web
Informix - Sample SQL queries








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.SliceNo, slice(TS.SliceNo,overlay.image)::GIF as LesionDensity
from TalairachSlices TS, temp_image_1 overlay order by SliceNo ;
Detailed outline


OO DBMSs
OR DBMS






complex data types
inheritance
UDFs
ORACLE-specific extensions
Informix
Conclusions
Conclusions

OO and OR DBMS strive for




complex data types
inheritance
UDFs
OR DBMSs: overwhelming market share
(why?)
Conclusions

OR DBMSs: overwhelming market share
(why?)



SQL is more standardized than OO query
languages
legacy data are in SQL
more SQL programmers are available