Open Source Database Management Research Articles

Library management database design and application

Under the data-centric business environment, efficient database design is pivotal for efficient data management across various sectors. For libraries and bookstores, a well-tailored database is essential to categorize, index, and swiftly retrieve vast amounts of data, ensuring efficient operations. MySQL is an open-source database management system which facilitates efficient data manipulation. Based on MySQL, this essay delves into the realm of library database design and application, addressing the need for streamlined systems. This research addresses the need for adaptation in the evolving technology landscape and offers practical insights for librarians and researchers aiming to optimize information management in modern libraries. Employing the basic steps for database design, we explore one methodology for designing a library management database based on the design of database requirement, modeling, backend implementation, efficiency optimization and testing using MySQL. Through this approach, the study provides a potential solution that meets the basic requirements of libraries and enhances information management practices.

OPTIMIZATION OF MYSQL DATABASE

The performance of MySQL, a well-known open-source relational database management system used in a variety of sectors, including e-commerce, finance, and healthcare, can be improved through the use of physical programming and data tuning. While data tuning involves refining the database to increase efficiency, physical programming involves optimizing the physical storage of data. This article gives a general introduction of MySQL and its architecture, looks at the many methods and tools used in physical programming and data tuning, and talks about the advantages of these techniques and how they affect MySQL's performance. 

LARGE VECTOR SPATIAL DATA STORAGE AND QUERY PROCESSING USING CLICKHOUSE

Abstract. The exponential growth of geospatial data resulting from the development of earth observation technology has created significant challenges for traditional relational databases. While NoSQL databases based on distributed file systems can handle massive data storage, they often struggle to cope with real-time query. Column-storage databases, on other hand, are highly effective at both storage and query processing for large-scale datasets. In this paper, we propose a spatial version of ClickHouse that leverages R-Tree indexing to enable efficient storage and real-time analysis of massive remote sensing data. ClickHouse is a column-oriented, open-source database management system designed for handling large-scale datasets. By integrating R-Tree indexing, we have created a highly efficient system for storing and querying geospatial data. To evaluate the performance of our system, we compare it with HBase, a popular distributed, NoSQL database system. Our experimental results show that ClickHouse outperforms HBase in handling spatial data queries, with a response time approximately three times faster than HBase. We attribute this performance gain to the highly efficient R-Tree indexing used in ClickHouse, which allows for fast spatial data query.

Optimization of MySQL database

The performance of MySQL, a well-known open-source relational database management system used in a variety of sectors, including e-commerce, finance, and healthcare, can be improved through the use of physical programming and data tuning. While data tuning involves refining the database to increase efficiency, physical programming involves optimizing the physical storage of data. This article gives a general introduction of MySQL and its architecture, looks at the many methods and tools used in physical programming and data tuning, and talks about the advantages of these techniques and how they affect MySQL's performance.

Database Management Education in MYSQL

The purpose of this research is to find out what is database management in MySQL or read My Sequel is a Database Management System often abbreviated as DBMS, MySQL is an open-source database management system. MySQL can be used to create and manage databases and their contents, from the smallest to the very large and to convey information to its users. MySQL is also included in the RDBMS or Relational Database Management system, where in its database structure so that when the data retrieval process uses the relational database method and becomes a liaison between the software and the database server. The thing to remember is that MySQL can be used for free.

The effect of ENEAS application in patients with endometriosis and its influence on the level of IL-8 and MCP-1.

