ABSTRACT
The SQL standard offers three primitive operations (insert, delete, and update which is here called modify) to update a relation based on a generic query. This paper compares the expressiveness of programs composed of these three operations, with the general notion of update that simply replaces the content of the relation by the result of a query. It turns out that replacing cannot be expressed in terms of insertions, deletions, and modifications, and neither can modifications be expressed in terms of insertions and deletions. The expressive power gained by if-then-else control flow in programs is investigated as well. Different ways to perform replacing are discussed: using a temporary variable; using the new SQL merge operation; using SQL's data change delta tables; or using queries involving object creation or arithmetic. Finally the paper investigates the power of alternating the different primitives. For example, an insertion followed by a modification cannot always be expressed as a modification followed by an insertion.
- S. Abiteboul. Updates, a new frontier. In M. Gyssens, J. Paredaens, and D. Van Gucht, editors, ICDT'88, volume 326 of Lecture Notes in Computer Science, pages 1--18. Springer-Verlag, 1988. Google Scholar
Digital Library
- S. Abiteboul, R. Hull, and V. Vianu. Foundations of Databases. Addison-Wesley, 1995. Google Scholar
Digital Library
- S. Abiteboul and P. Kanellakis. Object identity as a query language primitive. J. ACM, 45(5):798--842, 1998. Google Scholar
Digital Library
- S. Abiteboul and V. Vianu. Equivalence and optimization of relational transactions. J. ACM, 35:70--120, 1988. Google Scholar
Digital Library
- S. Abiteboul and V. Vianu. Procedural languages for database queries and updates. J. Comput. Syst. Sci., 41(2):181--229, 1990. Google Scholar
Digital Library
- S. Abiteboul and V. Vianu. Datalog extensions for database queries and updates. J. Comput. Syst. Sci., 43(1):62--124, 1991. Google Scholar
Digital Library
- A. Aho and J. Ullman. Universality of data retrieval languages. In Conference Record, 6th ACM Symposium on Principles of Programming Languages, pages 110--120, 1979. Google Scholar
Digital Library
- M. Andries, L. Cabibbo, J. Paredaens, and J. Van den Bussche. Applying an update method to a set of receivers. ACM Trans. Database Syst., 25(1):1--40, 2001. Google Scholar
Digital Library
- J. Bang-Jensen and G. Gutin. Digraphs. Springer, second edition, 2009.Google Scholar
- M. Benedikt, A. Bonifati, S. Flesca, and A. Vyas. Verification of tree updates for optimization. In K. Etessami and S. Rajamani, editors, Computer Aided Verification, 17th International Conference, volume 3576 of Lecture Notes in Computer Science, pages 379--393. Springer, 2005. Google Scholar
Digital Library
- M. Benedikt and J. Cheney. Semantics, types and effects for XML updates. In P. Gardner and F. Geerts, editors, Proceedings 12th International Symposium on Database Programming Languages, volume 5708 of Lecture Notes in Computer Science, pages 1--17. Springer, 2009. Google Scholar
Digital Library
- M. Benedikt and J. Cheney. Destabilizers and independence of XML updates. Proceedings VLDB, 3(1):906--917, 2010. Google Scholar
Digital Library
- M. Benedikt, D. Florescu, P. Gardner, G. Guerrini, M. Mesiti, and E. Waller. Report on the EDBT/ICDT 2010 workshop on updates in XML. SIGMOD Record, 39(1):54--57, 2010. Google Scholar
Digital Library
- A. Chandra and D. Harel. Computable queries for relational data bases. J. Comput. Syst. Sci., 21(2):156--178, 1980.Google Scholar
Cross Ref
- A. Chandra and D. Harel. Structure and complexity of relational queries. J. Comput. Syst. Sci., 25:99--128, 1982.Google Scholar
Cross Ref
- Database reference--SQL--statements--MERGE. IBM DB2 10.1 Information Center for Linux, UNIX, and Windows, http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/, 15 November 2012.Google Scholar
- S. Dekeyser, J. Hidders, and J. Paredaens. A transaction model for XML databases. World Wide Web: Internet and Web Information Systems, 7:29--57, 2004. Google Scholar
Digital Library
- B. Freitag, H. Decker, M. Kifer, and A. Voronkov, editors. Transactions and Change in Logic Databases, volume 1472 of Lecture Notes in Computer Science. Springer, 1998.Google Scholar
- G. Ghelli, K. Høgsbro Rose, and J. Siméon. Commutativity analysis for XML updates. ACM Trans. Database Syst., 33(4):article 29, 2008. Google Scholar
Digital Library
- E. Grädel and S. Siebertz. Dynamic definability. In Proceedings 15th International Conference on Database Theory, 2012. Google Scholar
Digital Library
- J. Hidders, J. Paredaens, and R. Vercammen. On the expressive power of XQuery-based update languages. In Database and XML Technologies, Proceedings 4th XSym, volume 4156 of Lecture Notes in Computer Science, pages 92--106. Springer, 2006. Google Scholar
Digital Library
- R. Hull and C. Yap. The format model, a theory of database organization. J. ACM, 31(3):518--537, 1984. Google Scholar
Digital Library
- D. Karabeg and V. Vianu. Simplification rules and complete axiomatization for relational update transactions. ACM Trans. Database Syst., 16(3):439--475, 1991. Google Scholar
Digital Library
- A. Klug. Calculating constraints on relational expressions. ACM Trans. Database Syst., 5(3):260--290, 1980. Google Scholar
Digital Library
- C. Laasch and M. Scholl. Deterministic semantics of set-oriented update sequences. In Proceedings, 9th International Conference on Data Engineering, pages 4--13. IEEE Computer Society Press, 1993. Google Scholar
Digital Library
- A. Levy and Y. Sagiv. Queries independent of updates. In R. Agrawal, S. Baker, and D. Bell, editors, Proceedings 19th International Conference on Very Large Data Bases, pages 171--181. Morgan Kaufmann, 1993. Google Scholar
Digital Library
- U. W. Lipeck and B. Thalheim, editors. Modelling Database Dynamics. Workshops in Computing. Springer-Verlag, 1992.Google Scholar
- MySQL reference manuals. http://dev.mysql.com/doc/.Google Scholar
- Information technology -- database languages -- SQL -- part 2: Foundations. International Standard ISO/IEC 9075, 2012.Google Scholar
- I. Tatarinov, Z. Ives, A. Halevy, and D. Weld. Updating XML. In Proceedings 2001 ACM SIGMOD International Conference on Management of Data, pages 413--424. ACM Press, 2001. Google Scholar
Digital Library
- J. Van den Bussche, D. Van Gucht, M. Andries, and M. Gyssens. On the completeness of object-creating database transformation languages. J. ACM, 44(2):272--319, 1997. Google Scholar
Digital Library
- XQuery update facility 1.0. W3C Recommendation 17 March 2011.Google Scholar
- F. Zemke. What's new in SQL:2011. SIGMOD Record, 41(1):67--73, 2012. Google Scholar
Digital Library
Index Terms
On the expressive power of update primitives
Recommendations
PATAXÓ: A framework to allow updates through XML views
XML has become an important medium for data exchange, and is frequently used as an interface to (i.e., a view of) a relational database. Although a lot of work has been done on querying relational databases through XML views, the problem of updating ...
Expressive Power and Succinctness of the Positive Calculus of Relations
Relational and Algebraic Methods in Computer ScienceAbstractIn this paper, we study the expressive power and succinctness of the positive calculus of relations. We show that (1) the calculus has the same expressive power as that of three-variable existential positive (first-order) logic in terms of binary ...
Semantics and Expressive Power of Nondeterministic Constructs in Deductive Databases
Nondeterministic extensions are needed in logic-based languages, such as first-order relational languages and Datalog, to enhance their expressive power and support the efficient formulation of low-complexity problems and database queries. In this paper,...






Comments