Database Schema Research Articles

CatSQL : Towards Real World Natural Language to SQL Applications

Natural language to SQL (NL2SQL) techniques provide a convenient interface to access databases, especially for non-expert users, to conduct various data analytics. Existing methods often employ either a rule-base approach or a deep learning based solution. The former is hard to generalize across different domains. Though the latter generalizes well, it often results in queries with syntactic or semantic errors, thus may be even not executable. In this work, we bridge the gap between the two and design a new framework to significantly improve both accuracy and runtime. In particular, we develop a novel CatSQL sketch, which constructs a template with slots that initially serve as placeholders, and tightly integrates with a deep learning model to fill in these slots with meaningful contents based on the database schema. Compared with the widely used sequence-to-sequence-based approaches, our sketch-based method does not need to generate keywords which are boilerplates in the template, and can achieve better accuracy and run much faster. Compared with the existing sketch-based approaches, our CatSQL sketch is more general and versatile, and can leverage the values already filled in on certain slots to derive the rest ones for improved performance. In addition, we propose the Semantics Correction technique, which is the first that leverages database domain knowledge in a deep learning based NL2SQL solution. Semantics Correction is a post-processing routine, which checks the initially generated SQL queries by applying rules to identify and correct semantic errors. This technique significantly improves the NL2SQL accuracy. We conduct extensive evaluations on both single-domain and cross-domain benchmarks and demonstrate that our approach significantly outperforms the previous ones in terms of both accuracy and throughput. In particular, on the state-of-the-art NL2SQL benchmark Spider, our CatSQL prototype outperforms the best of the previous solutions by 4 points on accuracy, while still achieving a throughput up to 63 times higher.

Performance Evaluation of NoSQL Document Databases: Couchbase, CouchDB, and MongoDB

NoSQL document databases emerged as an alternative to relational databases for managing large volumes of data. NoSQL document databases ensure big data storage and good query performance and are essential when the data scheme does not fit into the scheme of relational databases. They store their data in the form of documents and can handle unstructured, semi-structured, and structured data. This work evaluates the top three open-source NoSQL document databases: Couchbase, CouchDB, and MongoDB with Yahoo! Cloud Serving Benchmark (YCSB), which has become a standard for NoSQL database evaluation. The performance and scale-up of document databases are assessed using YCSB workloads with a different number of records and threads, where the runtime is measured for each database. In the experimental evaluation, we concluded that MongoDB is the database with the best runtime, except for the workload composed by scan operations. In addition, we identified CouchDB as the database with the best scale-up when varying the number of threads.

Schema dependency-enhanced curriculum pre-training for table semantic parsing

Large pre-trained models exhibit improved table-semantic-parsing performances by leveraging large-scale corpora to enhance the representation learning ability of semantic parsers. However, existing table pre-training methods do not sufficiently consider the explicit interactions among natural language (NL) questions, SQL queries, and the corresponding database schemas, which are essential for table semantic parsing. To overcome the aforementioned limitation, this study designs a novel schema dependency prediction (SDP) objective to incorporate SQL-aware schema linking information into table pre-training. Specifically, SDP aims to predict the schema dependency connecting the NL question, table schema, and triggered SQL operations. We further propose a schema-aware curriculum learning approach (SAC) to mitigate the impact of noise present in the pre-training data and train the table pre-training model in an easy-to-hard manner. We evaluate the effectiveness of our pre-training framework by fine-tuning it on three downstream benchmarks (Spider, Spider-DK, and SQUALL). Experimental results demonstrate that the proposed pre-training method outperforms existing methods considered for comparison.

Spatiotemporal Database Schema for Data Driven Applications in Smart Agriculture

Background: Data driven sustainable agriculture involves the collection, storage, processing, and analysis of enormous spatiotemporal data related to crop production processes, systems, infrastructure, and environment. Literature survey on smart agriculture research projects indicates that there is a need for improving handling of spatiotemporal data. There are gaps in handling spatial and temporal variability of parameters, division of crop field into management zones to reduce the variability for optimizing application of inputs such as fertilizers, water or pesticides. These gaps are to be addressed at design level of spatiotemporal database for smart agriculture. Objective: To engineer a spatiotemporal database schema that can be used for data driven sustainable agriculture. Methods: The methodology involves spatiotemporal data representation and modeling, Object oriented analysis and design of the database and Verification of the database using life cycle model and algorithmic steps. Findings: The resulting database is capable to store spatial and temporal variability of soil, plant and water parameters as well as handle spatial split, spatial merge, geometry and location changes of spatiotemporal objects. Novelty: Novel Use cases in smart agriculture along with spatiotemporal attributes are identified so that efficient applications can be realized. Adaption of the spatiotemporal database schema for Smart Irrigation System and its implementation methodology are presented. Keywords: Spatiotemporal Database Design; Data-Driven Agriculture; Agriculture Informatics; Database Verification; Spatiotemporal Data Analysis

