OO What Are They Doing to Relational Databases

O-O, What Are They Doing to Relational Databases? (The Evolution of DB 2 Universal Database) Michael J. Carey IBM Almaden January 1999

Plan for Today's Presentation l. The relational DBMS revolution l. The object-relational DBMS evolution l. O-R features in DB 2 Universal Database V 5. 2 l. Some O-R implementation tradeoffs (V 5. 2) l. What lies ahead for DB 2 UDB & O-R databases? l. Questions (and possibly answers) Please ask questions throughout. . . !

The Relational DBMS Revolution l. The pre-relational era (1970's) n Graph-based data models l n Hierarchical (IMS), network (Codasyl) Low-level, navigational interfaces l Labor-intensive and error-prone l. The relational era (1980's) n Simple, abstract data model Database = set of relations ("tables") l 3 schema levels: views, base tables, physical schema l Algebra of set-oriented operations l n High-level, declarative interfaces SQL, QBE, et al l Embedded languages, 4 GLs l

The Relational Model (in one slide) l. Employees and departments Department Employee ? dno 10 20 eno 1 7 22 name Toy Shoe name Lou Laura Mike salary 10000000 150000 80000 select E. name, E. salary, D. no from Employee E, Department D where E. salary < 100000 and D. name = 'Shoe' and E. dept = D. dno; dept 10 20 20

Relational Databases: A Success Story l. The relational model has been a big success Simplicity has made research tractable n Data independence yields productivity gains n Both academia and industry have benefitted n l. Relational DBMS "goodies" include Efficient query optimization and execution n Well-defined transaction semantics and support n Excellent multi-user performance and robustness n Views for data independence, authorization n Constraints, triggers, and stored procedures for (shared) business rule capture/enforcement n "The" success story for parallel computing n

We've Achieved Nirvana. . . Right? l. The world is becoming increasingly complex New data types are appearing (e. g. , multimedia) n Real-world data doesn't fit neatly into tables n Entities and relationships (vs. tables) l Variance among entities (vs. homogeneity) l Set-valued attributes (vs. normalization) l n Advanced applications bring complex data l E. g. , CAD/CAM data management, web data management, geographic information management, medical data management, (your favorite application goes here) l. So maybe objects are the answer. . . ? n Yes, if we can keep all the relational "goodies"!

The Object-Relational DBMS Evolution l. O-R extension #1: Abstract data types (ADTs) n New column types and functions/methods E. g. , text, image, audio, video, time series, point, line, OLE. . . l For modeling new kinds of facts about enterprise entities l n Infrastructure for extenders/datablades/cartridges l. O-R extension #2: Row types n Types and functions/methods for rows of tables Desirable features include references, inheritance, methods, late binding, and collection-valued attributes l For modeling enterprise entities with relationships & behavior l n Infrastructure for DBMS-native object management l. Recent SQL 3 merger: Structured types n Can use for types of columns and/or tables

"Not Your Father's Employee Type" l. Beyond name, rank, and serial number n New attribute types l n Location (2 -d point), job description (text), photo (image), . . . Associated functions l Distance(point, point), contains(text, string), . . . l. Beyond your basic employee record n Employees come in different flavors l n Employees have many known relationships l n Emp, RSM, Programmer, Manager, Temp, . . . Manager, department, projects, . . . Employees have behavior l Age(Emp), qualified(Emp, Job), hire(Emp), . . . An Employee is a simple "business object"

The OSF Project at IBM Almaden l. OSF stands for "Object Strike Force" Semi-autonomous group "outside" UDB development n Focus: object-relational extensions for DB 2 UDB n Both near-term and longer-term interests n Collaborate with our Toronto and Santa Teresa labs n l. Significant activities to date Prototyped "row type" support for DB 2 UDB n Delivered in DB 2 UDB Version 5. 2 (9/98) n Significantly revised SQL 3 draft standard n Working on next step plus future technologies n

DB 2 Universal Database, Version 5 l. DB 2 for Common Servers (Version 2) User-defined column types (UDTs/distinct types) n User-defined functions (UDFs) n Binary/character large objects (BLOBs/CLOBs) n l. Distinct types: new data types for columns Ex: create distinct type US_Dollar as Real with comparisons; n US_Dollar is an available UDT with functions =, <>, <, <=, >, >=, US_Dollar(Real), Real(US_Dollar) n l. User-defined functions: associated operations n create function CA_Tax (US_Dollar) returns US_Dollar external name 'money!US_Dollar' language C;

DB 2 Universal Database, Version 5 (cont. ) l. Lots of other interesting features as well, e. g. : Constraints and triggers n Recursive queries n OLAP support (cube and rollup) n Extenders (based on UDTs/UDFs) n l. Wide range of hardware/software platforms PCs: Windows 95, NT, OS/2, SCO n Unix workstations: AIX, Solaris, HP/UX n Parallel platforms: SMPs, MPPs (e. g. , SP 2) n l. Descended from Almaden's Starburst system n Extensible query compiler (with rule-based query rewrite and query optimizer components)

