Reasoning with Aggregation Constraints in

We investigate the problem of using materialized views to compute answers to SQL queries with grouping and aggregation, in the presence of multiset tables. This problem is important in many applications, such as data warehousing, mobile computing, global information systems, and maintaining physical data independence, where access to local or cached materialized views may be cheaper than access to the underlying database. In addition, this problem has obvious potential in optimizing query evaluation. The problem is formally stated as nding a rewriting of an SQL query Q where the materialized views occur in the FROM clause, and the rewritten query is multiset-equivalent to Q. First, we study the case where the query has grouping and aggregation but the views do not, and show that usability of a view in evaluating a query essentially requires an isomorphism between the view and a portion of the query. We present a rewriting algorithm that generates all possible rewritings of the query using the views (for the case of equality predicates); when using multiple views, considering the views iteratively in any order yields all possible rewritings. Second, we study the case where the query and the views both have grouping and aggregation, identify the conditions under which the aggregation information present in a view is suucient to perform the aggregate computations required in the query, and present a rewriting algorithm. Third, we outline how our techniques can be extended to take advantage of set-valued queries and views in the presence of keys or SELECT DISTINCT.