Title: Active Database Systems
1Active Database Systems
- INFS 770 Knowledge Management for E-Business
- Dr. Larry Kerschberg
2Passive 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.
3Active 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.
4Rules 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.
5Applications 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
6Taxonomy 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.
7Taxonomy 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.
8Taxonomy 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.
9Taxonomy 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)
10Taxonomy 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.
11Research 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
12Classification 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.
13Classification 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.
14Classification 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.
15Classification 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.
16Rule 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.
17Rule 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.
18Production 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
19OPS5 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.
20Acme 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
21OPS RETE Network
22POSTGRESStonebraker 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.
23POSTGRES 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
24POSTGRES 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.
25POSTGRES 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.
26POSTGRES 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.
27POSTGRES 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())
28POSTGRES 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.
29POSTGRES 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
30POSTGRES 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.
31POSTGRES 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
32Rule 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
33Rule 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 ()).
34Rule 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.
35Rule 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!
36Execution 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
37Starburst 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
38Starburst 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.
39HiPAC 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
40HiPAC 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
41HiPAC 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.
42HiPAC Coupling Modes