Docstoc

Object-Relational Databases

Document Sample
Object-Relational Databases Powered By Docstoc
					                Object-Relational Databases

                               Piyush Ojha
                  School of Computing and Mathematics
                            University of Ulster
                         pc.ojha@ulster.ac.uk

                               24 February 2009


                                      Abstract


      We discuss four-fold classification of database applications according to the
   complexity of the data and the extent and nature of querying that is required and
   note that object-relational databases are appropriate when complex data is to be
   queried extensively. We discuss in some detail four characteristic features of
   Object-Relational Database Management Systems, viz extensible data types, com-
   plex objects, inheritance and rules.
      The extent of the support for these features in the SQL 1999 standard and
   Oracle 10g is outlined.




1 Sources
 1. M Stonebraker and P Brown, Object-Relational DBMSs: Tracking the Next Great
    Wave, 2nd Edition, Morgan Kaufmann, 1999.

 2. Andrew Eisenberg and Jim Melton, SQL:1999 Formerly Known As SQL3, ACM
    SIGMOD Record, vol 28, No 1, March 1999.
   A readable, concise summary of new features in the SQL 1999 standard.




                                          1
2 Classification of DBMS Applications



                             2               4
              Query        (RDBMS)         (ORDBMS)



                              1                3
          No Query
                            (FS)            (ODBMS)



                         Simple Data         Complex Data

 Fig1 : A Matrix for Classifying DBMS Applications (Stonebraker and
                                Brown)
                        Key: FS- File System
         RDBMS - Relational Database Management System
                       ODBMS - Object DBMS
                ORDBMS - Object-Relational DBMS



2.1 Example Applications

2.1.1 Simple Data, No Queries (Quadrant 1)

   • Text Editor – Only needs to ‘get’ and ‘put’ file ‘queries’
     Data model – character string of arbitrary length
     The file system provides the data management services
   • Video on Demand
     To transfer a sequence of frames over a communication link at a guaranteed rate
     (30 frames/sec)
     Data is still essentially simple in spite of the performance guarantees that are
     required
     The file system provides all data management services



2.1.2 Simple Data with Queries (Quadrant 2)


Relational DBMSs provide the data management services for this class

                                            2
Any business application, e.g management of assignment of employees to projects

DBMS requirements

   • Query Language SQL-89 or SQL-92
   • Client Tools Toolkit for database design, designing forms for data entry and dis-
     play, report writer, ability to call DBMS services from 3GLs (C, Fortran, COBOL,
     Java etc)
   • Performance Proper transaction management ensures consistency of data
     Recovery methods ensure data is never lost, regardless of the type of failure
   • Security/Architecture Since the database holds sensitive data, security is required
     DBMS must run in a separate address space from a client application
     Data files can be read and written only by the DBMS
     Client-Server architecture

                              Query
             Client                            Server

                              Answer

              Fig: Client-Server Architecture


2.1.3 Complex Data, No Queries (Quadrant 3)


Example: Allocation of space in an open plan office

In RDB terms, the data is contained in two tables:


          create table employee (
            name             varchar(30),
            space            polygon,
            adjacency        set-of(employee));

          create table floors (
            number           int,
            asf              swiss_cheese_polygon);



asf– assignable square feet

The following (pseudocode) program is executed for re-allocating space:

                                           3
       main ()
       {
          read all employees;
          read all floors;
          compact ();
          write all employees;
       }



The program reads employee and floors data, reallocates space (compact function)
and rewrites employee; No queries

This is a simplified and more accessible version of the problem of designing a computer
chip (definitely no queries here!)

Problem best handled by support for persistent storage in the programming language
in which compact is written (Object Database management System)

‘Systems that focus on providing tight integration with a programming language and
high performance for updates to persistent variables are known as object-oriented
DBMSs.’ (Stonebraker and Brown, page 13)

Reading and writing data is then transparent; user must provide compact.

Many scientific applications fall in this category

