Database Schema Research Articles

A user-friendly NoSQL framework for managing agricultural field trial data

Field trials are one of the essential stages in agricultural product development, enabling the validation of products in real-world environments rather than controlled laboratory or greenhouse settings. With the advancement in technologies, field trials often collect a large amount of information with diverse data types from various sources. Managing and organizing extensive datasets can impose challenges for small research teams, especially with constantly evolving data collection processes with multiple collaborators and introducing new data types between studies. A practical database needs to be able to incorporate all these changes seamlessly. We present DynamoField, a flexible database framework for collecting and analyzing field trial data. The backend database for DynamoField is powered by Amazon Web Services DynamoDB, a NoSQL database, and DynamoField also provides a front-end interactive web interface. With the flexibility of the NoSQL database, researchers can modify the database schema based on the data provided by various collaborators and contract research organizations. This framework includes functions for non-technical users, including importing and exporting data, data integration and manipulation, and performing statistical analysis. Researchers can utilize cloud computing to establish a secure NoSQL database with minimum maintenance, this also enables collaboration with others worldwide and adapt to different data-collecting strategies as their research progresses. DynamoField is implemented in Python, and it is publicly available at https://github.com/ComputationalAgronomy/DynamoField.

DevelopingGeoAIIntegrated Mass Valuation Model Based onLADMValuation Information Great Britain Country Profile

ABSTRACTAccess to information pertaining to land facilitates strategic planning for individuals, businesses, and governmental entities and allows them to anticipate and address contemporary challenges such as climate change, housing requirements, and economic prosperity. Improvements in information technologies contribute to data creation, analysis, and dissemination processes that enable gaining knowledge about land‐related events. Greater use of location data within property technologies gives rise to an efficient and sustainable land administration system. GeoAI can contribute greatly to the solution of spatial problems by extracting information from complex data through geospatial intelligence. Property valuation requires a spatially explicit model to evaluate locational factors with spatial analyses. In this study, a Land Administration Domain Model (LADM)‐based mass valuation model is developed for Great Britain by integrating GeoAI techniques. First, the LADM Valuation Information package is matched with the country's current organizational structure and it is extended according to the needs to create a country profile. After developing the conceptual model, a database schema is created and automatic conversion to the physical model is conducted. Loading different data sources into the database, a mass valuation application is carried out through spatial analysis and Random Forest regression analysis. As a result, a highly accurate and holistic valuation model is implemented based on the international land administration standards.

Methodology of a Comprehensive Approach to the Analysis, Structuring, and Aggregation of Data Based on the Requirements of Resource-Intensive Applications

The work presents a methodology for in-depth data description for resource-intensive applied tasks, particularly in the field of materials science. It consists of three basic stages. In the first stage, quantitative and semantic requirements are formalized, and working datasets are defined, which can be classified as reference, structured, instrumental, or experimental-computational, depending on the source. These datasets are stored in a specialized research area for further investigation. The second stage involves the analysis and hierarchical structuring of the accumulated information content. This allows for the refinement and comparison of data, storage of different sets, and the application of various processing tools. In the third stage, the data is aggregated and represented as objects in the domain area according to the database schema of the application support system. The steps outlined in the proposed methodology enable full utilization of the specialized research area of informational content for building object models of application scenarios, structuring the domain data representation schema, and forming event-information forms. The practical application of this methodology in the examined subject area clarified the requirements, criteria, and filters for information search, data description, parameter cataloging, and their verification when solving a number of practical tasks. The software implementation of this approach could be oriented towards a web platform for local and cloud technologies, allowing remote collaborative access to content requirements and solution catalogs with role-based access for different categories of researchers and users.

RGISQL: Integrating Refined Grammatical Information into Relational Graph Neural Network for Text-to-SQL Task

The text-to-SQL task aims to convert natural language questions into corresponding SQL queries based on a given database schema. Previous models that rely on graph neural networks often struggle to accurately capture the complex grammatical relationships present in these questions, leading to poor performance when generating queries for longer requests. To address these challenges, we propose RGISQL, which integrates refined grammatical information extracted from the question and employs segmentation processing to effectively manage long queries. Additionally, RGISQL minimizes the complexity of edge embeddings by reducing the coupling within graph neural networks. By utilizing grammatical dependency trees, RGISQL is better equipped to capture the inherent structure and grammatical rules of questions. This refined grammatical information offers additional contextual and semantic cues for the model, thereby enhancing both its generalizability and interpretability. Furthermore, we dynamically assess the importance of different edges based on the graph structure, which helps reduce the coupling of edge embeddings and further improves the model’s performance. Multiple sets of experiments conducted on the Spider and Spider-Syn datasets demonstrate that RGISQL outperforms other baselines, achieving the best results in both datasets.

