skip to main content
10.1145/1559795.1559829acmconferencesArticle/Chapter ViewAbstractPublication PagesmodConference Proceedingsconference-collections
research-article

Equivalence of SQL queries in presence of embedded dependencies

Published:29 June 2009Publication History

ABSTRACT

We consider the problem of finding equivalent minimal-size reformulations of SQL queries in presence of embedded dependencies [1]. Our focus is on select-project-join (SPJ) queries with equality comparisons, also known as safe conjunctive (CQ) queries, possibly with grouping and aggregation. For SPJ queries, the semantics of the SQL standard treats query answers as multisets (bags), whereas the stored relations are treated either as sets, which is called bag-set semantics, or as bags, which is called bag semantics. (Under set semantics, both query answers and stored relations are treated as sets.)

In the context of the above Query-Reformulation Problem, we develop a comprehensive framework for equivalence of CQ queries under bag and bag-set semantics in presence of embedded dependencies, and make a number of conceptual and technical contributions. Specifically, we develop equivalence tests for CQ queries in presence of arbitrary sets of embedded dependencies under bag and bag-set semantics, under the condition that chase [10] under set semantics (set-chase) on the inputs terminates. We also present equivalence tests for CQ queries with grouping and aggregation in presence of embedded dependencies. We use our equivalence tests to develop sound and complete (whenever set-chase on the inputs terminates) algorithms for solving instances of the Query-Reformulation Problem with CQ queries under each of bag and bag-set semantics, as well as for instances of the problem with aggregate queries.

Our contributions are clearly applicable beyond the Query-Reformulation Problem considered in this paper. Specifically, the results of this paper can be used in developing algorithms for rewriting CQ queries and queries in more expressive languages (e.g., including grouping and aggregation, or arithmetic comparisons) using views in presence of embedded dependencies, under bag or bag-set semantics for query evaluation.

References

  1. S. Abiteboul, R. Hull, and V. Vianu. Foundations of Databases. Addison-Wesley, 1995. Google ScholarGoogle ScholarDigital LibraryDigital Library
  2. A. Chandra and P. Merlin. Optimal implementation of conjunctive queries in relational data bases. In STOC, 1977. Google ScholarGoogle ScholarDigital LibraryDigital Library
  3. S. Chaudhuri, R. Krishnamurthy, S. Potamianos, and K. Shim. Optimizing queries with materialized views. In ICDE, pages 190--200, 1995. Google ScholarGoogle ScholarDigital LibraryDigital Library
  4. S. Chaudhuri and M. Vardi. Optimization of real conjunctive queries. In PODS, pages 59--70, 1993. Google ScholarGoogle ScholarDigital LibraryDigital Library
  5. R. Chirkova and M. R. Genesereth. Equivalence of SQL queries in presence of embedded dependencies. Technical Report TR-2008-27, NCSU, 2008. http://www.csc.ncsu.edu/research/tech/reports.php.Google ScholarGoogle Scholar
  6. S. Cohen. Equivalence of queries combining set and bag-set semantics. In PODS, pages 70--79, 2006. Google ScholarGoogle ScholarDigital LibraryDigital Library
  7. S. Cohen, W. Nutt, and Y. Sagiv. Containment of aggregate queries. In ICDT, pages 111--125, 2003. Google ScholarGoogle ScholarDigital LibraryDigital Library
  8. S. Cohen, W. Nutt, and A. Serebrenik. Rewriting aggregate queries using views. In PODS, pages 155--166, 1999. Google ScholarGoogle ScholarDigital LibraryDigital Library
  9. A. Deutsch. XML Query Reformulation over Mixed and Redundant Storage. PhD thesis, Univ. Pennsylvania, 2002. Google ScholarGoogle ScholarDigital LibraryDigital Library
  10. A. Deutsch, A. Nash, and J. Remmel. The chase revisited. In PODS, pages 149--158, 2008. Google ScholarGoogle ScholarDigital LibraryDigital Library
  11. A. Deutsch, L. Popa, and V. Tannen. Query reformulation with constraints. SIGMOD Record, 35(1):65--73, 2006. Google ScholarGoogle ScholarDigital LibraryDigital Library
  12. O. M. Duschka and M. R. Genesereth. Answering recursive queries using views. In PODS, pages 109--116, 1997. Google ScholarGoogle ScholarDigital LibraryDigital Library
  13. R. Fagin, P. Kolaitis, R. Miller, and L. Popa. Data exchange: semantics and query answering. Theoretical Computer Science, 336(1):89--124, 2005. Google ScholarGoogle ScholarDigital LibraryDigital Library
  14. H. Garcia-Molina, J. Ullman, and J. Widom. Database Systems: The Complete Book. Prentice Hall, 2002. Google ScholarGoogle ScholarDigital LibraryDigital Library
  15. G. Gou, M. Kormilitsin, and R. Chirkova. Query evaluation using overlapping views: completeness and efficiency. In SIGMOD Conf., pages 37--48, 2006. Google ScholarGoogle ScholarDigital LibraryDigital Library
  16. A. Halevy. Answering queries using views: A survey. VLDB Journal, 10(4):270--294, 2001. Google ScholarGoogle ScholarDigital LibraryDigital Library
  17. T. Jayram, P. Kolaitis, and E. Vee. The containment problem for real conjunctive queries with inequalities. In PODS, pages 80--89, 2006. Google ScholarGoogle ScholarDigital LibraryDigital Library
  18. A. Klug. On conjunctive queries containing inequalities. Journal of the ACM, 35(1):146--160, 1988. Google ScholarGoogle ScholarDigital LibraryDigital Library
  19. A. Levy, A. Mendelzon, Y. Sagiv, and D. Srivastava. Answering queries using views. In PODS, 1995. Google ScholarGoogle ScholarDigital LibraryDigital Library
  20. C. Li. Rewriting queries using views. Encyclopedia of Database Systems, Springer, in print, 2008.Google ScholarGoogle Scholar
  21. W. Nutt, Y. Sagiv, and S. Shurin. Deciding equivalences among aggregate queries. In PODS, pages 214--223, 1998. Google ScholarGoogle ScholarDigital LibraryDigital Library

Index Terms

  1. Equivalence of SQL queries in presence of embedded dependencies

      Recommendations

      Comments

      Login options

      Check if you have access through your login credentials or your institution to get full access on this article.

      Sign in
      • Published in

        cover image ACM Conferences
        PODS '09: Proceedings of the twenty-eighth ACM SIGMOD-SIGACT-SIGART symposium on Principles of database systems
        June 2009
        298 pages
        ISBN:9781605585536
        DOI:10.1145/1559795
        • General Chair:
        • Jan Paredaens,
        • Program Chair:
        • Jianwen Su

        Copyright © 2009 ACM

        Publisher

        Association for Computing Machinery

        New York, NY, United States

        Publication History

        • Published: 29 June 2009

        Permissions

        Request permissions about this article.

        Request Permissions

        Check for updates

        Qualifiers

        • research-article

        Acceptance Rates

        Overall Acceptance Rate476of1,835submissions,26%

      PDF Format

      View or Download as a PDF file.

      PDF

      eReader

      View online with eReader.

      eReader
      About Cookies On This Site

      We use cookies to ensure that we give you the best experience on our website.

      Learn more

      Got it!