Feng Shao Antal Novak Jayavel ShanMugaSundaram

1. Triggers over Nested views of Relational Data Feng Shao Antal Novak Jayavel ShanMugaSundaram Cornell University Stanford University Cornell University Presenter: Venugopal Reddy Pendiyala

2. We propose active system in this article that we can have a triggers over nested views The main technical contribution of this article is a systematic way to translate triggers over nested views of relational data into SQL triggers over flat base tables. Purpose of The Article

3. The nested relational algebra works on nested relations and contains all the traditional relational operators Fig: Nested Relational Algebra Operators Fig: Example of Nested Relations

4. The Nest operator takes an input relation and a list of columns to be nested (nesting columns), • The Un nest is the converse of the Nest and flattens a nested relation based on an un nesting column. • Example data base • Nested catalog view.

5. A nested relational algebra expression can also be viewed as a tree or graph of algebra operators, or an operator graph. • Operators (boxes) 1 and 2 produce the rows in the product and vendor tables.

6. There is a fairly direct mapping between nested relations and XML Ex: XML catalog view • Trigger specification language for triggers over XML documents syntax: • CREATE TRIGGER Name AFTER Event • ON Target WHERE Condition DO Action

7. Semantics And System Architecture • When ever an event is performed on the row in table we need a identity of a row in order to perform the action we specify that as a key. • We use the term canonical key to denote a unique key for the operator. • If the value of the canonical key of a row r is updated, we do not consider r as being updated; r is considered to be deleted and a new row r_ with the new key value is inserted.

8. System architecture.

9. Trigger Parsing and Event Pushdown • The first step in our architecture is to convert the trigger. • The trigger Event and the Target graph are then analyzed by the Event Pushdown module • Starting at the top operator of the Target graph, we can determine the set of events on all of its input operators which can cause the Event specified in the trigger.

10. Affected-Row Graph Generation • The Affected-Row Graph Generator constructs an operator graph which, when evaluated, produces the OLD ROW and NEW ROW values for each affected row. Fig: Gparams: Producing parameters to action

11. Technical Challenges in Producing Gaffected • We avoid materialization because a) It would require a sophisticated middleware database b) It would require the view to be updated for every relevant relational update • Proposed Algorithm: The algorithm first detects the keys of the rows affected by an update (affected keys) • Create A K Graph: Finding Affected Keys. The algorithm takes as input an operator O (the top operator in the Target graph G), an operator O old (the top operator in Gold), and a base table T

12. It returns the top operator O’, of an operator graph and a set of key columns of O’, K. Create AK Graph:

13. Create ARGraph: Producing Affected Rows.. • O is joined with oG to get NEW ROW, joined with oG old to get OLD ROW • Finally ,OLD ROW and NEW ROW are joined on the key columns of oG • Optimizations for Create AR Graph • This graph performs the check to ensure that an view trigger is not fired unnecessarily due to spurious results • First can be UPDATE VENDOR SET PRICE = 1 ∗ PRICE • Second, if OLD ROW and NEW ROW are large, the comparison can be quite expensive

14. Injective Views. • To avoid trigger to be fired unnecessarily due to spurious results. • Injective views have the property that there is a one-to-one mapping. • A view with graph G is injective with respect to a table T iff T ∗ →C, where C is the set of output columns of oG. • Non injective Views. If a view is not injective, then in general, we need to explicitly check whether OLD ROW and NEW ROW differ. • Finally the graph that produces parameters for the Action after selecting only the (OLD ROW, NEW ROW) pairs that satisfy the trigger condition

15. Trigger Grouping And Push Down • Trigger Grouping module, groups similar triggers together for improved scalability. • The Trigger Pushdown module takes the grouped trigger graph and pushes down selection conditions to produces a set of SQL triggers • create a constants table Fig: Converting select to join.

16. This direct replacement of a select with a join does not work for complex nested conditions. • The basic idea is to use the constants table to set up a correlation in the Gparams graph to produce a Ggrouped graph. Fig: Correlated Ggrouped graph.

