Active Database Systems

1
Active Database Systems

• INFS 770 Knowledge Management for E-Business
• Dr. Larry Kerschberg

2
Passive versus Active Databases

• Passive databases only execute explicit queries
  and transactions.
• Semantics of the domain are expressed in code
  associated with application programs.
• Constraints and semantics distributed across many
  application programs.
• Database may be updated without going through
  an application program, thereby making the
  database inconsistent with the rules governing
  the data.
• Users and application programs need to
  periodically poll the database to verify if
  anticipated conditions are valid.
• Could become a performance bottleneck
• The applications and knowledge-based systems
  interfacing with the database may be out of sync
  with one another.

3
Active Databases

• An active database is capable of monitoring the
  database state to
• Alert applications programs, expert systems, and
  more general knowledge-based systems,
• Update the database through rule-generated
  updates.
• Shared application semantics may be embedded
  within the database itself.
• Research has focused on integrating AI production
  systems with the database.
• Issues related to expressing the rules, their
  execution models, and rule optimization.

4
Rules in Active Databases

• Rules provide a uniform and powerful mechanism to
  express
• Integrity constraints and their enforcement
• Assertions about the database
• Triggers and alerters
• Corporate Policy Specification
• Configuration Management Policies
• Update semantics for shared objects with alerters
  to notify of changes to relevant objects
• Performance monitoring for 1) load balancing, 2)
  query optimization, and database reorganization
• Usage monitoring for knowledge discovery
  regarding user profiles and preferences.

5
Applications for Active Databases

• Management Information Systems
• Integrity Constraint Enforcement,
• Alerters when certain database states have been
  reached, such as inventory reorder points,
  process control, workflow management
• Automatic control of complex processes
• Shutdown of critical processes
• Monitoring of critical life functions.
• Concurrent Engineering
• Simultaneous notification of changes to designs
• Business-to-Business Messaging and Workflow
  Coordination
• Factory Automation
• Network and System Management
• Defense Applications

6
Taxonomy of Research Issues

• Rules Languages
• Event Causes the rule to be triggered
• Condition is checked when the rule is triggered
• Action is executed when the rule is triggered
  and its condition is true.
• Events types of meaningful events
• Data modification insert, delete, update
• Data retrieval the SELECT statement in SQL,
  fetch in OODB.
• Time temporal events specifying when a rule
  should be triggered, e.g., January 1, 2000,
  repeated times, etc.
• Application-defined an application may define
  an event such as a user login, or
  high-temperature exceeded, or purchase order
  posted to marketplace.

7
Taxonomy of Research Issues

• Conditions
• Database predicates an SQL Where clause
  specifying a condition on the database state, for
  example, that the average of a set of values must
  be below a certain value.
• Restricted predicates - an active database might
  restrict its rule conditions to allow comparison
  operations but not aggregate operations or joins
  this is to improve performance of the system.
• Database queries the condition may be based on
  the result of a database query evaluation, for
  example if the query result is null, then the
  condition evaluates to true.
• Application procedures a rule condition might
  be specified as a call to procedure written in an
  application programming language. If the
  procedure returns a value true, then the
  condition is holds, and does not hold if the
  value returned is false.

8
Taxonomy of Research Issues

• Actions
• An action is executed when the rule is triggered
  and the condition is true.
• Data modification operations SQL insert,
  delete, update while an object system might allow
  rule actions to specify object creation, object
  deletion, method calls to modify objects.
• Data retrieval operations SQL select
  operations, and oodb operations such as fetch or
  method invocation.
• Other operations rollback or commit for
  transactions, or granting or revoking privileges.
• Application procedures the rule action might be
  a call to a procedure written in an application
  programming language.

9
Taxonomy of Research Issues

• Rule Commands
• Create, delete, modify rules
• The study of the side-effects of changing rules
  is an area of research.
• Deactivate (disable), or activate (enable) rules.
• Rule Structuring
• Rule sets can be defined for different aspects of
  an application, and they can be activated based
  on the state of the application.
• Rule Priorities
• Several rules are triggered, so we need a
  mechanism to decide on the order in which they
  are executed.
• Rule Processing Granularity
• Instance versus set-at-a-time processing of
  tuples
• Conflict Resolution (See later slides on OPS5)

10
Taxonomy of Research Issues

• Rule Coupling Modes
• Immediate the triggered rule is executed
  immediately, within the transaction.
• Deferred rule action execution deferred until
  the transaction commits.
• Thus the event may occur during the transaction,
  then the condition is evaluated at the end of the
  transaction.
