Carnegie Mellon Univ.Dept. of Computer Science15-415 - Database Applications C. Faloutsos OO and OR DBMSs
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 15-415 - C. Faloutsos
Detailed outline • OO DBMSs • OR DBMS • complex data types • inheritance • UDFs • ORACLE-specific extensions • Conclusions 15-415 - C. Faloutsos
Why more than RDBMSs? • RDBMS: tuples, of numbers + strings • What apps need only those? 15-415 - C. Faloutsos
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? 15-415 - C. Faloutsos
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 15-415 - C. Faloutsos
Their specs? • complex objects (sets/vectors) • inheritance • new data types (image, video, ...) and user defined functions (UDFs) 15-415 - C. Faloutsos
Two solutions: • Object Oriented DBMSs • Object Relational DBMSs 15-415 - C. Faloutsos
OO DBMS • roughly, ‘C++’ with persistence • commercial systems: • O2; ObjectStore; Objectivity • ODMG: defined standards • BUT: OODBMS have small market share • Hence: OR-DBMSs 15-415 - C. Faloutsos
OR DBMSs traditional DBMS with attempts to provide • user defined data types • support for large / complex objects • inheritance 15-415 - C. Faloutsos
SQL-3 proposed extensions • complex types (sets, lists, multisets) • inheritance (IS-A hierarchies) • User Defined Functions (UDFs) 15-415 - C. Faloutsos
Complex types sample syntax eg, create type MyDate ( day decimal(2), month char(3), year decimal (4) ); 15-415 - C. Faloutsos
Complex types sample syntax eg, Row Types: create row type Doc ( callnum varchar2(10), title char(20), authors list(varchar2(20)) ); create table document oftype Doc; 15-415 - C. Faloutsos
Complex types sample syntax DML - insertions: insertinto document values (‘QA123.45’, ‘DB systems’, set(‘Smith’, ‘Johnson’) ); 15-415 - C. Faloutsos
Inheritance sample syntax single inheritance: create type Person ( ssn varchar2(10), name char(20)); create type Student (major varchar2(5)) under Person; 15-415 - C. Faloutsos
Inheritance sample syntax multiple inheritance: create type Teacher ( salary integer) under Person; create type TA under Student, Teacher; 15-415 - C. Faloutsos
Inheritance sample syntax multiple inheritance: constraints: one TA record corresponds to exactly one ‘Teacher’ and ‘Student’ record insertions/deletions/updates: appropriately propagated. 15-415 - C. Faloutsos
Object Ids and references can define ‘object ids’ for each object, and use them, effectively as pointers. 15-415 - C. Faloutsos
Query language extensions sample syntax (recall:) create row type Doc ( callnum varchar2(10), title char(20), authors list(varchar2(20)) ); create table document oftype Doc; find titles, (co-)authored by ‘Smith’ 15-415 - C. Faloutsos
Query language extensions sample syntax select title from document where ‘Smith’ in authors; 15-415 - C. Faloutsos
SQL-3 proposed extensions - overview • complex types (sets, lists, multisets) • inheritance (IS-A hierarchies) • User Defined Functions (UDFs) 15-415 - C. Faloutsos
UDFs sample syntax createfunctionauthor-count (adoc document) returns integer as selectcount (authors) from adoc; select title from document d whereauthor-count(d) > 1 15-415 - C. Faloutsos
UDFs UDFs: stay within the DBMS, for everybody to use! 15-415 - C. Faloutsos
Detailed outline • OO DBMSs • OR DBMS • complex data types • inheritance • UDFs • ORACLE-specific extensions • Conclusions 15-415 - C. Faloutsos
ORACLE-specific • Large objects • PL/SQL and UDFs 15-415 - C. Faloutsos
ORACLE-specific • Large objects, eg., video, images, 3d-MRI scans • new data types: 15-415 - C. Faloutsos
ORACLE-specific • Large objects, eg., video, images, 3d-MRI 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) 15-415 - C. Faloutsos
ORACLE-specific • stored procedures • PL/SQL: a ‘C’-like language • too large to describe here (see book on reserve) • example of a stored procedure: 15-415 - C. Faloutsos
ORACLE-specific SQL> create or replace procedure del-st-rec (s-id number) as begin deletefrom student where s-id = ssn; end del-st-rec; SQL> execute del-st-rec ( 123 ); 15-415 - C. Faloutsos
Detailed outline • OO DBMSs • OR DBMS • complex data types • inheritance • UDFs • ORACLE-specific extensions • Conclusions 15-415 - C. Faloutsos
Conclusions • OO and OR DBMS strive for • complex data types • inheritance • UDFs • OR DBMSs: overwhelming market share (why?) 15-415 - C. Faloutsos
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 15-415 - C. Faloutsos
