Ronciling Differences

In this paper we study a problem motivated by the management of changes in databases. It turns out that several such change scenarios, e.g., the separately studied problems of view maintenance (propagation of data changes) and view adaptation (propagation of view definition changes) can be unified as instances of query reformulation using views provided that support for the relational difference operator exists in the context of query reformulation. Exact query reformulation using views in positive relational languages is well understood, and has a variety of applications in query optimization and data sharing. Unfortunately, most questions about queries become undecidable in the presence of difference (or negation), whether we use the foundational set semantics or the more practical bag semantics. We present a new way of managing this difficulty by defining a novel semantics, Z- relations, where tuples are annotated with positive or negative integers. Z-relations conveniently represent data, insertions, and deletions in a uniform way, and can apply deletions with the union operator (deletions are tuples with negative counts). We show that under Z-semantics relational algebra (R A) queries have a normal form consisting of a single difference of positive queries, and this leads to the decidability of their equivalence.We provide a sound and complete algorithm for reformulating R A queries, including queries with difference, over Z-relations. Additionally, we show how to support standard view maintenanc

Contributions aux notions d’équivalence relationnelle dans un contexte d’essais

Plusieurs systèmes d’aide à l’enseignement ont la capacité d’évaluer et de comparer les travaux des étudiants. Dans un contexte d’enseignement des systèmes de gestion de bases de données relationnelles, une telle comparaison nécessite l’étude des équivalences entre les requêtes et leurs résultats. Plus précisément, les équivalences de requêtes peuvent être vérifiées selon deux modèles, à savoir le modèle SQL et le modèle relationnel. Bien que plusieurs travaux de recherche aient déjà été menés pour la comparaison de requêtes et de résultats basés sur des équivalences syntaxiques et sémantiques, la plupart d’entre eux ne traitent pas les deux modèles. Dans ce mémoire, nous proposons quatre algorithmes: les deux premiers pour la vérification de l’équivalence sémantique (selon le modèle SQL et selon le modèle relationnel) et les deux autres algorithmes pour la vérification de l’équivalence des résultats (selon le modèle SQL et selon le modèle relationnel). Les deux premiers reposent principalement sur un algorithme proposé par Cohen. L’algorithme appliqué au modèle SQL prend en compte les nuls ainsi que sept paramètres, à savoir le nom des attributs, le nombre d’attributs, le nombre de lignes, le type des attributs, la valeur des attributs, l’ordre des attributs et l’ordre des lignes. L’algorithme appliqué au modèle relationnel prend en compte cinq paramètres, à savoir le nom des attributs, le nombre d’attributs, le nombre de lignes, le type des attributs et la valeur des attributs. Ces algorithmes s’appliquent pour un sous-ensemble important des requêtes SELECT (comprenant notamment les clauses GROUP BY et HAVING). Ils ont été mis en œuvre dans l’outil Themis que nous avons développé. Themis permet en outre de vérifier partiellement la syntaxe des requêtes

Automatic physical database design : recommending materialized views

This work discusses physical database design while focusing on the problem of selecting materialized views for improving the performance of a database system. We first address the satisfiability and implication problems for mixed arithmetic constraints. The results are used to support the construction of a search space for view selection problems. We proposed an approach for constructing a search space based on identifying maximum commonalities among queries and on rewriting queries using views. These commonalities are used to define candidate views for materialization from which an optimal or near-optimal set can be chosen as a solution to the view selection problem. Using a search space constructed this way, we address a specific instance of the view selection problem that aims at minimizing the view maintenance cost of multiple materialized views using multi-query optimization techniques. Further, we study this same problem in the context of a commercial database management system in the presence of memory and time restrictions. We also suggest a heuristic approach for maintaining the views while guaranteeing that the restrictions are satisfied. Finally, we consider a dynamic version of the view selection problem where the workload is a sequence of query and update statements. In this case, the views can be created (materialized) and dropped during the execution of the workload. We have implemented our approaches to the dynamic view selection problem and performed extensive experimental testing. Our experiments show that our approaches perform in most cases better than previous ones in terms of effectiveness and efficiency

QUERY FROM EXAMPLES

Ph.DDOCTOR OF PHILOSOPH

Equivalences among aggregate queries with negation

Query equivalence is investigated for disjunctive aggregate queries with negated subgoals, constants and comparisons. A full characterization of equivalence is given for the aggregation functions count, max, sum, prod, top2 and parity. A related problem is that of determining, for a given natural number N, whether two given queries are equivalent over all databases with at most N constants. We call this problem bounded equivalence. A complete characterization of decidability of bounded equivalence is given. In particular, it is shown that this problem is decidable for all the above aggregation functions as well as for cntd (count distinct) and avg. For quasilinear queries (i.e., queries where predicates that occur positively are not repeated) it is shown that equivalence can be decided in polynomial time for the aggregation functions count, max, sum, parity, prod, top2 and avg. A similar result holds for cntd provided that a few additional conditions hold. The results are couched in terms of abstract characteristics of aggregation functions, and new proof techniques are used. Finally, the results above also imply that equivalence, under bag-set semantics, is decidable for non-aggregate queries with negation.