17. Trigger Pushdown Optimizations • The final step is to generate a SQL trigger. • we apply an important optimization to avoid directly computing the contents of T old (the pre update version of T), which can be expensive since it is not directly made available by the relational database system. • The Trigger Activation module then activates the appropriate triggers and passes in the nested relational rows as parameters to their actions. • The trigger first finds the affected keys by taking a union of the product names associated with rows in the pruned transition tables.

18. Experimental Evaluation • We have developed and evaluated a prototype of the proposed techniques in the context of Quark XML middleware system. • Quark uses an internal algebra called XQGM, we use the term element ->row. • we considered two metrics: (1) the compile time for a trigger (2) the run time Experimental Parameters

19. Varying # Triggers • we set # fired triggers/ updated element to be 1 • Grouped-Eopt and Grouped-Aopt provided a 25–35% improvement over Grouped due to aggregation optimization. • Grouped-Eopt-AoptMv showed a 25% performance degradation over Grouped-Eopt-Aopt

20. Varying Hierarchy Depth • The hierarchy depth is defined as the depth of the relational schema • The run time of all the approaches increased approximately linearly with the hierarchy depth.

21. Varying # Fired Triggers/Updated Element • The GROUPED approaches scaled gracefully and linearly with the number of fired triggers,

22. Varying # Updated Elements • (GROUPED-EOPT and GROUPED-EOPT-AOPT) are striking because it avoided computing unnecessary OLD Rows, increasing the number of update rows

23. Varying # Leaf Tuples Per Element • The effect of varying the number of leaf tuples per element in the view

24. Varying # Leaf Tuples • The total number of leaf tuples increased ,but the number of leaf nodes in the affected rows remained the same

25. Performance Using DBLP Data • The DBLP data set contains about 725000 articles.

26. shows the performance results obtained by varying the number of triggers

27. Conclusion • Translating triggers over nested views of relational data into SQL triggers. • Translating relational updates into their corresponding nested view updates. • We have presented various optimizations and quantified their efficiency and scalability.

28. Future Work • To investigate whether our general algorithm for detecting changes over nested views can be adapted for incrementally maintaining materialized views with nested predicates. • A direction of future work is to identify the general class of views where the final inequality check can be pushed down to the relational level.

