Active database

This is the content for advance database management system for undergraduate level.

1. ACTIVE DATABASES ADBMS BY: DABBAL S. MAHARA 2018 1

2. Conventional Databases Passive database Traditional database management systems (DBMSs) are passive in the sense that commands are executed by the database (e.g., query, update, delete) as and when requested by the user or application program. It has passive update principle. That is, client controls DBMS updates. However, some situations cannot be effectively modeled by this pattern. 2

3. Conventional Databases Passive Transaction Model 3

4. Problem with Traditional Database Systems: Example As an example, consider a railway database where data are stored about trains, timetables, seats , fares , and so on, which is accessed by different terminals . In some circumstances (e.g., public holidays, cultural events) it may be beneficial to add additional coaches to specific trains if the number of spare seats a month in advance is below a threshold value. How to administer this situation in the passive database? 4

5. Resolution of the Situation Two options are possible: Add the additional monitoring functionality to all booking programs so that the preceding situation is checked each time a seat is sold . However , this approach leads to the semantics of the monitoring task being distributed, replicated, and hidden among different application programs. 5

6. Resolution of the Situation The second approach relies on a polling mechanism that periodically checks the number of seats available. T he difficulty in this approach stems from ascertaining the most appropriate polling frequency . If too high, there is a cost penalty. If too low, the reaction may be too late (e.g., the coach is added, but only after several customers have been turned away). 6

7. Some more examples of real world problem not well suited for passive update principle: Inventory control - reordering items when quantity in stock falls below threshold . Travel waiting list - book ticket as soon as right kind is available Stock market - Buy/sell stocks when price below/above threshold 7

8. Active Databases Active databases support the preceding application by moving the reactive behavior from the application (or polling mechanism) into the DBMS. Active databases are thus able to monitor and react to specific circumstances of relevance to an application . An active database system must provide a knowledge model (i.e., a description mechanism ) and an execution model (i.e., a runtime strategy) for supporting this reactive behavior. 8

9. Active Databases General Idea: Active DBMS provides : Regular DBMS primitives + definition of application-defined situations + triggering of application-defined reactions It means: being able to react automatically to situations in the database allowing the specification and implementation of reactive behavior Active DBMSs Embed situation-action rules in database Support many functionalities: E.g. Integrity control, derived data, change notification Active DBMS functionality commercially available in SQL:99 as triggers 9

10. Active Databases Recognize predefined situations in database Trigger predefined actions when situations occur Actions are usually database updates Active Rules – rules that are automatically triggered by events in the database. 10

11. Triggers Triggers is a concept that is technique for specifying certain types of active rules in the database. A data base that has a set of associated triggers is called an active data base. Trigger is like a procedure that is automatically invoked by the DBMS in response to specified changes to data base. Trigger is like a ‘Daemon that monitors a data base, and is executed when the data base is modified in a way that matches the event specification 11

12. Trigger Parts Triggers work in ECA model. It has three parts: Event : A change to data base that activates the trigger Condition : A trigger restriction specifies a Boolean (logical) expression must be TRUE for the trigger to fire Action : A procedure that is executed when the trigger is activated. Similar to stored procedures, a trigger action can contain PL/SQL statements 12

13. Types of Triggers An SQL statement may change several rows. Apply action once per SQL statement. Apply action for each row changed by SQL statement. Row Triggers A row trigger is fired each time the table is affected by the triggering statement. If a triggering statement affects no rows, a row trigger is not executed at all. Statement Triggers : A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects (even if no rows are affected) 13

14. Trigger Timings Actions may apply before or after the triggering event is executed. Before Trigger Execute the trigger action before the triggering statement. Eliminate unnecessary processing of the triggering statement . After Trigger AFTER triggers are used when you want the triggering statement to complete before executing the trigger action 14

15. Availability Triggers included in SQL 1999 (SQL 3) Not in earlier standards. Included much earlier in most products: Oracle, Sybase, DB2 As a consequence syntax may differ from the standard. 15

