ORNRdatabases

Document Sample
ORNRdatabases Powered By Docstoc
					         Extensions to Relational Databases

              Object-Relational and
           Nested-Relational Databases

                                                By
                                   Dr. Akhtar Ali




Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   1
                                Lecture Theme
• The need for extensions in Relational Data Model

• Classification of database systems

• Introduce extensions to the basic relational model, namely
   – Object-Relational
   – Nested-Relational

• Applications that would benefit from extended relational
  databases

• Introduction to Object-Relational features of Oracle 8i


   Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   2
                                    References
• R. Elmasri and S. B. Navate, Fundamental of Database
  Systems – 3rd Edition, 2000, Chapter 13

• Thomas Connolly and Carolyn Begg, Database Systems
  - 3rd Edition, 2002, Chapter 27

• Michael Stonebraker, Paul Brown and Dorothy Moore,
  Object-Relational DBMSs: The Next Great Wave and
  Object-Relational DBMSs: Tracking the Next Great
  Wave (1996, 1998)

• Prof. Norman Paton, Object Relational Databases,
  University of Manchester.
  Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   3
  Why Extend Relational Data Model
• To eradicate the following weaknesses
    – Poor representation of ‘real world’ conceptual model
           • Usually the relational schema does not correspond to real world
             entities
    – Semantic overloading
           • The same relation is used to represent entities as well as
             relationships
    – Poor support for integrity and business rules
    – Fixed number of attributes & all attribute values must be
      atomic
    – Limited operations
    – Difficult to handle recursive queries
    – Impedance mismatch (when SQL is embedded in 3GLs)
           • Type System mismatch, Evaluation Strategy mismatch
    – Poor navigational access
  Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   4
 Michael Stonebraker’s Classification
• Michael Stonebraker presents this four-quadrant matrix in the book
  entitled “Object-Relational DBMSs: The Next Great Wave”
    – This is a classification of both database applications and systems.




  Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   5
                       Lower-Left Quadrant
• Those application that process simple data and require
  no query capability e.g. text processors (word, emacs)

    – Information has little internal structure.

    – Document updates are relatively infrequent.

    – Documents are of modest size.

    – Queries are simple string or pattern searches.




  Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   6
                       Upper-Left Quadrant
• Those application that process simple data and require
  complex query capability e.g. a typical business
  application require RDBMS.

    – Information has straightforward and fixed structure.

    – Information collection may be large.

    – Information storage must be reliable.

    – Queries are relatively complex.

    – Updates are frequent and Security is vital.


  Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   7
                     Lower-Right Quadrant
• Those application that process complex data and require
  no query capability e.g. a CAD application requires
  OODBMS.

    – Information has complex structure.

    – Analysis are complex.

    – Information is moderate in quantity.

    – Queries are not central.

    – Updates are periodic.


  Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   8
                     Upper-Right Quadrant
• Those application that process complex data and require
  complex query capability e.g. an Image Data Archive
  requires ORDBMS.

    – Information has complex structure.

    – Information may include special data types.
           • Images, Spatial information

    – Information is large in quantity.

    – Queries are important.

    – Updates are periodic.

  Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   9
          Object-Relational Databases
• Object-Relational databases (ORDBSs) seek to:

   – Retain the relational model as a subset.

   – Retain the strengths of the relational model and all the
     technologies that come with it.

   – Supports complex data types (BLOBS, ADTs, Spatial, and
     Multimedia, …).

   – Supports object-oriented design.

   – Reduces impedance mismatch (type system).


 Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   10
            Advantages of ORDBMSs
• Resolves many of known weaknesses of RDBMS.

• Reuse and sharing:
   – reuse comes from ability to extend server to perform
     standard functionality centrally;
   – gives rise to increased productivity both for developer and
     end-user.


• Preserves significant body of knowledge and
  experience gone into developing relational
  applications.



 Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   11
      Disadvantages of ORDBMSs
• Complexity.
• Increased costs.
• Supporters of relational approach believe simplicity
  and purity of relational model are lost.
• Some believe RDBMS is being extended for what will
  be a minority of applications.
• OO purists not attracted by extensions either.
• SQL now extremely complex.



  Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   12
                  Classification Problems
• Most of OODBMSs claim to be in Upper-Right quadrant
  not just ORDBSs.




 Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   13
Myth: OODBs do not support queries




 Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   14
             Characteristics of ORDBs
• According to Stonebraker, ORDBs have the following
  characteristics.

    1. Base type extensions

    2. Complex objects

    3. Inheritance

    4. Active Rules

• Oracle 8i supports 1,2, and 4. Item 4 is independent of
  object extensions. Oracle 9i has most of the features
  (SQL3 compliant). We assume using Oracle 9i
  throughout.
  Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   15
