CS 240 A Databases and Knowledge Bases Applications

CS 240 A: Databases and Knowledge Bases Applications of Active Database Carlo Zaniolo Notes From Ch 3 of Advanced Database Systems by Zaniolo, Department of Computer Science Ceri, Faloutsos, Snodgrass, University of California, Los Angeles Subrahmanian and Zicari Morgan Kaufmann, 1997

A Taxonomy of Active Rule Applications Internal to the database: u u Integrity constraint maintenance Support of data derivation (including data replication). Extended functionalities (used in many applications): u u Version managers u Event tracking and logging u Workflow management systems Security administration Business Rules (application specific): u Trading rules for the bond market u Warehouse and inventory management u Energy management rules

Internal VS Extended Rules Perform classical DBMS functions Can be approached with structured approaches and techniques Can be automatically or semi automatically generated Can be declaratively specified

Declarative Design of Active Rules for Integrity and View Maintenance Internal applications of active databases are: u Static u Declarative u High level, easy to understand Approach u User specifies application at declarative (high) level u System derives low level rules that implement it ± automatically ± or semi automatically

Framework Rules should be programmed by DBA Rule programming should be assisted by rule design tools Rule derivation can be: u Completely automatic (for few well defined problems) u Partially automatic—requiring some interaction with users

Integrity Constraint Maintenance Constraints are static conditions u u Every employee's department exists Every employee's salary is between 30 and 100 Rules monitor dynamic database changes to enforce constraints u when change to employees or departments if an employee's department doesn't exist then fix the constraint u when change to employee salaries if a salary is not between 30 and 100 then fix the constraint Generalizing: u when potentially invalidating operations u if constraint violated u then fix it

Integrity Preserving Rules Constraint: condition C Rules(s): when operations that could make C become false if C is false then make C true or abort transaction Example: C = every employee's department must exist Operations = insert into emp, delete from dept, update to emp. deptno, update to dept. dno Condition: u There is some employee violating C (due to those ops) Action: make C true u Rollback insertion of emp u Rollback deletion of dept u Put emp into a dummy dept

Example: Referential Integrity Constraint: Abort Rules EXISTS (SELECT * CREATE RULE Dept. Emp 1 ON Emp WHEN INSERTED, UPDATED(Deptno) FROM Dept IF EXISTS (SELECT * FROM Emp WHERE Dno = Emp. Deptno) WHERE NOT EXISTS (SELECT * FROM Dept Denial form: WHERE Dno = Emp. Dept. No)) NOT EXISTS (SELECT * THEN ROLLBACK FROM Dept WHERE Dno = Emp. Deptno) CREATE RULE Dept. Emp 2 ON Dept WHEN DELETED, UPDATED(Dno) IF EXISTS (SELECT * FROM Emp WHERE NOT EXISTS (SELECT * FROM Dept WHERE Dno = Emp. Dept. No)) THEN ROLLBACK

Referential Integrity using Repair Rules for EMP CREATE RULE Dept. Emp 1 ON Emp WHEN INSERTED IF EXISTS ( SELECT * FROM INSERTED WHERE NOT EXISTS (SELECT * FROM Dept WHERE Dno =Emp. Dept. No)) THEN UPDATE Emp SET Dept. No = NULL WHERE Emp. No IN (SELECT Emp. No FROM INSERTED) AND NOT EXISTS (SELECT * FROM Dept WHERE Dno = Emp. Dept. No))

Repair Rules for EMP (cont. ) CREATE RULE Dept. Emp 2 ON Emp WHEN UPDATED(Deptno) IF EXISTS (SELECT * FROM NEW UPDATED WHERE NOT EXISTS (SELECT * FROM Dept WHERE Dno = Emp. Dept. No)) THEN UPDATE Emp SET Dept. No = 99 WHERE Emp. No IN (SELECT Emp. No FROM NEW UPDATED) AND NOT EXISTS (SELECT * FROM Dept WHERE Dno = Emp. Dept. No))

Repair Rule for Dept See Chapter 3 of ADS textbook

View Maintenance Logical tables derived from base tables u u Portion of database specified by retrieval query Used to provide different abstraction levels similar to external schemas. Referenced in retrieval queries. Virtual views u u Not physically stored Implemented by query modification Materialized views u Physically stored u Kept consistent with base tablesee Chapter 2 of textbook

Virtual Views define derived data by static database queries Table high paid = All employees with high salaries Virtual views are not stored in the database Rules dynamically detect queries on virtual views and transform into queries on base tables: When retrieve from high paid then retrieve from emp where sal > X

Materialized Views Rules(s): when operations happen that can change the result of Q then modify V How to generate rule(s) from view? Generate triggering operations by analyzing Q Example: V = all employees with high salaries Ops = insert into emp, delete from emp, update emp. sal Generate action to modify V 1. 2. 3. Evaluate query Q, set V = result Evaluate Q using changed values, update V Determine if you can use 2 or have to use 1 by analyzing Q