Endometriosis is a gynecological disease that endometrial cells develop outside the uterus. This event happens when the endometrial glands grow outside the endometrium and uterine muscles, especially in the pelvis. Although endometriosis is widespread, the clinical manifestations of the disease are very different, and it is challenging to adapt to the conventional classification system to divide patients into homogeneous groups. Given the importance of endometriosis, a correct, accurate, and timely diagnosis of this disease can significantly prevent its complications. Using health-related software is one of these ways. Enhanced Endometriosis Archiving Software (ENEAS) is a web-based application based on one of the most widely used open-source database management systems (MySQL), allowing the direct link to other open-source software for data management and storage. In the current study, the effect of ENEAS application was considered in patients with endometriosis, and its influence on IL-8 and MCP-1 gene expression was evaluated. For this purpose, 100 women with endometriosis were divided into two groups of 50 patients. The first group (control group) was examined by a gynecologist and received medication and treatment. In the case group, their demographic and clinical information were entered into ENEAS software. To study the expression of the IL-8 gene and MCP-1 gene, after collecting 5 ml of blood samples in tubes containing anticoagulant, RNA extraction was performed by Total RNA Purification Kit (Cat. 17200, 37500, 17250). Then cDNA synthesis was performed for this purpose, and a Bioneer DNA synthesis kit (South Korea) was used. The results showed that the expression level of the IL-8 gene in the case group was significantly reduced compared to the control group (P = 0.035). MCP-1 gene expression was also decreased compared to the control group, but this decrease was not significant. Therefore, those who used this application for treatment had reduced expression of IL-8 and MCP-1 genes. This event indicates that this application has reduced the amount of inflammation caused by endometriosis with proper analysis.

CGRdb2.0: A Python Database Management System for Molecules, Reactions, and Chemical Data.

This work introduces CGRdb2.0─an open-source database management system for molecules, reactions, and chemical data. CGRdb2.0 is a Python package connecting to a PostgreSQL database that enables native searches for molecules and reactions without complicated SQL syntax. The library provides out-of-the-box implementations for similarity and substructure searches for molecules, as well as similarity and substructure searches for reactions in two ways─based on reaction components and based on the Condensed Graph of Reaction approach, the latter significantly accelerating the performance. In benchmarking studies with the RDKit database cartridge, we demonstrate that CGRdb2.0 performs searches faster for smaller data sets, while allowing for interactive access to the retrieved data.

Peculiarities of creating a database for the IoT system of urban forest management in the city of St. Petersburg

The article deals with the issues related to problems of creating databases for IoT-systems in general and in conjunction with geographic information systems of urban forest management. Highlighted are specific problems and ways of solving them on the basis of modern information technology. As one way to solve most of the problems faced by the developer, the paper reviews the use of open source database management system Tarantool with a non-blocking application server. The article also describes a tree structure for mapping devices, which are connected to the IoT system. It greatly simplifies their management and mapping. The results described in the article allow to judge the possibility of combining two systems such as a geographic information management system and the Internet of Things.

가정에서 발생하는 위험 또는 위반 상황 인식 및 대응을 위한 윤리적 에이전트 개발

This paper introduces an AI agent that can recognize any hazardous or violent situation that may occur at a user’s home and notifies the user about the risk in advance before the consequential accident occurs. The AI agent analyzes a current situation based on the 5W1H principle to determine whether the user is in a dangerous, immoral, or illegal situation at home, according to the user’s age group. This AI agent has been developed by adding a new abnormal behavior code into the existing behavioral classification code to provide appropriate advice or response in the case of an abnormal situation. This agent is implemented using the open source cognitive agent architecture, chatbot engine, and database management system. The abnormal situation-recognition performance of the developed agent was validated through textual conversations between the agent and kids.

Performance Impact of Optimization Methods on MySQL Document-Based and Relational Databases

Databases are an important part of today’s applications where large amounts of data need to be stored, processed, and accessed quickly. One of the important criteria when choosing to use a database technology is its data processing performance. In this paper, some methods for optimizing the database structure and queries were applied on two popular open-source database management systems: MySQL as a relational DBMS, and document-based MySQL as a non-relational DBMS. The main objective of this paper was to conduct a comparative analysis of the impact that the proposed optimization methods have on each specific DBMS when carrying out CRUD (CREATE, READ, UPDATE, DELETE) requests. To perform the analysis and performance evaluation of CRUD operations for different amounts of data, a case study testing architecture based on Java was developed and used to show how the databases’ proposed optimization methods can influence the performance of the application, and to highlight the differences in response time and complexity. The results obtained show the degree to which the proposed optimization methods contributed to the application’s performance improvement in the case of both databases; based on these, a detailed analysis and several conclusions are presented to support a decision for choosing a specific approach.

