An overview of Object-relational databases

Document Sample
An overview of Object-relational databases Powered By Docstoc
					Object-relational databases
   What is an ORDBMS ?
   Need for ORDBMSs
   Characteristics of an ORDBMS
   SQL3 – ‘object-oriented’ SQL
   State of the art: Oracle 8 and IBM DB2 UDB
   References
What is an ORDBMS ?
A combination of:

(1) OO features
      Complex objects
      Functions
      Inheritance and overloading


(2) RDBMS features
      Tables, views
      Transactions, recovery, indexing, optimization
      SQL queries
A brief look at the object-relational model
   Add ‘object oriented-ness’ to tables

   Data is still stored in tables

   Support for abstract data types, complex objects &
    user defined functions allows complex relationships
    and data to be stored and queried

   SQL3 (‘object-oriented’ SQL) is the language for data
    definition, manipulation, and query.
        Need for the O-R approach

                Relational   Object-relational
                 DBMS            DBMS

Query                        Object-Oriented
               File System

        Simple Data          Complex Data
Need for the O-R approach..
  New data types are appearing (e.g., multimedia)
  Real-world data doesn't fit neatly into tables
      Entities and relationships (vs. tables)
      Variance among entities (vs. homogeneity)
      Set-valued attributes (vs. normalization)
  Advanced applications bring complex data
      E.g., CAD/CAM data management, web data
       management, geographic information management,
       medical data management
      So maybe objects are the answer...?
  Yes, if we can keep all the relational "goodies"!
Characteristics of object-relational

   SQL support for Base Data Type Extension

   SQL support for Complex Objects

   SQL support for Inheritance

   Support for a production Rule System
Base Data Type Extension
1. Creating a new base data type:
  Create type my_type (
     internallength = 8,
      input = mytypeInput,
      output = mytypeOutput);

  Create table my_table (name varchar(20) ,
    some_data my_type);
Base Data Type Extension..
2. Creating a user defined function:
create function hourly_pay(int) returns float
  as select ($1/2000);

Function usage:
Select name from emp where
  hourly_pay(salary) >12.50;

   Functions can also be defined in C, Java and
    used in SQL queries.
Additional requirements
   Dynamic linking of user-defined
   Client or Server Activation of user-
    defined functions
   Secure User-Defined Functions
   Arbitrary-length data types
Complex objects
       Type constructors:
         Composites (records)
         Sets
         References
 create type phone_t ( area varchar(3), number
     varchar(3), description varchar(20));

 create type dept_t (dname varchar(30), floor
 int, phone phone_t, manager varchar(30),
      manager_ref ref(employee_t));

 create table dept of type dept_t;
SQL extensions needed for composite
1. User-defined functions take arguments or return a result of a
    composite type.

e.g.: select sum_digits(phone) from dept where dname = ‘HR’;

2. Functions returning a composite type can appear in the from
    clause of an SQL query.

3. The ‘cascaded dot notation’ references attributes of a composite

e.g.: select phone.number from dept where dname = ‘HR’;

   These extensions are also required for sets of composites.
   References are a substitute for the primary
    key-foreign key concept.
   The manager_ref column is a reference
    (pointer) to a record of type employee_t.
   SQL extensions needed:
       A deref function that returns the composite
       Allow functions to take arguments or return
        results of type reference.
   Inheritance only applies to types.

  Data inheritance example:
create type person_t(name varchar(20));

create type student_t (gpa float, address varchar(10)) under

create type employee_t(salary int, department varchar(20),
   address varchar(60)) under person_t;

create type student_employee_t(percent float) under
Inheritance hierarchy

      employee_t                   student_t

   Ambiguity: ‘Address’ appears in both
    student_t and employee_t types.

   Alternatives:
       Redefine the address field in one of super
       Database administrator resolves the
Table hierarchy
                              Select name from emp where
                              salary = 10000; will examine
                              ‘emp’ and ‘student_emp’
emp                 student   tables.

                              To examine ONLY the ‘emp’
                              Select name from only(emp)
                              where salary = 10000;
           Function inheritance
                 person_t                  The student_emp_t type
Overpaid                       Overpaid    inherits the overpaid
                                           function from employee_t.

  employee_t                   student_t
                                            Select from emp e
                                            where overpaid(e);
                                            is evaluated on the emp and
               student_emp_t                student_emp tables.

Production Rule system
1.   Update update rule (triggers)
2.   Query update rule
3.   Update query rule
4.   Query query rule

 Multiple rules fired by 1 event

 Chain rules cause infinite loops

 Aborting action part of a rule terminates whole
The SQL3 standard
   An ANSI, ISO standard

   Object-oriented features added to SQL2

   The foundation for ORDBMS products such as Oracle8 and DB2

   New data types
       LOB (large object)
       Composite types: ROW and ARRAY

   New predicates:
       SIMILAR
       DISTINCT
   Object oriented extensions to SQL3:
       Support   for   structured user-defined types
       Support   for   user-defined functions
       Support   for   triggers
       Support   for   references
 OSF (Object Strike Force) group at IBM
     Focus: O-R extensions in DB2 Universal Database
     version 5.2

 V5.2 of UDB contains new O-R features
    Structured types with inheritance
    Object tables and table hierarchies
    References and path expressions
    Object views and view hierarchies
New O-R features in DB2 UDB 5.2
 Structured types and references
     Named types with attributes, O-O subtyping model
     Ref(T) for directly modelling relationships

  Typed tables and table hierarchies
     Oid (user-provided) plus a column per attribute of T
     Subtables for querying and managing subtype instances

  Query language extensions
     Substitutability for queries/updates (data independence ++)
     Path expressions for querying relationships easily
     Functions/predicates for runtime type inquiries

  Object views (via a novel approach)
     Virtual table hierarchies for flexible access control
     Also facilitates O-O views of legacy tables
       Object views in DB2 UDB
                                     create type VPerson_t as (
                                        name Varchar(40));

vdept                                create type VEmp_t under
                                     VPerson_t as (dept Ref(VDept_t));
        mgr                          create type VStudent_t under
                                     VPerson_t as (kind Varchar(8));
         vemp             vstudent
                                     create type VDept_t as ( name
                                     Varchar(20), mgr Ref(VEmp_t));
Typed view hierarchies
   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 ...;
Oracle 8
   Claimed as “object-enabled” RDBMS.

   Oracle takes an evolutionary approach
    to object-orientation. In general, Oracle
    supports OO with a special layer on top
    of the relational database.
O-O features

1. User-Defined datatype (UDT)

2. User-Defined function (UDF)

3. Object View and Object Cache, Object
 Type Translator
   Applications that have complex data and
    queries should use ORDBMSs.
   Major vendors (Oracle, DB2, Illustra) have
    ORDBMS products
   A better and standardized solution to the
    multiple inheritance problem is needed.
   Commercial ORDBMS products should
    conform to SQL3, not just support variants
    of it – interoperability issues.
1. Object-Relational DBMSs The Next Great Wave –
   Michael Stonebraker
2. SQL:1999, formerly known as SQL3
   Andrew Eisenberg, Jim Melton
3. O-O, What are they doing to Relational databases?
   Michael J. Carey IBM Almaden January 1999
4. Modeling Object Relational Databases, DBMS April
5. Object Database vs. Object-Relational Databases
   Steve McClure IDC Bulletin #14821E - August 1997
Questions ?

Shared By: