This Article 
 Bibliographic References 
 Add to: 
Extending SQL with Generalized Transitive Closure
October 1993 (vol. 5 no. 5)
pp. 799-812

We present SQL/TC, an extension of SQL, to allow the expression of generalized transitive closure queries. The extension permits the user to pose queries that compute paths between two points and information associated with these paths. Such queries may specify selections on arcs, paths, or sets of paths, The output of a query may include the aggregation of information for different paths between the same endpoints. Our notation is declarative, preserves the spirit of SQL, and allows a declarative and concise formulation of transitive closure queries.

[1] R. Agrawal, "Alpha: An Extension of Relational Algebra to Express a Class of Recursive Queries,"Proc. Third Int'l Conf. Data Eng., CS Press, Los Alamitos, Calif., Order No. FN762, 1987, pp. 580-590.
[2] R. Agarwal and H. V. Jagadish, "Hybrid transitive closure algorithms," inProc. 16th Int. Conf. Very Large Data Bases, Brisbane, Australia, Aug. 1990, pp. 326-334.
[3] R. Agrawal, S. Dar, and H. V. Jagadish, "Direct transitive closure algorithms: Design and performance evaluation,"ACM Trans. Database Syst., 1990. (Preliminary version appeared as: R. Agrawal and H. V. Jagadish, "Direct Algorithms for Computing the Transitive Closure of Database Relations," inProc. 13th Int. Conf. Very Large Data Bases, Sept. 1987.)
[4] A. V. Aho, E. Hopcroft, and J. D. Ullman,The Design and Analysis of Computer Algorithms. Reading, MA: Addison-Wesly, 1975.
[5] A. Aho and J. Ullman, "Universality of data retrieval languages," inProc. 6th ACM Symp: Principles of Programming Languages, San Antonio, TX, Jan. 1979.
[6] P. M. G. Apers, M. A. W. Houtsma, and F. Brandse, "Extending a relational interface with recursion," inProc. 6th Advanced Database Symp., pp. 159-166, Aug. 1986.
[7] F. Bancilhon, "Naive evaluation of recursively defined relations," inOn Knowledge Based Management Systems--Integrating Data-base and AI Systems, Brodie and Mylopoulos, Eds. New York: Springer-Verlag, 1985.
[8] F. Bancilhon and R. Ramakrishnan, "An amateur's introduction to recursive query processing strategies," inProc. 1986 ACM-SIGMOD Conf. Management of Data(Washington, DC), May 1986, pp. 16-52.
[9] B. Carre,Graphs and Networks. Oxford: Clarendon Press, 1978.
[10] M. Consens and A. Mendelzon, "GraphLog: A visual formalism for real life recursion," inProc. 9th ACM Symp. Principles of Database Syst., 1990, pp. 404-416.
[11] I. F. Cruz, A. O. Mendelzon, and P. T. Wood, "A graphical query language supporting recursion," inProc. ACM SIGMOD Conf. Management of Data, pp. 16-52, 1986.
[12] I. F. Cruz, A. O. Mendelzon, and P. T. Wood, "G+: Recursive queries without recursion," inProc. 2nd Int. Conf. Expert Database Systems, pp. 355-368, 1988.
[13] I. F. Cruz and T. S. Norvell, "Aggregative closure: An extension of transitive closure," inProc. IEEE 5th Int. Conf. Data Engineering, Los Angeles, CA, Feb. 1989.
[14] S. Dar, R. Agrawal, and H. V. Jagadish, "Optimization of generalized transitive closure," inProc. IEEE 7th Int. Conf. Data Engineering, Feb. 1991.
[15] S. Dar and H. V. Jagadish, "A spanning tree transitive closure algorithm," inProc. IEEE 8th Int. Conf. on Data Engineering, Feb. 1992.
[16] R. W. Engles, "Structured tables,"ANSI X3H2-87-331, Dec. 1987.
[17] A. Guttman, "New features for relational database systems to support CAD applications," Ph.D. dissert., Comput. Sci. Dept., Univ. California, Berkley, June 1984.
[18] T. Haerder, K. Meyer-Wegener, B. Mitschang, and A. Sikeler, "PRIMA--A DBMS prototype supporting engineering applications,"VLDB, 1987.
[19] M. Hardwick, G. Samaras, and D. Spooner, "Evaluating recursive queries in CAD using an extended projection function," inProc. 3rd Int. Data Eng. Conf., IEEE, Los Angeles, CA, Feb. 1987.
[20] Y. E. Ioannidis, "On the computation of the transitive closure of relational operators, " inProc. 12th Int. Conf. Very Large Databases, Kyoto, Japan, 1986.
[21] Y. E. Ioannidis and R. Ramakrishnan, "Efficient transitive closure algorithms," inProc. 14th Int. Conf. on Very Large Data Bases, Los Angeles, Aug. 1988, pp. 382-394.
[22] H. Jakobsson, "Mixed-approach algorithms for transitive closure," inProc. 10th Symp. Principles Database Systems, pp. 199-205, 1991.
[23] B. Jiang, "A suitable algorithm for computing partial transitive closures,"Proc. 6th Int. Conf. Data Eng., Los Angeles, CA, 1990, pp. 264-271.
[24] S. M. Kuck and S. Pax, "A relational calculus with transitive closure," Univ. Illinois, Urbana Champaign, Urbana, Illinois, 1987.
[25] V. Linnemann, "Non first normal form relations and recursive queries: An SQL based approach," inProc. IEEE 3rd Int. Conf. Data Engineering, pp. 591-598, Feb. 1987.
[26] H. Lu, "New strategies for computing the transitive closure of a database relation," inProc. VLDB, Brighton, 1987, pp. 267-274.
[27] W. S. Luk, W. Zhang, and J. Han, "Path: An approach to incorporate list processing in a relational database, " inProc. 2nd Int. Conf. Software Eng. Knowledge Eng., 1990.
[28] J. Melton, Ed., "(ISO-ANSI Working Draft) Database Language SQL2 and SQL3,"ANSI XSH2-90-001, Dec. 1989.
[29] A. O. Mendelzon and P. T. Wood, "Finding regular simple paths in graph databases," inProc. Int. Conf. Very Large Databases, Amsterdam, The Netherlands, 1989.
[30] P. B. Mitschang, "Extending the relational algebra to capture complex objects," inProc. 15th Int. Conf. Very Large Data Bases, pp. 297-305, Aug. 1989.
[31] K. Morris, J. D. Ullman, and A. Van Gelder, "Design overview of the Nail! system," inProc. Int. Conf. Logic Programming, London, 1986.
[32] I. Mumicket al., "Magic is relevant", inProc. 1990 ACM-SIGMOD Conf. on Management of Data, Atlantic City, NJ, May 1990.
[33] I. S. Mumick, S. J. Finkelstein, H. Pirahesh, and R. Ramakrishnan, "Magic conditions," inProc. Principles Database Syst., Apr. 1990.
[34] I. Mumick and H. Pirahesh, "Overbound and right-linear queries," inProc. 10th Symp. Principles of Database Systems, May 1991.
[35] J. F. Naughton, R. Ramakrishnan, Y. Sagiv, and J. D. Ullman, "Efficient evaluation of right-, left-, and multi-linear rules," inProc. 1989 ACM-SIGMOD Conf. Management of Data(Portland, OR), June 1989, pp. 235-242.
[36] P. Pistor and F. Andersen, "Designing a generalized NF2 model with an SQL-type interface," inProc. 12th Int. Conf. Very Large Databases, pp. 278-285, Aug. 1986.
[37] A. Rosenthal, S. Heiler, U. Dayal, and F. Manola, "Traversal recursion: a practical approach to supporting recursive applications," inProc. 1986 ACM SIGMOD Conf.(Washington, DC), 1986, pp. 166-176.
[38] M. A. Roth, H. F. Korth, and D. S. Batory, "SQL/NF: A query language for - 1NF relational databases,"Inform. Syst., vol. 12, no. 1, 1987.
[39] Y. P. Saraiya, "Linearizing non-linear recursions in polynomial time," inProc. 8th Symp. Principles of Database Systems, 1989.
[40] H.-J. Schek and M. H. Scholl, "The relational model with relation-valued attributes,"Inform. Syst., vol. 11, no. 2, 1986.
[41] P. Shaw, "CLOSURE expressions,"ANSI X3H2-87-330, Dec. 1987.
[42] P. Shaw, "CLOSURE expressions: Handling cycles,"ANSI X3H2-88-63, Feb. 1988.
[43] P. Shaw, "A generalization of recursive expressions for non-linear recursion of fixed degree,"ANSI X3H2-88-93REV, Apr. 1988.
[44] S. Sippu and E. Soisalon-Soininen, "A generalized transitive closure for relational queries," inProc. Int. Conf. Principle of Database Syst., 1988.
[45] D. L. Spooner, M. Hardwick, and G. Samaras, "Some conceptual ideas for extending SQL for object-oriented engineering database systems," inProc. IEEE 1st Int. Conf. Data Knowledge Syst. Manufacturing and Engineering, pp. 163-169, Oct. 1987.
[46] D. Srivastava and R. Ramakrishnan, "Pushing constraint selections," inProc. 10th Symp. Principles of Database Systems, pp. 301-315, 1992.
[47] J. Sullivan, "Tree structured traversal,"ANSI X3H2-87-306, Nov. 1987.
[48] J. D. Ullman and M. Yannakakis, "The input/output complexity of transitive closure," inProc. ACM-SIGMOD Conf., 1990.
[49] P. Valduriez and H. Boral, "Evaluation of recursive queries using join indices," inProc. 1st Int. Conf. Expert Database Systems, pp. 197-208, Apr. 1986.
[50] W. Zhang, C. T. Yu, and D. Troy, "A necessary condition for a doubly recursive rule to be equivalent to a linear recursive rule,"ACM Trans. Database Syst., 1991.
[51] M. M. Zloof, "Query-by-example: Operations on the transitive closure," RC 5526, IBM, Yorktown, Heights, NY 1975.

Index Terms:
SQL; generalized transitive closure; SQL/TC; relational algebra; deductive databases; recursion; relational algebra; SQL
S. Dar, R. Agrawal, "Extending SQL with Generalized Transitive Closure," IEEE Transactions on Knowledge and Data Engineering, vol. 5, no. 5, pp. 799-812, Oct. 1993, doi:10.1109/69.243510
Usage of this product signifies your acceptance of the Terms of Use.