An interactive dashboard for analyzing user interaction patterns in the i2b2 clinical data warehouse

BackgroundClinical data warehouses provide harmonized access to healthcare data for medical researchers. Informatics for Integrating Biology and the Bedside (i2b2) is a well-established open-source solution with the major benefit that data representations can be tailored to support specific use cases. These data representations can be defined and improved via an iterative approach together with domain experts and the medical researchers using the platform. To facilitate these discussions, it is important to understand how users interact with the system.ObjectiveThe objective of this work was to develop metrics for describing user interactions with clinical data warehouses in general and i2b2 in particular. Moreover, we aimed to develop a dashboard featuring interactive visualizations that inform data engineers and data stewards about potential improvements.MethodsWe first identified metrics for different data usage dimensions and extracted the relevant metadata about previous user queries from the i2b2 database schema for further analysis. We then implemented associated visualizations in Python and integrated the results into an interactive dashboard using Dash.ResultsThe identified categories of metrics include frequency of use, session duration, and use of functionality and features. We created a dashboard that extends our local i2b2 data warehouse platform, focusing on the latter category, further broken down into the number of queries, frequently queried concepts, and query complexity. The implementation is available as open-source software.ConclusionA range of metrics can be derived from metadata logged in the i2b2 database schema to provide data engineers and data stewards with a comprehensive understanding of how users interact with the platform. This can help to identify the strengths and limitations of specific instances of the platform for specific use cases and aid their iterative improvement.

A Mobile App Development for E-Waste ‎Management in Bahrain (Athar)‎‎

Bahrain still suffers from electronic solid waste annually due to ineffective waste ‎disposal management. Compared to other Gulf Cooperation Council (GCC) countries, Bahrain ‎generates the largest waste quantity per person, approximately 1.2 to 1.8 ‎million tons of hazardous waste annually. Therefore, the present research article aims to manage the e-waste discarding mitigation by arranging the waste collection, ‎presenting it for sale/purchase/donation (C2C, C2B, B2G), and organizing the waste recycling ‎process. Derived from this aim, the article explores the e-waste ‎phenomena and proposes the development of an e-waste mobile application named “Athar”. ‎ The significance of “Athar” lies in firstly, enabling citizens to live in a ‎clean environment with limited diseases. Secondly, assisting the ‎Ministry of Tourism attract more tourists. ‎ The research article embraces a seven-phased Agile-based SDLC method to analyze, design, implement, test, and evaluate Athar through the utilization of several research techniques, including questionnaires to collect user and system requirements, other techniques such as data flow diagrams, entity relationship diagram, ‎database schema, etc. for the system design, ‎OutSystems programming language to implement the mobile app, and one questionnaire based on ‎Nielsen heuristics for usability evaluation. Findings demonstrate the adequacy of the Athar application with an outstanding usability score of 89.1%.

Enhancing Healthcare Informatics: Integrating Category Theory Reasoning into OMOP-CDM Ontology Model.

The task of managing diverse electronic health records requires the consolidation of data from different sources to facilitate clinical research and decision-making support, with the emergence of the Observational Medical Outcomes Partnership - Common Data Model (OMOP-CDM) as a standard relational database schema for structuring health records from different sources. Working with ontologies is strongly associated with reasoners. Implementing them over expansive and intricate Ontologies can pose computational challenges, potentially resulting in slow performance. In this paper, we propose the implementation of a new reasoner based on categorical logic over a translation of OMOP-CDM into an ontology model. This enables enhancements to the efficiency and scalability of implementing such models.

Quality Assessment Framework of Clinical Routine Data for Secondary Use.

To systematically and comprehensively identify data issues in large clinical datasets, we adopted a harmonized data quality assessment framework with Python scripts before integrating the data into FHIR® for secondary use. We also added a preliminary step of categorizing data fields within the database scheme to facilitate the implementation of the data quality framework. As a result, we demonstrated the efficiency and comprehensiveness of detecting data issues using the framework. In future steps, we plan to continually utilize the framework to identify data issues and develop strategies for improving our data quality.