From Relations to Object-Relations
• Structured types for attributes (beside atomic)
    – Composite attributes (e.g. structs or records)
    – Arrays
    – Nested relation i.e. the value of an attribute can be an entire
      relation
    – This removes the restriction of first normal form (1NF)
• Methods
    – Special operations can be defined for, and applied to
      instances of a user-defined type (UDT)
• Identifiers for tuples
    – Tuples plays the role of objects.
    – Each tuple has a unique ID that is generated by the system
• References
    – An attribute can have a value that is a pointer to an object.
      This feature allows objects sharing.

  Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   16
                         Nested-Relations
• Relations that violate the 1NF rule of relational model are
  called Nested-Relations.
• Nested-Relational Model (NRM) are sometimes referred
  to as the Non-1NF or Non-First Normal Form (NFNF) or
  NF2 relational model.
• Nested-Relational Model offers very limited features
    – Composite attributes (e.g. structs or records)
    – Nested relation i.e. the value of an attribute can be an entire
      relation
• NRM does not support Object-Sharing
• NRM based system supports new algebraic operations
  for manipulating nested-relations
    – Nest (to convert 1NF into NF2)
    – Unnest (to convert NF2 into 1NF)

  Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   17
Relational Design for Movie Database
• Sample Relational Database




• A Relational Query
    Select s.name, sa.street, sa.city, m.title, m.year,
      m.length
    From Stars s, Star_Adress sa, Movies m, Movie_Star ms
    Where s.name = sa.name
      And s.name = ms.name
      And ms.title = m.title

  Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   18
             Nested-Relational Design
• Nested-Relational Table




• A Nested-Relational Query
    Select s.name, s.address, s.movies
    From Stars s
• Watch out for duplication of data!

  Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   19
              Object-Relational Design
• Object-Relational Database




• An Object-Relational Query
    Select s.name, s.address, s.movies
    From Stars s
• Watch out for Object Sharing!


  Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   20
 Oracle Object-Relational Features
• User Defined Object Types (UDOT)
   – A type having attributes and methods
• Objects are instances of UDOTs.
• Type Inheritance
   – You can specialize an object type by creating subtypes that
     have some added, differentiating feature, e.g. attributes
     or/and methods
• Object Tables
   – An object table is a special kind of table in which each row
     represents an object.
• Object Views
   – An object view is a way to access relational data using
     object-relational features.
   – It lets you develop object-oriented applications without
     changing the underlying relational schema.

 Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   21
Oracle Object-Relational Features …
• REF Data type
    – A REF is a logical "pointer" to a row object. REFs and
      collections of REFs model associations among objects,
      particularly many-to-one/many-to-many relationships.
    – REFs provide an easy mechanism for navigating between
      objects.
• Collections
    – two collection datatypes: varrays and nested tables
    – A varray is an ordered collection of elements: the position
      of each element has an index number, and you use this
      number to access particular elements.
    – A nested table can have any number of elements: no
      maximum is specified in the definition of the table; also, the
      order of the elements is not preserved.
    – Elements of a nested table are actually stored in a separate
      storage table.

  Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   22
User Defined Object Types (UDOT)
• An Object type declaration is like a record or tuple construct.
• Methods can be associated with a UDOT.
• Object types can be used as Domain for attributes.
    CREATE TYPE PointType AS OBJECT (
      x NUMBER,
      y NUMBER );
    /
    CREATE TYPE LineType AS OBJECT (
      end1 PointType,
      end2 PointType,
      MEMBER FUNCTION length(scale IN NUMBER) RETURN
      NUMBER,
      PRAGMA RESTRICT_REFERENCES(length, WNDS));
    /
• Methods are defined separately.
• Pragma is a directive to the compiler. WNDS means that this
  method does not modify database tables.


  Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   23
                   Methods on a UDOT
• Methods
   – Methods body is written in PL/SQL.
   CREATE TYPE BODY LineType AS
   MEMBER FUNCTION
     length(scale NUMBER) RETURN NUMBER IS
      BEGIN
             RETURN scale * SQRT((SELF.end1.x -
     SELF.end2.x) *                     (SELF.end1.x -
     SELF.end2.x) +
                    (SELF.end1.y - SELF.end2.y) *
                    (SELF.end1.y - SELF.end2.y) );
      END;
   END;
   /
   – Methods can be invoked from PL/SQL or SQL
     queries (if free from side-effect defined by Pragma
     clause).
 Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   24
                              Object Tables
• Tables can be created that store collections of objects.
    CREATE TYPE Movie AS OBJECT (
      Title varchar(15),
      Year number(4,0),
      Lenght number(3, 0));
    /
    CREATE TABLE Movie_Tab OF Movie (PRIMARY KEY (Title));


    – Such a table can be viewed as:
           • A single column table in which each entry is a Movie object.
           • A multi-column table in which each attribute of Movie is a column.
    – Objects that appear in object tables are called row objects.
    – Objects that appear as attributes of other objects or in table
      columns are called column objects.


  Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   25
                             Nested Tables