• Such rules are used to enforce integrity
  constraints, so they can check to ensure that
  that transaction does not violate integrity
  constraints.
• Decoupled executes in a separate transaction,
  after the triggering transaction
• Dependent decoupled indicates that triggered rule
  will only execute if the original transaction
  commits.
• Independent decoupled indicates that a separate
  transaction is spawned regardless of whether the
  original transaction commits.
• Causality among transactions requires that the
  spawned transaction is later in the serialization
  ordering.

11
Research Issues

• Rule Paradigm Event Condition Action
• Event widget fails acceptance test
• Condition 30 failure rate over last 5 minutes
• Action begin
• shut off machine
• reroute job flow
• initiate diagnostics and repair
• end
• Execution Models
• When should rules be fired?
• Set-at-a-time or tuple-at-a-time execution
• Relationship to transaction processing in DBMS
• Relationship to AI production system processing

12
Classification of DBMS Rule Systems (1)

• on event event can be an update event or a
  retrieve event
• do action action can be an update or a
  retrieve.
• Emp (name, age, salary, dept, manager)
• Category 1 Update Event, Update Action
• on replace to EMP.salary where
• EMP.name Joe
• then do replace EMP (salary new.salary) where
• EMP.name Sam.
• Rule tells DBMS to watch for an event which
  updates Joes salary, and when this event occurs,
  the engine should perform the corresponding
  action, that is, propagate Joes salary to Sam.
• Forward propagation of updates is call
  forward-chaining.
• Starburst and POSTGRES in operational prototypes
  INGRES and Sybase in commercial systems, RPL,
  HiPAC and ARIEL for research prototypes.

13
Classification of DBMS Rule Systems (2)

• Emp (name, age, salary, dept, manager)
• Category 2 Update Event, Retrieve Action
• on replace to EMP.salary where
• EMP.name Joe
• do retrieve (new.salary).
• Rule acts as an alerter, notifying the user of
  Joes new salary whenever it is updated.
• HiPAC and POSTGRES support alerters.

14
Classification of DBMS Rule Systems (3)

• Emp (name, age, salary, dept, manager)
• Category 3 Retrieve Event, Retrieve Action
• on retrieve to EMP.salary where
• EMP.name Sam
• do instead retrieve (EMP.salary) where
• EMP.name Joe.
• Rule tells DBMS to look for an event which
  retrieves Sams salary, and when this event
  occurs, the action to be performed is to retrieve
  Joes salary instead.
• Sams salary is unimportant because Joes is
  retrieved instead.
• Backward chaining of retrievals can be
  implemented in this way.
• Virtual or derived views can be maintained
• POSTGRES implements this form of retrieval NAIL
  and LDL support recursive types of queries.

15
Classification of DBMS Rule Systems (4)

• Emp (name, age, salary, dept, manager)
• Category 4 Retrieve Event, Update Action
• on replace to EMP.salary
• then do append to AUDIT
• (name current.name,
• salary current.salary, user user ()).
• Rule supports the concept of an Audit Trail.
• Every access to an employees salary
  automatically appends a new record to the AUDIT
  table documenting the employee name and salary as
  well as the user who requested the information.
• POSTGRES supports this operation.

16
Rule System Implementation

• Brute Force
• Maintain a list of of all the rules affecting
  each table in the database
• Each individual update is matched against the
  conditions of rules.
• Discrimination Networks
• RETE and TREAT networks allow for efficient
  indexing and support large collections of rules.
• Marking
• The system uses record marking
• Each rule is processed against every database
  table and every record is marked with a flag
  indicating which rule (or rules) to be awakened.
  
• Each record is marked with one or more rules to
  be awakened if one or more events occur to this
  record.

17
Rule System Implementation

• Query Rewrite
• Popular technique in backward-chaining systems
• Technique available in POSTGRES, LDL, and NAIL.
• Consider the query
• on retrieve to EMP.salary where
• EMP.name Sam
• do instead retrieve (EMP.salary) where
• EMP.name Joe.
• and the command
• retrieve (EMP.salary) where
• EMP.name Sam.
• can be rewritten to
• retrieve (EMP.salary) where EMP.name Sam.

18
Production System Paradigm

• Integration of Production Rules alá OPS5 with
  database
• Global Database (Working Memory)
• Productions (Rules) (Production Memory)
• If C1 ??C2 ??C3?? Cn Then A1 ? A1 ? A1?? Am
• (Left Hand Side) (Right Hand Side)
• System state expressions in Working Memory.
• Control fire a production whose LHS is
  satisfied.