Demonstration of the VeriEQL Equivalence Checker for Complex SQL Queries

Equivalence checking for SQL queries has many real-world applications but typically requires supporting an expressive SQL language in order to be practical. We develop VeriEQL, a system that can prove and disprove equivalence of complex SQL queries. Specifically, given two SQL queries under a database schema, VeriEQL can verify whether these two queries always produce identical results on all possible input databases up to a bounded size that conform to the schema. This paper demonstrates VeriEQL in three scenarios, including validating the correctness of query optimizations, grading SQL queries on online coding platforms, and finding implementation bugs in database management systems.

DataLoom: Simplifying Data Loading with LLMs

Schema discovery and data loading is a crucial step in any data analysis pipeline. While this used to be a rare task, in the highly dynamic field of machine learning and modern business intelligence on top of data lakes, today it has become a frequent, but often underestimated, activity. Existing tools often focus on single files, presume prior knowledge of the data on the user's side or a significant amount of manual labor. In this paper, we improve the process of mapping a "chaotic" set of files to an initial database schema that can then be iteratively refined and loaded. The idea is to take the previously tedious parts of this process and automate them through the use of Large Language Models (LLMs) while leaving already well-understood problems such as constraint discovery to existing algorithms. We thus carefully orchestrate the use of LLMs for the "soft" problems and traditional algorithms for the "hard" problems. This creates a more seamless schema discovery and data loading experience that minimizes the time to first insight for users. We show this vision on modern schema discovery and data loading in our web-based prototype called DataLoom that serves as our demonstration.

Symposium 1: Analyzing Special Needs Support through Networks and Learning Analytics in Primary Education

In this short paper, analytical issues of networked learning are related to special needs support and learning analytics research in primary education. Very brief suggestions for these analytical issues are discussed for the networked learning research field to continue to expand upon. In part, the discussion is intended to form the underpinnings of a work-in-progress research project with cross-disciplinary networked connections across multiple universities. Currently, teachers are faced with an expansion of their professional role that may be too economically demanding, which may result in consequences that is not beneficial for children with special needs. Researchers that aim to study inclusive education face the challenge to represent findings in a way that does not increase workload for teachers in primary education. Any failings in this regard may strengthen the intertwining of educational results and economical interest, which to a large extent could be considered as opposed to networked learning theory, dualistically separated from pedagogical concerns. The inclusive goal of addressing diversity for children with special needs may be promoted by providing an educational setting with networked connections that enhances variation to modes of behavior, by addressing specific and contextual strengths and challenges of students appropriately. With learning analytics, specific and contextual strengths and challenges of students might be easier to highlight with new database schemas, algorithms, and user interfaces to represent networked data. Radical behaviorist methodology may further emphasize specific contextual findings from primary education through strictly defined events of behavior, which may be of interest to the networked learning research community to further expand upon. Thus, this paper proposes suggestions for networked learning research of primary education, with approaches commonly implemented in special education, such as radical behaviorist methodology. The suggestions are based on networked consequences from three social dimensions: political, economic, and technology-developmental. 1 The political dimension is discussed through the notion of inclusive education for children with special needs and its current state, 2 the economic dimension is discussed through the 21st century role expansion of teachers, 3 the technology-developmental dimension is discussed through learning analytic research and its connection to bodily and biological associations. This dimension is especially noteworthy, as learning analytics is currently underutilized in primary education research as opposed to common higher education research.

Basic principles of designing and developing reporting systems for medical facilities

The basic principles of designing and developing reporting systems for medical facilities are discussed. Problems are identified and solutions are proposed for building the architecture of systems: the use of distributed services and methods of managing them to improve performance, the use of message queues for communication between them. The general methods of data storage and the use of data warehouses to simplify the scheme of databases used for reporting by medical institutions are considered. The principles and approaches for creating those parts of the systems responsible for final data processing and report generation with support for various document output formats are proposed.

APPOINTMENT MAKER USING COMPUTERIZED VOICE