Quadrant 3 DBMS requirements


   • Query language None

   • Client tools Whatever development tools are required for the programming lan-
     guage (e.g. Netbeans Integrated Development Environment for Java)

   • Performance The performance of a program handling persistent data should be
     comparable to the performance of a program handling non-persistent data

   • Security/ Architecture Trade-off between performance and security
     Consider
     J = J+1;
     If J is non-persistent, this statement is extremely fast; if it is persistent, the state-
     ment is an update;
     In a client-server architecture, a switch to the server storage space must occur;
     the statement takes 100 to 1000 times longer to execute;
     Running the DBMS in the same address space as the client program compro-
     mises security; a malicious program can bypass the DBMS and use the operating
     system to read and write data;



                                             4
      Fig 3: Client-Server Architecture for Relational Databases



                       Client Program           +        DBMS

               Client Program              +        DBMS


              Fig 4: Insecure Architecture for persistent languages

         Fig 4: Insecure Architecture for persistent languages
     The following architecture allows better security at the cost of some loss in per-
     formance

                                   Object or page requests
                 Client
                   +       Object or page requests
                                                                   DBMS
          Part of the DBMS
           Client
              +                              Data             DBMS
     Part of the DBMS
                                        Data
        Fig 5: Architecture for better security with persistent languages

   Fig 5: Architecture for better security with persistent languages
2.1.4 Complex Data with Queries (Quadrant 4)


Example: State of California Department of Water Resources (DWR): System for man-
aging a library of 500,000 35mm slides

Clients request a picture by content, e.g. San Francisco Bay at sunset, or an endan-
gered species of waterfowl on the banks of the American River

Queries are ad hoc

Indexing all pictures by predefined concepts is prohibitively expensive; moreover, con-
cepts of interest change over time

The existing system (pre 1995) was based on a written caption for each slide and query
pictures were retrieved by keywords; did not work very well

Alternative database


      create table slides (
        id                int,
        date              date,
        caption           document

                                          5
         picture               photo_CD_image);

       create table landmarks (
         name              varchar(30),
         location          point);



photo_CD_image – collection of five digital images ranging from 128 × 192 thumbnail to
2048 × 3072 full colour image

Need to classify the images electronically, e.g. to find the geographical location of
the image, or to ascertain certain attributes of the image (does it contain a sunset?)
through image-understanding programs

The names and map locations of all landmarks in California are available in a public
domain database (landmarks table)

Examine the caption to see if it contains the name of a landmark; if it does, the location
of the landmark is taken as the location of the image

Pattern matching methods can be used for (limited) image understanding

