REPUBLIQUE TUNISIENNE MINISTERE DE LENSEIGNEMENT SUPERIEUR Elaborated By

REPUBLIQUE TUNISIENNE MINISTERE DE L’ENSEIGNEMENT SUPERIEUR Elaborated By : Nadia Ben Said Salma Ben Harrath Web Intelligence University Year : 2014 -2015

Plan 1 Conclusion Introduction: preliminary aspects Active Databases : Definition 2 3 Active Database Rules 4 Advantages of Active Database 5 6 Exercice: Use of Triggers Article presentation: A new approach to active rule scheduling 7 Conclusion 2/26

Introduction : Preliminary aspects

Introduction Definition Of Active Database Rules Advantage Of Active Database Exercices Conclusion Introduction Passive DBMS Définition Applications Problem Disadvantage Passive DBMS Ø Conventional DBMS are passive: they execute operations only upon explicit request. Ø Traditional databases are passive: i. e. queries, updates, transactions executed • Stock Inventory control only when market Travel waiting List requested. -- reordering when quantity stock falls below threshold. Buy/sellitems stocks when price in below/above threshold - book ticket as soon as right kind is available

Introduction Definition Of Active Database Rules Advantage Of Active Database Exercices Conclusion Introduction Capabilities such as automatic monitoring of conditions & ability to take actions (e. g. re: timing) require an ACTIVE DBMS. there is the need of reactive capabilities: the DBMS autonomously reacts to some events and executes specified operations

Definition Ac. TIVE Database

Introduction Definition Of Active Database Rules Advantage Of Active Database Exercices Conclusion Definition of Active Database Ø An active database is a database in which some operations are automatically executed once a given situation arises Ø The situation may correspond to the fact that: § Some specified events arise, or § Specific conditions or state transitions are detected Ø We refer to active DBMS (ADBMS) to DBMS for which we can specify active rules, also called triggers Ø An active rule (trigger) is a language construct for defining the system reactions

Introduction Definition Of Active Database Rules Advantage Of Active Database Conclusion Exercices Architectural approaches Passive Database Active Database Specification of the situations to be monitored Queries and updates (re) actions (updates) Passive DBMS Active DBMS check Applications External Events reply Applications periodic polling

Introduction Definition Of Active Database Rules Advantage Of Active Database Exercices Conclusion Two initial approaches: Ø specific code in applications programs to perform these tasks (problems – maintenance can be difficult 1 2 conditions/actions might be spread over a few applications programs. Also, can be hard to understand such code fragments) Ø building special applications software that periodically polls the DB to determine relevant events (generally all coded in one application program. Frequency of poling is an issue though) 9/26

Active Database Rules (Trigger)

Introduction Definition Of Présentation Active Database Rules Advantage Of Active Database Exercices Conclusion Specification of active rules : the ECA paradigm The most well paradigm for trigger definition is the Event-Condition-Action (ECA) Event: If it arises, it activates the trigger. Condition: if it is satisfied, the trigger action is executed. Action: It is a sequence of operations that can also modify the database; such sequence is executed only if the condition is satisfied.

Introduction Definition Of Présentation Active Database Rules Advantage Of Active Database Exercices Conclusion Specification of active rules : the ECA paradigm What is an event? Ø Possibility of defining rules that can be activated before or after an event Ø Possibility combining events “An event is ofsomething which(composite happens, events): which is of interest, and which can be mapped onto some time instant” Logical operators: and, or, etc. Application defined events (external event): room temperature too high Sequence: a trigger is activated only if two or more events arise according to a specified order DBMS operations: login of users, transaction management and authorization Data accesses: queriesaon tables is Temporal composition: trigger activated when event E 2 deletions, arises 5 modifications seconds Data modifications: insertions, after event E 1

Introduction Definition Of Présentation Active Database Rules Advantage Of Active Database Exercices Conclusion Specification of active rules : the ECA paradigm What is a condition? “A condition is an additional check that is executed when the trigger is evaluated and before the action is executed Predicates Queries Application procedures WHERE clause of SQL; it is useful to have simple predicates because their evaluation is efficient the condition is true if and only if the query return the empty set (a possible meaning) call to a procedure

Introduction Definition Of Présentation Active Database Rules Advantage Of Active Database Exercices Conclusion Specification of active rules : the ECA paradigm What is an action? “An action is a sequence of operations that is executed when its trigger is considered and the trigger condition is true” If the trigger includes several events, in the action it is possible to execute different activities through the use of conditional predicates Ø IF inserting Ø IF deleting Ø IF updating, IF updating(‘column_name’)

Introduction Definition Of Active Database Rules Advantage Of Active Database Exercices Timing Options {BEFORE|AFTER|INSTEADOF} INSTEAD OF – do something AFTER BEFORE – before the – executes after the other than the triggering event is processed. triggering eventtriggering makes any event. Map an insertion on a alter the changes to the Can't database. Youtriggering event view to physical tables. can alter the triggering event. Conclusion