The Appointment Maker Using Computerized Voice is a novel system designed to streamline the process of scheduling appointments in healthcare settings through the integration of voice recognition and natural language processing technologies. By leveraging speech-to-text and text-to-speech capabilities, the system enables users to interact with the appointment booking interface using spoken commands, eliminating the need for manual input and enhancing accessibility for individuals with diverse technological proficiencies. This intuitive interface simplifies the appointment scheduling process, allowing users to book, reschedule, or cancel appointments with ease. Furthermore, the Appointment Maker employs machine learning models to understand and interpret user queries, providing accurate responses and assistance in real-time. By leveraging pre-trained language models and fine-tuning them on domain-specific data, the system demonstrates robust performance in understanding diverse user inputs and generating contextually relevant responses. This capability enhances user experience by providing personalized and timely assistance, akin to interacting with a human receptionist.The Appointment Maker features a backend database for storing and managing appointment and user information, ensuring data integrity, security, and efficient resource utilization. The relational database schema facilitates seamless coordination between users, healthcare providers, and administrative staff, enabling efficient scheduling and management of appointments. Overall, the Appointment Maker Using Computerized Voice represents a significant advancement in healthcare administration, offering a user-friendly and efficient solution for appointment scheduling while improving accessibility and patient experience. Key Words: Computerized Voice ,Natural Language Understanding (NLU), Speech to Text, Text to Speech.

Knowledge-based query system for the critical minerals

Critical minerals are increasingly used in advanced, modern technologies. Exploration for these minerals require efficient mechanisms to search for the latest geological knowledge about the petrogenesis and spatial distribution of these essential resources. Although the current text-based deposit classification schemes help geoscientists to understand how and where these critical minerals form, they cannot easily be queried by software without extensive natural language processing and knowledge modeling. Ontologies can explicitly specify the knowledge scattered in the texts and tables of these schemes and the Critical Minerals Mapping Initiative (CMMI) database by way of logical structures whose results can automatically be processed and queried. They can also draw new knowledge by inference from the ones that are explicitly specified in them. These qualities make ontologies a perfect choice for digital knowledge storage, search, and extraction. The Critical Minerals Ontology (CMO) is described herein by reusing the logical class and property structures of the top-level Basic Formal Ontology (BFO) and mid-level Common Core Ontologies (CCO) and Relation Ontology (RO). The CMO formally models the knowledge about the critical mineral systems using the latest deposit classification scheme and the CMMI database schema. The ontology specifies the geochemical and geological processes that operate in various geotectonic environments of mineral systems to form the critical minerals in different deposit types. It models the properties of both the host minerals that contain the rare-earth elements and those that bear other types of elements. The CMO also represents uses of specific critical minerals in the manufacturing of industrial products, their alternate substitutes, and countries that produce, import, and export them. A query system, applying the Python programming language, accesses the knowledge modeled in the CMO and allows users through interactive web pages to query the ontology and extract different types of information from it. The ontology and the query system are useful for research in ore mineralogy and critical mineral prospecting. The information modeled by the ontology and served by the query system allows users to classify their ore specimen data into specific deposit types.

An empirical hybrid strategy for NoSQL database distribution

A case study of a pharmaceutical distribution company with a relational database that is migrated to a document-oriented NoSQL database for which a hybrid distribution strategy based on fragmentation and replication is implemented. The legacy relational database schema was thoroughly analyzed and evaluated to determine the collections and documents needed to move all the data and leverage the capabilities of the document-based database. In addition, a hybrid data distribution strategy was designed to effectively balance the performance, scalability, and fault tolerance of the system. Finally, integrated operational tests were performed on the distributed system nodes to verify that they function properly and ensure their availability for the company's operations.

Consistency of Relations over Monoids

The interplay between local consistency and global consistency has been the object of study in several different areas, including probability theory, relational databases, and quantum information. For relational databases, Beeri, Fagin, Maier, and Yannakakis showed that a database schema is acyclic if and only if it has the local-to-global consistency property for relations, which means that every collection of pairwise consistent relations over the schema is globally consistent. More recently, the same result has been shown under bag semantics. In this paper, we carry out a systematic study of local vs. global consistency for relations over positive commutative monoids, which is a common generalization of ordinary relations and bags. Let K be an arbitrary positive commutative monoid. We begin by showing that acyclicity of the schema is a necessary condition for the local-to-global consistency property for K-relations to hold. Unlike the case of ordinary relations and bags, however, we show that acyclicity is not always sufficient. After this, we characterize the positive commutative monoids for which acyclicity is both necessary and sufficient for the local-to-global consistency property to hold; this characterization involves a combinatorial property of monoids, which we call the transportation property. We then identify several different classes of monoids that possess the transportation property. As our final contribution, we introduce a modified notion of local consistency of K-relations, which we call pairwise consistency up to the free cover. We prove that, for all positive commutative monoids K, even those without the transportation property, acyclicity is both necessary and sufficient for every family of K-relations that is pairwise consistent up to the free cover to be globally consistent.