Example Query: Find a sunset picture taken within 20 miles of Sacramento.


             select id
             from slides P, landmarks L, landmarks S
             where sunset(P.picture) and
             contains(P.caption, L.name) and
             L.location || S.location and
             S.name = `Sacramento';



|| is a user defined function that takes two points and returns true if the distance
between them is less than 20 miles

sunset and contains are also user-defined functions

Quadrant 4 DBMS requirements


   • Query language Of the above three user-defined functions, only || is expressible
     in SQL-92
     Essentially an arithmetic function like
     (L.location.x − S.location.x) ∗ ∗2 + (L.location.y − S.location.y) ∗ ∗2 < 20;
     sunset and contains are not expresible in SQL-92.
     The query langugae must allow user-defined functions (SQL3 does)

                                            6
   • Client tools In the DWR example, there is a need to visualise the data in a variety
     of ways
     Many (but not all) quadrant 4 applications have a high multimedia content which
     requires visualization

   • Performance High performance is required for decision support queries; careful
     query optimization is essential
     For example, sunset is an expensive function and must be computed only when
     it is essential; contains (P.caption, L.name) and L.location || S.location
     predicate which is easier to compute should be evaluated first. In other words,
     the sunset function should be computed only for pictures taken within 20 miles of
     Sacramento.
     If an expensive function is required frequently, it is worthwhile to precompute it
     and store the result.
     Appropriate data access methods for such queries are also essential

   • Security/ Architecture High security is required and a client-server architecture is
     the most suitable
     Queries are computation-heavy and relatively little is gained in performance by
     reducing security



2.1.5 Universal Applications


These are applications in the top 2 quadrants, i.e. simple and complex data applica-
tions with queries

Example: Risk Assessment and Fraud Detection in the Insurance Industry

Consider a Customer and Claims database (a typical simple-data-with-queries busi-
ness application)

Enhance the application to include


   • a diagram of each accident site

   • a scanned image of the police report

   • a photograph of the damaged car

   • geographical location (longitude and latitude) of the accident site

   • geographical location of the customer’s home


The enhanced database can be used to support


   • finer granularity of risk assessment; more and finer categories of risk

                                            7
   • fraud detection: compare the damaged car in a new claim with an older claim to
     decide if the new claim is reasonable


The enhanced content and functionality makes it a complex-data-with-queries applica-
tion.

See Stonebraker and Brown (pp 22-23) for an example of complex-data-with-queries
example which has no multimedia content

Stonebraker and Brown contend that the commercial DBMS market will be driven by


   • business data processing applications being enhanced to become universal ap-
     plications, and
   • new multimedia applications


which belong in quadrant 4. Hence Object-Relational databases are the next big wave.



3 Nested Relations Model

This is a very simple object-reltional model in which the type of an attribute, apart from
being a base data type, can also be a relation. For example, in the following relational
schema


Actor(ActorName, Sex, Nationality, DateOfBirth,
      Addresses(StreetName, City, Postcode),
      Films(FilmTitle, ReleaseDate, RunningTime, Genre) ),


the attribute Addresses stands for the addresses of the homes owned by the actor and
its datatype is a relation. Likewise, the attribute Films stands for all films in which an
actor has appeared and its data type is also a relation.

The Actor schema is subject to redundancy because many actors would typically ap-
pear in a film and therefore the details of films would be stored more than once. (We
assume that there are few instances of actors sharing a home.) This may be circum-
vented by permitting the type of an attribute to the a reference to a row in a table or a
set of such references. Thus, the schema


Films(FilmTitle, ReleaseDate, RunningTime, Genre)

Actor(ActorName, Sex, Nationality, DateOfBirth,
      Addresses(StreetName, City, Postcode),
      Films({*Films}) ).

                                            8
Here Films is an ’ordinary’ relation, *Films is a reference to a row in the Films table
and {*Films} is a set of such references.



4 Characteristics of Object-Relational DBMSs

According to Stonebraker and Brown, ORDBMSs possess FOUR characteristics:


  1. Extensible Base Data Types

  2. Complex Objects

  3. Inheritance

  4. Rules



4.0.6 Need for Base Type Extension and Complex Objects

   • Naturalness The types which are naturally single quantities are viewed as base
     types whereas objects which are naturally composites of two or more visible (pub-
     lic) attributes are naturally complex objects

   • Encapsulation Base types are completely encapsulated. The only operation is a
     retrieval or execution of a function that takes an argument of base type.
     Row types (as defined by Stonebraker and Brown and in SQL 1999) are com-
     pletely transparent; all their attributes are visible (public).
     Typically, Java objects contain a combination of public and private data.
     The internal representation of a base type can be changed without affecting the
     programs that use it.
     The public attributes of a complex object can not be changed without affecting
     the programs that use it.
     Base types suitable for encapsulated data; complex types suitable for unencap-
     sulated data

   • OIDs In an OO programming language, complex objects are assigned unique
     identifiers (OIDs) by the system whereas base types aren’t.
     Thus, space penalty for using objects; references are available only for objects.



4.1 Extensible Base Data Types

SQL-92 supports the following data types:



                                            9
   • Integer

   • Floating-point number

   • Character string of fixed or variable length

   • Date, time, datetime, interval

   • Numeric and decimal


A number of pre-defined functions and operators on these data types are also sup-
ported.

These data types are too limited for many real-world problems. The problems are
difficult to code and the performance is poor.

Often performance would improve if the user was able to define a new base data type
and associated functions and operators.

Example 1: A Calendar for the Bond Market


create table Bond (
       bond_id          integer,
       coupon_rate      float,
       face_value       decimal(10,2),
       bought           date,
       matures          date,
       value            decimal(10,2) );

update bond
       set value = face_value + coupon_rate * (matures - bought)


The date functions in implementations of SQL-92 are based on the Gregorian Calendar.
Apparently, the US bond market does not use the Gregorian calendar but a calendar
where every month is 30 days. Thus the accrued interest is the same in January and
February.

The query does not update the value correctly.

Requires date subtraction based on bond calendar.

The application requires Bond calendar dates. The functions have to be defined by the
user and must run in the client program.. This causes


   • more work for the developer;

   • slower performance because of the need to cross the client-server boundary


                                          10
Example 2: A Car Pool

The relation schema

Employee(Name, StartDate, Salary, StreetAddress, City, Postcode)

is not adequate for supporting the formation of car pools because geographically cities
are too large and postcode areas too small for the purpose.

One can build adjacency information for postcodes into the database but that would be
tedious.

Alternatively one can record the latitude and longitude of each employee’s home:

Employee(Name, StartDate, Salary, StreetAddress, City, Postcode, Lat, Long).

Then one can find employees living within a short distance (say a mile) ofa given em-
ployee by a query similar to the following SQL-92 query (the actual arithmetic expres-
sion would be more complex):


SELECT r.Name FROM emp j, emp r
  WHERE j.Name = 'Joe' AND
  ((j.Long - r.Long)**2 + (j.Lat - r.Lat)**2) < 1;


This is somewhat non-intuitive. Moreover, 2D data is not amanable to indexing in a B-
tree and the query optimizer can not optimize the query efficiently. A sequential search
of all employees is necessary.

If data types were extensible, one would be able to define a new data type point and
a function for calculating the distance between two points. In the revised schema

Employee(Name, StartDate, Salary, StreetAddress, City, Postcode, Location),

the type of Location is point and the above query would be rewritten more intuitively
as


SELECT r.Name FROM emp j, emp r
  WHERE j.Name = 'Joe' AND
  distance(j.Location, r.Location) < 1;


2D data can be indexed using R-tree data structure and this query can be executed
efficiently. The type point and the functions that operate on it are defined by an expert
database designer whereas the now intuitive query may be made by a less skilled user.




                                          11
4.1.1 User-Defined Types in SQL 1999


There are two kinds of UDTs in SQL1999:


   • distinct type

   • structured type


A distinct type is simpler and permits differentiation between the same underlying
base types, e.g.

CREATE TYPE SuppNameType AS VARCHAR(15) FINAL;

CREATE TYPE PartNameType AS VARCHAR(15) FINAL;

Now although SuppNameType and PartNameType are both VARCHAR(15), they are differ-
ent types. An attempt to treat an instance of one type as an instance of another type
(e.g. in a primary key - foreign key match) will generate an error.

(Keyword FINAL denotes the fact that the type does not admit subtypes.)

This is a very limited form of extensible base data type.

There seems to be no Oracle equivalent except perhaps a structured type with one
attribute:

CREATE TYPE SuppNamneType AS OBJECT( suppname VARCHAR(15));

An SQL:1999 structured type has the following characteristics:


   • It may be defined to have one or more attributes, each of which can be any
     SQL type, including built-in types or other structured types, nested as deeply as
     desired.

   • All aspects of its behaviour are provided through methods, functions and proce-
     dures.

   • All attributes are encapsulated through system-generated ’get’ and ’set’ functions
     that provide the only access to their values. These system generated functions
     can not be overloaded.

   • Values of structured types may be compared only through user-defined functions.

   • It may participate in type hierarchies.


                                                            Eisenberg and Melton, 1999

For example

                                           12
CREATE TYPE Person AS (
  fname         VARCHAR(15),
  lname         VARCHAR(15),
  streetaddress VARCHAR(30),
  city          VARCHAR(20),
  postcode      VARCHAR(10),
  dob           DATE,
  age           INT          )
METHOD calc_age();


The body of the method is created separately:


CREATE METHOD calc_age() FOR Person
BEGIN
  SET SELF.age = /* code for calculating age from dob */
END;


Values of a structured type are created by a constructor function:


CREATE FUNCTION Person (
       fn VARCHAR(15),
       ln VARCHAR(15),
       sa VARCHAR(30),
       c VARCHAR(20),
       pc VARCHAR(10),
       d DATE           )
RETURNS Person
BEGIN
  SET fname=fn;
  SET lname=ln;
  SET streetaddress=sa;
  SET city=c;
  SET dob=d;
  SET salary =s;
END



4.2 Complex Objects

Support in SQL for complex objects composed of base and user-defined types is de-
sirable.




                                         13
4.2.1 Type Constructors


Stonebraker and Brown advocate support for the following type constructors as the
building blocks for complex objects:


   • Composites (row types)

   • Sets (collection types)

   • References


Other complex objects can then be constructed from these constructors.

Rows: A row is a data type consisting of a record of values.

In SQL 1999, a row type would be used as follows:


CREATE TABLE People (
   name    ROW ( fname VARCHAR(15),
                  lname VARCHAR(15)),
   address ROW ( streetaddress VARCHAR(30),
                  city          VARCHAR(20),
                  postcode      VARCHAR(10) ),
   dob     DATE,
   age     INT );


The row type can contain elements which are user-defined types or complex objects.

This is equivalent to


CREATE TYPE Person AS (
   name    ROW ( fname VARCHAR(15),
                 lname VARCHAR(15)),
   address ROW ( streetaddress VARCHAR(30),
                 city          VARCHAR(20),
                 postcode      VARCHAR(10) ),
   dob     DATE,
   age     INT )
   METHOD calc_age();

CREATE TABLE People OF Person;


Every tuple of People contains just one column of type Person.

Sets: More generally, a collection type which could be set, list, multiset or array.

                                         14
Set: Unordered collection without duplicates

List: Ordered collection with duplicates

Multiset: Unordered collection with duplicates

Array: ordered collection (with duplicates)

If T is a type, collection(T) must also be a type.

Collections of base types, collections of row types, collections of collections and col-
lections of references must be supported.

In SQL 1999, only arrays are supported.


CREATE TABLE People (
   name            ROW ( fname VARCHAR(15),
                         lname VARCHAR(15)),
   address         ROW ( streetaddress VARCHAR(30),
                         city          VARCHAR(20),
                         postcode      VARCHAR(10) ),
   dob             DATE,
   age             INT,
   childrens_fname VARCHAR(15) ARRAY[5] );


This allows the first names of up to five children to be stored in an array.

References: Useful to support references (pointers) as a type constructor.

If T is a type, ref(T) must also be a data type.

In SQL 1999 an attribute can be a reference to a structured type. For example


CREATE TYPE Person AS (
   name    ROW ( fname VARCHAR(15),
                 lname VARCHAR(15)),
   address ROW ( streetaddress VARCHAR(30),
                 city          VARCHAR(20),
                 postcode      VARCHAR(10) ),
   dob     DATE,
   age     INT )
   METHOD calc_age();

CREATE TABLE People OF Person
REF IS person_oid SYSTEM GENERATED;

CREATE TYPE Department (

                                              15
   depname VARCHAR(20),
   head REF(Person) scope People);

CREATE TABLE Departments of Department;


In the above declarations, note the following:


   • attribute head in type Department is a reference to a tuple in the People table;
   • the referenced table, People, has an attribute, person_oid, that stores the identi-
     fier of the tuple; this attribute is system generated and acts like the primary key;
   • it is also possible for the reference to be user generated or to be derived from the
     primary key.


We can first insert a tuple in Departments with NULL value for head and later modify it:


INSERT INTO Departments
  VALUES (`Sales', NULL);

