Object Relational Databases

1
Section 1

• Object Relational Databases

2
Section Content

• 1.1 Introduction
• 1.2 Abstract Data Types
• 1.3 Inheritance and Identity
• 1.4 Rules
• 1.5 Using Oracle

3
1.1 Introduction

• The need for richer storage mechanisms
• Multimedia applications
• Incorporation of business rules
• Reusability (inheritance)
• Nested complex types
• Relationships
• Options
• Object-oriented databases ?
• Object-relational databases ?

4
Advantages

• The main advantages come from reuse and sharing.
• Reuse comes from the ability to extend the
  database server so that core functionality is
  performed centrally, rather than coded in each
  application.
• An example is a complex type (or extended base
  type) which is defined within the database, but
  is used by many applications. Previously it was
  required to define this type in every application
  that used it, and develop the interface between
  the software type and its representation in the
  database. Sharing is a consequence of this reuse.
• From a practical point of view, end-users are
  happier to make the smaller leap from
  relational to object-relational, rather that have
  to deal with a completely different paradigm
  (object-oriented).

5
Disadvantages

• The ORDBMS is more complex and thus has increased
  costs.
• Relational purists believe that the simplicity of
  the original model was its strength.
• Pure object-oriented database engineers are
  unhappy with the object-relational terminology
  which is based on the relational model and not on
  object-oriented software engineering concepts.
• An example is user-defined data types v
  classes.
• Thus, there is a large semantic gap between the
  o-o and o-r database worlds.
• ORDBMS engineers are data focused while OODB
  engineers have models which attempt to mirror the
  real-world (data behaviour).

6
Third Generation Database System Manifesto

• The third-generation DSM was devised by
  Stonebrakers group (of proposers) and defines
  those principles that ORDBMS designers should
  follow.
• A third-generation DBMS must have a rich type
  system.
• Inheritance is a good idea.
• Functions (including database procedures and
  methods) and encapsulation are a good idea.
• Unique identifiers for tuples should be assigned
  by the DBMS only if a user-defined primary key is
  unavailable.
• Rules (triggers or constraints) will become a
  major feature in future database systems. They
  should not be associated with a specific function
  or collection.

7
Manifesto (contd.)

• All programming access to a database should be
  through a non-procedural, high-level access
  language (such as SQL).
• There should be more that one way to specify
  collections one using enumeration of members,
  and a second using the query language to specify
  membership.
• Updateable views are essential.
• Performance indicators have nothing to do with
  data models.
• Third-generation DBMSs must be accessible from
  multiple high-level languages.
• Persistent forms of multiple high-level languages
  are a good idea.
• SQL is intergalactic data-speak regardless of
  its many faults.
• Queries and results should be the lowest level of
  communication between client and server.

8
Sections Covered

• 1.1 Introduction
• 1.2 Abstract Data Types
• 1.3 Inheritance and Identity
• 1.4 Rules
• 1.5 Using Oracle

9
1.2 Abstract Data Types

• There is a need to extend the base types provided
  in RDBMS and SQL as many real-world problems are
  difficult to express using simple base types.
• All types are defined as Abstract Data Types.
• An ADT includes a name, length (in bytes),
  procedures for converting a value from internal
  (database) to external (user) representation (and
  vice versa), and a default value.
• DEFINE TYPE int4 IS (InternalLength 4,
  InputProc CharToInt4, OutputProc Int4toChar,
  Default 0)
• Using Postgres the ADT int4 is defined. The
  CharToInt4 and Int4toChar procedures are
  implemented in C or Java and registered with
  the system using a DEFINE PROCEDURE command.

10
ADT Operations

• Operations on ADTs are defined by specifying the
  number and type of operand, the return type, the
  precedence and associativity of the operator, and
  the procedure that implements it.
• It may also specify procedures to be called (eg.
  a sort).
• DEFINE OPERATOR (int4,int4) RETURNS int4
• IS (Proc Plus, Precedence 5, Associativity
  left)
• The procedure Plus (that implemented ) is
  programmed using C or Java.