New O-R Features in DB 2 UDB V 5. 2 l. Structured types and references Named types with attributes, O-O subtyping model n Ref(T) for directly modelling relationships n l. Typed tables and table hierarchies Oid (user-provided) plus a column per attribute of T n Subtables for querying and managing subtype instances n l. Query language extensions Substitutability for queries/updates (data independence ++) n Path expressions for querying relationships easily n Functions/predicates for runtime type inquiries n l. Object views (via a novel approach) Virtual table hierarchies for flexible access control n Also facilitates O-O views of legacy tables n

A Simple Example l. Employee and department tables in the (late) 90's person dept mgr emp dept exec student

Structured Types and References l. Create structured types (and references) create type Person_t as ( name Varchar(40), birthyear Integer ); create type Emp_t under Person_t as ( salary Integer, dept Ref(Dept_t) ); create type Exec_t under Emp_t as ( bonus Integer ); create type Student_t under Person_t as ( major Varchar(20) );

Structured Types and References (cont. ) l. Create structured types (cont). create type Dept_t as ( name Varchar(20), budget Integer, headcount Integer, mgr Ref(Emp_t) ); l. Okay, so I lied (a little) on the last slide. . . alter type Emp_t add attribute dept Ref(Dept_t);

Typed Tables and Table Hierarchies l. Now create typed tables (and subtables) create table person of Person_t (ref is oid user generated); create table emp of Emp_t under person (dept with options scope dept); create table exec of Exec_t under emp; create table student of Student_t under person; create table dept of Dept_t (ref is oid user generated, mgr with options scope emp);

SQL Query Extensions (by example) l. Substitutability select E. * from emp E where E. birthyear > 1970 and E. salary > 50000; l. Data modification (insert; update/delete) insert into emp values (Emp_t('e 100'), 'John Smith', 1968, 65000, (select oid from dept where name = 'Database')); update person set birthyear = birthyear + 1 where name = 'John Smith'; l. Path expressions select E. name, E. dept->name from emp E where E. dept->mgr->name = 'Lou Gerstner';

Querying Table Hierarchies: An Example Dept Person Emp name birthyear P 1 Harold 1970 P 2 Carol 1958 oid name birthyear P 3 Hamid 1956 P 4 Lou 1940 select name, dept->name from Emp where birthyear < 1960 oid D 1 dept _ name . . . Databases . . . select * from Person where name like 'H%'

SQL Query Extensions (cont. ) l. Support for type-dependent queries select * from only (emp) E where dept->budget > 10000000; select name from person P where deref(oid) is of type (only Emp_t, Student_t); select type_name(deref(E. oid)), E. * from outer (emp) E where e. oid = Emp_t('e 13');

Other Data Definition Features lref is for object id column n Unique, user-generated (on insert) lscope clause for reference columns n Provides critical information to the query optimizer lnot null constraints Definable at any level of a table hierarchy n Enforced for indicated table and its subtables n lunique constraints n Root level (and columns) only lcreate index for physical schema Unique or non-unique index on root table n Non-unique index on subtable n

Other Data Definition Features (cont. ) l. Authorization model for table hierarchies grant and revoke on table or subtables n Substitutability: implicit subtable authorization on columns inherited from an authorized supertable n Ex #1: select privilege on person table l Ex #2: update privilege on salary column of emp table l n Some operations require authorization everywhere deref function l is of type predicate and type_xxx functions l n SQL 3 also supports granting of table/subtable privileges with hierarchy option

Object Views in DB 2 UDB l. Typed views and view hierarchies vperson vdept mgr vemp vstudent dept l. Requirements: virtual table hierarchies Typed rows with (derived) object ids n Views may be quite different from base data n Support for interconnected "view schemas" n

Types For Object Views l. Create types for use in views create type VPerson_t as ( name Varchar(40) ); create type VEmp_t under VPerson_t as ( dept Ref(VDept_t) ); create type VStudent_t under VPerson_t as ( kind Varchar(8) ); create type VDept_t as ( name Varchar(20), mgr Ref(VEmp_t) );

Typed View Hierarchies l. Now create typed views (and subviews) create view vperson of VPerson_t (ref is oid user generated) as select VPerson_t(Varchar(oid)), name from only (person); create view vemp of VEmp_t under vperson (dept with options scope vdept) as select VEmp_t(Varchar(oid)), name, VDept_t(Varchar(dept)) from emp where salary > 0; create view vstudent of VStudent_t under vperson as select VStudent_t(Varchar(oid)), name, case when major like '%Engineer%' then 'Geek' else 'non-Geek' end from student; create view vdept of VDept_t. . . ;

O-R Implementation Issues/Tradeoffs l. Some guiding principles for DB 2 UDB V 5. 2 Performance must equal/exceed relational equivalents n Design amenable to future plans w. r. t. type evolution n Structured types must be supported in columns (someday) n Localize initial changes to query compiler where possible n l Want "free" indexing, rewrites, optimization, parallelization, . . . l. Influenced by discussions with a CAD/CAM vendor Information on existing approach and installations n Requirements for efficiency of new products n l. Let's look briefly at two areas Table hierarchy representation n References and path query processing n