UPDATE Departments
  SET head = (SELECT REF(p) FROM People AS P
                WHERE name=(`John', `Smith') )
  WHERE name = `Sales';


4.2.2 Complex Objects in Oracle


The following Oracle constructs support the features discussed in the previous subsec-
tion.


   • User-defined objects
   • %ROWTYPE
   • VARRAY
   • Nested tables
   • Object tables
   • REFs


We have seen examples of the use of user-defined objects, varying arrays, nested
tables and object tables in the practicals. Examples of the use of %ROWTYPE and REFs
are given below.

%ROWTYPE

                                           16
DECLARE
  CURSOR c1 IS
    SELECT * FROM Actor WHERE Nationality = `American';
  -- declare actor_rec to represent a row from the Actor table
  actor_rec c1%ROWTYPE;
BEGIN
  -- open the explicit cursor and fetch some data into actor_rec
  OPEN c1;
  FETCH c1 INTO actor_rec;
  DBMS_OUTPUT.PUT_LINE('Actor Name: ' || actor_rec.ActorName);
END;


%REFs

An Oracle REF is a logical pointer to a row object. In the following example, a table
containing two employees, where one employee manages the other, is created.


CREATE TYPE emp_person_typ AS OBJECT (
  name      VARCHAR2(30),
  manager REF emp_person_typ);

CREATE TABLE emp_person_obj_table of emp_person_typ;

INSERT INTO emp_person_obj_table VALUES (
  emp_person_typ(`John Smith', NULL));

INSERT INTO emp_person_obj_table
  SELECT emp_person_type(`Bob Jones', REF(e))
    FROM emp_person_obj_table e
    WHERE e.name = `John Smith';


Remark: Check the last INSERT.



4.3 Inheritance

Inheritance can be at the level of types or at the level of tables.

Inheritance of types allows one to define new data types




                                            17
4.3.1 Type Inheritance


                           Person


       Teacher                                Student



                      Student_Teacher



Fig 6: A multiple inheritance hierarchy of types

SQL 1999 does not support multiple inheritance. (Multiple inheritance can cause an-
mbiguities.)

The following SQL 1999 statements define a hierarchy of types with single inheritance:


CREATE TYPE Person AS (
   name    ROW ( fname VARCHAR(15),
                 lname VARCHAR(15)),
   address ROW ( streetaddress VARCHAR(30),
                 city          VARCHAR(20),
                 postcode      VARCHAR(10) ),
   dob     DATE,
   age     INT )
   METHOD calc_age();

CREATE TYPE Student
  UNDER Person
  ( course        VARCHAR(25),
    average_marks DECIMAL(5,2) );

CREATE TYPE Teacher
  UNDER Person
  (department VARCHAR(25),
   salary      INT       );


Student and Teacher inherit all the attributes and methods from Person and have ad-
ditional attributes of their own.

We could also have overridden the inherited method or defined new methods for the
subtypes.

                                         18
4.3.2 Table Inheritance


                           People


       Teachers                                 Students



                     Student_Teachers



Fig 7: A multiple inheritance hierarchy of tables

SQL 1999 does not support multiple table inheritance.

The following statements create a (single-inheritance) hierarchy of tables correspond-
ing to the above type hierarchy:


CREATE TABLE People OF Person;

CREATE TABLE Students OF Student
  UNDER People;

CREATE TABLE Teachers OF Teacher
  UNDER People;


The types of the subtables must be subtypes of the type of the parent table.

With the above declarations, every tuple in Students and Teachers tables is also im-
plicitly a tuple in People. Thus, a query on People,

SELECT .....    FROM People ...;.

will examine tuples in People, Students and Teachers tables. However, only the at-
tributes and methods of People may be accessed.

(A query that wishes to examine only the tuples in People must be phrased as follows:

SELECT .....    FROM ONLY People ...;.

Oracle supports single type inheritance.




                                           19
4.4 Rules

Rules protect the integrity of data and simplify maintenance

The general form is

on < event > when < condition > do < action >

DBMS watches for the occurrence of an event and if the specified condition is true,
executes the action.

The action is taken either just before or immediately after the event is processed.

Four basic types of rules:


   • Update-update rules,

   • Query-update rules

   • Update-query rules

   • Query-query rules


Update-update rules

The event and the action are both updates

Such rules are often called ’triggers’

Very useful for supporting data integrity; whenever an update causes integrity violation,
a corrective action is taken to restore data integrity.

SQL 1999 trigger syntax is (more or less) the same as the syntax of a Cloudscape or
Oracle trigger.


CREATE TRIGGER TriggerName
{ BEFORE | AFTER }
{ INSERT | DELETE | UPDATE [ OF ColumnName [, ColumnName]* ]
ON TableName
[ ReferencingClause ]
[ FOR EACH { ROW | STATEMENT } ]
[WHEN (condition) ]
TriggerAction


The referencing clause has the following syntax:



                                           20
REFERENCING
{
    { OLD | NEW } [ ROW ] [ AS ] CorrelationName |
    { OLD | NEW } TABLE [ AS ] Identifier
}


For example


CREATE TRIGGER Calc_TotalPrice
  AFTER UPDATE OF UnitPrice ON Shipment
  UPDATE Shipment SET TotalPrice=UnitPrice*Quantity;


keeps TotalPrice consistent in

SHIPMENT( ShipNum, SuppID*, CoffeeName, UnitPrice, Quantity, TotalPrice)

The rules can, of course, be more general. The action can be any update.

Query-Update Rules

Event – query; Action – update

This kind of rule is very useful for auditing. For example, when a user accesses the
Teacher table and retrieves salaries, we may wish to log the name of the user and the
timestamp in an audit table.

Update-Query Rules

Event – update; Action – query

These rules are useful for raising alerts. For example, when a withdrawl is made from
an account and it is overdrawn, we may wish to raise an alert which would cause an
e-mail to be sent.

Query-Query Rules

Event – retrieval; Action – retrieval

When TotalPrice is retrieved, the rule causes UnitPrice * Quantity to be returned.
Alternative mechanism for making TotalPrice consistent with UnitPrice and Quantity.

Finally, one can write hybrid rules which combine aspects of all four types of rules.

Only update-update rules are supported in SQL 1999.




                                           21
4.5 Further Remarks

 1. One event can fire many rules
    The order in which rules are fired determines the final state of the database.
    Generally, the order is determined by the system.
    Cloudscape triggers which respond to the same event are fired in the order in
    which they were defined.
    Clearly, the outcomes of such rules must not conflict.
    On an update to Mike’s salary, if one rule sets Jane’s salary to be the same as
    Mike’s and another rule sets it to twice Mike’s new salary, the outcome may be
    unpredictable.

 2. Chains of rules can cause infinite loops. Such circularity can be hard to spot in
    complex rule systems. The DBMS should spot these problems.

 3. Aborting the action part of a rule terminates the whole transaction
    Normally a rule action is executed in the same transaction that causes the event.
    The action part of the rule aborts if the transaction is aborted. This is often
    desirable.
    Consider a rule that synchronises Jane’s salary to Mike’s salary, i.e. it updates
    Jane’s salary whenever Mike’s salary is updated.
    If Mike’s salary is updated and the transaction is then aborted, the update to
    Jane’s salary should also be aborted.
    However, sometimes this is not desirable. For example, consider a rule which
    makes an entry in an audit table whenever salaries are updated.
    It is likely that even when the transaction which updates salaries fails, we would
    still like an entry in the audit table. In this case, the action should run in a different
    transaction (which always commits) from the user query.
    It is desirable to be able to specify whether the action would run in a separate
    transaction or the same transaction as the user statement. However, SQL 1999
    does not support this.




                                            22

				
DOCUMENT INFO