Geodesic
Updating of multi-table views based on commutative database transformations
Vestnik Ûžno-Uralʹskogo gosudarstvennogo universiteta. Seriâ Vyčislitelʹnaâ matematika i informatika, Tome 8 (2019) no. 2, pp. 92-106 Cet article a éte moissonné depuis la source Math-Net.Ru

Voir la notice de l'article

In modern relational database technologies, views implement the external layer of the ANSI-SPARC architecture, which encapsulates details of the database conceptual structure from end-users. However, when using views, we need to solve the problem of correct view updating: DBMS must execute insertion, deletion, and updating tuples of the view while providing correct modifications of corresponding target relation(s) of this view. To solve this problem, the SQL standard introduces a strict restriction: only one tuple in the target relation can correspond to the modified tuple in the view. In addition, triggers are not a satisfactory solution of this problem because of necessity of such a trigger for each view of the database, and unpredictable sequence in execution of triggers that belong to the same view, etc. The paper presents an approach to solve the problem of correct view updating based on the commutative database transformations. This does not limit the tuple uniqueness in the target relation that corresponds to the updated tuple in the view. We describe the DBMS Coprocessor, which is deployed on the client computer and provides commutative transformations in the database relations stored on the server side. The coprocessor generates a transaction's script that implements commutative transformations and runs the transaction on the server. We present implementation of the Coprocessor for the PostgreSQL open-source DBMS. Experimental evaluation confirms the effectiveness of the proposed approach in OLAP and OLTP applications.
Mots-clés : commutative transformation
Keywords: relational algebra, multi-table view, view updating, relational DBMS, trigger. 
@article{VYURV_2019_8_2_a5,
     author = {V. S. Zykin and M. L. Zymbler},
     title = {Updating of multi-table views based on commutative database transformations},
     journal = {Vestnik \^U\v{z}no-Uralʹskogo gosudarstvennogo universiteta. Seri\^a Vy\v{c}islitelʹna\^a matematika i informatika},
     pages = {92--106},
     year = {2019},
     volume = {8},
     number = {2},
     language = {ru},
     url = {http://geodesic.mathdoc.fr/item/VYURV_2019_8_2_a5/}
}
TY  - JOUR
AU  - V. S. Zykin
AU  - M. L. Zymbler
TI  - Updating of multi-table views based on commutative database transformations
JO  - Vestnik Ûžno-Uralʹskogo gosudarstvennogo universiteta. Seriâ Vyčislitelʹnaâ matematika i informatika
PY  - 2019
SP  - 92
EP  - 106
VL  - 8
IS  - 2
UR  - http://geodesic.mathdoc.fr/item/VYURV_2019_8_2_a5/
LA  - ru
ID  - VYURV_2019_8_2_a5
ER  - 
%0 Journal Article
%A V. S. Zykin
%A M. L. Zymbler
%T Updating of multi-table views based on commutative database transformations
%J Vestnik Ûžno-Uralʹskogo gosudarstvennogo universiteta. Seriâ Vyčislitelʹnaâ matematika i informatika
%D 2019
%P 92-106
%V 8
%N 2
%U http://geodesic.mathdoc.fr/item/VYURV_2019_8_2_a5/
%G ru
%F VYURV_2019_8_2_a5
V. S. Zykin; M. L. Zymbler. Updating of multi-table views based on commutative database transformations. Vestnik Ûžno-Uralʹskogo gosudarstvennogo universiteta. Seriâ Vyčislitelʹnaâ matematika i informatika, Tome 8 (2019) no. 2, pp. 92-106. http://geodesic.mathdoc.fr/item/VYURV_2019_8_2_a5/

[1] V. S. Zykin, Multi-table Data View Editor: Certificate of State Registration of Computer Programs – No. 2018661249, registration date: 04.09.2018; Copyright holder: Omsk State Technical University.

[2] V. S. Zykin, “Referential Integrity of Data in Corporate Information Systems”, Informatics and Applications, 9:3 (2015), 119–127

[3] S. V. Zykin, V. S. Zykin, “Commutative Conversion in the Database when Editing a Multitable Query”, Information Technologies, 24:5 (2018), 330–338 | DOI

[4] F. Bancilhon, N. Spyratos, “Update Semantics of Relational Views”, ACM Trans. Database Syst, 6:4 (1981), 557–575 | DOI

[5] L. Bertossi, B. Salimi, “Causes for Query Answers from Databases: Datalog Abduction, View-updates, and Integrity Constraints”, Int. J. Approx. Reason, 90 (2017), 226–252 | DOI

[6] U. Dayal, P. A. Bernstein, “On the Correct Translation of Update Operations on Relational Views”, ACM Trans. Database Syst, 7:3 (1982), 381–416 | DOI

[7] H. Garcia-Molina, J. D. Ullman, J. Widom, Database System Implementation, Prentice Hall, 2000, 653 pp.

[8] S. Ghandeharizadeh, J. Yap, “SQL Query to Trigger Translation: A Novel Transparent Consistency Technique for Cache Augmented SQL Systems”, Proceedings of the 28th International Workshop on Database and Expert Systems Applications, DEXA 2017 (August 28–31, 2017, Lyon, France), 37–41 | DOI

[9] G. Gottlob, P. Paolini, R. Zicari, “Properties and Update Semantics of Consistent Views”, ACM Trans. Database Syst, 13:4 (1988), 486–524 | DOI

[10] Y. Hayamizu, R. Kawamichi, K. Goda, M. Kitsuregawa, “Benchmarking and Performance Analysis of Event Sequence Queries on Relational Database”, Proceedings of the 10th TPC Technology Conference Performance Evaluation and Benchmarking for the Era of Artificial Intelligence, TPCTC (August 27–31, 2018, Rio de Janeiro, Brazil), 110–125 | DOI

[11] “Interim Report: ANSI/X3/SPARC Study Group on Data Base Management Systems”, FDT - Bulletin of ACM SIGMOD, 7:2 (1975), 1–140

[12] ISO/IEC 9075:1987 Information Technology. Database Languages. SQL., Washington, 1987

[13] ISO/IEC 9075-11:2016 Information technology. Database languages. SQL., Part 11: Information and Definition Schemas (SQL/Schemata), Washington, 2016, 327 pp.

[14] A. Keller, “Algorithms for Translating View Updates to Database Updates for Views Involving Selections, Projections and Joins”, Proceedings of the 4th ACM SIGACT-SIGMOD Symposium on Principles of Database Systems, PODS’85 (March 25–27, 1985, Portland, USA), ACM, 1985, 154–163 | DOI

[15] R. Langerak, “View Updates in Relational Databases with an Independent Scheme”, ACM Trans. Database Syst, 15:1 (1990), 40–66 | DOI

[16] J. Lechtenb\"ørger, “The Impact of the Constant Complement Approach Towards View Updating”, Proceedings of the 22nd ACM SIGMOD-SIGACT-SIGART Symposium on Principles of Database Systems, PODS’03 (June 9–11, 2003, San Diego, CA, USA), ACM, 2003, 49–55 | DOI

[17] Y. Li, C. Levine, “Extending TPC-E to Measure Availability in Database Systems”, Proceedings of the 10th Technology Conference Measurement and Characterization (August 29 – September 3, 2011, Seattle, WA, USA), 111–122 | DOI

[18] Y. Masunaga, “A Relational Database View Update Translation Mechanism”, Proceedings of the 10th International Conference on Very Large Data Bases, VLDB’84 (August 27–31, 1984, Singapore), 309–320

[19] S. V. Mosin, S. V. Zykin, “Truth Space Method for Caching Database Queries”, Modelirovanie i analiz informatsionnykh sistem, 22:2 (2015), 248–258

[20] M. Stonebraker, “Triggers and Inference In Database Systems”, On Knowledge Base Management Systems (Islamorada), 1985, 297–314