Abstract 894: Self-serve data and cohort selection in the California Teachers Study: A web-based tool

Abstract Background: Cancer Epidemiology Cohorts (CECs) amass vast amounts of participant health, lifestyle, environmental, genetic, and biologic data. Sharing these well-annotated participant data supports research across diverse cancer outcomes. CEC data often contain PHI, which limits use of open-access databases that have become a boon to other fields. Instead, most CECs create custom datasets for each project, which is labor intensive and hinders data sharing. Objective: The California Teachers Study (CTS), a prospective CEC of 133,477 women that began in 1995, sought to address this issue by building a user-friendly tool for researchers to independently and flexibly choose CTS data—i.e., to execute cohort selection—in a web-based platform. We also aimed to maintain existing data privacy and security; support a full range of study designs, exposures, and outcomes; and provide real-time query result visualizations. Methods: To support these computational demands, we chose an open-source column-oriented database management system optimized for fast analysis of large volumes of data. We curated and tagged participant self-reported data for improved searching and sorting. We built conditional queries that are modified by user selections, and included free text entry to handle outliers. To address privacy, the web-based tool displays summary data in aggregate and outputs the final dataset to a secure server. Users select their cancer endpoint definitions by SEER code, site group name, or ICD code; select analysis start and end points by participant-specific event type or hard-coded date; opt in or out of various censoring rules; and select self-reported data by questionnaire, topic, or search terms. After users submit the query, a folder is created for their project in the secure CTS remote desktop environment, which contains the dataset, a custom data dictionary, and starter scripts in SAS and in R. Results: Our first fully self-service data query module supports cancer cohort analyses and requires no CTS staff intervention to provision data. Two-thirds of recent CTS data requests have been cancer cohort analyses; if this continues, CTS could experience up to a 60% reduction in staff effort spent on creating data sets. This improvement enables instant access for researchers and improves data sharing for not only cancer cohort analyses, but across the full range of research projects. Discussion: A challenge to automating complex processes like cohort selection is focusing on edge cases. By focusing on automating the single most common request type (cancer cohort analyses), we immediately added value for our staff and users. The inclusion of data request modules for currently non-automatable study designs and data domains enables the tool to function as a single channel through which all data requests flow. Other CECs may also consider automating the most common aspects of their data request processes and sharing their results. Citation Format: Jennifer L. Benbow, Emma Spielfogel, Kai Lin, Sandeep Chandra, Paul Hughes, James V. Lacey. Self-serve data and cohort selection in the California Teachers Study: A web-based tool [abstract]. In: Proceedings of the American Association for Cancer Research Annual Meeting 2021; 2021 Apr 10-15 and May 17-21. Philadelphia (PA): AACR; Cancer Res 2021;81(13_Suppl):Abstract nr 894.

Developing an online software to remotely manage apparel production lines

This research aims to create an online software specially designed to plan and manage apparel production lines, using different programming codes Hypertext Preprocessor (PHP), Hypertext Markup Language (HTML), and an open source database management system a relational database management system (MySQL), the online database and the website where designed to allow users to have an easy interface to communicate with that database.

Multimedia Retrieval and Analysis with Cottontail DB