A Heterogeneous Graph to Abstract Syntax Tree Framework for Text-to-SQL.

Text-to-SQL is the task of converting a natural language utterance plus the corresponding database schema into a SQL program. The inputs naturally form a heterogeneous graph while the output SQL can be transduced into an abstract syntax tree (AST). Traditional encoder-decoder models ignore higher-order semantics in heterogeneous graph encoding and introduce permutation biases during AST construction, thus incapable of exploiting the refined structure knowledge precisely. In this work, we propose a generic heterogeneous graph to abstract syntax tree (HG2AST) framework to integrate dedicated structure knowledge into statistics-based models. On the encoder side, we leverage a line graph enhanced encoder (LGESQL) to iteratively update both node and edge features through dual graph message passing and aggregation. On the decoder side, a grammar-based decoder first constructs the equivalent SQL AST and then transforms it into the desired SQL via post-processing. To avoid over-fitting permutation biases, we propose a golden tree-oriented learning (GTL) algorithm to adaptively control the expanding order of AST nodes. The graph encoder and tree decoder are combined into a unified framework through two auxiliary modules. Extensive experiments on various text-to-SQL datasets, including single/multi-table, single/cross-domain, and multilingual settings, demonstrate the superiority and broad applicability.

On the Complexity of Binary Polynomial Optimization Over Acyclic Hypergraphs

In this work, we advance the understanding of the fundamental limits of computation for binary polynomial optimization (BPO), which is the problem of maximizing a given polynomial function over all binary points. In our main result we provide a novel class of BPO that can be solved efficiently both from a theoretical and computational perspective. In fact, we give a strongly polynomial-time algorithm for instances whose corresponding hypergraph is beta -acyclic. We note that the beta -acyclicity assumption is natural in several applications including relational database schemes and the lifted multicut problem on trees. Due to the novelty of our proving technique, we obtain an algorithm which is interesting also from a practical viewpoint. This is because our algorithm is very simple to implement and the running time is a polynomial of very low degree in the number of nodes and edges of the hypergraph. Our result completely settles the computational complexity of BPO over acyclic hypergraphs, since the problem is NP-hard on alpha -acyclic instances. Our algorithm can also be applied to any general BPO problem that contains beta -cycles. For these problems, the algorithm returns a smaller instance together with a rule to extend any optimal solution of the smaller instance to an optimal solution of the original instance.

Privacy preserving subgraph isomorphism query for dynamic graph database

Graph is an important structure for presenting the data with internal connections. Subgraph isomorphism query is one of the most important operations in graph database. Unfortunately, the calculation of subgraph isomorphism query is very complex. The data owners choose to store the graph data in the cloud server and let the cloud server undertake the complex query calculation. In order to solve the security risks from the untrusted cloud server, several privacy preserving subgraph isomorphism query schemes have been proposed. Nonetheless, all existing schemes are designed for static graph database. Once the graph database is dynamically updated, these schemes will not work well anymore. In order deal with this problem, we propose a privacy preserving subgraph isomorphism query scheme for dynamic graph database. We use enumeration and hash mapping methods to quickly generate feature vectors for graphs. We use clustering algorithm to group feature vectors and use feature vector list to store a group of similar feature vectors, which not only reduces the size of the tree index, but also makes the index easy to be updated. In order to realize privacy protection and subgraph isomorphic query, the improved secure Euclidean distance algorithm is adopted in our scheme. We consider three types of operations: addition, deletion and modification, which are used to update the index and database synchronously. When the index needs to be updated, our scheme can quickly locate the index nodes that need to be updated and make the update operation. Security analysis and experiments show that our scheme is secure and efficient.

Data Mining Techniques for Web Mining: A Survey

The data mining (DM) is the computational process that consists of searching, extracting, and analyzing patterns in large data sets, including methods at the intersection of artificial intelligence, machine learning, statistics, and database schemes. Specifically, its primary goal is to extract information from a raw data set and transform it into an expected structure for further use. Moreover, an evolving perspective of DM is web mining (WM), which refers to the whole of DM and related routines. It is used to discover and extract information from web records and services automatically, that is, WM’s purpose is to obtain valuable data from the World Wide Web. Due to its importance, a survey about DM techniques in WM is necessary, as performed in this paper.