• In this case there are 2 operands of type int4
  the return type is int4, the precedence is 5 (in
  relation to other operations on int4) and
  parsing starts from the left.

11
Collection Data Types

• A Collection type Tc is a named group of
  instances of another type Tb. For example, a
  collection Tc called DatabaseStudents is a
  collection of all type Tb (Student type) which
  are studying CA306.
• There are three built-in forms of collections
  SET, MULTISET and LIST. They differ in the rules
  that are applied to their contents.
• SETs obey the rules of mathematical sets
  (relations). This means that a set can contain no
  more than one instance of a given value. In other
  words, an object (ref) can appear only once in a
  set.
• LISTs contain numbered elements.
• MULTISETs are SETs which permit duplicated (often
  referred to as BAGs).

12
Defining COLLECTION types

• SET (integer not NULL)
• LIST (varchar(40) not NULL)
• Multiset (PersonName not NULL)
• LIST (LIST (Revenue not NULL) not NULL)
• It is possible to define a table that includes
  several COLLECTION columns.
• CREATE TABLE Collection_Sampes (
• Id integer not null primary key,
• List_sample LIST(varchar(16) not NULL),
• Set_sample SET(integer not null),
• Mset_sample MULTISET(Authors not NULL)
• )

13
COLLECTION instances

• Each COLLECTION type has a corresponding
  constructor SET, MULTISET, or LIST, which
  enforce their respective rules. For example, if
  you try to insert a duplicate into a SET, it is
  disallowed.
• INSERT INTO Collection_Samples
• VALUES (
• 1,
• LIST John, Paul, George, Ringo,
• SET 63,64,65,66,67,68,69,
• MULTISETJohn,Paul,Paul,Paul,John,John )

14
Sections Covered

• 1.1 Introduction
• 1.2 Abstract Data Types
• 1.3 Inheritance and Identity
• 1.4 Rules
• 1.5 Using Oracle

15
1.3 Inheritance and Identity

• A type is declared using the CREATE command.
• A type inherits all attributes from its parents
  unless an attribute is overridden in the type
  definition.
• Multiple inheritance is supported but a clash of
  (inherited) names will disallow the type
  definition.
• Key specifications are also inherited.
• CREATE Person (fname char15, lname
  char15, sex char, DataOfBirth data)
• KEY (lname)
• CREATE Employee (StaffNo char5, position
  char10, salary float4, Dept char4)
• INHERITS(Person)

16
Inheritance Examples

• The type Employee includes those attributes
  declared explicitly, together with those
  inherited from the Person relation.
• The key is the inherited key from Person.
• An instance is added to the Employee type using
  the APPEND command.
• APPEND Employee(StaffNo A123, lnameBloggs,
  fnameJoe, sex M, DateOFBirth10/10/71,
  positionSales, Salary 35000)
• A query to return members of this relation uses
  the RETRIEVE command.
• RETRIEVE (E.StaffNo, E.lname, E.position) FROM E
  IN Employee

17
Object Identity

• Each type has an implicitly named attribute oid
  to represent the unique identifier of an object
  instance.
• Each oid is created and maintained by the
  database.
• Users can access but not update an oid.
• The oid can be used by applications in the normal
  way.
• CREATE Dept (Manager Employee, dname
  char25,
• location char25)
• KEY (dname)
• The Manager attribute is a reference to an object
  of the Employee type.

18
Identity Example

• If it is necessary to add a new department
  (object) and create a reference to an object of
  another type (Employee), we could do so using a
  query.
• APPEND (Manager Employee(e.oid), dname
  Sales, location floor 2)
• FROM e in Employee
• WHERE e.StaffID A332
• This creates a link between the new instance of
  the Dept type and an existing instance of the
  Employee type.

19
Sections Covered

• 1.1 Introduction
• 1.2 Abstract Data Types
• 1.3 Inheritance and Identity
• 1.4 Rules
• 1.5 Using Oracle

20
1.4 Rules

• Rules are valuable in that they protect the
  integrity of data in a database.
• Relational databases have referential integrity
  for foreign key management.