• A nested table is an unordered set of data elements that are all of
  the same data type. It has a single column of built-in type or UDOT.
• First define a type to be table of a some built-in type or UDOT.
    CREATE TYPE Movie_Tab AS TABLE OF Movie;

    – Then define a column or attribute in an other table or type of this
      new nested table type.

    CREATE TABLE Star (
      Name varchar(15),
      …,
      Movies Movie_Tab DEFAULT Movie_Tab() )
      NESTED TABLE Movies STORE AS Star_Movie;

    – Tuples in the Movies column are stored in a separate storage table
      called Star_Movie.


  Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   26
                        Nested Tables …
• Querying Nested Tables
   – This query returns values not very useful for users.
       SELECT S.Name, S.Movies
       FROM  Star S
       WHERE S.Name = 'Fisher'
   – We can unnest the nested table to get some useful values.
       SELECT   S.Name, M.Title, M.Year
       FROM   Star S, TABLE (S.Movies) M
       WHERE S.Name = 'Fisher'
   – The above query returns the following values.




 Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   27
                 References To Objects
• Nested tables do not support sharing of objects and Relationships
  are value-based.
• References provide OID based 1:1 and 1:many relationships with
  object-relational features.
        CREATE TYPE PointTP AS OBJECT (
          id integer,
          x NUMBER,
          y NUMBER );
        /
        CREATE TABLE Points OF PointTP ( Primary Key (id) );
        /
        CREATE TABLE Lines2 (
          end1 REF PointTP,
          end2 REF PointTP)
        /

    – end1 and end2 attributes store references (pointers) to PointTP
      objects.


  Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   28
             Collections of References
• Many:Many relationships can be represented by nested tables of
  references.
• References provide OID based 1:1, many:1, 1:many, many:many
  relationships with object-relational features.
    Create Type Movie_ref_t as Object (
      MovieRef REF Movie);
    /
    Create Type Movie_set_t as Table of Movie_ref_t;
    /
    CREATE TABLE Star (
      Name varchar(15),
      …,
      Movies Movie_set_t)
      NESTED TABLE Movies STORE AS Star_Movie;

    – Type Movie_ref_t is an object with a single attribute that is a
      reference to an object of type Movie.
    – Now Movies is a nested table that contain references to Movie
      objects.

  Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   29
            Populating Collections
  INSERT INTO TABLE (
     SELECT S.Movies
     FROM   Star S
     WHERE S.Name = 'Hamill')
  SELECT REF(m)
  FROM Movie_Tab m
  WHERE m.Title in
  ('Star Wars', 'Empire', 'War on Terror')
  /


  – The first select finds the nested table
  – The second select returns Reference to the movie
    objects that are then stored in the Movies nested
    table.
Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   30
Querying Collections using DEREF




Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   31
Querying Collections using Cursor




Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   32
Querying Collections using Unnesting




 Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   33
                           Type Inheritance
• You can specialize the attributes or methods of a
  subtype in these ways:
    – Add new attributes that its parent supertype does not
      have.
    – Add entirely new methods that the parent does not have.
    – Change the implementation of some of the methods a
      subtype inherits from its parent so that the subtype's
      version executes different code from the parent's.
• FINAL and NOT FINAL Types and Methods
    –   A type defined as NOT FINAL can be specialized.
    –   By default all UDOTs are FINAL
    –   By default all Methods are NOT FINAL
    –   If you want to create a subtype of an UDOT defined as
        FINAL, you can do so using ALTER TYPE.



  Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   34
             Type Inheritance: Examples
•   A FINAL Type Definition
    CREATE TYPE Person_typ AS OBJECT (
         NINUM CHAR(9),
         NAME VARCHAR2(30),
         ADDRESS VARCHAR2(100)) FINAL;
•   Changing a type from FINAL to NOT FINAL
    ALTER TYPE Person_typ                    NOT FINAL;
•   NOT FINAL type with a FINAL Method
    CREATE TYPE T AS OBJECT (...,
    MEMBER PROCEDURE Print(),
    FINAL MEMBER FUNCTION foo(x NUMBER)... ) NOT FINAL;
•   Subtypes
    CREATE TYPE Student_typ UNDER Person_typ (
         DEPTID NUMBER,
         MAJOR VARCHAR2(30)) NOT FINAL;
    CREATE TYPE PartTimeStudent_typ UNDER Student_typ (
         NUMOFHOURS NUMBER(2));


    Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   35
                                     Summary
• Why Relational Model is not good enough.

• Discussed classification of database applications and
  systems.

• Introduction to Object-Relational and Nested-
  Relational databases.

• Object-Relational features of Oracle 9i.




  Advanced Databases (CM036): Lecture # 10 (Object-Relational and Nested-Relational Databases   36

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:3
posted:10/18/2011
language:English
pages:36