V' Megalooikonomou

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 reqs?

6
Why more than RDBMSs

• Q Other apps, with more reqs?
• 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 of type Doc

14
Complex types
sample syntax

• DML - insertions
• insert into 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
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

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

• create function author-count (adoc document)
• returns integer as
• select count (authors)
• from adoc
• select title from document d
• where author-count(d) gt 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

• SQLgt create or replace procedure del-st-rec
• (s-id number) as
• begin
• delete from student
• where s-id ssn
• end del-st-rec
• SQLgt execute del-st-rec ( 123 )

30
Illustra?Informix?IBMs Informix Dynamic Server

• Illustra
• Informix Dynamic Server (Universal Data Option)
• IBM DB2 Universal Database Informix Product
  family

31
Illustra?Informix?IBMs 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_i
  mage(sum_images((
• select image from patient_images where
  image.description'All Lesions')),
  'redgreenscale')))
• select TS.SliceNo, slice(TS.SliceNo,overlay.ima
  ge)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