ObjectRelational DBMSs

1
Object-Relational DBMSs

• Chapter 28.1, 28.6 (except 28.6.3), 28.7

2
ORDBMS Features

• OO features being added include
• user-defined types
• methods
• complex objects
• object identity.
• Not supported
• Built-in inheritance mechanism
• Polymorphism

3
Stonebrakers View of the Database World
4
Built-in Data Types (Oracle8)

• CHAR(n) fixed length character data
• VARCHAR2(n) string of characters with variable
  length up to n characters maximum
• NUMBER, NUMBER(n), NUMBER(p,q)
• DATE
• LONG variable length character data up to 2Gb
• RAW(n), LONG RAW - variable length binary data
• CLOB reference to a character large object
• BLOB reference to a binary large object

5
Abstract Data Type

• Type specification
• CREATE TYPE clause
• Attributes
• Method declaration
• Type body
• Method implementations

6
Object Type Specification

• CREATE TYPE Type_name AS OBJECT (
• attribute-name attribute data type,
• STATIC I MEMBER method-type method-name
  (args)
• RETURN data-type,
• )

7
Method Declaration

• STATIC type method
• MEMBER type instance method
• Method-type
• Function returns a value
• Procedure doesnt return a value
• Method name
• Differs from attribute names

8
Method Implementation Using PL/SQL

• CREATE TYPE BODY type-name AS
• STATIC I MEMBER method-type method-name
  (args)
• RETURN data-type,
• IS
• BEGIN
• END
• END

9
Method Implementation Using Java

• CREATE TYPE BODY type-name AS
• STATIC I MEMBER method-type method-name
  (args)
• RETURN data-type,
• AS LANGUAGE JAVA NAME Java-stored procedure
  signature
• LOADJAVA utility

10
Method Implementation

• TYPE BODY can be added, removed or replaced
• SELF refers to the current object instance
  (similar to THIS in Java)
• SELF is used with member methods only

11
MAP Method

• The need to compare objects
• Sort rows based on a field containing object
  instances
• Sort rows
• Only one either MAP or ORDER method can be
  declared in a object type
• MAP has no arguments and returns a scalar value
• MAP MEMBER FUNCTION method-name RETURN data-type

12
ORDER Method

• ORDER MEMBER FUNCTION method-name (arg arg_type)
  RETURN INTEGER
• ORDER method result
• Negative value current object is less than
  the other object
• Zero value - current object is equal to the
  other object
• Positive value - current object is greater than
  the other object

13
Object Types

• Incomplete object type
• CREATE TYPE Type_name
• Nested object type
• An attribute as an object instance
• Object type with references
• REF data type
• An attribute of REF data type cannot be a primary
  key of a table

14
Object Type Applications

• Data type for a relational database field
• Nested object instance
• Reference of an object instance
• Data type for an object attribute
• To create object table
• Each row is an instance of the object type

15
Referencing Objects

• OID is assigned to the object instance which is a
  row of an object table
• Nested object type instance does not receive OID

16
Modifying Object Types

• Add or remove an attribute or add a new method
• Delete existing object type and re-create it
• CREATE OR REPLACE TYPE and then CREATE OR REPLACE
  TYPE BODY
• Modify implementation of existing methods
• CREATE OR REPLACE TYPE BODY

17
Deleting Object Types

• DROP TYPE type-name
• The type cannot be dropped if it is used in the
  definition of any database object
• DROP TYPE BODY type-name
• Removes method implementation only

18
Object Tables

• Extent a set of instances of a given type
• CREATE TABLE table-name OF object-type-name
• (constraints,)
• OBJECT IDENTIFIER IS SYSTEM GENERATED
• Constraints affect manipulations with object
  attributes

19
Constraint Declaration

• Column constraints
• PRIMARY KEY
• UNIQUE
• CHECK
• NOT NULL
• WITH ROWID
• SCOPE IS - referential integrity constraint

20
Modifying Object Tables

• Constraints can be added
• PRIMARY KEY
• UNIQUE
• CHECK
• NOT NULL, NULL
• SCOPE IS
• Can be removed
• All of the above except SCOPE

21
Examples

• ALTER TABLE Transcript ADD (SCOPE FOR (sid) IS
  Student)
• ALTER TABLE Transcript MODIFY (sid NULL)
• ALTER TABLE Transcript ADD (UNIQUE cid)

22
Delete Tables

• DROP TABLE table-name
• Drop a table from a schema
• Remove or disable referential integrity
  constraints and references, after that delete the
  table
• DROP TABLE table-name CASCADE

23
Insert Data into Relational Table (with ADT
columns)

• Object type constructor
• Object-type-name ( value1,,valueN)
• INSERT INTO table-name VALUES (
• Value1, , Object-constructor, ValueN)

24
Retrieve and Update Relational Table (with ADT
columns)

• The object property is reached by specifying
• Table-name.Column-name.Attribute-name
• Alias.Column-name.Attribute-name
• SELECT statement
• The attribute may appear in select-list
• The attribute may appear in qualification
• UPDATE statement
• The attribute may appear under SET clause and in
  qualification

25
Relational Tables with Object References

• References are maintained for row object
  instances only
• An attribute in a relational table refers to a
  row object from object table
• An attribute of the referenced object instance
  can be accessed by
• Alias.ref-column-name.attribute-name

26
Insert Data into Relational Table (Object
References)

• An attribute can be REF type attribute
• To insert a reference
• Obtain a reference to appropriate object instance
• Store the reference into the field
• REF(Alias) function returns the reference of
  specified object instance

27
Insert a Row into a Relational Table with a
Reference

• INSERT INTO table-name
• SELECT value1,,REF(Alias),, valueN
• FROM Object-table-name Alias
• WHERE qualification
• Qualification determines the instance which
  reference is required.

28
Insert a Row with a Reference

• If an attribute of reference type doesnt have
  NOT NULL constraint
• Insert a row with Null value for the attribute
• Use the following UPDATE statement
• UPDATE table-name SET
• Column-name(SELECT REF())
• WHERE qualification

29
Insert Object Instance into Object Table

• Atomic object
• Using insert statement with values
• Using object constructor
• Complex Object
• Using object constructor
• Using SELECT statement with REF() function
• Using nesting object constructors

30
Retrieve Data from Object Table

• SELECT is in effect
• DEREF() function
• Argument OID
• The result object instance
• VALUE() function
• Argument Alias of object table
• The result object instances that satisfy
  qualification in SELECT statement

31
Data Manipulation in Object Tables

• An attribute can be reached by
• Alias.columnC.columnR.field
• Delete an object instance
• DELETE statement is in effect
• Set references in other tables to Null.