Temple University – CIS Dept. CIS331– Principles of Database Systems

1. 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)

2. 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

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

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

5. 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?

6. 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

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

8. Two solutions: • Object Oriented DBMSs • Object Relational DBMSs

9. 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

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

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

12. Complex types sample syntax e.g., create type MyDate ( day decimal(2), month char(3), year decimal (4) );

13. Complex types sample syntax e.g., Row Types: create row type Doc ( callnum varchar2(10), title char(20), authors list(varchar2(20)) ); create table document oftype Doc;

14. Complex types sample syntax DML - insertions: insertinto document values (‘QA123.45’, ‘DB systems’, set(‘Smith’, ‘Johnson’) );

15. Inheritance sample syntax single inheritance: create type Person ( ssn varchar2(10), name char(20)); create type Student (major varchar2(5)) under Person;

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

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

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

19. 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’

20. Query language extensions sample syntax select title from document where ‘Smith’ in authors;

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

22. UDFs sample syntax createfunctionauthor-count (adoc document) returns integer as selectcount (authors) from adoc; select title from document d whereauthor-count(d) > 1

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

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

25. ORACLE-specific • Large objects • PL/SQL and UDFs

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

27. ORACLE-specific • Large objects, e.g., 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)

28. ORACLE-specific • stored procedures • PL/SQL: a ‘C’-like language • too large to describe here (see book on reserve) • example of a stored procedure:

29. 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 );

30. IllustraInformixIBM’s Informix Dynamic Server • Illustra • Informix Dynamic Server (Universal Data Option) • IBM DB2 Universal Database – Informix Product family

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

32. 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 ;

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

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

35. 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