Data retrieval and aggregates in SQL ^*/NR

Abstract

Standard SQL is incapable of handling recursive database queries and nested relations. A proposed solution to allow recursion in SQL was given in SQL ^* [KC93], while a solution to allow nested relations in SQL was given in SQL/N F [RKB87]. However, these two problems with SQL were handled separately, and an extended SQL that handles both recursive queries and nested relations is still lacking. To overcome this shortcoming, we propose an extended SQL, called SQL ^*/NR, that not only can handle both recursive queries and nested relations, but also allows aggregate operators. A query Q in SQL ^*/NR is processed by first transforming Q into rule expressions in LDL/NR, a logic database language for nested relations, and the transformed rule expressions are evaluated for retrieving the desired result of Q. Transforming Q into rule expressions in LDL/N R is desirable since LDL/NR handles recursion on nested relations with a built-in mechanism for recursive query processing. In this paper, we define SQL ^*/NR and include an approach for transforming SQL ^*/NR queries into rule expressions in LDL/NR. SQL ^*/NR, as defined, enhances the expressive power of standard SQL and SQL/NF and has the expressive advantage over SQL ^*.