A workload-driven method for designing aggregate-oriented NoSQL databases

Due to the scalability and availability problems with traditional relational database systems, a variety of NoSQL stores have emerged over the last decade to deal with big data. How data are structured in a NoSQL store has a large impact on the query and update performance and the storage usage. Thus, different from the traditional database design, not only the data structure but also the data access patterns need to be considered in the design of NoSQL database schemas. In this paper, we present a general workload-driven method for designing key-value, wide-column, and document NoSQL database schemas. We first present a generic logical model Query Path Graph (QPG) that can represent the data structures of the UML class diagram. We also define mappings from the SQL-based query patterns to QPG and from QPG to aggregate-oriented NoSQL schemas. We use a cost model to measure the query and update performance and optimize the QPG schemas. We evaluate the proposed method with several typical case studies by simulating workloads on databases with different schema designs. The results demonstrate that our method preserves the generality and the quality of the design.

Advancing caching and automation with FDO

Advancing caching and automation with FDO

Towards Text-to-SQL over Aggregate Tables

ABSTRACT Text-to-SQL aims at translating textual questions into the corresponding SQL queries. Aggregate tables are widely created for high-frequent queries. Although text-to-SQL has emerged as an important task, recent studies paid little attention to the task over aggregate tables. The increased aggregate tables bring two challenges: (1) mapping of natural language questions and relational databases will suffer from more ambiguity, (2) modern models usually adopt self-attention mechanism to encode database schema and question. The mechanism is of quadratic time complexity, which will make inferring more time-consuming as input sequence length grows. In this paper, we introduce a novel approach named WAGG for text-to-SQL over aggregate tables. To effectively select among ambiguous items, we propose a relation selection mechanism for relation computing. To deal with high computation costs, we introduce a dynamical pruning strategy to discard unrelated items that are common for aggregate tables. We also construct a new large-scale dataset SpiderwAGG extended from Spider dataset for validation, where extensive experiments show the effectiveness and efficiency of our proposed method with 4% increase of accuracy and 15% decrease of inference time w.r.t a strong baseline RAT-SQL.

DOOML: A New Database & Object-Oriented Modeling Language for Database-Driven Web Application Design and Development

A database driven web application is a very common software solution to rising business problems. Modeling the database and the software architecture can be challenging, hence there not being one combined modeling language for database and software architecture, specifically suited for web application development. In this paper we present Database object-oriented Modeling Language (DooML) and its primary Archetype Diagram: a notation for specifying the design of a database schema and corresponding object-oriented software architecture. It combines the syntax for drawing Entity Relationship Diagrams, the Relational Model and Universal Modeling Language Class Diagrams as well to create a mixed diagram, stating database design as well as software design specifications. By default, DooML ensures that the approach of advanced web application development is model-driven and both database-oriented as well as object-oriented.

"Evaluation of NoSQL Database MongoDB with Respect to JSON Format Data Representation "

The current information technology advancement is attributed to the development of more data-intensive applications. However, due to stringent limitations on data structure, data relations, and other factors, relational databases do not allow us to work with huge data sets or maintain high volume databases. Unstructured data from many industries, including a variety of forms, must be processed and stored in databases. Thus, NoSQL types offer solutions to a number of problems associated with huge data bases. Largely because of the benefits like flexibility and horizontal scalability, NOSQL (Not Only SQL) is in demand. This essay explores many No SQL database types, such as MongoDB, CouchDB, HBase, Cassandra, etc. A document-oriented, cross-platform database called MongoDB offers excellent performance, high availability, and simple scalability. The MongoDB Query Language (MQL), created for ease of use by developers, is used by MongoDB. The documentation contrasts the syntax of MQL versus SQL for typical database operations. When your data is document-centric and doesn't fit well into the schema of a relational database, when you need to support vast size, when you need to quickly prototype, and for a few additional use cases, NoSQL databases like MongoDB are a solid option.The proper encryption methods used to various crucial data fields provide data protection without adversely affecting the database's performance in terms of speed or memory usage. Due to the use of appropriate encryption techniques, this is achievable. Since document-oriented and unstructured data are generally kept in databases, it is frequently used to handle various sorts of data. Examples of such databases include MongoDB, Cassandra, CouchDB, Redis, Hyper-table, and others. Since they are open source, there is a critical necessity to offer good security and protect the user's private information when it is in transit or at rest. There is a need for a single solution that can improve the security of data transfer by providing an improved encryption method that speeds up processes and uses less memory when maintaining databases.