Analysis and retrieval of media collections get more and more challenging the larger the collections become. Keeping everything in the main memory becomes less feasible, and more and more time and effort have to be spent to deal with the data management. However, traditional relational databases do not support primitives often used in multimedia workloads, such as the nearest-neighbour search on vectors. In this column, we introduce Cottontail DB, an open-source database management system for multimedia features. Cottontail DB supports traditional relational database operations and text retrieval based on Lucene and, most importantly, efficient vector-space retrieval operations for large datasets. Cottontail DB is the new data storage system powering the vitrivr multimedia retrieval stack, which was also previously featured in the SIGMM Records [7]. Just like the other components of vitrivr, Cottontail DB is released under the permissive MIT license. It is written in Kotlin, runs on all major operating systems, and comes with a flexible and easy-to-use gRPC API, which makes it usable in many applications, independent of the programming languages used. Cottontail DB's clean and modular architecture enables the easy extension of its functionalities and also makes it useful in an educational context. In the following, we will give a brief introduction on how Cottontail DB works, what we are using it for, and, most importantly, how it can help you manage your data. To learn more about Cottontail DB, including performance evaluations, we kindly refer readers to our Open Source Software Track Contribution at ACM MM 2020 [1], where Cottontail DB was honored with that year's Best Open Source Award.

Design of energy-loaded systems using the Neo4j graph database

The article presents a study of the tasks that determine the features of power-loaded networks, and tasks that are solved using graph models for power-loaded networks. The tasks that determine the features of power-loaded networks are communication check, isolation of network components and components, combinatorial tasks on graphs, calculation of flow distribution, graph simplification, optimal impact on the power grid, and determination of power proportions. This article presents the high-level architecture of Neo4j. Neo4j is an open-source graph database management system implemented in Java and is considered the most comprehensive and promising graph database management system. In comparison with modelling a graph database using a relational database management system, this approach allows applying additional optimization in the case of data with a more complex and loaded structure. The article proposes a model of an energy-loaded network, because it is a system of interaction of a large number of loads or consumers, active sources, communication lines. The declarative query language for graph databases Cypher was used for implementation. A graph database of an electrical network with a description of analytical data processing using a mathematical algorithm and software specifics of the work of queries in a multi-relational database environment has been built. When a query is executed for a user-specified network segment, a connectivity component is allocated; then for the selected elements of the network graph, correspondence is made with the elements of the network itself.

Oracle 19c, SQL Server 2019, Postgresql 12 and MySQL 8 database systems comparison

This article presents a comparative analysis of four popular database technologies. Commercial Oracle Database and SQL Server systems are compared with open source database management systems: PostgreSQL and MySQL. These systems have been available on the market for over a dozen years. Versions released in 2019 were selected for testing and comparasion. For the purposes of the comparative analysis, a database schema was developed and instantiated. Then, test scenarios were developed. They were prepared on the basis of the most popular operations performed with the use of database systems.

Performance Analysis of NoSQL and Relational Databases with CouchDB and MySQL for Application’s Data Storage

In the current context of emerging several types of database systems (relational and non-relational), choosing the type and database system for storing large amounts of data in today’s big data applications has become an important challenge. In this paper, we aimed to provide a comparative evaluation of two popular open-source database management systems (DBMSs): MySQL as a relational DBMS and, more recently, as a non-relational DBMS, and CouchDB as a non-relational DBMS. This comparison was based on performance evaluation of CRUD (CREATE, READ, UPDATE, DELETE) operations for different amounts of data to show how these two databases could be modeled and used in an application and highlight the differences in the response time and complexity. The main objective of the paper was to make a comparative analysis of the impact that each specific DBMS has on application performance when carrying out CRUD requests. To perform the analysis and to ensure the consistency of tests, two similar applications were developed in Java, one using MySQL and the other one using CouchDB database; these applications were further used to evaluate the time responses for each database technology on the same CRUD operations on the database. Finally, a comprehensive discussion based on the results of the analysis was performed that centered on the results obtained and several conclusions were revealed. Advantages and drawbacks for each DBMS are outlined to support a decision for choosing a specific type of DBMS that could be used in a big data application.

The design of Apache IoTDB distributed framework