Materialized Views (cont. ) define view V as select Cols from Tables where Predicate Materialized initially, stored in database Refreshed at rule processing points Changes to base tables => View Maintaining Rules Recomputation approach (easy but bad) u when changes to base table then recompute view Incremental approach (better but harder an not always applicable) when changes to base tables then change view Incremental rules is difficult in the presence of duplicates and certain base table operations

Example Relational view selecting departments with one or more employee who earns more than 50, 000 DEFINE VIEW High. Paid. Dept AS (SELECT DISTINCT Dept. Name FROM Dept, Emp WHERE Dept. Dno = Emp. Deptno AND Emp. Sal > 50 K) Critical events 1. 2. 3. 4. 5. 6. 7. insertions into Emp insertions into Dept deletions from Emp deletions from Dept updates to Emp. Deptno updates to. Emp. Sal updates to Dept. Dno

Refresh Rules written in Starburst CREATE RULE Refresh. High. Paid. Dept 1 ON Emp WHEN INSERTED, DELETED, UPDATED(Deptno), UPDATED(Sal) THEN DELETE * FROM High. Paid. Dept; INSERT INTO High. Paid. Dept: (SELECT DISTINCT Dept. Name FROM Dept, Emp WHERE Dept. Dno = Emp. Deptno AND Emp. Sal > 50 K) CREATE RULE Refresh. High. Paid. Dept 2 ON Dept WHEN INSERTED, DELETED, UPDATED(Dno) THEN DELETE * FROM High. Paid. Dept; INSERT INTO High. Paid. Dept: (SELECT DISTINCT Dept. Name FROM Dept, Emp WHERE Dept. Dno = Emp. Deptno AND Emp. Sal > 50 K) ____________________ Dept and Emp are switched in the ADS book

Incremental Refresh Rules Incremental refresh rule for Insert on Dept: CREATE RULE Incr. Refresh. High. Paid. Dept 1 ON Dept WHEN INSERTED THEN INSERT INTO High. Paid. Dept: (SELECT DISTINCT Dept. Name FROM INSERTED, Emp WHERE INSERTED. Dno = Emp. Deptno AND Emp. Sal > 50 K) u This rule is not needed if there is a FK constraint from Emp to Dept Incremental refresh rules for Insert on Emp ? Incremental refresh rules for Delete on Dept? Incremental refresh rules for Delete on Emp ?

Replication A special case of data derivation (identical copies). Main application: distributed systems (copies on different servers). Typical approach: asynchronous. u u Capture Step: Active rules react to changes on one copy and collect changes into deltas. Apply step: Deltas are propagated to other copies at the appropriate time. Alternatives: u Primary Secondary u Symmetric

Conclusion Active rules are now used primarily for integrity constraint maintenance Special constructs are now available in SQL: 2003 for concrete views and replication u Active rules still have an edge on delta maintenance For more complex applications active rules have proven to be impractical—the XCON experience. Attempts to improve situation (e. g. , Chimera have produced little benefits)

Limitations of Active Rules: according to Stottler Henke Artificial Intelligence History Early to mid 1980 s. A succession of early expert systems were built and put in use by companies. Including: a hydrostatic and rotary bacteria killing cooker diagnosis program at Campbell's Soup based on Aldo Cimino's knowledge; a lathe and grinder diagnosis analyzer at GM's Saginaw plant using Charlie Amble's skills at listening for problems based on sounds; a mineral prospecting expert system called PROSPECTOR that found a molybdenum deposit; a Bell system that analyzed problems in telephone networks, and recommended solutions; FOLIO, an investment portfolio advisor; and WILLARD, a forecaster of large thunderstorms. AI groups were formed in many large companies to develop expert systems. Venture capitalists started investing in AI startups, and noted academics joined some of these companies. 1986 sales of AI based hardware and software were $425 million. Much of the new business were developing specialized hardware (e. g. , LISP computers) and software (e. g. , expert system shells sold by Teknowledge, Intellicorp, and Inference) to help build better and less expensive expert systems. Rule based expert systems start to show limits to their commercially viable size. 1987 Circa: XCON, the Digital Equipment Company expert system had reached about 10, 000 rules, and was increasingly expensive to maintain. Reasons for these limits include: As new rules are added to expert systems, it becomes increasingly difficult to decide the order in which active rules ought to be acted upon. Unexpected effects may occur as new rules are added!

Active Rule Experience Inflexibility of these expert systems in applying rules, and the tunnel vision implied in their limited knowledge, that can result in poor conclusions…difficulties with "non monotonic" reasoning. Rule based expert systems couldn't draw conclusions from similar past cases. Such analogical reasoning is a common method used by humans. Expert systems don't know what they don't know, and might therefore provide wrong answers to questions with answers outside their knowledge. This behavior is called "brittleness. " Expert systems can't share their knowledge Expert systems can't learn, that is, they can't establish correspondence and analogies between objects and classes of objects. It was gradually realized that expert systems are limited to "any problem that can be and frequently is solved by your in house expert in a 10 to 30 minute phone call, " as expressed by Morris W. Firebaugh at the University of Wisconsin. These problems are only partially related to the fact that AI applications seek very advanced functionalities. Problems such as difficulty to predict and manage rule behavior also appear in databases dealing with much fewer active rules.