Rules in Active DBMS

Conventional (passive) database management systems (DBMS) solely serve as systems to store data in persistent data structures and to answer queries about the data stored. These passive DBMS do not actively perform any actions on their own.

Active DBMS on the other hand, use rules – mainly based on the ECA paradigm – to describe activities to be carried out by the system. Active DBMS have been defined as “database systems that respond automatically to events generated internal or external to the system itself without user intervention” [8].

Active DBMS monitor events and then react appropriately; hence, active databases present a reactive behavior (compared to the passive behavior of typical DBMS): they execute not only user transactions, but also the rules specified.

Many commercial relational systems like Oracle, DB2 Sybase offer this functionality, in the form of triggers (standardised in SQL-3); other examples for active relational DBMS are Ariel [40], Postgres [68] and Starbust [75]. There do also exist object oriented active databases such as HiPac [26], Sentinel [18] and EXACT [28].

In most relational active DBMS, the event-, condition- and action-components of the ECA rules are implemented as follows:

• Events are the beginning or the end of SQL INSERT, UPDATE or DELETE operations.

However, there exist implementations like [36] for Sybase or [48] for Sentinel that extend the scope of the DBMS by temporal and complex events.

• Conditions that determine whether the rule should be executed are represented as boolean SQL expressions. However, those conditions are to be evaluated not once but for all the tuples that may be affected by the operation. This means that some tuples may be changed by a trigger while others are left unchanged, because the condition did not apply to them.

• Actions to be taken are usually a sequence of SQL statements or whole database trans-actions; however, external programs and procedural attachments are also supported by many systems.

In the following we illustrate how to specify active rules as triggers in the commercial database system Oracle:

CREATE TRIGGER totalrevenue AFTER INSERT ON sales FOR EACH ROW WHEN(NEW.id IS NOT NULL)

UPDATE department

SET revenue = revenue + NEW.amount WHERE NEW.dept = department.id

The trigger is named totalrevenue and the event it reacts to is an INSERT operation on the table sales. The trigger is called after the INSERT is performed; another option would be to call the trigger before or instead of the insert operation. The keyword NEW represents each of the inserted tuples. In the example it is used to access the value of field amount of the new sales entries, which is then added to a field revenue of the department which has generated the sale(s).

There are several options for how the triggered event is related to the evaluation of the rule’s condition. There are three main possibilities for rule consideration [30]:

1. Immediate consideration: The condition is evaluated as part of the same transaction as the triggering event, and is evaluated immediately, either before, after or instead of executing the triggering event.

2. Deferred consideration: The condition is evaluated at the end of the transaction that included the triggering event. In this case, there could be many triggered rules waiting to have their conditions evaluated.

3. Detached consideration: The condition is evaluated as a separate transaction, spawned from the triggering transaction.

Similarly, there are several possibilities concerning the relationship between evaluating the rule condition and the execution of the rule action. The three possible options are again immediate, deferred and detached execution; most active systems use the first option, i.e. the action is immediately executed after the condition is successfully evaluated [30].

Besides the reactive behavior described above, modern database systems are able to capture and enforce another type of rules, i.e. integrity constraints, which have been laid out in Section 4.2.4. Constraints are declarations of conditions about the database that must remain true.

These include attributed-based, tuple-based and referential integrity constraints. The database system checks for the violation of the constraints on actions that may cause a violation and aborts the action accordingly. Below we briefly illustrate those constraints:

• Constraints on attributes: Database systems allow to attach constraints to the fields definitions of tables. For instance, a modifier not null may be used to disallow NULL values for the defined attribute; the unique and primary key modifier force the field value for each tuple to be unique. Here an example of table definition that puts several constraints on the attributes of the table:

CREATE TABLE employee(

id INTEGER NOT NULL PRIMARY KEY, name VARCHAR (5) NULL,

projects SMALLINT NOT NULL DEFAULT 0 )

The id value has to be unique and must not be NULL, the name may be NULL and the projectsvalue must not be NULL but has to be 0 (zero) as a default value.

• Constraints on tables: Some database systems allow to create constraints that go beyond the scope of single attribute constraints but rather may span over multiple attributes. For this purpose, the CHECK clause is used. In the example below, a constraint on the table employeeis introduced which enforces that every employee is associated to precisely one department of the organization:

CREATE TABLE employee(

id NUMERIC(4) PRIMARY KEY, dept VARCHAR (5)

CHECK( dept IS NOT NULL AND 1 = (SELECT COUNT(*)

FROM departments AS d WHERE d.id = dept)), )

• Assertions on the data model : to allow constraints with an even wider scope – spanning over the whole data model – the CREATE ASSERTION construct is provided by the SQL.

The example below tells the DBMS to ensure the (overly simplified) policy that there must always be more projects than project managers in the organization:

CREATE ASSERTION haveProjects (

CHECK ((SELECT COUNT(*) FROM projectmanger) <

(SELECT COUNT(*) FROM projects)) )

• Referential integrity constraints: Another very popular type of constraint rules in databases are referential integrity constraints, which are enforced on so called “foreign keys”, i.e. attributes whose values refer to keys of other (associated) tables. These con-structs allow to define rules which tell the DBMS how to behave if a referenced value changes or gets deleted. In the example below, we tell the DBMS that the field dept of the table employee needs always be kept in sync with the corresponding value of the department’s id:

CREATE TABLE EMPLOYEE(

id NUMERIC(4) PRIMARY KEY, dept VARCHAR (5) NOT NULL,

FOREIGN KEY (dept) REFERENCES departments(id) ON UPDATE CASCADE

)

In addition to the (integrity) constraints illustrated above, many relational DBMS support the creation of relational views, which can be seen as a (restricted) kind of derivation rule.

For instance, to derive all accounting clerks from the organization’s workforce, a view can be defined as follows:

CREATE VIEW accountingclerk ( SELECT * FROM employee WHERE dept=’acct’)