skip to main content
research-article

Evaluation of an Implementation of Cross-Row Constraints Using Materialized Views

Published: 20 December 2019 Publication History

Abstract

SQL assertions are a powerful means used to specify cross-row constraints, and have been available in the SQL standard since 1992. Unfortunately, assertions are not supported in commercial database management systems. Although triggers and application programs can be efficiently used to constrain database content, they are more complex to write and more error-prone. The objective of this paper is to analyze whether the use of materialized views could be a viable solution as regards the automatic implementation of SQL assertions. Materialized views are views that physically store the result of a query and are periodically updated. The method consists of defining a materialized view which contains the number of tuples that violate the condition expressed in the assertion. The materialized view will contain a CHECK constraint that guarantees that the number of tuples that violate the assertion is equal to zero. The proposed method is an easy and automatic means of implementing the integrity constraints described using assertions. We have carried out a series of tests, and although triggers perform better than materialized views in most situations, there are some in which materialized views would be an efficient option. They are easily automatable and less error prone than triggers.

References

[1]
C.J. Date, 2015. SQL and Relational Theory: How to Write Accurate SQL Code. Third ed., O'Reilly.
[2]
A. Behrend, R. Manthey and B. Pieper, 2001. An Amateur's Introduction to Integrity Constraints and Integrity Checking in SQL, Datenbanksysteme in Büro, Technik und Wissenschaft. A. Heuer, F. Leymann and D. Priebe, eds, Informatik aktuell. Springer, Berlin, Heidelberg, 405--423.
[3]
ANSI Standard, 1992. The SQL 92 Standard. http://savage.net.au/SQL/sql-92.bnf.htm
[4]
J. Melton and A. R. Simon, 2002. SQL 1999: Understanding Relational Language Components, Morgan Kaufmann.
[5]
Y.I. Chang and F.L. Chen, 1997. RBE: A Rule-by-example Active Database System, Software: Practice and Experience, 27(4):365--394.
[6]
M. Brucato, A. Abouzied and A. Meliou, 2017. A Scalable Execution Engine for Package Queries. SIGMOD Record, 46(1): 24--31.
[7]
Oracle. http://www.oracle.com
[8]
T. Koppelaars, 2016. SQL Assertions / Declarative multirow constraints". https://community. oracle.com/ideas/ 13028.
[9]
R. Elmasri and S. Navathe,2010. Fundamentals of Database Systems, Sixth ed., Addison-Wesley.
[10]
H.T. Al-Jumaily, D. Cuadra and P. Martínez, 2008 ?OCL2Trigger: Deriving active mechanisms for relational databases using Model-Driven Architecture", Journal of Systems and Software, 81(12):2299--2314.
[11]
H. Zhang, H.B.K. Tan, L. Zhang, X. Lin, X. Wang, C. Zhang and H. Mei, 2011. Checking enforcement of integrity constraints in database applications based on code patterns", Journal of Systems and Software, 84(12):2253- 2264.
[12]
Oracle Database SQL Language Reference, 11g Release 2 (11.2).http://docs.oracle.com/cd/E11882_01/server.112/e41 084.pdf
[13]
P. Lane and P. Potineni, 2014. Oracle Database Data Warehousing Guide, 12c Release 1 (12.1). Oracle.
[14]
X. Oriol, E. Teniente and G. Rull, 2016. TINTIN: a Tool for Incremental INTegrity checking of Assertions in SQL Server", 19th International Conference on Extending Database Technology (EDBT): 632--635.

Cited By

View all
  • (2021)DAMS: Dynamic Association for View Materialization Based on Rule Mining SchemeRecent Innovations in Computing10.1007/978-981-15-8297-4_43(529-544)Online publication date: 13-Jan-2021
  • (2021)Simple Star SchemasData Warehousing and Analytics10.1007/978-3-030-81979-8_2(19-47)Online publication date: 28-Jul-2021

Recommendations

Comments

Information & Contributors

Information

Published In

cover image ACM SIGMOD Record
ACM SIGMOD Record  Volume 48, Issue 3
September 2019
39 pages
ISSN:0163-5808
DOI:10.1145/3377391
Issue’s Table of Contents
Permission to make digital or hard copies of part or all of this work for personal or classroom use is granted without fee provided that copies are not made or distributed for profit or commercial advantage and that copies bear this notice and the full citation on the first page. Copyrights for third-party components of this work must be honored. For all other uses, contact the Owner/Author.

Publisher

Association for Computing Machinery

New York, NY, United States

Publication History

Published: 20 December 2019
Published in SIGMOD Volume 48, Issue 3

Check for updates

Qualifiers

  • Research-article

Contributors

Other Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

  • Downloads (Last 12 months)2
  • Downloads (Last 6 weeks)0
Reflects downloads up to 20 Jan 2025

Other Metrics

Citations

Cited By

View all
  • (2021)DAMS: Dynamic Association for View Materialization Based on Rule Mining SchemeRecent Innovations in Computing10.1007/978-981-15-8297-4_43(529-544)Online publication date: 13-Jan-2021
  • (2021)Simple Star SchemasData Warehousing and Analytics10.1007/978-3-030-81979-8_2(19-47)Online publication date: 28-Jul-2021

View Options

Login options

View options

PDF

View or Download as a PDF file.

PDF

eReader

View online with eReader.

eReader

Media

Figures

Other

Tables

Share

Share

Share this Publication link

Share on social media