Relational into Non-Relational Database Migration with Multiple-Nested Schema Methods on Academic Data

Teguh Bharata Adji; Dwi Retno Puspita Sari; Noor Akhmad Setiawan

doi:10.22146/ijitee.46503

Relational into Non-Relational Database Migration with Multiple-Nested Schema Methods on Academic Data

https://doi.org/10.22146/ijitee.46503

Teguh Bharata Adji^(1*), Dwi Retno Puspita Sari⁽²⁾, Noor Akhmad Setiawan⁽³⁾

(1) Universitas Gadjah Mada
(2) Universitas Gadjah Mada
(3) Universitas Gadjah Mada
(*) Corresponding Author

Abstract

The rapid development of internet technology has increased the need of data storage and processing technology application. One application is to manage academic data records at educational institutions. Along with massive growth of information, decrement in the traditional database performance is inevitable. Hence, there are many companies choose to migrate to NoSQL, a technology that is able to overcome the traditional database shortcomings. However, the existing SQL to NoSQL migration tools have not been able to represent SQL data relations in NoSQL without limiting query performance. In this paper, a relational database transformation system transforming MySQL into non-relational database MongoDB was developed, using the Multiple Nested Schema method for academic databases. The development began with a transformation scheme design. The transformation scheme was then implemented in the migration process, using PDI/Kettle. The testing was carried out on three aspects, namely query response time, data integrity, and storage requirements. The test results showed that the developed system successfully represented the relationship of SQL data in NoSQL, provided complex query performance 13.32 times faster in the migration database, basic query performance involving SQL transaction tables 28.6 times faster on migration results, and basic performance Queries without involving SQL transaction tables were 3.91 times faster in the migration source. This shows that the theory of the Multiple Nested Schema method, aiming to overcome the poor performance of queries involving many JOIN operations, is proved. In addition, the system is also proven to be able to maintain data integrity in all tested queries. The space performance test results indicated that the migrated database transformed using the Multiple Nested Schema method showed a storage requirement of 10.53 times larger than the migration source database. This is due to the large amount of data redundancy resulting from the transformation process. However, at present, storage performance is not a top priority in data processing technology, so large storage requirements are a consequence of obtaining efficient query performance, which is still considered as the first priority in data processing technology.

Keywords

Multiple Nested Schema, Data Transformation, Data Migration, NoSQL, Big Data

Full Text:

PDF

References

R.M. Stair and G.W. Reynolds, Fundamentals of Information Systems, Boston, USA: Courses Technology, 2008.

K. Hallgren (2016) “How to Approach Data-Driven Decisions in Education,” [Online] https://www.mathematica-mpr.com/commentary/ data-driven-decisions-in-education, access date: 08-Nov-2018.

J. Manyika, M. Chui, B. Brown, J. Bughin, R. Dobbs, C. Roxburgh, and A.H. Byers, “Big Data: The Next Frontier for Innovation, Competition, and Productivity,” McKinsey Co. Tech. Report, pp. 1-156, 2011.

S. Yin and O. Kaynak “Big Data for Modern Industry: Challenges and Trends,” Proc. of the IEEE, Vol. 103, No. 2, pp. 143–146, 2015.

G. Zhao, L. Li, Z. Li, and Q. Lin, “Multiple Nested Schema of Hbase for Migration from SQL,” Proc. - 2014 9th Int. Conf. P2P, Parallel, Grid, Cloud Internet Comput. 3PGCIC 2014, 2014, pp. 338–343.

A. Nayak, A. Poriya, and D. Poojary, “Type of NOSQL Databases and Its Comparison with Relational Databases,” Int. J. Appl. Inf. Syst., Vol. 5, No. 4, pp. 16–19, 2013.

V. Manoj, “Comparative Study of Nosql Document, Column Store Databases and Evaluation of Cassandra,” Int. J. Database Manag. Syst., Vol. 6, No. 4, pp. 11–26, 2014.

K. Chodorow, MongoDB: The Definitive Guide 2nd Edition. USA: O’Reilly Media, Inc., 2013.

J. Speelpenning, J. Lounsberry, and A. Price-budgen, “Data Modeling and Relational Database Design Publishers,” Oracle, Student Guide, Vol. 1, pp. 1-320, July, 2001.

M. Dagar, S. Mittal, and M. Singh, “Conversion from Relational-Based Database to Column-Based Database,” Int. J. Sci. Res. Comput. Sci., Vol. 1, No. 1, pp. 29–35, 2013.

W.C. Chung, H.P. Lin, S.C. Chen, M.F. Jiang, and Y.C. Chung, “JackHare: a Framework for SQL to NoSQL Translation Using MapReduce,” Autom. Softw. Eng., Vol. 21, No. 4, pp. 489–508, 2014.