• The general form of a rule is on the occurrence
  of event x do action y.
• The are four variations in the proposed standard
  for ORDBs update-update, query-update,
  update-query, and query-query rules.

21
Update-Update Rules

• In this case, the event is an update, and the
  action is an update.
• This is useful in cases where it is necessary to
  implement an audit eg. Create a new tuple in the
  Audit relation with username, date and
  description, each time a change is made to the
  Salary relation.
• CREATE RULE Salary_Update AS
• ON UPDATE TO Salary
• DO
• insert into Audit
• Values (username, date, Salary.lname)
• In the above example, the current username, date
  and the lname of the updated employee (in Salary)
  are recorded. Note that if we were only
  interested in one or some group of employees we
  could use a where clause (see next example).

22
Query-Update Rules

• In this case, the event is a query, and the
  action is an update.
• Similar to the previous example a user is
  accessing the Salary relation (for a specific
  employee), and the system automatically records
  it. In this case, only for employee A515.
• CREATE RULE Salary_Access AS
• ON SELECT TO Salary where salary.StaffID A515
• DO
• insert into Audit
• Values (username, date, Salary.lname)
• Many relational databases systems cannot
  implement query-update rules.

23
Update Query Rules

• In this case, the event is an update, and the
  action is a query (which uses the results in a
  message).
• Suppose that the deletion of tuples from the
  Author table is not recommended since new titles
  may come into stock.
• CREATE RULE Author_Delete_Alert AS
• ON DELETE TO Author
• DO
• ShowMessage Deleting Author.nameprevents new
  titles being entered into the database
• The query in this case is select Author.name
  which is used in the message.

24
Query-Query Rules

• In this case, both the event and the action are
  read-only queries.
• A example is where one retrieval operation will
  require an attribute from some other relation.
• For example, when viewing details for a customer
  (from the Customer relation), their credit may be
  listed as A2, where the actual value for A2
  is inside a Credit relation. (Note we could do
  the same using a join query)
• CREATE RULE Credit_View AS
• ON SELECT TO Customer X
• DO
• Select C.value
• From Credit C
• Where C.id X.CredRating

25
Guidelines

• Designers of rules must guard against or be aware
  of
• Multiple rules fired by the same event.
• Chain rules that cause infinite loops.
• Aborting the action part of a rule may terminate
  the whole transaction. In general, this should be
  avoided, and the rule part of the transaction
  becomes a new transaction in itself.

26
Triggers

• A trigger is an SQL statement that is executed by
  the DBMS as a side effect of a modification to a
  table.
• The basic format of a CREATE TRIGGER statement
• CREATE TRIGGER name
• BEFORE AFTER lteventgt ON lttable namegt
• REFERENCING ltsome valuesgt
• FOR EACH ROW STATEMENT
• WHEN lttrigger conditiongt
• A trigger has a name and an associated timing
  (before or after).

27
Trigger Timing

• BEFORE INSERT
• BEFORE UPDATE
• BEFORE DELETE
• AFTER INSERT
• AFTER UPDATE
• AFTER DELETE

28
AFTER INSERT trigger

