1 / 35

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

This article provides a general overview of object-oriented and object-relational DBMSs, including their complex data types, inheritance, user-defined functions, and ORACLE-specific extensions.

Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related