Apache Internet of Things Database (IoTDB) is a new open-source timeseries database management system. A distributed data management system not only needs to solve the problem of metadata synchronization between nodes caused by data partition and multiple replicas, but also needs to support efficient query request processing. To solve the problem of metadata synchronization among nodes, we propose a dual-layer granularity metadata management strategy. Based on the consistency hash partitioning method and Raft protocol, we designed a distributed framework that supports both strong consistency query and eventual consistency query. Based on the single-machine version of Apache IoTDB, we carried out the system implementation and experimental test. Compared with the single-level granularity management strategy, the test results showed that the two-level granularity metadata management strategy takes less memory resources and improves the write performance by $5%\sim10%$. Also, the results showed that the read and write performance of the distributed Apache IoTDB increases linearly with the extension of cluster size.

Development of Access Control Mechanism Based on Fingerprint Biometrics and Mobile Phone Identityfor Industrial Internet of ThingsCritical Infrastructure Protection

This paper details the development of an access control mechanism based on fingerprint biometrics and mobile phone International Mobile Equipment Identity (IMEI) modalities for the industrial internet of thing (Industrial IoT) Critical Infrastructure (CI) protection. The idea behind this study is to harden physical security through human identification and authentication processes to Smart CI places such as buildings, military bases, hospitals, airports and other important infrastructure. Fingerprint and mobile phone IMEI are very recognized and accepted identities hence used in police criminal investigation and legal community. Other uses of mobile phone identities include e-medicine, mobile banking, mobile money and remote machine operation. The main objective of this study is to develop a prototype application for authentication and identification of legitimate Industrial IoT human entities/objects based on the mentioned two identities as opposed to traditional knowledge (password, phrase or personal identification number (PIN) etc.,) and possession (token, smart card, identity card etc.,) based Access Control instruments. To achieve this, the study based on eXtreme programming methodology was conducted using visual studio 2010 on DotNet framework 4.0 with C# object oriented programming language. The backend database employed was MySQL open-source Relational database management system (RDMBS). The research produced a number of key results include the development of fingerprint biometric and mobile phone IMEI human identity security layers (modules) and many others. The developed prototype application performance was evaluated by enrolled some fingerprints, IMEI and captured related individual personal information. The result indicated 99.999% accuracy levels. In conclusion, the study shows that the integration of fingerprint and mobile phone IMEI identities in access control roles can improve the security of the Industrial IoT institution and alleviate problems associated with traditional identity authentication methods.

A Chinese ancient book digital humanities research platform to support digital humanities research

PurposeWith the rapid development of digital humanities, some digital humanities platforms have been successfully developed to support digital humanities research for humanists. However, most of them have still not provided a friendly digital reading environment and practicable social network analysis tool to support humanists on interpreting texts and exploring characters’ social network relationships. Moreover, the advancement of digitization technologies for the retrieval and use of Chinese ancient books is arising an unprecedented challenge and opportunity. For these reasons, this paper aims to present a Chinese ancient books digital humanities research platform (CABDHRP) to support historical China studies. In addition to providing digital archives, digital reading, basic search and advanced search functions for Chinese ancient books, this platform still provides two novel functions that can more effectively support digital humanities research, including an automatic text annotation system (ATAS) for interpreting texts and a character social network relationship map tool (CSNRMT) for exploring characters’ social network relationships.Design/methodology/approachThis study adopted DSpace, an open-source institutional repository system, to serve as a digital archives system for archiving scanned images, metadata, and full texts to develop the CABDHRP for supporting digital humanities (DH) research. Moreover, the ATAS developed in the CABDHRP used the Node.js framework to implement the system’s front- and back-end services, as well as application programming interfaces (APIs) provided by different databases, such as China Biographical Database (CBDB) and TGAZ, used to retrieve the useful linked data (LD) sources for interpreting ancient texts. Also, Neo4j which is an open-source graph database management system was used to implement the CSNRMT of the CABDHRP. Finally, JavaScript and jQuery were applied to develop a monitoring program embedded in the CABDHRP to record the use processes from humanists based on xAPI (experience API). To understand the research participants’ perception when interpreting the historical texts and characters’ social network relationships with the support of ATAS and CSNRMT, semi-structured interviews with 21 research participants were conducted.FindingsAn ATAS embedded in the reading interface of CABDHRP can collect resources from different databases through LD for automatically annotating ancient texts to support digital humanities research. It allows the humanists to refer to resources from diverse databases when interpreting ancient texts, as well as provides a friendly text annotation reader for humanists to interpret ancient text through reading. Additionally, the CSNRMT provided by the CABDHRP can semi-automatically identify characters’ names based on Chinese word segmentation technology and humanists’ support to confirm and analyze characters’ social network relationships from Chinese ancient books based on visualizing characters’ social networks as a knowledge graph. The CABDHRP not only can stimulate humanists to explore new viewpoints in a humanistic research, but also can promote the public to emerge the learning interest and awareness of Chinese ancient books.Originality/valueThis study proposed a novel CABDHRP that provides the advanced features, including the automatic word segmentation of Chinese text, automatic Chinese text annotation, semi-automatic character social network analysis and user behavior analysis, that are different from other existed digital humanities platforms. Currently, there is no this kind of digital humanities platform developed for humanists to support digital humanities research.