Introduction Definition Of Présentation Active Database Rules Advantage Of Active Database Exercices Conclusion Format for writing a trigger Oracle: Create trigger name {before | after} {insert | delete | update [of list-of column-names]} on table-name [referencing references] [for each row] [when condition] PL/SQL block; SQL 99: CREATE TRIGGER Name {BEFORE | AFTER} Event ON Relation [REFERENCING { OLD [ROW] [AS] Variable |NEW [ROW] [AS] Variable | OLD TABLE [AS] Variable | NEW TABLE [AS] Variable}] [FOR EACH {ROW |STATEMENT}] [WHEN Condition]

Introduction Definition Of Présentation Active Database Rules Advantage Of Active Database Conclusion Exercices SQL Oracle Data model Relational, object-relational Primitive events Operations on the database Composite events No Yes Parameter passing No No Transition tables Net effect Yes (tuple, table) No Termination Syntactic check Rule ordering Type + creation order Yes (tuple) No Based on timeout Type + non deterministic choice

Advantages Of Active Database

Introduction Definition Of Présentation Active Database Rules Advantage Of Active Database B • Simplify the applications program • Testing Fiability A Exercices • Facilitate the operations without user’s order. C Advantages Of Active Database E Conclusion D • Maintain the security of the process. • Configuration of Taken decisons

Use of triggers Exercice

Introduction Definition Of Active Database Rules Advantage Of Active Database Exercices Conclusion Triggers: The Problem -Examples from COMPANY Database EMPLOYEE(Name, SSN, Salary, DNO, Supervisor. SSN, Job. Code) DEPARTMENT(DNO, Total. Salary, Manager. SSN) STARTING_PAY(Job. Code, Start. Pay) 1. Limit all salary increases to 50%. 2. Enforce policy that salaries may never decrease. 3. All new hires for a given job code get the same starting salary, which is available in the STARTING_PAY table.

Introduction Definition Of Active Database Rules Advantage Of Active Database Conclusion Exercices Triggers: correction -Examples from COMPANY Database EMPLOYEE(Name, SSN, Salary, DNO, Supervisor. SSN, Job. Code) create trigger emp_salary_limit before update of EMPLOYEE for each row where (new. Salary > 1. 5 * old. Salary) set new. Salary = 1. 5 * old. Salary; “new” refers to the new tuple. “old” refers to the old tuple.

Introduction Definition Of Active Database Rules Advantage Of Active Database Exercices Conclusion Triggers: correction -Examples from COMPANY Database EMPLOYEE(Name, SSN, Salary, DNO, Supervisor. SSN, Job. Code) create trigger emp_salary_no_decrease before update of EMPLOYEE for each row where (new. Salary < old. Salary) begin log the event; signal error condition; end Method depends on DBMS.

Introduction Definition Of Active Database Rules Advantage Of Active Database Exercices Conclusion Triggers: correction -Examples from COMPANY Database EMPLOYEE(Name, SSN, Salary, DNO, Supervisor. SSN, Job. Code) STARTING_PAY(Job. Code, Start. Pay) create trigger emp_start_pay before insert on EMPLOYEE for each row set Salary = ( select Start. Pay from STARTING_PAY where Job. Code = new. Job. Code)

Article presentation A new approach to active rule scheduling Abbas Rasoolzadegan, Rohollah. Alesheykh , Mohammad. Reza. Meybodi Engineering Applicationsof. Artificial Intelligence 39(2015)55– 79

Introduction Definition Of Active Database Rules Advantage Of Active Database Article Presentation Conclusion Ø One of the main modules of an ADS is the rule scheduler, which has asignificant impact on the effectiveness and efficiency of ADSs. During the rule scheduling process, the rule scheduler is responsible for choosing one of the activated or ready-to-be-executed rules to evaluate its condition section or execute its action section, respectively. Average Response Time, Throughput, Response Time Standard Deviation are used to evaluate the effectiveness Time Overhead per Transaction, and CPU are used to evaluate the Utilization efficiency of rule scheduling approaches.

Introduction Definition Of Active Database Rules Advantage Of Active Database Article Presentation Rules Processing Cycle . Conclusion

Introduction Definition Of Active Database Rules Results Ø The results of the experiments show that. Ex-SJFEs. TLA has a positive impact on the average response time, response time standard deviation, and through put of an. ADS and does not have any negative impact on its time over head per transaction and CPU utilization Advantage Of Active Database § process is the estimation of the actual execution times of active rules as accurate as possible § One of the most challenges against introducing an effective rule scheduling Article Presentation Conclusion to improve the most effective unconstrained rule scheduling approach §a new rule scheduling approach was developed which is equipped with a learning 28/26 automaton

Introduction Definition Of Active Database Rules Advantage Of Active Database Article Presentation Conclusion scheduling process (1) estimating the actual execution time of each active rule more precisely (2) decreasing the priority of the selection of the activated rules whose condition sections are false at the evaluation time. two viewpoints

Introduction Definition Of Active Database Rules v. The results of experiments revealed that the proposed approach improved the rule scheduling process according to the evaluation criteria. Advantage Of Active Database Article Presentation Conclusion

Conclusion

Introduction Definition Of Présentation Active Database Rules Advantage Of Active Database Exercices Conclusion Traditional Database needs Active There a number of research prototypes, & Database work in this area is ongoing. Maintain Simple Active Rules Security 32