Active database concepts

Introduction

This unit introduces database concepts for some of the common features that are needed by advanced applications and are being used widely.
We will cover active rules that are used in active database applications.

• This concept comes with database trigger. A database trigger is a procedural code that is automatically executed in response to certain events.
• Triggers are fired when data is modified.
• Triggers are existed in early versions of SQL specifications and now are available with SQL-99 and later standards.

Generalized Model for Active Databases and Oracle Triggers

• The model that has been used to specify active database rules is referred to as the Event-Condition-Action (ECA) model. A rule in the ECA model has three components:
• The event(s) that triggers the rule:These events are usually database update operations that are explicitly applied to the database.
• The condition that determines whether the rule action should be executed: Once the triggering event has occurred, an optional condition may be evaluated. If no condition is specified, the action will be executed once the event occurs. If a condition is specified, it is first evaluated, and only if it evaluates to true will the rule action be executed.
• The action to be taken: The action is usually a sequence of SQL statements, but it could also be a database transaction or an external program that will be automatically executed.

consider some examples to illustrate these concepts.

Each employee having a name (Name), Social Security number (Ssn), salary (Salary), department to which they are currently assigned (Dno, a foreign key to DEPARTMENT), and a direct supervisor  Supervisor_ssn, a (recursive) foreign key to EMPLOYEE).

For this example, we assume that NULL is allowed for Dno, indicating that an employee may be temporarily unassigned to any department. Each department has a name (Dname), number (Dno), the total salary of all employees assigned to the department (Total_sal), and a manager (Manager_ssn, which is a foreign key to EMPLOYEE).

Notice that the Total_sal attribute is really a derived attribute, whose value should be the sum of the salaries of all employees who are assigned to the particular department.

Maintaining the correct value of such a derived attribute can be done via an active rule. First we have to determine the events that may cause a change in the value of Total_sal, which are as follows:

1. Inserting (one or more) new employee tuples

2. Changing the salary of (one or more) existing employees

3. Changing the assignment of existing employees from one department to another

4. Deleting (one or more) employee tuples

In the case of event 1, we only need to recompute Total_sal if the new employee is immediately assigned to a department—that is, if the value of the Dno attribute for the new employee tuple is not NULL (assuming NULL is allowed for Dno).Hence, this would be the condition to be checked. A similar condition could be checked for event 2 (and 4) to determine whether the employee whose salary is changed (or who is being deleted) is currently assigned to a department. For event 3, we will always execute an action to maintain the value of Total_sal correctly, so no condition is needed (the action is always executed).

The action for events 1, 2, and 4 is to automatically update the value of Total_sal for the employee’s department to reflect the newly inserted, updated, or deleted employee’s salary. In the case of event 3, a twofold action is needed: one to update the Total_sal of the employee’s old department and the other to update the Total_sal of the employee’s new department.

The four active rules (or triggers) R1, R2, R3, and R4—corresponding to the above situation.

Design and Implementation Issues for Active Databases

• The first issue concerns activation, deactivation, and grouping of rules. In addition to creating rules, an active database system should allow users to activate, deactivate, and drop rules by referring to their rule names. A deactivated rule will not be triggered by the triggering event. 
• This feature allows users to selectively deactivate rules for certain periods of time when they are not needed. The activate command will make the rule active again. The drop command deletes the rule from the system. Another option is to group rules into named rule sets, so the whole set of rules can be activated, deactivated, or dropped. It is also useful to have a command that can trigger a rule or rule set via an explicit PROCESS RULES command issued by the user.
• The second issue concerns whether the triggered action should be executed before, after, instead of, or concurrently with the triggering event. A before trigger executes the trigger before executing the event that caused the trigger. It can be used in applications such as checking for constraint violations. An after trigger executes the trigger after executing the event, and it can be used in applications such as maintaining derived data and monitoring for specific events and conditions. An instead of trigger executes the trigger instead of executing the event, and it can be used in applications such as executing corresponding updates on base relations in response to an event that is an update of a view.
• Another issue concerning active database rules is the distinction between row-level rules and statement-level rules. Because SQL update statements (which act as triggering events) can specify a set of tuples, one has to distinguish between whether the rule should be considered once for the whole statement or whether it should be considered separately for each row (that is, tuple) affected by the statement.

Potential Applications for Active Databases

• An active database may be used to monitor, say, the temperature of anindustrial furnace.
• used to enforce integrity constraints
• the automatic maintenance of derived data
• to maintain the consistency of materialized views