Abstract 3290: The Side-Out Foundation Metastatic Breast Cancer Database, an open-access portal for multi-omic molecular data and more

Abstract Introduction: With the high volume of molecular data being generated daily from human malignancies, there is an increasing need to create novel internet-based portals where this information is readily and easily accessible to physicians, scientists, and the general public. Although numerous databases have been created to capture the molecular characteristics of primary tumors, limited resources are available when it comes to the metastatic lesions. We have developed a novel open-access database to capture demographic, clinical, and pathological information, outcome data, and multi-omic based molecular profiles from metastatic breast cancer (MBC) patients. Materials and Methods: The portal was created using the open-source relational database management system MySQL and the custom-codes were written using the PHP server-side scripting language. User interface, management, and authentication were created in WordPress. The database is primarily used to record information collected through the Side-Out clinical trials, a series of prospective Phase II trials targeting refractory MBC (NCT01074814, NCT01919749, NCT03195192). De-identified patients' information includes patients' demographics, treatment history, pathological characteristics of the primary tumor, outcome data, and multi-omic molecular profiles. Molecular information collected from each lesion include genomic (NGS-based whole/targeted exome sequencing), transcriptomic (RNA microarray/RNA Seq), proteomic (protein expression by IHC), and phosphoproteomic (protein pathway activation mapping by Reverse Phase Protein Microarray) data. Over 700 data fields are collected for each patient. A higher level of security for the recorded information is achieved by using a secondary database along with custom-codes during the data entry process. Investigators are exploring how Block-chain database design can be used to make portion of the data public while encrypting patient identifiers and key variables. Results: We present an overview of the portal, its usability, how access can be requested by interested third party, the user-friendly interface for downloading clinical, pathological and molecular data, and a few examples of how these data can be used to explore different aspects of metastatic breast cancers. Conclusions: To our knowledge, this is the first web-based publicly accessible database portal where broad multi-omic profiles are captured from the MBC patients along with demographic, clinical and pathological data. This open-access portal represents a unique and highly valuable tool as it integrates different aspects of the disease and can be used for correlative analyses and hypothesis-generating studies. Finally, this web-based portal allows free-of-charge dissemination of data from existing or upcoming clinical and translational studies targeting breast cancer patients. Citation Format: Elisa Baldelli, Shiva Ghaemi, Sanja Avramovic, Janusz Wojtusiak, Lance A. Liotta, Farrokh Alemi, Emanuel F. Petricoin, Bryant Dunetz, Mariaelena Pierobon. The Side-Out Foundation Metastatic Breast Cancer Database, an open-access portal for multi-omic molecular data and more [abstract]. In: Proceedings of the American Association for Cancer Research Annual Meeting 2018; 2018 Apr 14-18; Chicago, IL. Philadelphia (PA): AACR; Cancer Res 2018;78(13 Suppl):Abstract nr 3290.