29. REFERENCES • Quark: An efficient XQuery full-text implementation. In Proceedings of theACMSIGMOD • International Conference on Management of Data (Chicago, IL). • BOHANNON, P., BUNEMAN, P., CHOI, B., AND FAN,W. 2004. Incremental evaluation of schema-directed • XML publishing. In Proceedings of the ACM SIGMOD International Conference on Management • of Data (Paris, France). 503–514. • BONIFATI, A., BRAGA, D., CAMPI, A., AND CERI, S. 2002. Active XQuery. In Proceedings of the International • Conference on Data Engineering (ICDE, San Jose, CA). 403–414. • BRAGANHOLO, V. P.,DAVIDSON, S. B., AND HEUSER, C. A. 2004. From XML view updates to relational • view updates: Old solutions to a new problem. In Proceedings of VLDB (Toronto, Ont., Canada). • 276–287. • BRAGANHOLO, V. P., DAVIDSON, S. B., AND HEUSER, C. A. 2003. On the updatability of XML views • over relational databases. In Proceedings of WebDB. 31–36. • CERI, S. AND WIDOM, J. 1990. Deriving production rules for constraint maintenance. In Proceedings • of the International Conference on Very Large Databases (VLDB, Brisbane, Queensland, • Australia). 566–577. • CERI, S. ANDWIDOM, J. 1991. Deriving production rules for incremental view maintenance. In Proceedings • of the International Conference on Very Large Databases (VLDB, Barcelona, Catalonia, • Spain). 577–589. • CHAN, C. Y., FELBER, P., GAROFALAKIS, M. N., AND RASTOGI, R. 2002. Efficient filtering of XML • documents with XPath expressions. VLDB J. 11, 354–379. • CHANDRA, A. AND MERLIN, P. 1977. Optimal implementation of conjunctive queries in relational • data bases. In Proceedings of the ACM Symposium on Theory of Computing (STOC). 77–90. • CHEN, J., DEWITT, D. J., TIAN, F., AND WANG, Y. 2000. NiagaraCQ: A scalable continuous query • system for Internet databases. In Proceedings of the ACM SIGMOD International Conference on • Management of Data (Dallas, TX). 379–390. • COCHRANE, R., KULKARNI, K., AND MATTOS, N. 1999. Active database features in SQL3. In Active • Rules in Database Systems. Springer-Verlag, Berlin, Germany, 197–220. • COCHRANE, R., PIRAHESH, H., ANDMATTOS,N. 1996. Integrating triggers and declarative constraints • in SQL database systems. In Proceedings of the International Conference on Very Large Databases • (VLDB, Mumbai (Bombay), India). 567–578. • CODD, E. F. 1983. A relational model of data for large shared data banks. Commun. ACM 26, 1, • 64–69. • DAYAL, U. AND BERNSTEIN, P. A. 1982. On the correct translation of update operations on relational • views. ACM Trans. Database Syst. 7, 3, 381–416. • DIAO, Y., FISCHER, P., FRANKLIN, M., AND TO, R. 2002. YFilter: Efficient and scalable filtering of • XML documents. In Proceedings of the International Conference on Data Engineering (ICDE, • San Jose, CA). 341–352. • DIMITROVA, K., EL-SAYED, M., AND RUNDENSTEINER, E. 2003. Order-sensitive view maintenance of • materialized XQuery views. In Proceedings of the International Conference on Conceptual Modeling( • ER) (Chicago, IL). 144–157. • EL-SAYED, M., WANG, L., DING, L., AND RUNDENSTEINER, E. 2002. An algebraic approach for incremental • maintenance of materialized XQuery views. In Proceedings of the InternationalWorkshop • on Web Information and Data Management (WIDM, SAIC Headquaters, LcLean, VA). 88–91. • FERNANDEZ, M. F.,TAN, W. C., AND SUCIU, D. 2000. SilkRoute: Trading between relations and XML. • Comput. Netw. 33, 1–6, 723–745. • GLUCHE,D.,GRUST, T.,MAINBERGER, C., AND SCHOLL,M. 1997. Incremental updates for materialized • OQL views. In Proceedings of the International Conference on Deductive and Object-Oriented • Databases (DOOD, Montreux, Switzerland). 52–66. • GRIFFIN, T. AND LIBKIN, L. 1995. Incremental maintenance of views with duplicates. In Proceedings • of the ACM SIGMOD International Conference on Management of Data. 328–339. • GUPTA, A. AND MUMICK, I. S. 1995. Maintenance of materialized views: Problems, techniques • and applications. IEEE Quart. Bull. Data Eng. (Special Issue on Materialized Views and Data • Warehousing) 18, 2, 3–18. • GUPTA, A. AND SUCIU,D. 2003. Stream processing of XPath queries with predicates. In Proceedings • of the ACMSIGMOD International Conference on Management of Data (San Diego, CA). 419–430.