• CREATE TRIGGER InsertMailshotTable
• AFTER INSERT ON PropertyForRent
• REFERENCING NEW ROW AS pfr
• BEGIN
• INSERT INTO Mailshot VALUES
• (SELECT .
• FROM .
• WHERE .
• END

29
Sections Covered

• 1.1 Introduction
• 1.2 Abstract Data Types
• 1.3 Inheritance and Identity
• 1.4 Rules
• 1.5 Using Oracle

30
1.5 Using Oracle

• Recent and current versions of Oracle have tried
  to include object-oriented features in some form.
• Those features include
• User-defined data types
• Methods
• Object identifiers
• references

31
User-Defined Data Types

• Oracle supports two user-defined data types
  object and collection types.
• CREATE TYPE AddressType AS OBJECT (
• street VARCHAR(25),
• city VARCHAR(25),
• postcode VARCHAR(2) )
• The CREATE TYPE command allows us to create a new
  type. Types can subsequently be used to define
  tables.

32
Employee Example

• Methods can be specified in user-defined types.
• CREATE TYPE PersonType AS OBJECT (
• fname VARCHAR(25),
• lname VARCHAR(25),
• sex CHAR,
• DOB date,
• address AddressType,
• MEMBER FUNCTION Get_age RETURN INTEGER )
• The Get_age method uses the DOB attribute to
  calculate the current age.
• A table definition for employees
• CREATE TABLE Employee OF PersonType (lname
  PRIMARY KEY)

33
Methods

• Methods are classified as member, static or
  comparison.
• A member function is a function that always has
  an implicit SELF parameter as its first
  parameter, whose type is the containing object
  type. This method adheres to true object-oriented
  style and finds all its arguments among the
  attributes of the object.
• A static method is a function that does not have
  an implicit SELF parameter. These methods are
  invoked using a type qualifier eg.
  Person.Get_age().
• A comparison method is used for comparing
  instances of object types. They can be defined in
  two ways
• a map method uses Oracle to compare built-in
  types
• an order method uses its own internal logic to
  compare two objects.
• Note that both types cannot be defined for the
  same object.

34
Constructor Methods

• Every object type has a system-defined
  constructor method that constructs a new object.
• The constructor method has the same name as the
  object type and has parameters with the same
  names and types as the object types attributes.
• Employee(Joe, Bloggs ,M, 10/10/71,
• AddressType(10 Glasnevin Ave, Dublin, 9))

35
Object Identifiers

• Objects that appear in object tables are called
  row objects and objects that occupy relational
  tables are called column objects.
• Every row object in an object table has an
  associated logical logical identifier.
• The unique OID may be specified to come from the
  rows primary key, or to be system-generated.
• OIDs can be used to fetch and navigate objects.
• CREATE TABLE Employee OF PersonType (lname
  PRIMARY KEY)
• OBJECT IDENTIFIER PRIMARY KEY

36
References

• Oracle provides a built-in data type called REF
  to encapsulate references to row objects of a
  specified object type.
• A REF can be used to examine or update the object
  it refers to, and to obtain a copy of the object
  it refers to.
• A REF value can be assigned another REF value or
  can be assigned NULL. Users cannot assign
  arbitrary values to a REF type.
• CREATE TYPE DepartmentType AS OBJECT (
• dname VARCHAR(25),
• address AddressType,
• manager REF Employee,
• MEMBER FUNCTION Get_Emp_Count RETURN INTEGER )

37
Collection Types

• Oracle supports two collection types array and
  table types.
• An array is an ordered set of data elements of
  the same type. Each element has an index.
• CREATE TYPE FullnameType AS VARRAY(3) of
  VARCHAR(25)
• The above definition creates a collection of 3
  strings used as a name. This definition may then
  be used in some TYPE or TABLE definition.
• A nested table is an unordered set of data
  elements of the same data type. It has a single
  column of either built-in or (user-defined)
  object type.

38
Nested Tables

• If the column is an object type, it can be viewed
  as a multi-column table.
• Begin by creating a table of object types.
• CREATE TYPE AddressType AS OBJECT (
• street VARCHAR(25),
• city VARCHAR(25),
• postcode VARCHAR(2) )
• CREATE TYPE AddressCollection AS TABLE OF
  AddressType

39
Nested Table Example

• Once defined, it is then necessary to declare an
  object of this type (declares a table), and then
  insert into the target table.
• address AddressCollection
• Now create the table for employees which have
  multiple addresses.
• CREATE TABLE Employee OF PersonType (lname
  PRIMARY KEY)
• OBJECT IDENTIFIER PRIMARY KEY
• NESTED TABLE address STORE AS AddressStorageTable
  

40
Differences

• Arrays have a maximum size nested tables do not.
• Individual elements can be deleted from a nested
  table, but not from an array.
• Oracles stores array data in-line but stores
  nested tables as an external (system generated)
  table.
• When stored in the database, arrays retain their
  ordering, but nested tables do not.