16. Creating a Trigger: Syntax: CREATE [OR REPLACE ] TRIGGER trigger_name {BEFORE | AFTER } { INSERT [OR] | UPDATE [OR] | DELETE } [ OF col_name ] ON table_name [ FOR EACH ROW] DECLARE Declaration-statements BEGIN Executable-statements END trigger_name ; 16

17. E-C-A Model Most active databases support a knowledge model that uses rules with three components: an event, a condition , and an action. The event part of a rule describes a happening to which the rule may be able to respond . The condition part of the rule examines the context in which the event has taken place. The action describes the task to be carried out by the rule if the relevant event has taken place and the condition has evaluated to true. 17

18. Event An event is something that happens at a point in time. Possible alternatives : structure operation ( insert , update , access ); behavior invocation (the message display is sent to an object of type widget ); transaction ( abort, commit, begin- t ransaction); e xception ( an attempt to access some data without appropriate authorization); c lock ( the first day of every month) ; External (the temperature reading goes above 30 degrees) 18

19. Condition The condition indicates whether rule action should be executed. In ECA-rules, the condition is generally optional . Once the triggering event has occurred , the condition may be evaluated. If condition evaluates to be true, the rule action will be executed. If no condition is specified, the action will be executed once the event occurs. 19

20. Action The range of tasks that can be performed if the rule condition is evaluated to be true. It is usually a sequence of SQL statements. But actions may: Perform some behavior invocation within the database or an external call, inform the user or system administrator of some situation, abort a transaction, take some alternative course of action using do-instead 20

21. Execution Model of Active Databases The execution model specifies how a set of rules is treated at runtime. Although the execution model of a rule system is closely related to aspects of the underlying DBMS (e.g., data model, transaction manager ), there are a number of phases in rule evaluation. 21

22. Execution Model The signaling phase refers to the appearance of an event occurrence caused by an event source. The triggering phase takes the events produced thus far, and triggers the corresponding rules. The evaluation phase evaluates the condition of the triggered rules. The scheduling phase indicates how the rule conflict set is processed. The execution phase carries out the actions of the chosen rule instantiations. 22

23. Design and Implementation Issues for Active Databases An active database should allow users to make the following changes to triggers (rules ) Activate Deactivate Drop A deactivated rule will not be triggered by the event. This feature will allow user 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. 23

24. Design and Implementation Issues for Active Databases The second issue is whether the triggered action should be executed before, after or concurrently with the triggering event. The issue is whether the action being should be considered to be part of the same transaction that triggered the rule. An event can be considered in 3 ways Immediate consideration Deferred consideration Detached consideration 24

25. Design and Implementation Issues for Active Databases Immediate consideration Part of the same transaction and can be one of the following depending on the situation, Oracle uses this model Before After Instead of Deferred consideration Condition is evaluated at the end of the transaction Detached consideration Condition is evaluated in a separate transaction 25

26. Active Database Applications In the case of active rules , the following categories of application can be distinguished . ECA rules have been used: To support integrity constraints, In materialized views Maintenance of derived data C oordination of distributed computation T ransaction models A dvanced data modeling constructs A utomatic screen updating in the context of database change. 26

27. SQL>CREATE OR REPLACE TRIGGER derive_commission_trg 2 BEFORE UPDATE OF sal ON emp 3 FOR EACH ROW 4 WHEN ( new.job = 'SALESMAN') 5 BEGIN 6 : new.comm := : old.comm * (: new.sal /: old.sal ); 7 END; 8 / Example: Calculating Derived Columns Trigger name: derive_commission_trg Timing: BEFORE executing the statement Triggering event: UPDATE of sal column Filtering condition: job = ‘SALESMAN’ Target: emp table Trigger parameters: old, new Trigger action: calculate the new commission to be updated 27