Implementing Table Hierarchies l. Implementation table approach n One physical table per table hierarchy with: Type tag column (to distinguish subtable rows) l Object id column l Columns for all columns of the root table and its subtables l l. Vertical partitioning approach n One physical root table with: Type tag column l Object id column l Columns for each root table column l n N physical delta tables (one per subtable) with: Object id column l Columns for each column introduced by this subtable l

Implementing Table Hierarchies (cont. ) l. Horizontal partitioning approach n N separate physical tables with: Object id column l Columns for every subtable column (inherited or not) l l. So what did we do for UDB V 5. 2. . . ? n Vertical partitioning approach rejected quickly Too many joins to materialize subtable rows l Multi-column constraints and indices problematic l n Horizontal partitioning approach rejected eventually Uniqueness issue for user-generated object ids l Query complexity for multi-hierarchy join queries l è n Ex: select p. name, q. name from Person p, Person q where. . . Implementation table approach taken for V 5. 2 Appeared to give us the most "free" functionality l Adopted despite row size (null columns) downside l

References and Path Expressions l. Reference values in tables should have a scope "Other end" info for query rewrite and join optimization n Ditto for authorization checking (static vs. dynamic) n Schema makes overly wide references unnecessary n Uniqueness is hierarchy-relative, enforced with an index n l. V 5. 2 self-references (object ids) are user-generated CAD/CAM vendor had "legacy references" in files n Different users have different id generation schemes n Loading cyclic data (e. g. , emp<-> dept) is messy and slow n Ditto for creating objects from an object cache n

References and Path Expressions (cont. ) l. Path expressions are logically equivalent to subqueries select E. name, E. dept->mgr->name from emp E where E. dept->headcount > 10; l. Actual approach: shared subquery generation (QGM) Compute common paths (prefixes) once to save work n Not every SQL context accepts an actual subquery n Also need to handle non-serializable locking levels n l. Efficiency obtained through query rewrite, e. g. : Subquery to outer-join transformation n Outer-join to join transformation where possible n

Where We Are Today in UDB l. V 5. 2 of UDB contains new O-R features Structured types with inheritance n Object tables and table hierarchies n References and path expressions n Object views and view hierarchies n l. Moreover, so does the SQL 3 standard Includes object views and user-defined references n IBM, Oracle, Informix heading in same general direction n l. Work continuing on O-R extensions n Let's have a brief look. . .

Additional Object Table Support l. Business rule mechanisms for typed tables Check constraints on tables/subtables (w/inheritance) n Referential integrity constraints to and from tables/subtables n Triggers on tables/subtables n l. Object modeling and management extensions User-defined reference types (ref using) n More flexible object view definitions n Type and instance (i. e. , row) evolution n l. Structured types for attributes/columns Work in progress at IBM Santa Teresa Lab n Functions/methods just around the corner as well n

Other Exploratory O-R Work l. Efficient support for collection types Multivalued attributes (e. g. , Project. team) n Flavors: set, multiset, array, list, . . . n Need to integrate into SQL, support querying well n Some experience from a first prototype n l. Other activities (and open problems) Java mappings & bindings for O-R data n XML & data-centric web sites ("d-commerce") n Business object servers (caching/consistency) n Heterogeneous data & O-R database systems n User-defined and/or external indexing n Optimizer "hooks" for new data types n Etc. ! n

Partial List of UDB O-R Contributors l. Almaden Research Center n Mike Carey, Don Chamberlin, Srinivasa Narayanan, Bennet Vance; C. M. Park; Guido Moerkotte l. Santa Teresa Lab Nelson Mattos n Gene Fuh, Michelle Jou, Brian Tran n l. Toronto Lab Doug Doole, Serge Rielau, Rick Swagerman n Leo Lao, Walid Rjaibi, Calisto Zuzarte n Cheryl Greene, various other consultants/hecklers n l. And as for future versions of UDB n Your name could appear here! (MS/Ph. D)

The End

What About Object-Oriented DBMSs? l. OOPL + DBMS = OO-DBMS Commonly based on C++ or Smalltalk n Persistence, collections, queries, versions, . . . n l. Lots of interesting and useful research results O-O data models and query languages n O-O query processing, system architecture, performance n Various products (O 2, Objectstore, Versant, Objectivity, . . . ) n l. No widespread commercial acceptance Many differences across systems (despite ODMG-93) n Never really caught up to RDBMS techology n Schema compilation, evolution painful l Missing many of the relational "goodies" l Single-language focus, lack of (relational) tools l

Stonebraker Fellow Criteria (found on web) l. Industrial database researcher l. Ph. D from UC Berkeley l. Must agree with the following motto: Databases are the answer. . . ! n What was the question again. . . ? n l. At least 6' tall l. Had a Ph. D thesis advisor with first name Mike l. Produced a Ph. D student with first name Mike