Large-Scale Benchmarking of Multireference Vertical-Excitation Calculations via Automated Active-Space Selection.

We have calculated state-averaged complete-active-space self-consistent-field (SA-CASSCF), multiconfiguration pair-density functional theory (MC-PDFT), hybrid MC-PDFT (HMC-PDFT), and n-electron valence state second-order perturbation theory (NEVPT2) excitation energies with the approximate pair coefficient (APC) automated active-space selection scheme for the QUESTDB benchmark database of 542 vertical excitation energies. We eliminated poor active spaces (20–40% of calculations) by applying a threshold to the SA-CASSCF absolute error. With the remaining calculations, we find that NEVPT2 performance is significantly impacted by the size of the basis set the wave functions are converged in, regardless of the quality of their description, which is a problem absent in MC-PDFT. Additionally, we find that HMC-PDFT is a significant improvement over MC-PDFT with the translated PBE (tPBE) density functional and that it performs about as well as NEVPT2 and second-order coupled cluster on a set of 373 excitations in the QUESTDB database. We optimized the percentage of SA-CASSCF energy to include in HMC-PDFT when using the tPBE on-top functional, and we find the 25% value used in tPBE0 to be optimal. This work is by far the largest benchmarking of MC-PDFT and HMC-PDFT to date, and the data produced in this work are useful as a validation of HMC-PDFT and of the APC active-space selection scheme. We have made all the wave functions produced in this work (orbitals and CI vectors) available to the public and encourage the community to utilize this data as a tool in the development of further multireference model chemistries.

MMKP: A mind mapping knowledgebase prototyping tool for precision medicine.

BackgroundWith significant advancements in the area of precision medicine, the breadth and complexity of the relevant knowledge in the field has increased significantly. However, the difficulty associated with dynamic modelling and the disorganization of such knowledge hinders its rapid development potential.ResultsTo overcome the difficulty in using the relational database model for dynamic modelling, and to aid in the organization of precision medicine knowledge, we developed the Mind Mapping Knowledgebase Prototyping (MMKP) tool. The MMKP implements a novel design that we call a “polymorphic foreign key”, which allows the establishment of a logical linkage between a single table field and a record from any table. This design has advantages in supporting dynamic changes to the structural relationships in precision medicine knowledge. Knowledge stored in MMKP is presented as a mind map to facilitate human interaction. When using this tool, medical experts may curate the structure and content of the precision knowledge in a flow that is similar to the human thinking process.ConclusionsThe design of polymorphic foreign keys natively supports knowledge modelling in the form of mind mapping, which avoids the hard-coding of medical logic into a rigid database schema and significantly reduces the workload that is required for adapting a relational data model to future changes to the medical logic. The MMKP tool provides a graphical user interface for both data management and knowledgebase prototyping. It supports the flexible customization of the data field constraints and annotations. MMKP is available as open-source code on GitHub: https://github.com/ZjuLiangsl/mmkp.

Revolutionising Collection Management at the National Museum of Namibia

Despite its aridity, Namibia contains a surprisingly species-rich biota with high levels of endemism and an unprecedented land portion of intact wilderness. However, as a developing country, the expanding human population is increasing pressure on the country’s rich, but severely understudied ecosystems. An efficient flow of baseline biodiversity data is key for facilitating biosystematics and biogeographic research, which inform conservation planning and sustainable development. The National Museum of Nambia (NMNW) is the custodian of important specimen collections representing Namibian biodiversity, with holdings of about 500 000 specimens from a broad range of vertebrate and invertebrate groups. The NMNW is increasing its drive to make these data available to the necessary stakeholders to facilitate research and conservation assessments, based on digitised specimen collections. The first digitisation initiatives , in the 1980s and 1990s, used software such as FileMaker Pro. These collection-specific database development projects were not aligned with each other or standardised, and resulted in separately developed, heterogeneous databases. Recent changes in research priorities, standards (e.g., Darwin Core Darwin Core Task Group 2009), and data-types (e.g. multimedia files, genomic samples and DNA sequences) now necessitate the use of the latest technologies to revolutionise collection management and data management, facilitate workflows and integrate data into a single platform. We describe plans to implement Specify software (Specify Collections Consortium 2022) for collections / biodiversity data management at NMNW. We will form a small team to execute the required data cleansing and migration operations in-house, to develop a Specify database to manage the herpetology collection, in the first instance. Proper management of genomic and image metadata, and the longevity of database management in a staff-constrained environment, will require special database design considerations. Of particular importance is the representation of both individual organisms and the specimen-lots containing them, and we describe the capability of Specify software to handle such data. We also describe the approach that will be followed in introducing new users to a conceptually complex database schema and application. This will draw from previous experience in developing Specify databases for, and undertaking training courses with, several natural history museums in South Africa.

