Chapter 24

2. Chapter 24 Enhanced Data Models for Advanced Applications

3. Active Database Concepts and Triggers • Generalized Model for Active Databases and Oracle Triggers • Design and Implementation Issues for Active Databases • Examples of Statement-Level Active Rules in STARBURST • Potential Applications for Active Databases • Triggers in SQL-99

4. Generalized Model for Active DB and Oracle Triggers • Event-Condition-Action (ECA model) • Event : trigger the rule • Usually database update • Condition : determines whether the rule action should be executed • No condition – executed once the event occurs • With condition – executed only when it is true • Action : usually a sequence of SQL statements

5. Generalized Model for Active DB and Oracle Triggers • Example (fig. 24.1) • Assume that null is allowed for DNO • TOTAL_SAL is a derived attribute • Events • Inserting new employee tuples • Changing the salary of existing employees • Changing the assignment of existing employees from one department to another • Deleting employee tuples

6. Generalized Model for Active DB and Oracle Triggers • Condition • WHEN clause represents its condition • DNO for new tuple is not null (1) • An employee whose salary is changed is currently assigned to a department (2 and 4) • No condition, always execute it (3) • Action • Defined in PL/SQL block • Automatically update TOTAL_SAL (1,2,& 4) • Update TOTAL_SAL on the old department and later, the new department

7. Generalized Model for Active DB and Oracle Triggers • CREATE TRIGGER : trigger name • AFTER : the rule will be triggered after the events occur • ON : specifies the relation on which the rule is specified • FOR EACH ROW : the rule will be triggered once for each row that is affected by event • WHEN : specify any conditions checked after the rule is triggered but before the action is executed • NEW and OLD

8. Generalized Model for Active DB and Oracle Triggers • Examples R1, R2, R3, and R4 • Row-level trigger vs. statement-level trigger • Row-level trigger • Has FOR EACH ROW keyword • The rule is triggered separately for each tuple • Statement-level trigger • Doesn’t have FOR EACH ROW keyword • The rule would be triggered once for each triggering statement

9. Generalized Model for Active DB and Oracle Triggers • For example (statement-level trigger), • UPDATE EMPLOYEESET SALARY = 1.1 * SALARYWHERE DNO = 5; • Another example (See fig. 24.2 (b)) • To check whenever an employee’s salary is greater than the salary of his/her supervisor • We can call external procedure INFORM_SUPERVISOR, which notify the supervisor

10. Design and Implementation Issues for Active Databases • How rules are designed and implemented • In addition to creating rules, activate, deactivate, and drop rules are necessary. • Deactivated rule will not be triggered by the triggering event • The activate command will make the rule active again • The drop command deletes the rule from the system

11. Design and Implementation Issues for Active Databases • When triggered action should be executed • Before, after, or concurrently with the triggering event. • Most commercial systems are limited to one or two of the options that we will now discuss

12. Design and Implementation Issues for Active Databases • Condition evaluation = rule consideration • Options for how the event is related to the evaluation of the rule’s condition • Immediate consideration : the condition is evaluated as part of the same transaction as the triggering event, and is evaluated immediately • Evaluate the condition before executing the triggering event • Evaluate the condition after executing the triggering event • Evaluate the condition instead of executing the triggering event • Deferred consideration : The condition is evaluated at the end of the transaction that included the triggering event. • Detached consideration : The condition is evaluated as a separate transaction, spawned from the triggering transaction

13. Design and Implementation Issues for Active Databases • Options for the relationship between evaluating the rule condition and executing the rule action • Immediate, deferred, and detached • Most active systems use the immediate option • Oracle • uses the immediate consideration (with before/after option) and immediate execution model. • Row-level rules and statement-level rules • STARBURST system uses the deferred consideration • Uses the deferred consideration • Only statement-level rules • One of the difficulties that prevent the active rules being widespread is that there are no easy-to-use techniques for designing, writing, and verifying rules. (see fig.24.4)

14. Examples of Statement-Level Active Rules in STARBURST • STARBURST, IBM project, experimental DBMS • In STARTBURST, only statement-level rules are allowed (see fig. 24.5) • CREATE RULE : rule name • ON : the relation on which the rule is specified • WHEN : to specify the events • IF : to specify any conditions (optional) • THEN : to specify the action

15. Examples of Statement-Level Active Rules in STARBURST • Events : inserted, deleted, and updated • INSERTED, DELETED, NEW-UPDATED, and OLD-UPDATED are used to refer to 4 transition tables that include • The newly inserted tuples, • The deleted tuples, • The updated tuples after they were updated, • And the updated tuples before they were updated, respectively

16. Examples of Statement-Level Active Rules in STARBURST • In statement-level semantics, since the rule designer only refer to the transition tables as a whole and the rule is triggered only once, so the rule must be different from the row-level semantics • For example, because multiple employee tuples may be inserted in a single insert statement, we have to check if at least one of the newly inserted employee tuples is related to a department

17. Examples of Statement-Level Active Rules in STARBURST • In R1S (fig. 24.5) • EXISTS (SELECT * FROM INSERTED WHERE DNO IS NOT NULL) • WHEN (NEW.DNO IS NOT NULL) • Examples in figure 24.5 • STARBURST uses deferred consideration, which defer evaluation of condition and execution after the transaction ends

18. Examples of Statement-Level Active Rules in STARBURST • Example (fig. A)

19. Potential Applications for Active Databases • Potential applications of active rules • Notification • Monitoring system • Enforce integrity constraints • GPA alert and course prerequisites • Business rule • Salary of employee can’t exceed that of manager • Maintenance of derived data • Maintain the derived attribute TOTAL_SAL

20. Triggers in SQL-99 • Similar to 24.1.1 • Basic events : INSERT, DELETE, and UPDATE • UPDATE • One may specify the attributes to be updated • FOR EACH ROW and FOR EACH STATEMENT • Can specify particular tuple variable names for the old and new tuples (fig. 24.6) • Differences between row-level and statement-level trigger in REFERENCING clause

21. Temporal Database Concepts • Time Representation, Calendars, and Time Dimensions • Incorporating Time in Relational Databases Using Tuple Versioning • Incorporating Time in Object-Oriented Databases Using Attribute Versioning • Temporal Querying Constructs and the TSQL2 Language • Time Series Data

22. Multimedia Databases • Introduction to Spatial Database Concepts • Introduction to Multimedia Database Concepts

23. Introduction to Deductive Databases • Overview of Deductive Databases • Prolog/Datalog Notation • Datalog Notation • Clausal Form and Horn Clauses • Interpretation of Rules • Datalog Programs and Their Safety • Use the Relational Operations • Evaluation of Nonrecursive Datalog Queries

24. Summary