• Recognize-Act Cycle
• do forever
• recognize match Left-Hand-Side (LHS) of rule
  with data in DB
• if matched, add rule to candidate set
• conflict resolution select one rule from
  candidate set
• act execute RHS of selected rule.
• end

19
OPS5 Conflict Resolution

• Specificity ordering If the conditions of one
  triggering rule are a superset of the conditions
  of another triggering rule, use the rule with the
  superset because it is more specialized to the
  current situation.
• Rule ordering Arrange rules in a priority list.
  The triggering rule appearing earliest in the
  list will be fired.
• Data ordering Arrange all possible aspects of
  the situation in a priority list. The triggering
  rule having the highest priority condition has
  the highest priority.
• Size ordering The rule with the most conditions
  to be satisfied is the one triggered.
• Recency ordering The most recently used rule
  takes precedence. It could also be the least
  recently used.
• Context limiting Separation of rules into
  groups that apply to a specific context. The
  context can be deactivated or activated within
  productions.

20
Acme Airline Active Database

• Relations Employee (id, category), Certified
  (id, aircraft_type)
• P1 If there is a mechanic certified to
  repair 727s, do action-1.
• (p P1 (employee ?id ltigt ?category mechanic)
• (certified ?id ltigt ?aircraft_type 727)
• ? ? action-1
• P2 If there is a pilot certified to fly
  747s, do action-2.
• (p P2 (employee ?id ltigt ?category pilot )
• (certified ?id ltigt ?aircraft_type 747)
• ? ? action-2
• P3 If there is a pilot not certified to fly
  747s, do action-3.
• (p P3 (employee ?id ltigt ?category pilot )
• (certified ?id ltigt ?aircraft_type 747)
• ? ? action-3

21
OPS RETE Network
22
POSTGRESStonebraker Rowe (UCBerkeley)

• Goals of POSTGRES project
• Provide support for complex objects, time-varying
  data,
• User extensibility for data types, operators and
  access methods,
• Facilities for active databases (i.e., alerters,
  triggers, and rules) and inference (forward- and
  backward-chaining),
• Make as few changes to the relational model as
  possible.
• POSTGRES Data Model Relational Model extended
  to support semantic modeling constructs,
• Abstract Data Types,
• Data of type Procedure stored as a field in a
  relation, and
• Active Database Facilities
• Alerters send messages to application programs
• Triggers initiate processing when conditions
  are satisfied, and may execute actions to insert,
  delete or modify information in tuples.

23
POSTGRES Data Model

• Data Model Features
• Support for primary keys, Inheritance of both
  data and procedures, Attributes that reference
  tuples in other relations,
• Virtual columns in a relation.
• POSTQUEL POSTGRES Query Language
• Relation-valued expressions may appear any place
  that a relation could appear in QUEL (the INGRES
  query language).
• Transitive Closure and execute commands have been
  added,
• Set operators have been included.
• POSTGRES Alerters and Triggers
• Example Whenever a tuple is inserted or changed
  for employees of category pilot, certified to fly
  a 747, then an application program is alerted.
• range of c id certified, range of e is employee
• retrieve always (e.id, e.name)
• where e.category pilot and e.id c.id and
  c.aircraft_type 747

24
POSTGRES Rule Language

• The POSTGRES syntax is
• define tuple rewrite rule rulename is
• on event to object
• where qualification
• do instead action
• where
• event can be any operation caused by a POSTQUEL
  command such as retrieve, append, delete, or
  replace.
• object can be either a relation or a column of a
  relation.
• qualification is any POSTGRES predicate.
• action may be a collection of POSTGRES commands
  or the special command abort.
• Optional tuple, rewrite specify which rule
  implementation to use, TLS or QRS.
• If keyword instead not present then both the rule
  action and triggering event are executed.

25
POSTGRES Rule Processing

• Backward Chaining
• If rule action is retrieve then the event must by
  retrieve, and the rule is a backward chaining
  rule.
• Similar to Prolog or deductive databases as a way
  to derive data goal-directed reasoning.
• Object may be either a relation or a column
• If object is a relation, then the query generates
  a set of tuples as the result of the retrieve
  action called view-type backward chaining rules.
• If keyword instead is present, the tuples stored
  in the retrieved relation are ignored, and only
  the tuples generated by the action of the rule
  are retrieved. Otherwise both set of tuples are
  retrieved.
• If object is a column of a relation, then the
  rule will calculate the values of that column.
• Rule Processing
• TLS Tuple Level System processes on a
  tuple-by-tuple basis
• QLS Query Rewrite System uses query
  modification to generate the query.

26
POSTGRES Rule Processing

• Forward Chaining If the rule action is not
  retrieve, then the rule is a forward chaining
  rule.
• retrieve commands are allowed in rule actions
  only for rules whose event is retrieve.

27
POSTGRES Query Examples

• Q1 Append to relation TEMP the users who try to
  delete employee tuples, but no tuples will be
  deleted from EMP
• define rule an_instead_rule is
• on delete to EMP
• do instead append TEMP(username user())
• Q2 This will append to TEMP as above, but will
  not prevent the deletion of tuples from EMP
• define rule not_an_instead_rule is
• on delete to EMP
• do append TEMP(username user())

28
POSTGRES Query Examples

• Q3 Examples of the keywords CURRENT and NEW
  denoted the current attribute value versus the
  new value.
• This rule is activated every time the salary of
  an employee is increased by more than 10 the
  action is to abort the transaction.
• define rule r1 is
• on replace to EMP.salary
• where New.salary gt 1.1CURRENT.salary
• do abort
• However, if we wish to disallow the update, but
  let the transaction finish, then the rule would
  be
• define rule r2 is
• on replace to EMP.salary
• where New.salary gt 1.1CURRENT.salary
• do instead
• In this case, the event which activated the rule,
  should not be processed.

29
POSTGRES Query Examples
An update to Johns salary will trigger rule r3,
and Freds salary will also be updated. This new
update will trigger rule r4 which will triple
Mikes salary. This is an example of rule
propagation.

• Rule Propagation Forward Chaining
• define rule r3
• on replace to EMP.salary
• where CURRENT.name John
• do replace E(Salary NEW.salary)
• from E in EMP
• where E.name Fred
• define rule r4
• on replace to EMP.salary
• where CURRENT.name Fred
• do replace E(Salary 3NEW.salary)
• from E in EMP
• where E.name Mike

30
POSTGRES Query Examples

• Rule Propagation Backward Chaining
• define rule r5
• on retrieve to EMP.salary
• where CURRENT.name Fred
• do instead retrieve (salary E.salary)
• from E in EMP
• where E.name John
• define rule r6
• on retrieve to EMP.salary
• where CURRENT.name Mike
• do instead retrieve (salary 3E.salary)
• from E in EMP
• where E.name Fred

Comments on retrieve event rules Rule r5 will be
activated every time a user attempts to retrieve
Freds salary, but the action retrieves Johns
salary. Rule r6 will be activated when Mikes
salary is requested, but 3 times Freds salary
will be presented. Note however, the r6s
retrieval of Freds salary will activate rule r5,
so we have an example of column-type backward
chaining.
31
POSTGRES Query Examples
Rule r7 This rule states that whenever tuples
are requested from HIGH_SAL_EMP, employees having
salary greater than 100K are retrieved from
EMP. Rule r8 This rule states that whenever a
department is deleted so too are all of its
employees. (Very strong consequences of a
reorganization).

• View-type Backward Chaining
• define rule r7
• on retrieve to HIGH_SAL_EMP
• do instead retrieve (EMP.all)
• where EMP.salary gt 100,000
• Forward Chaining Rule
• define rule r8
• on delete to DEPT
• do delete EMP
• where EMP.dept CURRENT.dname

32
Rule System Semantics (1)
Rule on EMP class on replace to EMP.salary
where EMP.name Jones then do replace EMP
(salary new.salary) where EMP.name
Brown Together with User Command replace EMP
(salary 1000) where EMP.dept shoe. Note
that the command retrieves a set of EMP tuples
and the value of Browns salary will have
different values depending on when rule is
activated and fired.

• Dimensions for rule semantics
• Time of Wake-up
• Immediately (POSTGRES)
• End of command (no nested activations)
• End of transaction (Starburst)
• After the end of transaction (HiPAC)
• Transaction Context
• Chaining Mechanism
• Backward Chaining
• Forward Chaining
• Semantics of Backward Chaining
• Ordering of rules

33
Rule System Semantics (2)

• Transaction Context
• Rule can be awakened in the same transaction
• User can retrieve a salary and then abort the
  transaction, thereby audited.
• A different transaction
• Rule should be activated once a transaction
  commits.

on replace to EMP.salary then do append to AUDIT
(name current.name, salary
current.salary, user user ()).
34
Rule System Semantics (3)

• Forward Chaining Rule versus Backward Chaining
  Rule

on replace to EMP.salary where EMP.name
Jones then do replace EMP (salary
new.salary) where EMP.name Brown
on retrieve to EMP.salary where EMP.name
Brown then do instead retrieve (EMP.salary)
where EMP.name Jones.
Comments Backward Chaining case If Jones is
deleted from the database, the Browns salary
will become Null. Forward Chaining case If
Jones is deleted Brown will still have the last
salary that was assigned.
35
Rule System Semantics (4)

• Rule Ordering
• the ordering of the firing of rules will
  determine the value of the final result.
• Consider the rules to the right.
• Case 1
• If Jones is employed in the shoe department,
  then both rules will determine Browns salary.
• Case 2
• If Brown is not in the shoe department, then
  his salary will be determined by the rule that
  fired last.

on replace to EMP.salary where EMP.name
Jones then do replace EMP (salary
new.salary) where EMP.name Brown on
replace to EMP.salary where EMP.dept
shoe then do replace EMP (salary 5000)
where EMP.name Brown.
Need to have an ordering mechanism for deciding
how rules should be fired!
36
Execution Models

• Rule firing options Given two rules A and B
• 1) One rule is fired, and the other ignored
• supported by exception mechanism of POSTGRES
• 2) Both rules fire in random order
• 3) Both rules fire, but in a predetermined order.
• BEFORE and AFTER syntax of STARBURST rule system
• Rule firing possibilities
• Activate only A
• Activate only B
• Activate B then A
• Activate A then B
• Activate both in random order
• chaos for the database administrator

