Carnegie Mellon Univ. Dept. of Computer Science 15-415 - Database Applications

1
Carnegie Mellon Univ.Dept. of Computer
Science15-415 - Database Applications

• C. Faloutsos
• OO and OR DBMSs

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
• ODMG defined standards
• BUT OODBMS have small market share
• Hence OR-DBMSs

10
OR DBMSs

• traditional DBMS with attempts to provide
• 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

• eg,
• create type MyDate (
• day decimal(2),
• month char(3),
• year decimal (4)
• )

13
Complex types
sample syntax

• eg, 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, eg., video, images, 3d-MRI scans
• new data types

27
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)

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
Detailed outline

• OO DBMSs
• OR DBMS
• complex data types
• inheritance
• UDFs
• ORACLE-specific extensions
• Conclusions

31
Conclusions

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

32
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