Model for dynamic and hierarchical data repository in relational database

• Autorzy: Piech M. , Fracz W. , Turek W. , Kisiel-Dorohinicki M. , Dajda J. , Byrski A.

Identyfikatory

DOI

10.7494/csci.2018.19.4.3088

• Języki publikacji: EN

Abstrakty

EN

The aim of this research is to build an open schema model for a digital sources repository in a relational database. This required us to develop a few advanced techniques. One of them was to keep and maintain a hierarchical data structure pushed into the repository. A second was to create constraints on any hierarchical level that allows for the enforcement of data integrity and consistency. The created solution is mainly based on a JSON file as a native column type, which was designed for holding open schema documents. In this paper, we present a model for any repository that uses hierarchical dynamic data. Additionally, we include a structure for normalizing the input and description for the data in order to keep all of the model assumptions. We compared our solution with a well-known open schema model – Entity-Attribute-Value – in the scope of saving data and querying about relationships and contents from the structure. The results show that we achieved improvements in both the performance and disk space usage, as we extended our model with a few new features that the previous model does not include. The techniques developed in this research can be applied in every domain where hierarchical dynamic data is required, as demonstrated by the digital book repository that we have presented.

Słowa kluczowe

EN

JSON; relational databases; EAV; CQRS; PostgreSQL; open schema model

• Wydawca: Wydawnictwa AGH
• Czasopismo: Computer Science
• Rocznik: 2018
• Tom: Vol. 19 (4)
• Strony: 479--500
• Opis fizyczny: Bibliogr. 18 poz., rys., tab., wykr.

Twórcy

autor

Piech M.

• AGH University of Science and Technology, Faculty of Computer Science, Electronics and Telecommunications, Department of Computer Science, Krakow, Poland

autor

Fracz W.

• AGH University of Science and Technology, Faculty of Computer Science, Electronics and Telecommunications, Department of Computer Science, Krakow, Poland

autor

Turek W.

• AGH University of Science and Technology, Faculty of Computer Science, Electronics and Telecommunications, Department of Computer Science, Krakow, Poland

autor

Kisiel-Dorohinicki M.

• AGH University of Science and Technology, Faculty of Computer Science, Electronics and Telecommunications, Department of Computer Science, Krakow, Poland

autor

Dajda J.

• AGH University of Science and Technology, Faculty of Computer Science, Electronics and Telecommunications, Department of Computer Science, Krakow, Poland

autor

Byrski A.

• AGH University of Science and Technology, Faculty of Computer Science, Electronics and Telecommunications, Department of Computer Science, Krakow, Poland

Bibliografia

• [1] Chasseur C., Li Y., Patel J.M.: Enabling JSON Document Stores in Relational Systems. In: Proceedings of the 16th International Workshop on the Web and Databases 2013, WebDB 2013, New York, NY, USA, June 23, pp. 1–6, 2013.
• [2] Chen H.: Javascript object notation schema definition language. US13596694, 2014. https://www.google.com/patents/US20140067866.
• [3] Chen R.S., Nadkarni P., Marenco L., Levin F., Erdos J., Miller P.L.: Exploring Performance Issues for a Clinical Database Organized Using an Entity-Attribute- -Value Representation, Journal of the American Medical Informatics Association, vol. 7(5), pp. 475–487, 2000. https://www.ncbi.nlm.nih.gov/pmc/articles/ PMC79043/.
• [4] DB-Engines Ranking of Search Engines. https://db-engines.com/en/ranking/ search+engine.
• [5] Fowler M.: CQRS. Martin Fowler’s Blog, 2011. https://martinfowler.com/b liki/CQRS.html.
• [6] Johnson S.B.: Generic data modeling for clinical repositories Journal of the American Medical Informatics Association, vol. 3(5), pp. 328–339, 1996. https: //www.ncbi.nlm.nih.gov/pmc/articles/PMC116317/.
• [7] Kilic U., Aksakalli K.I.: Comparison of Solr and Elasticsearch Among Popular Full Text Search Engines and Their Security Analysis. https://doi.org/10.1 3140/RG.2.2.24563.32803.
• [8] Liu Z.H., Hammerschmidt B., McMahon D.: JSON data management: supporting schema-less development in RDBMS. In: SIGMOD’14 Proceedings of the 2014 ACM SIGMOD International Conference on Management of Data, pp. 1247–1258, 2014. https://doi.org/10.1145/2588555.2595628.
• [9] Liu Z.H., Hammerschmidt B., McMahon D., Liu Y., Chang H.J.: Closing the functional and Performance Gap between SQL and NoSQL. In: SIGMOD’16 Proceedings of the 2016 International Conference on Management of Data, pp. 227–238, 2016. https://doi.org/10.1145/2882903.2903731.
• [10] McHugh J., Abiteboul S., Goldman R., Quass D., Widom J.: Lore: A Database Management System for Semistructured Data, ACM SIGMOD Record, vol. 26(3), pp. 54–66, 1997. https://doi.org/10.1145/262762.262770.
• [11] Nadkarni P.M., Marenco L., Chen R., Skoufos E., Shepherd G., Miller P.: Organization of Heterogeneous Scientific Data Using the EAV/CR Representation, Journal of the American Medical Informatics Association, vol. 6(6), pp. 478–493, 1999. https://www.ncbi.nlm.nih.gov/pmc/articles/PMC61391/.
• [12] Orend K.: Analysis and Classification of NoSQL Databases and Evaluation of Their Ability to Replace an Object-Relational Persistence Layer. Master’s Thesis, Technische Universität München, 2010.
• [13] Piech M., Marcjan R.: A new approach to storing dynamic data in relational databases using JSON, Computer Science, vol. 19(1), pp. 3–20, 2018. https: //doi.org/10.7494/csci.2018.19.1.2505.
• [14] Rys M.: XML and relational database management systems: inside Microsoft R SQL ServerTM 2005. In: SIGMOD’05 Proceedings of the 2005 ACM SIGMOD international conference on Management of data, pp. 958–962, 2005. https:// doi.org/10.1145/1066157.1066301.
• [15] Sleator D.D., Tarjan R.E.: A Data Structure for Dynamic Trees, Journal of Computer and System Sciences, vol. 26(3), pp. 362–391, 1983. https://doi.or g/10.1016/0022-0000(83)90006-5.
• [16] Tahara D., Diamond T., Abadi D.J.: Sinew: A SQL System for Multi-Structured Data. In: SIGMOD’14 Proceedings of the 2014 ACM SIGMOD International Conference on Management of Data, pp. 815–826, 2014. https://doi.org/10.1 145/2588555.2612183.
• [17] Tatarinov I., Viglas S.D., Beyer K., Shanmugasundaram J., Shekita E., Zhang C.: Storing and Querying Ordered XML Using a Relational Database System. In: SIGMOD’02 Proceedings of the 2002 ACM SIGMOD international conference on Management of data, pp. 204–215, 2002. https://doi.org/10.1145/564691.5 64715.
• [18] Whang K.Y., Park B.K., Han W.S., Lee Y.K.: Inverted index storage structure using subindexes and large objects for tight coupling of information retrieval with database management systems, US Patent US6349308, 2002. https://patents. google.com/patent/US6349308.