37
Starburst Rule System

• Starburst is an extensible relational DBMS
  prototype developed at IBM Almaden Research
  Center.
• Rule Syntax based on SQL
• create rule name on table
• when transition predicate
• if condition
• then list of actions
• precedes list of rules
• follows list of rules
• Rules are set-oriented
• Triggering conditions based on arbitrary set of
  changes
• Actions perform the set of changes
• Conditions and Actions refer to sets of changes
• Rule processing fully integrated with query and
  transaction processing

38
Starburst Rule System

• Operations on rules drop, alter, deactivate,
  activate
• Transition predicate triggering operations such
  as inserted, deleted, updated, updated(c1, ...,
  cn)
• Condition arbitrary SQL predicate
• Actions can be any DB operationinsert, delete,
  update, select, rollback, create table, etc.
• Precedes and follows rule ordering for conflict
  resolution

create rule name on table when transition
predicate if condition then list of actions
precedes list of rules follows list of rules
Rules processed at rule processing points (rpp)
Automatic rpp at end of transaction, others
user-specifiedTransitions Initial
triggering transition is user-generated
Rules create additional transitions.
39
HiPAC Rule System

• Based on Object-Oriented Data Model (DAPLEX)
• Rules are objects Class Rule Subclass of Object
• Rules can be created, read, modified, and deleted
  like other objects, and are subject to
  concurrency control and access control.