28. Example: Counting Statement Execution Whenever an employee record is deleted from database, counter in an audit table registering the number of deleted rows for current user in system variable USER is incremented. SQL>CREATE OR REPLACE TRIGGER audit_emp 2 AFTER DELETE ON emp 3 FOR EACH ROW 4 BEGIN 5 UPDATE audit_table SET del = del + 1 6 WHERE user_name = USER 7 AND table_name = 'EMP’; 7 END; 8 / 28

29. SQL>CREATE OR REPLACE TRIGGER audit_emp_values 2 AFTER DELETE OR UPDATE ON emp 3 FOR EACH ROW 4 BEGIN 5 INSERT INTO audit_emp_values ( user_name , 6 timestamp, id, old_last_name , new_last_name , 7 old_title , new_title , old_salary , new_salary ) 8 VALUES (USER, SYSDATE, : old.empno , : old.ename , 9 : new.ename , : old.job , : new.job , 10 : old.sal , : new.sal ); 11 END; 12 / Example: Recording Changes Whenever some details for an employee are deleted or updated, both the previous and new details are recorded in an audit table to allow tracing the history of changes. An insert operation cannot be recorded with this trigger as old.empno has no value. 29

30. Example: Protecting Referential Integrity SQL>CREATE OR REPLACE TRIGGER cascade_updates 2 AFTER UPDATE OF deptno ON dept 3 FOR EACH ROW 4 BEGIN 5 UPDATE emp 6 SET emp.deptno = : new.deptno 7 WHERE emp.deptno = : old.deptno ; 8 END 9 / Whenever the department number changes, all employee records for this department will automatically be changed as well, so that the employees will continue to work for the same department. 30

31. Controlling Triggers using SQL Disable/Re-enable database trigger Disable or Re-enable all triggers for table Removing a trigger from database 31 ALTER TRIGGER trigger_name DISABLE | ENABLE ALTER TABLE table_name DISABLE | ENABLE ALL TRIGGERS DROP TRIGGER trigger_name

32. The Problem: Examples from COMPANY Database Limit all salary increases to 50%. Enforce policy that salaries may never decrease. Maintain TotalSalary in DEPARTMENT relation as employees and their salaries change. Inform a supervisor whenever a supervisee’s salary becomes larger than the supervisor’s. All new hires for a given job code get the same starting salary, which is available in the STARTING_PAY table. 32 EMPLOYEE(Name, SSN , Salary, DNO, SupervisorSSN , JobCode ) DEPARTMENT( DNO , TotalSalary , ManagerSSN ) STARTING_PAY( JobCode , StartPay )

33. 1. COMPANY Database Limit all salary increases to 50% before trigger emp_salary_limit 33 create trigger emp_salary_limit before update of EMPLOYEE for each row when ( new .Salary > 1.5 * old .Salary ) set new. Salary = 1.5 * old .Salary ; EMPLOYEE(Name, SSN , Salary, DNO, SupervisorSSN, JobCode) “ new ” refers to the new tuple. “ old ” refers to the old tuple.

34. 2. COMPANY Database Enforce policy that salaries may never decrease before trigger emp_salary_no_decrease 34 create trigger emp_salary_no_decrease before update of EMPLOYEE for each row when ( new .Salary < old .Salary ) begin log the event; signal error condition ; end Method depends on DBMS. EMPLOYEE(Name, SSN , Salary, DNO, SupervisorSSN , JobCode )

35. 5. COMPANY Database: All new hires for a given job code get the same starting salary, which is available in the STARTING_PAY table. before trigger emp_start_pay 35 create trigger emp_start_pay before insert on EMPLOYEE for each row set Salary = ( select StartPay from STARTING_PAY where JobCode = new .JobCode) EMPLOYEE(Name, SSN , Salary, DNO, SupervisorSSN, JobCode) STARTING_PAY( JobCode , StartPay)

36. Thank You ! 36