30. HANSON, E., CARNES, C., HUANG, L., KONYALA, M., L. NORONHA, S. P., PARK, J., AND VERNON, A. 1999. • Scalable trigger processing. In Proceedings of the International Conference on Data Engineering • (ICDE, Sydney, Australia). 266–275. • JAESCHKE, G. AND SCHEK, H. J. 1982. Remarks on the algebra of non first normal form relations. • In Proceedings of the Symposium on Principles of Database Systems (PODS). ACM Press, New • York, NY, 124–138. • KAWAGUCHI, A., LIEUWEN, D.,MUMICK, I., AND ROSS, K. 1997. Implementing incremental view maintenance • in nested data models. In Proceedings of the International Workshop on Database Programming • Languages (DBPL, Estes Park, CO). 202–221. • KELLER, A. M. 1985. Algorithms for translating view updates to database updates for views • involving selections, projections, and joins. In Proceedings of PODS. 154–163. • KUNO, H. A. AND RUNDENSTEINER, E. A. 1998. Incremental maintenance of materialized objectoriented • views in multiView: Strategies and performance evaluation. IEEE Trans. Knowl. Data • Eng. 10, 5, 768–792. • LANGERAK, R. 1990. View updates in relational databases with an independent scheme. ACM • Trans. Database Syst. 15, 1, 40–66. • NGUYEN, B., ABITEBOUL, S., COBENA, G., AND PREDA, M. 2001. Monitoring XML Data on the Web. • In Proceedings of the ACM SIGMOD International Conference on Management of Data (Santa • Barbara, CA). 437–448. • PALPANAS, T., SIDLE, R., COCHRANE, R., AND PIRAHESH, H. 2002. Incremental maintenance for nondistributive • aggregate functions. In Proceedings of the International Conference on Very Large • Databases (VLDB, Hong Kong, China). 802–813. • PAPAKONSTANTINOU, Y., GARCIA-MOLINA, H., AND WIDOM, J. 1995. Object exchange across heterogeneous • information sources. In Proceedings of the 11th Conference on Data Engineering, P. S. Yu • and A. L. P. Chen, Eds. IEEE Computer Society Press, Los Alamitos, CA, 251–260. • PAPAMARKOS, G., POULOVASSILIS, A., AND WOOD, P. 2003. Event-condition-action rule languages for • the semantic Web. In Proceedings of the Workshopon Semantics Web and Databases. • PIRAHESH, H., HELLERSTEIN, J. M., AND HASAN, W. 1992. Extensible/rule based query rewrite optimization • in Starburst. SIGMOD Rec. 21, 2, 39–48. • QUASS, D. 1996. Maintenance expressions for views with aggregation. In Proceedings of theWorkshop • on Materialized Views: Techniques and Applications (VIEWS). 110–118. • ROTH, M. A., KORTH, H. F., AND SILBERSCHATZ, A. 1988. Extended algebra and calculus for nested • relational databases. ACM Trans. Database Syst. 13, 4, 389–417. • RYS, M. 2001. State-of-the-art XML support in RDBMS: Microsoft SQL server’s XML features. • IEEE Data Eng. Bull. 24, 2, 3–11. • RYS, M. 2005. XML and relational database management systems: Inside microsoft SQL server • 2005. In Proceedings of the ACM SIGMOD International Conference on Management of Data. • ACM Press, New York, NY, 958–962. • RYS, M., NORRIE, M., AND SCHEK, H. 1996. Intra-transaction parallelism in the mapping of an • object-model to a relational multi-processor system. In Proceedings of the International Conference • on Very Large Databases (VLDB, Mumbai (Bombay), India). 460–471. • SAGIV, Y. AND YANNAKAKIS, M. 1978. Equivalence among relational expressions with the union • and difference operation. In Proceedings of the International Conference on Very Large Databases • (VLDB). 535–548. • SESHADRI, P., PIRAHESH, H., AND LEUNG, T. 1996. Complex query decorrelation. In Proceedings of • the International Conference on Data Engineering (ICDE, New Orleans, LA), 450–458. • SHANMUGASUNDARAM, J., KIERNAN, J., SHEKITA, E., FAN, C., AND FUNDERBURK, J. 2001. Querying XML • views of relational data. In Proceedings of the International Conference on Very Large Databases • (VLDB, Rome, Italy). 261–270. • SHANMUGASUNDARAM, J., SHEKITA, E., BARR, R., CAREY, M., LINDSAY, B., PIRAHESH, H., AND REINWALD, B. • 2000. Efficiently publishing relational data as XML documents. In Proceedings of the International • Conference on Very Large Databases (VLDB, Edinburgh, Scotland). 65–76. • SHAO, F., NOVAK, A., AND SHANMUGASUNDARAM, J. 2005. Triggers over XML views of relational data. • In Proceedings of the International Conference on Data Engineering (ICDE, Tokyo, Japan).

31. Questions ?