• Concepts for organizing a large rule base
  subclasses, collection (sets, sequences),
  attributes, relationships to other objects
• Mechanisms for focusing on a subset of the rule
  base
• Class Flight_Rule Subclass of Rule   with
  additional attributesApplicability,
  Definition_Date
• On Takeoff,Do Disable r in Flight_Rule where
  Applicability(r) on ground Enable r
  in Flight_Rule where Applicability(r)
  after-take-off and Definition_Date(r) after
  1/1/94

40
HiPAC Events

• Primitive Events
• Database operations generic (read, insert,
  delete, update) type-specific operations Move
  (Ship, old_Pos, new_Pos) transaction operations
  Abort, end-of-transaction, BOT
• Temporal
• absolute at 192000 hours on 4/12/1994
• relative 30 seconds after Takeoff
• periodic at 192000 hours every Tuesday
• External
• Messages/signals from humans, devices, or
  applications processes
• Events may be instance-oriented and set-oriented
• Events are themselves objects composite events
• Event signals each event occurrence is signaled

41
HiPAC Event-Condition-Action Paradigm

• Conditions consist of two parts
• A collection of queries
• condition is true if all queries return non-empty
  or true answers
• results are saved and passed to the action
• A coupling mode
• specifies when condition is evaluated relative to
  transaction in which the event was signaled
• Actions consist of two parts
• A program consisting of database operations, rule
  operations, event signals, or calls to
  application procedures
• A coupling mode
• specifies when action is executed relative to
  transaction in which the condition was evaluated.

42
HiPAC Coupling Modes