Database Schema Research Articles

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

LAYERS: A Decision-Support Tool to Illustrate and Assess the Supply and Value Chain for the Energy Transition

Climate change mitigation strategies are developed at international, national, and local authority levels. Technological solutions such as renewable energies (RE) and electric vehicles (EV) have geographically widespread knock-on effects on raw materials. In this paper, a decision-support and data-visualization tool named “LAYERS” is presented, which applies a material flow analysis to illustrate the complex connections along supply chains for carbon technologies. A case study focuses on cobalt for lithium-ion batteries (LIB) required for EVs. It relates real business data from mining and manufacturing to actual EV registrations in the UK to visualize the intended and unintended consequences of the demand for cobalt. LAYERS integrates a geographic information systems (GIS) architecture, database scheme, and whole series of stored procedures and functions. By means of a 3D visualization based on GIS, LAYERS conveys a clear understanding of the location of raw materials (from reserves, to mining, refining, manufacturing, and use) across the globe. This highlights to decision makers the often hidden but far-reaching geo-political implications of the growing demands for a range of raw materials that are needed to meet long-term carbon-reduction targets.

Translating the Observational Medical Outcomes Partnership - Common Data Model (OMOP-CDM) Electronic Health Records to an OWL Ontology.

The heterogeneity of electronic health records model is a major problem: it is necessary to gather data from various models for clinical research, but also for clinical decision support. The Observational Medical Outcomes Partnership - Common Data Model (OMOP-CDM) has emerged as a standard model for structuring health records populated from various other sources. This model is proposed as a relational database schema. However, in the field of decision support, formal ontologies are commonly used. In this paper, we propose a translation of OMOP-CDM into an ontology, and we explore the utility of the semantic web for structuring EHR in a clinical decision support perspective, and the use of the SPARQL language for querying health records. The resulting ontology is available online.

Time series data encoding for efficient storage

Not only the vast applications but also the distinct features of time series data stimulate the booming growth of time series database management systems, such as Apache IoTDB, InfluxDB, OpenTSDB and so on. Almost all these systems employ columnar storage, with effective encoding of time series data. Given the distinct features of various time series data, it is not surprising that different encoding strategies may perform variously. In this study, we first summarize the features of time series data that may affect encoding performance, including scale, delta, repeat and increase. Then, we introduce the storage scheme of a typical time series database, Apache IoTDB, prescribing the limits to implementing encoding algorithms in the system. A qualitative analysis of encoding effectiveness regarding to various data features is then presented for the studied algorithms. To this end, we develop a benchmark for evaluating encoding algorithms, including a data generator regarding the aforesaid data features and several real-world datasets from our industrial partners. Finally, we present an extensive experimental evaluation using the benchmark. Remarkably, a quantitative analysis of encoding effectiveness regarding to various data features is conducted in Apache IoTDB.

DEVELOPING A GEODATABASE FOR EFFICIENT UAV-BASED AUTOMATIC CONTAINER CRANE INSPECTION

Abstract. Inspection of container cranes is an important task to maintain the all-day operation in harbours. In practice, manually carrying out the inspection process is not the best solution due to the complexity, time consumption, and cost. The manual inspection often is performed by specialists who capture images of areas of interest and then check and analyse these images visually. Once a critical area is spotted, industrial climbers are hired to check the part in-situ. The visual pre-inspection should be reliable and accurate, because the in-situ inspection is expensive, but to miss defected areas might lead to a failure of the crane. From this point of view, we came up with the main idea of the joint research project ABC-Inspekt which embodies an exploratory investigation in providing an automatic inspection of container cranes using the technology of Unmanned Aerial Vehicles (UAV). Images, which are captured systematically and regularly, are managed in a database to facilitate access. The access is done by an operator, but also by an image analysis approach which automatically identifies possible defects and stores them back into the database. In this paper, we introduce the database schema, the web frontend for data storage, access, and viewing, and show intermediate results for the data processing workflow.

Technical Perspective

The paper Structure and Complexity of Bag Consistency by Albert Atserias and Phokion Kolaitis [1] studies fundamental structural and algorithmic questions on the global consistency of databases in the context of bag semantics. A collection D of relations is called globally consistent if there is a (so-called "universal") relation over all the attributes that appear in all the relations of D whose projections yield the relations in D. The basic algorithmic problem for consistency is: given a database D, determine whether D is globally consistent. An obvious necessary condition for global consistency is local (or pairwise) consistency: every pair of relations in D must be consistent. This condition is not sufficient however: It is possible that every pair is consistent, but there is no single global relation over all the attributes whose projections yield the relations in D. A natural structural question is: Which database schemas have the property that every locally consistent database over the schema is also globally consistent?

Structure and Complexity of Bag Consistency