I.G. Winaya and A. Ashari, “Transformasi Skema Basis Data Relasional Menjadi Model Data Berorientasi Dokumen pada Mongodb,” Indonesian Journal of Computing and Cybernetics Systems (IJCCS), Vol. 10, No. 1, 2015.

L. Rocha, F. Vale, E. Cirilo, D. Barbosa, and F. Mourão, “A Framework for Migrating Relational Datasets to NoSQL,” Procedia Comput. Sci., Vol. 51, No. 1, pp. 2593–2602, 2015.

G. Liyanaarachchi, L. Kasun, M. Nimesha, K. Lahiru, and A. Karunasena, “MigDB - Relational to NoSQL Mapper,” 2016 IEEE Int. Conf. Inf. Autom. Sustain. Interoper. Sustain. Smart Syst. Next Gener. (ICIAfS 2016), 2016, pp. 1-6.

G. Zhao, Q. Lin, L. Li, and Z. Li, “Schema Conversion Model of SQL Database to NoSQL,” Proc. - 2014 9th Int. Conf. P2P, Parallel, Grid, Cloud Internet Comput. 3PGCIC 2014, 2014, pp. 355–362.

C. Li, “Transforming Relational Database into HBase: A Case Study,” Proc. 2010 IEEE Int. Conf. Softw. Eng. Serv. Sci. (ICSESS 2010), 2010, pp. 683–687.

A. Prasetyo, “Optimasi Query Basis Data Kecelakaan Lalu Lintas Menggunakan B-Tree Index pada Oracle 12C,” Bachelor Thesis, Universitas Gadjah Mada, 2016.

A. Silberschatz, H.F. Korth, and S. Sudarshan, Database System Concept, 6th Edition, New York, USA: McGraw-Hill, 2011.

J.A. Hoffer, V. Ramesh, and H. Topi, Modern Database Management, 11th Edition, New Jersey, USA: Pearson Education, 2013.

K.I. Satoto, R.R. Isnanto, R. Kridalukmana, and K.T. Martono, “Optimizing MySQL Database System on Information Systems Research, Publications, and Community Service,” Proc. - 2016 3rd Int. Conf. Inf. Technol. Comput. Electr. Eng. ICITACEE 2016, 2017, pp. 1–5.

D. Damodaran B, S. Salim, and S.M. Vargese, “Performance Evaluation of MySQL and MongoDB Databases,” Int. J. Cybern. Informatics, Vol. 5, No. 2, pp. 387–394, 2016.

(2017) “What is A Non Relational Database,” [Online], https://www.mongodb.com/scale/what-is-a-non-relational-database, [Accessed: 03-Sep-2018].

N. Leavitt, “Will NoSQL Databases Live Up to Their Promise?,” Computer (Long. Beach. Calif), Vol. 43, No. 2, pp. 12–14, 2010.

W. Naheman and J. Wei, “Review of NoSQL databases and performance testing on HBase,” Proc. - 2013 Int. Conf. Mechatron. Sci. Electr. Eng. Comput. MEC 2013, pp. 2304–2309, 2013.

V. Abramova, Jorge Bernardino, and P. Furtado, “Which NoSQL Database ? A Performance Overview,” Open J. Databases, vol. 1, no. 2, pp. 17–24, 2014.

Z. Wei-ping and C. Huan, “Using MongoDB to Implement Textbook Management System instead of MySQL.”

Y. Li and S. Manoharan, “A performance comparison of SQL and NoSQL databases,” IEEE Pacific RIM Conf. Commun. Comput. Signal Process. - Proc., pp. 15–19, 2013.

V. Gour, “Improve Performance of Extract , Transform and Load ( ETL ) in Data Warehouse,” vol. 02, no. 03, pp. 786–789, 2010.

“Data Transformation,” Technopedia. [Online]. Available: https://www.techopedia.com/definition/6760/data-transformation. [Accessed: 03-Oct-2018].

E. Prasetyo, L. Edi Nugroho, and M. Nurtiantara Aji, “Perancangan Data Warehouse Sistem Informasi Eksekutif untuk Data Akademik Program Studi,” J. Nas. Tek. Elektro dan Teknol. Inf. UGM, vol. 1, no. 3, pp. 13–20, 2012.

L. Kumar, S. Rajawat, and K. Joshi, “Comparative analysis of NoSQL ( MongoDB ) with MySQL Database International Journal of Modern Trends in Engineering and Research ( IJMTER ),” Int. J. Mordern Trends Eng. Res., vol. 02, no. 05, pp. 120–128, 2015.

DOI: https://doi.org/10.22146/ijitee.46503