A Portable and Reusable Database Infrastructure for Mass Spectrometry, and Its Associated Toolkit (The DIMSpec Project).

Nontargeted analysis (NTA) is a rapidly growing field of techniques that includes the identification of unknown chemical analytes in complex mixtures such as environmental, biological, and food matrices. The use of reference mass spectral databases is a key component of most NTA workflows, providing a high level of confidence for chemical identification when analytical standards are not available, yet effective interlaboratory sharing of research grade spectra remains challenging. The Database Infrastructure for Mass Spectrometry (DIMSpec) project focused on the creation of an open-source toolkit supporting storage and sharing of high-resolution mass spectra with attached sample and methodological metadata. As a demonstration of its utility, the DIMSpec toolkit was used to create a database of curated mass spectra for per- and polyfluoroalkyl substances (PFAS) generated from various sources. While the underlying toolkit is agnostic to analytical targets, this initial release (along with the database schema, mass spectral data, and database tools) should enable PFAS researchers to use these data for their own studies, including the identification of novel PFAS in the environment.

ORDerly: Data Sets and Benchmarks for Chemical Reaction Data.

Machine learning has the potential to provide tremendous value to life sciences by providing models that aid in the discovery of new molecules and reduce the time for new products to come to market. Chemical reactions play a significant role in these fields, but there is a lack of high-quality open-source chemical reaction data sets for training machine learning models. Herein, we present ORDerly, an open-source Python package for the customizable and reproducible preparation of reaction data stored in accordance with the increasingly popular Open Reaction Database (ORD) schema. We use ORDerly to clean United States patent data stored in ORD and generate data sets for forward prediction, retrosynthesis, as well as the first benchmark for reaction condition prediction. We train neural networks on data sets generated with ORDerly for condition prediction and show that data sets missing key cleaning steps can lead to silently overinflated performance metrics. Additionally, we train transformers for forward and retrosynthesis prediction and demonstrate how non-patent data can be used to evaluate model generalization. By providing a customizable open-source solution for cleaning and preparing large chemical reaction data, ORDerly is poised to push forward the boundaries of machine learning applications in chemistry.

Efficient schema-less text-to-SQL conversion using large language models

Large language models (LLMs) are increasingly being applied to several tasks including text-to-SQL (the process of converting natural language to SQL queries). While most studies revolve around training LLMs on large SQL corpora for better generalization and then perform prompt engineering during inference, we investigate the notion of training LLMs for schema-less prompting. In particular, our approach uses simple natural language questions as input without any additional knowledge about the database schema. By doing so, we demonstrate that smaller models paired with simpler prompts result in considerable performance improvement while generating SQL queries. Our model, based on the Flan-T5 architecture, achieves logical form accuracy (LFA) of 0.85 on the MIMICSQL dataset, significantly outperforming current state-of-the-art models such as Defog-SQL-Coder, GPT-3.5-Turbo, LLaMA-2-7B and GPT-4. This approach reduces the model size, lessening the amount of data and infrastructure cost required for training and serving, and improves the performance to enable the generation of much complex SQL queries.

Ethical Encrypted Search Scheme for Medical Database

E-medical records are highly sensitive and require secure storage, typically in encrypted form. However, encrypting the records can make them difficult to search and utilize within existing medical database systems. Additionally, managing access to these records, especially when multiple authorities are involved, presents challenges in ensuring security and scalability.To address these issues, we propose an authorized searchable encryption scheme designed for a multi-authority setting. This scheme utilizes the RSA (Rivest-Shamir-Adleman) function to enable each authority to control and limit search capabilities based on clients' privileges. Furthermore, to enhance scalability, we employ multi-authority attribute-based encryption, allowing the authorization process to be streamlined even across policies from multiple authorities.We have conducted thorough security and cost analyses, as well as experimental evaluations, demonstrating that our proposed scheme introduces only moderate overhead to existing searchable encrypted systems. Keywords--e-medical system, cloud storage, forward security.