Maintaining Transitive Closure of Graphs in SQL

It is common knowledge that relational calculus and even SQL are not expressive enough to express recursive queries such as the transitive closure. In a real database system, one can overcome this problem by storing a graph together with its transitive closure and maintaining the latter whenever updates to the former occur. This leads to the concept of an incremental evaluation system, or IES. Much is already known about the theory of IES but very little has been translated into practice. The purpose of this paper is to ll in this gap by providing a gentle introduction to and an overview of some recent theoretical results on IES. The introduction is through the translation into SQL of three interesting positive maintenance results that have practical importance { the maintenance of the transitive closure of acyclic graphs, of undirected graphs, and of arbitrary directed graphs. Interestingly, these examples also allow us to show the relationship between power and cost in the incremental maintenance of database queries.

[1]  Gultekin Özsoyoglu,et al.  Extending relational algebra and relational calculus with set-valued attributes and aggregate functions , 1987, TODS.

[2]  Kotagiri Ramamohanarao,et al.  Maintaining Constrained Transitive Closure by Conjunctive Queries , 1997, DOOD.

[3]  Thomas A. Schultz ADEPT — The advanced database environment for planning and tracking , 1998, Bell Labs Technical Journal.

[4]  Chaoyi Pang,et al.  Maintaining Transitive Closure in First Order After Node-Set and Edge-Set Deletions , 1997, Inf. Process. Lett..

[5]  Jianwen Su,et al.  Space-bounded FOIES (extended abstract) , 1995, PODS.

[6]  Shahrokh Saeednia,et al.  How to maintain both privacy and authentication in digital libraries , 2000 .

[7]  Kotagiri Ramamohanarao,et al.  Incremental FO(+, <) Maintenance of All-Pairs Shortest Paths for Undirected Graphs after Insertions and Deletions , 1999, ICDT.

[8]  Robert Sedgewick,et al.  Algorithms in C , 1990 .

[9]  C. J. Date Some principles of good language design: with especial reference to the design of database languages , 1984, SGMD.

[10]  Jianwen Su,et al.  Increment Boundedness and Nonrecursive Incremental Evaluation of Datalog Queries , 1995, ICDT.

[11]  Limsoon Wong,et al.  Query Languages for Bags and Aggregate Functions , 1997, J. Comput. Syst. Sci..

[12]  Jin-Yi Cai Lower Bounds for Constant-Depth Circuits in the Presence of Help Bits , 1990, Inf. Process. Lett..

[13]  Guozhu Dong Separating Auxiliary Arity Hierarchy of First-Order Incremental Evaluation Using (3+1)-ary Input Relations , 1997 .

[14]  Limsoon Wong,et al.  Local properties of query languages , 2000, Theor. Comput. Sci..

[15]  Limsoon Wong,et al.  Some Relationships between the FOIES and S1 Arity Hierarchies , 1997, Bull. EATCS.

[16]  Jianwen Su,et al.  Deterministic FOIES are strictly weaker , 2004, Annals of Mathematics and Artificial Intelligence.

[17]  Jianwen Su,et al.  First-Order Incremental Evaluation of Datalog Queries , 1993, DBPL.

[18]  Leonid LibkinBell SQL Can Maintain Polynomial-Hierarchy Queries , 1997 .

[19]  Jianwen Su,et al.  Arity Bounds in First-Order Incremental Evaluation and Definition of Polynomial Time Database Queries , 1998, J. Comput. Syst. Sci..

[20]  C. J. Date A critique of the SQL database language , 1984, SGMD.

[21]  Guozhu Dong,et al.  Incremental Evaluation of Datalog Queries , 1992, ICDT.

[22]  Limsoon Wong,et al.  On Impossibility of Decremental Recomputation of Recursive Queries in Relational Calculus and SQL , 1995, DBPL.

[23]  Serge Abiteboul,et al.  Foundations of Databases , 1994 .

[24]  Alfred V. Aho,et al.  Universality of data retrieval languages , 1979, POPL.

[25]  Limsoon Wong,et al.  BioKleisli: a digital library for biomedical researchers , 1997, International Journal on Digital Libraries.

[26]  Limsoon Wong,et al.  Incremental Recomputation of Recursive Queries with Nested Sets and Aggregate Functions , 1997, DBPL.

[27]  Jianwen Su,et al.  Incremental and Decremental Evaluation of Transitive Closure by First-Order Queries , 1995, Inf. Comput..