Can Türker
Skip Abstract Section
Abstract
The correctness of the data managed by database systems is vital to any application that utilizes data for business, research, and decision-making purposes. To guard databases against erroneous data not reflecting real-world data or business rules, semantic integrity constraints can be specified during database design. Current commercial database management systems provide various means to implement mechanisms to enforce semantic integrity constraints at database run-time.In this paper, we give an overview of the semantic integrity support in the most recent SQL-standard SQL:1999, and we show to what extent the different concepts and language constructs proposed in this standard can be found in major commercial (object-)relational database management systems. In addition, we discuss general design guidelines that point out how the semantic integrity features provided by these systems should be utilized in order to implement an effective integrity enforcing subsystem for a database.
- {AHV95} Abiteboul S., Hull R., Vianu V. Foundations of Databases. Addison-Wesley, Reading, Mass., 1995. Google Scholar
- {AWH92} Aiken A., Widom J., Hellerstein J.M. Behaviour of Database Production Rules: Termination, Confluence, and Observable Determinism. In: Stonebraker M., (ed.), Proc. of the 1992 ACM SIGMOD Int. Conf. on Management of Data, San Diego, Calif., ACM SIGMOD Record, 21(2):59-58, 1992. Google Scholar
- {BB82} Bernstein P.A., Blaustein B. Fast Methods for Testing Quantified Relational Calculus Assertions. In: Schkolnick M., (ed.), Proc. of the 1982 ACM SIGMOD Int. Conf. on Management of Data, Orlando, Fla., pp. 39- 50. ACM, New York, June 1982. Google Scholar
- {BBC80} Bernstein P.A., Blaustein B., ClarkeE. Fast Maintenance of Semantic Integrity Assertions Using Redundant Aggregate Data. In: Taylor A., (ed.), Proc. of the 6th Int. Conf. on Very Large Data Bases, VLDB'80, Montreal, Quebec, Canada, October 1-3, 1980, pp. 126-136. IEEE Computer Society, Los Altos, Calif., 1980.Google Scholar
- {BCL86} Blakeley J.A., Coburn N., Larson P.-A. Updating Derived Relations: Detecting Irrelevant and Autonomously Computable Updates. In: Chu W., Gardarin G., Ohsuga S., Kambayashi Y., (eds.), Proc. of the 12th Int. Conf. on Very Large Data Bases, VLDB'86, Kyoto, Japan, August 25-28, 1986, pp. 457-466. Morgan Kaufmann, Los Altos, Calif., 1986. Google Scholar
- {BCP96} Baralis E., Ceri S., Paraboschi S. Modularization Techniques for Active Rules Design. ACM Transactions on Database Systems, 21(1):1-29, 1996. Google Scholar
- {BCW94} Baralis E., Ceri S., Widom J. Better Termination Analysis for Active Databases. In: Paton N.W., Williams M.H., (eds.), Rules in Database Systems, Proc. of the 1st Int. Workshop, RIDS'93, Edinburgh, Scotland, August 1993, Workshops in Computing, pp. 163-179. Springer, London, 1994.Google Scholar
- {BdBZ93} Balsters H., de By R.A., Zicari R. Typed Sets as a Basis for Object-Oriented Database Schemas. In: Nierstrasz O., (ed.), ECOOP'93 - Object-Oriented Programming, Proc. of the 7th European Conf., Kaiserslautern, Germany, July 1993, Lecture Notes in Computer Science, vol. 707. Springer, Berlin Heidelberg New York, 1993, pp. 161-184. Google Scholar
- {BGM00} Bertino E., Guerrini G., Merlo I. Trigger Inheritance and Overriding in an Active Object Database System. IEEE Transactions on Knowledge and Data Engineering, 12(4): 588-608, 2000. Google Scholar
- {Bis98} Biskup J. Achievements of Relational Database Schema Design Theory Revisited. In: Thalheim B., Libkin L., (eds.), Semantics in Databases, Lecture Notes in Computer Science, vol. 1358. Springer, Berlin Heidelberg New York, 1998, pp. 29-54. Google Scholar
- {BM88} Bertino E., Musto D. Correctness of Semantic Integrity Checking in Database Management Systems. Acta Informatica, 26:25-57, 1988. Google Scholar
- {Bro78} Brodie M.L. Specification and Verification of Data Base Semantic Integrity. Dissertation, Technical Report CSRG-91, University of Toronto, April 1978.Google Scholar
- {Buc94} Buchmann A.P. Active Object Systems. In: Dogac A., Özsu M.T., Biliris A., Sellis T., (eds.), Advances in Object-Oriented Database Systems, Nato ASI Series, pp. 201-224. Springer, Berlin Heidelberg New York, 1994.Google Scholar
- {CCW00} Ceri S., Cochrane R., Widom J. Practical Applications of Triggers and Constraints: Success and Lingering Issues. In: El Abbadi A., Brodie M.L., Chakravarthy S., Dayal U., Kamel N., Schlageter G., Whang K.-Y., (eds.), VLDB'2000, Proc. of the 26th Int. Conf. on Very Large Data Bases, September 10-14, 2000, Cairo, Egypt, pp. 254-262. Morgan Kaufmann, Palo Alto, Calif., 2000. Google Scholar
- {CFPT94} Ceri S., Fraternali P., Paraboschi S., Tanca L. Automatic Generation of Production Rules for Integrity Maintenance. ACM Transactions on Database Systems, 19(3): 367-422, 1994. Google Scholar
- {Cha98} Chamberlin D. A Complete Guide to DB2 Universal Database. Morgan Kaufmann, San Francisco, Calif., 1998. Google Scholar
- {Cod70} Codd E.F. A Relational Model of Data for Large Shared Data Banks. Communications of the ACM, 13(6):377- 387, 1970. Google Scholar
- {Cod79} Codd E.F. Extending the Database Relational Model to Capture More Meaning. ACM Transactions on Database Systems, 4(4):397-434, 1979. Google Scholar
- {CPM96} Cochrane P., Pirahesh H., Mattos N.M. Integrating Triggers and Declarative Constraints in SQL Database Sytems. In: Vijayaraman T.M, Buchmann A.P., Mohan C., Sarda N.L., (eds.), Proc. of the 22nd Int. Conf. on Very Large Data Bases, VLDB'96, Bombay, India, September 3-6, 1996, pp. 567-578. Morgan Kaufmann, San Francisco, Calif., 1996. Google Scholar
- {CT95} Chomicki J., Toman D. Implementing Temporal Integrity Constraints Using an Active DBMS. IEEE Transactions on Knowledge and Data Engineering, 7(4): 566- 582, 1995. Google Scholar
- {CW90} Ceri S., Widom J. Deriving Production Rules for Constraint Management. In: McLeod D., Sacks-Davis R., Schek H.-J., (eds.), Proc. of the 16th Int. Conf. on Very Large Data Bases, VLDB'90, Brisbane, Australia, August 13-16, 1990, pp. 566-577. Morgan Kaufmann, Palo Alto, Calif., 1990. Google Scholar
- {Dat90} Date C.J. A Contribution to the Study of Database Integrity. In: Date C.J., (ed.), Relational Database Writings 1985-1989, pp. 185-215, Addison-Wesley, Reading, Mass., 1990.Google Scholar
- {Dat95} Date C.J. An Introduction to Database Systems, 1. Addison-Wesley, Reading, Mass., 6th edn., 1995.Google Scholar
- {dB95} de Brock B. Foundations of Semantic Databases. Prentice-Hall, New York, 1995. Google Scholar
- {DD97} Date C.J., Darwen H. A Guide to the SQL Standard. Addison-Wesley, Reading, Mass., 4th edn., 1997. Google Scholar
- {Deß93} Deßloch S. Semantic Integrity in Advanced Database Management Systems. PhD thesis, Universität Kaiserslautern, Fachbereich Informatik, September 1993.Google Scholar
- {EC75} Eswaran K.P., Chamberlin D.D. Functional Specifications of a Subsystem for Data Base Integrity. In: Kerr D.S., (ed.), Proc. of the 1st Int. Conf. on Very Large Data Bases, VLDB'75, September 24-24, 1975, Framingham, Mass., USA, pp. 48-68. Morgan Kaufmann, Los Altos, Calif., 1975.Google Scholar
- {EM99a} Eisenberg A., Melton J. SQL:1999, formerly known as SQL3. ACM SIGMOD Record, 28(1): 131-138, 1999. Google Scholar
- {EM99b} Eisenberg A., Melton J. SQLJ - Part 1: SQL Routines using the JavaTM Programming Language. ACM SIGMOD Record, 28(4):58-63, 1999. Google Scholar
- {EM00} Eisenberg A., Melton J. SQL Standardization: the Next Steps. ACM SIGMOD Record, 29(1): 63-67, 2000. Google Scholar
- {EN94} Elmasri R., Navathe S.B. Fundamentals of Database Systems. Benjamin/Cummings, Redwood City, Calif., 2nd edn., 1994. Google Scholar
- {FM93} Formica A., Missikoff M. Integrity Constraints Representation in Object-Oriented Databases. In: Finin T.W., Nicholas C.K., Yesha Y., (eds.), Information and Knowledge Management - Expanding the Definition of "Database", Selected Papers of the 1st Conf. CIKM'92, Baltimore, Md., USA, November, 1992, Lecture Notes in Computer Science, vol. 752, pp. 89-85. Springer, Berlin Heidelberg New York, 1993. Google Scholar
- {For99} Fortier P. SQL3 - Implementing the SQL Foundation Standard. McGraw-Hill, New York, 1999. Google Scholar
- {FP94} Fraternali P., Paraboschi S. A Review of Compensating Techniques for Integrity Maintenance. In: Paton N., Williams M.H., (eds.), Rules in Database Systems, Proc. of the 1st Int. Workshop, RIDS'93, Edinburgh, Scotland, August 1993, Workshops in Computing, pp. 333-346. Springer, London, 1994.Google Scholar
- {FP97} Fraternali P., Paraboschi S. Ordering and Selecting Production Rules for Constraint Maintenance: Complexity and Heuristic Solution. IEEE Transactions on Knowledge and Data Engineering, 9(1), 1997. Google Scholar
- {GA93} Grefen P.W.P.J., Apers P.M.G. Integrity Control in Relational Database Systems - An Overview. Data & Knowledge Engineering, 10:187-223, 1993. Google Scholar
- {GdB94} Grefen P.W.P.J., de By R.A. A Multi-Set Extended Relational Algebra: a Formal Approach to a Practical Issue. In: Proc. of the 10th IEEE Int. Conf. on Data Engineering, ICDE'94, Houston, Tex., USA, 14-18 February 1994, pp. 80-89. IEEE Computer Society, Los Alamitos, Calif., 1994. Google Scholar
- {Ger96} Gertz M. Diagnosis and Repair of Constraint Violations in Database Systems, Dissertationen zu Datenbanken und Informationssystemen, 19. infix-Verlag, Sankt Augustin, 1996.Google Scholar
- {GFA92} Grefen P.W.P.J., Flokstra J., Apers P.M.G. Performance Evaluation of Integrity Control in a Parallel Main-Memory Database System. In: Tjoa A.M., Ramos I., (eds.), Database and Expert System Applications, Proc. of the 3rd Int. Conf., DEXA'92, Valencia, Spain, pp. 96-101. Springer, Wien, 1992.Google Scholar
- {GL96} Gertz M., Lipeck U.W. Deriving Optimized Integrity Monitoring Triggers from Dynamic Integrity Constraints. Data & Knowledge Engineering, 20(2):163- 193, 1996. Google Scholar
- {GP99} Gulutzan P., Pelzer T. SQL-99 Complete, Really. R&D Books, Lawrence, Kansas, 1999.Google Scholar
- {GR93} Gray J., Reuter A. Transaction Processing: concepts and Techniques. Morgan Kaufmann, San Mateo, Calif., 1993. Google Scholar
- {Gra78} Gray J. Notes on Data Base Operating Systems. In: Bayer R., Graham R.M., Seegmüller G., (eds.), Operating Systems, An Advanced Course, Lecture Notes in Computer Science, vol. 60, pp. 393-481. Springer, Berlin Heidelberg New York, 1978. Google Scholar
- {Gre92} Grefen P.W.P.J. Integrity Control in Parallel Database Systems. PhD thesis, University of Twente, The Netherlands, 1992.Google Scholar
- {GSW96a} Guo S., Sun W., Weiss M.A. On Satisfiability, Equivalence, and Implication Problems Involving Conjunctive Queries in Database Systems. IEEE Transactions on Knowledge and Data Engineering, 8(4):604-616, 1996. Google Scholar
- {GSW96b} Guo S., Sun W., Weiss M.A. Solving Satisfiability and Implication Problems in Database Systems. ACM Transactions on Database Systems, 21(2):270-293, 1996. Google Scholar
- {HM75} Hammer M.M., McLeod D.J. Semantic Integrity in a Relational Data Base System. In: Kerr D.S., (ed.), Proc. of the 1st Int. Conf. on Very Large Data Bases, VLDB'75, September 24-24, 1975, Framingham, Mass., USA, pp. 25-47. Morgan Kaufmann, Los Altos, Calif., September 1975.Google Scholar
- {Hor92} Horowitz B.M. A Run-time Execution Model for Referential Integrity Maintenance. In: Golshani F., (ed.), Proc. of the 8th IEEE Int. Conf. on Data Engineering, ICDE'92, Tempe, Ariz., USA, February 2-3, 1992, pp. 548-556. IEEE Computer Society, Los Alamitos, Calif., 1992. Google Scholar
- {HS95} Herzog U., Schaarschmidt R. Parallel Execution of Integrity Constraint Checks. In: Pissinou N., Silberschatz A., Park E.K., Makki K., (eds.), Proc. of the 4th Conf. on Information and Knowledge Management (CIKM'95), Baltimore, Md., USA, pp. 218-225, ACM, November 1995. Google Scholar
- {IBM00} IBM Corporation. IBM DB2 Universal Database: SQL Reference, Version 7, 2000.Google Scholar
- {Inf99} Informix Corporation, Menlo Park, CA. Informix Guide to SQL: Syntax, Informix Dynamic Server. 2000, Version 9.2, December 1999. Google Scholar
- {Ing99} Ingres Corporation. Ingres Database Administrator's Guide, Version II, 1999.Google Scholar
- {Int99} International Organization for Standardization (ISO) & American National Standards Institute (ANSI), ANSI/ ISO/IEC 9075-2:99. ISO International Standard: database Language SQL - Part 2: Foundation (SQL/Foundation), September 1999.Google Scholar
- {KMC99} Kulkarni K., Mattos N., Cochrane R. Active Database Features in SQL3. In: Paton N., (ed.), Active Rules in Database Systems, pp. 198-218, Springer, Berlin Heidelberg New York, 1999.Google Scholar
- {Mai83} Maier D. The Theory of Relational Databases. Computer Science, Rockville, Md., 1983. Google Scholar
- {Mar91} Markowitz V.M. Safe Referential Integrity Structures in Relational Databases. In: Lohmann G.M., Sernadas A., Camps R., (eds.), Proc. of the 17th Int. Conf. on Very Large Data Bases, VLDB'91, Barcelona, Spain, September 3-6, 1991, pp. 123-132. Morgan Kaufmann, San Mateo, Calif., 1991. Google Scholar
- {Mar94} Markowitz V.M. Safe Referential Integrity and Null Constraint Structures in Relational Databases. Information Systems, 19(4):359-378, 1994. Google Scholar
- {Mic99} Microsoft Corporation. Microsoft SQL Server, Version 7.0, 1999.Google Scholar
- {MS93} Melton J., Simon A.R. Understanding the New SQL - A Complete Guide. Morgan Kaufmann, San Mateo, Calif., 1993. Google Scholar
- {Nic82} Nicolas J.-M. Logic for Improving Integrity Checking in Relational Data Bases. Acta Informatica, 18(3):227- 253, 1982.Google Scholar
- {Ora99} Oracle Corporation. Oracle8i SQL Reference, Release 8.1.6, December 1999.Google Scholar
- {Pat99} Paton N.W., (ed.). Active Rules in Database Systems. Springer, New York, 1999. Google Scholar
- {PD99} Paton N.W., Díaz O. Introduction. In: Paton N., (ed.), Active Rules in Database Systems, pp. 3-27, Springer, Berlin Heidelberg New York, 1999.Google Scholar
- {RH80} Rosenkrantz D.J., Hunt III H.B. Processing Conjunctive Predicates and Queries. In: Taylor A., (ed.), Proc. of the 6th Int. Conf. on Very Large Data Bases, VLDB'80, Montreal, Quebec, Canada, October 1-3, 1980, pp. 64- 72. IEEE Computer Society, Los Altos, Calif., 1980.Google Scholar
- {RH96} Reinert J., Härder T. Access Path Support for Referential Integrity in SQL2. The VLDB Journal, 5(3):196-214, July 1996. Google Scholar
- {RSSS98} Ross K.A., Srivastava D., Stuckey P.J., Sudarshan S. Foundations of Aggregation Constraints. Theoretical Computer Science, 193(1-2):149-179, 1998. Google Scholar
- {SKN89} Sun X., Kamel N.N., Ni L.M. Processing Implications on Queries. IEEE Transactions on Software Engineering, 15(10):1168-1175, 1989. Google Scholar
- {SKS97} Silberschatz A., Korth H.F., Sudarshan S. Database System Concepts. McGraw-Hill, New York, 3rd edn., 1997. Google Scholar
- {Sri93} Srivastava D. Subsumption and Indexing in Constraint Query Languages with Linear Arithmetic Constraints. Annals of Mathematics and Artificial Intelligence, 8(3- 4):315-343, 1993.Google Scholar
- {SST97} Saake G., Schmitt I., Türker C. Object Databases - Concepts, Languages, Architectures. International Thomson, Bonn, 1997. (In German).Google Scholar
- {Sto75} Stonebraker M. Implementation of Integrity Constraints and Views by Query Modification. In: King W.F., (ed.), Proc. of the 1975 ACM SIGMOD Int. Conf. on Management of Data, San Jose, Calif., pp. 65-78, ACM, 1975. Google Scholar
- {Syb99} Sybase Inc. Transact-SQL User Guide, Version 11.0, 1999.Google Scholar
- {Syb00} Sybase Inc. Sybase Adaptive Server Anywhere Reference, Version 6.0.3, 2000.Google Scholar
- {Tür99a} Türker C. Integrity Constraints and Specialization in Object Databases. In: Buchmann A.P., (ed.), BTW'99, Datenbanksysteme in Büro, Technik und Wissenschaft, GI-Fachtagung, Freiburg, März 1999, Informatik aktuell, pp. 369-378. Springer, Berlin Heidelberg New York, 1999. (In German).Google Scholar
- {Tür99b} Türker C. SemanticInte grity Constraints in Federated Database Schemata, Dissertationen zu Datenbanken und Informationssystemen, 63. infix-Verlag, Sankt Augustin, 1999.Google Scholar
- {Tür01} Türker C. Schema Evolution in SQL-99 and Commercial (Object-) Relational DBMS. In: Balsters H., De Brock B., Conrad S., (eds.), Database Schema Evolution and Meta-Modeling, 9th Int. Workshop on Foundations of Models and Languages for Data and Objects, Dagstuhl Castle, Germany, September 18-22, 2000, Post-Proceedings, Lecture Notes in Computer Science, vol. 2065. Springer, Berlin Heidelberg New York, 2001. To appear.Google Scholar
- {Ull88} Ullman J.D. Principles of Database and Knowledge-Base Systems, Volume I: classical Database Systems. Computer Science, Rockville, Md., 1988. Google Scholar
- {Ull89} Ullman J.D. Principles of Database and Knowledge-Base Systems, Volume II: the New Technologies. Computer Science, Rockville, Md., 1989. Google Scholar
- {WC96} Widom J., Ceri S., (eds.). Active Database Systems - Triggers and Rules for Advanced Database Processing. Morgan Kaufmann, San Francisco, Calif., 1996. Google Scholar
- {ZCF+97} Zaniolo C., Ceri S., Faloutsos C., Snodgrass R.T., Subrahmaniam V.S., Zicari R., (eds.). Advanced Database Systems. Morgan Kaufmann, San Francisco, Calif., 1997. Google Scholar
- {ZH90} ZhouY., Hsu M. ATheory for Rule Triggering Systems. In: Bancilhon F., Thanos C., Tsichritzis D., (eds.), Advances in Database Technology - EDBT'90, Proc. of the 2rd Int. Conf. on Extending Database Technology, Lecture Notes in Computer Science, vol. 416, pp. 407-421. Springer, Berlin Heidelberg New York, 1990. Google Scholar
Index Terms
- Semantic integrity support in SQL:1999 and commercial (object-)relational database management systems
Recommendations
Extending UML for Object-Relational Database Design
«UML» '01: Proceedings of the 4th International Conference on The Unified Modeling Language, Modeling Languages, Concepts, and ToolsThe most common way of designing databases is using de E/R model without taking into account other views of the system. However, new object-oriented design languages, such as UML (Unified Modelling Language), permit modelling the full system, including ...
Storing OWL ontologies in SQL3 object-relational databases
AIC'08: Proceedings of the 8th conference on Applied informatics and communicationsWhen a large amount of data is stored in OWL files, it is not efficient to maintain and query those data. The OWL syntax is based on XML, which is a meta-markup language. Thus, it is suitable for data description and data exchange, rather than for data ...
A Comparative Study of Various Nested Normal Forms
As object-relational databases (ORDBs) become popular in the industry, it is important for database designers to produce database schemes with good properties in these new kinds of databases. One distinguishing feature of an ORDB is that its tables may ...
Comments