Since the early days of relational databases, it was realized that acyclic hypergraphs give rise to database schemas with desirable structural and algorithmic properties. In a bynow classical paper, Beeri, Fagin, Maier, and Yannakakis established several different equivalent characterizations of acyclicity; in particular, they showed that the sets of attributes of a schema form an acyclic hypergraph if and only if the local-to-global consistency property for relations over that schema holds, which means that every collection of pairwise consistent relations over the schema is globally consistent. Even though real-life databases consist of bags (multisets), there has not been a study of the interplay between local consistency and global consistency for bags. We embark on such a study here and we first show that the sets of attributes of a schema form an acyclic hypergraph if and only if the local-to-global consistency property for bags over that schema holds. After this, we explore algorithmic aspects of global consistency for bags by analyzing the computational complexity of the global consistency problem for bags: given a collection of bags, are these bags globally consistent? We show that this problem is in NP, even when the schema is part of the input. We then establish the following dichotomy theorem for fixed schemas: if the schema is acyclic, then the global consistency problem for bags is solvable in polynomial time, while if the schema is cyclic, then the global consistency problem for bags is NP-complete. The latter result contrasts sharply with the state of affairs for relations, where, for each fixed schema, the global consistency problem for relations is solvable in polynomial time.

MIMIC-IV as a Clinical Data Schema.

Routinely collected electronic health records (EHR) in clinical information systems (CIS) are often heterogeneous, have inconsistent data formats and lack of documentation. We use the well-known open-source database schema of MIMIC-IV to address this issue aiming to support collaborative secondary analysis. Over 154 million data records from a German ICU have already been mapped and inserted into the schema successfully. However, discrepancies between the German and US health systems as well as specifics in our clinical source data hinder the direct translation to MIMIC. Evaluating and improving mapping completeness is part of the ongoing research.

CONCEPTUAL MODELING OF GIS OF THE PRIMARY ACCOUNTING OF THE CADASTRE OF THE NATURE RESERVE FUND (ON THE EXAMPLE OF THE NIZHNYOS NATIONAL NATURE PARK)

The article presents the results of conceptual modeling of the database of thematicgeospatial data for the primary accounting of cadastral information. Lands of the nature reserve fundhave a special nature conservation value. According to the results of accounting, survey andinventory of natural complexes, assessment of the state of nature reserve facilities form "Cards ofprimary accounting of territories and objects of nature reserve fund of Ukraine". Other documentsof the cadastre of territories and objects of the nature reserve fund are created by generalizing thedata of these cards. Nizhnosulsky National Nature Park was created to preserve valuable natural andhistorical-cultural complexes and objects of the Middle Dnieper. It covers an area of 18,635.11hectares. The rare fraction of the park's flora includes 13 species of vascular plants, including 10species listed in the Red Book of Ukraine. The Green Book of Ukraine includes 9 formations foundin the park. In the park, taking into account the value of natural complexes and objects, there arefour functional zones: protected area; regulated recreation; inpatient recreation; economic. Basedon the results of the analysis of the resources of the Nizhnosulsky National Nature Park and thecadastre of the nature reserve fund as an object of informatization, a conceptual scheme of thethematic geospatial database was compiled. The set of basic spatial data of such a system is proposedto include information on coordinate and altitude systems, administrative-territorial units, territorialcommunities, hydrographic objects and hydraulic structures, settlements, buildings and structures,roads, railways, utilities, soils, land plots, geographical names, relief, orthophotos. The set ofthematic data should include information on NPF objects, their functional zoning, land plots and lands, owners and users of land plots, economic activities, landscapes, rare types of environments,conservation objects, recreational value, as well as rare species of plants and animals. According tothe research results, a UML-diagram of the conceptual model of the thematic geospatial database ofGIS of the primary accounting of the cadastre of the nature reserve fund was created.

Load Balancing Optimization of In-Memory Database for Massive Information Processing of Internet of Things (IoTs)

Based on the analysis of the key technologies of the Internet of Things service platform architecture, a load balancing optimization scheme of in-memory database based on the massive information processing of the Internet of Things service platform is proposed. This scheme firstly proposes a system model that can satisfy the mass sensor information processing under the open platform environment and designs several functional unit modules of the system. By combining these functional units, the service can be configured for thousands of services and tenants. This paper presents an adaptive strategy selection method, which can automatically select the optimization strategy by dividing the position and querying the selection rate to improve the efficiency of the adaptive index algorithm. The index structure is initialized by parallel sorting algorithm, and the query statement is executed and the index structure is optimized by thread level parallel and radix sort methods. An elastic pipeline technique is proposed, which includes an elastic iterator model and a dynamic scheduler. The elastic iterator model is an upgrade of the traditional iterator model, adding the characteristics of dynamic multicore execution. In the process of query processing, dynamic scheduler monitors the load of each node in real time and dynamically adjusts the parallelism, so as to realize the load balance of in-memory database and maximize the utilization of hardware resources. The elastic pipeline realizes the isolation of parallelism from query compilation to avoid inappropriate parallelism allocation caused by missing and insufficient information during query compilation.