SpecDB: A relational database for archiving biomolecular NMR spectral data

NMR is a valuable experimental tool in the structural biologist’s toolkit to elucidate the structures, functions, and motions of biomolecules. The progress of machine learning, particularly in structural biology, reveals the critical importance of large, diverse, and reliable datasets in developing new methods and understanding in structural biology and science more broadly. Biomolecular NMR research groups produce large amounts of data, and there is renewed interest in organizing these data to train new, sophisticated machine learning architectures and to improve biomolecular NMR analysis pipelines. The foundational data type in NMR is the free-induction decay (FID). There are opportunities to build sophisticated machine learning methods to tackle long-standing problems in NMR data processing, resonance assignment, dynamics analysis, and structure determination using NMR FIDs. Our goal in this study is to provide a lightweight, broadly available tool for archiving FID data as it is generated at the spectrometer, and grow a new resource of FID data and associated metadata. This study presents a relational schema for storing and organizing the metadata items that describe an NMR sample and FID data, which we call Spectral Database (SpecDB). SpecDB is implemented in SQLite and includes a Python software library providing a command-line application to create, organize, query, backup, share, and maintain the database. This set of software tools and database schema allow users to store, organize, share, and learn from NMR time domain data. SpecDB is freely available under an open source license at https://github.rpi.edu/RPIBioinformatics/SpecDB.

ON DATABASE QUERIES OVER A WEAKLY O-MINIMAL DOMAIN WITH A SMALL COUNTABLE SPECTRUM

In the relational database model introduced by E.F. Codd, the database state is understood as a finite set of relationships between elements. The names of the relationships and their arnts (locations) are fixed and called the database schema. The individual information stored in the relationships of this schema is called the state of the database. Although relational databases were devised for finite sets of data, it is often convenient to assume that there is an infinite domain. We consider relational databases organized over an ordered domain with some additional relations – a typical example is the set of rational numbers together with the relation of linear order and binary operation of addition. If the first-order predicate logic language is used as the query language, then queries can use both database relationships and domain relationships, with variables changing throughout the domain. In the focus of our study are the first-order (FO) queries that are invariant under order-preserving permutations – such queries are called order-generic. It was discovered that for some domains order-generic FO queries fail to express more than pure order queries. Here we prove the collapse result theorem over a weakly o-minimal domain having convexity rank 1 and a small countable spectrum.

Implementation of DSL: didactic system of learning for children with special educational needs

In this paper the tool DSL (SDA: Sistema Didáctico de Aprendizaje, by its acronym in Spanish) is presented. This tool was designed for the Multiple Attention Center located at Santa Ana Xalmimilulco, Huejotzingo, Puebla, with the aim to help in the in the educative process, as didactic auxiliary in the cognitive process corresponding to the subjects of math and Spanish in the center by means of automation. The main aim is the production of an educational software, focused to didactic of basic cognitive functions. This skills and abilities it is develop in an environment of special education and taking as a base the specialized bibliography of the own Multiple Attention Center. The system was done in C#, taking in advantage the object oriented programming and the interface of the environment of development. Moreover the software it was developed by means of the prototypes paradigm, using Unified Modelling Language, UML, which is a graphic language to visualize, specify, build and document a system, backed up by the OMG (object Management Group). It offer a standard in order to describe a “plane” of the system (model),including the conceptual aspects such as business process and functions of the system, and concrete aspects as expressions of programming languages, data base schemas and reusable components. The results obtained as product of the development of this system was valued by the CAM’s personal. The system developed it is found currently operating in the installations of the center. It is a software with adequate characteristics to the environment, flexible, usable and verifiable that complies with the characteristics of an educational software fully. In future works it is could achieve the realization of software packages portables to other platforms and adaptable to run into web.

РАЗРАБОТКА СИСТЕМЫ УПРАВЛЕНИЯ МЕТРОЛОГИЧЕСКИМ ОБЕСПЕЧЕНИЕМ ПРЕДПРИЯТИЯ

The main processes related to metrological control in production are listed, the components and functionality of the metrological software management system at the enterprise are described, the system interface and database schema are presented, the problem of logical inference of predicate calculus is formulated and an example of the work of the Prologue program and the result of a query to the knowledge base are given, the purpose of further research is formulated