Column Names Research Articles

Intelligent Problem Solver in Database Systems based on Ontology Integration through Text-to-SQL

The knowledge of courses can be represented by using ontology to create intelligent educational systems. This study proposes the Onto-Linking model as a knowledge framework that expresses the knowledge of the inputted schema to investigate the schema linking problem of the Text-to-SQL model. It combines the ontology with the structure of the schema. The proposed ontology is utilized to encapsulate the semantics of the intellectual elements of the schema, such as the table names, column names, foreignprimary key restrictions, and information about the probing schema connection. Therefore, the model makes it easier to accurately translate natural language questions into SQL queries. It improves query creation, helps with error handling, and supports query validation by helping the model better grasp the query's intent. The outcomes of the pedagogically oriented model aimed at guiding learners to comprehend the process of reasoning to attain the respective solution.

Improving flexibility and ease of matrix subsetting: The submatrix command

Matrix manipulation in Stata can be a time-consuming and tedious task, especially when it is necessary to subset or rearrange elements from large matrices based on nonconsecutive elements. Compared with Mata, these tasks require more time, more code, and sometimes more complex output. The purpose of this article is to introduce submatrix, a command to manipulate matrix elements using row (and column) names, numbers, and equations.

Can Large Language Models Predict Data Correlations from Column Names?

Recent publications suggest using natural language analysis on database schema elements to guide tuning and profiling efforts. The underlying hypothesis is that state-of-the-art language processing methods, so-called language models, are able to extract information on data properties from schema text. This paper examines that hypothesis in the context of data correlation analysis: is it possible to find column pairs with correlated data by analyzing their names via language models? First, the paper introduces a novel benchmark for data correlation analysis, created by analyzing thousands of Kaggle data sets (and available for download). Second, it uses that data to study the ability of language models to predict correlation, based on column names. The analysis covers different language models, various correlation metrics, and a multitude of accuracy metrics. It pinpoints factors that contribute to successful predictions, such as the length of column names as well as the ratio of words. Finally, the study analyzes the impact of column types on prediction performance. The results show that schema text can be a useful source of information and inform future research efforts, targeted at NLP-enhanced database tuning and data profiling.

Few-shot Text-to-SQL Translation using Structure and Content Prompt Learning

A common problem with adopting Text-to-SQL translation in database systems is poor generalization. Specifically, when there is limited training data on new datasets, existing few-shot Text-to-SQL techniques, even with carefully designed textual prompts on pre-trained language models (PLMs), tend to be ineffective. In this paper, we present a divide-and-conquer framework to better support few-shot Text-to-SQL translation, which divides Text-to-SQL translation into two stages (or sub-tasks), such that each sub-task is simpler to be tackled. The first stage, called the structure stage, steers a PLM to generate an SQL structure (including SQL commands such as SELECT, FROM, WHERE and SQL operators such as <", ?>") with placeholders for missing identifiers. The second stage, called the content stage, guides a PLM to populate the placeholders in the generated SQL structure with concrete values (including SQL identifies such as table names, column names, and constant values). We propose a hybrid prompt strategy that combines learnable vectors and fixed vectors (i.e., word embeddings of textual prompts), such that the hybrid prompt can learn contextual information to better guide PLMs for prediction in both stages. In addition, we design keyword constrained decoding to ensure the validity of generated SQL structures, and structure guided decoding to guarantee the model to fill correct content. Extensive experiments, by comparing with ten state-of-the-art Text-to-SQL solutions at the time of writing, show that SC-Prompt significantly outperforms them in the few-shot scenario. In particular, on the widely-adopted Spider dataset, given less than 500 labeled training examples (5% of the official training set), SC-Prompt outperforms the previous SOTA methods by around 5% on accuracy.

Integrating Relational Structure to Heterogeneous Graph for Chinese NL2SQL Parsers

The existing models for NL2SQL tasks are mainly oriented toward English text and cannot solve the problems of column name reuse in Chinese text data, description in natural language query, and inconsistent representation of data stored in the database. To address this problem, this paper proposes a Chinese cross-domain NL2SQL model based on a heterogeneous graph and relative position attention mechanism. This model introduces relational structure information defined by the expert to construct initial heterogeneous graphs for database schemas and natural language questions. The heterogeneous graph is pruned based on natural language questions, and the multi-head relative position attention mechanism is used to encode the database schema and natural language questions. The target SQL statement is generated using a tree-structured decoder with predefined SQL syntax. Experimental results on the CSpider dataset demonstrate that our model better aligns database schema with natural language questions and understands the semantic information in natural language queries, effectively improving the matching accuracy of Chinese multi-table SQL statement generation.

Inferential Statistics II: Linear Regression via the Linear Least Square Method in R

Fig. 12 Description of a function as an algorithmic process, specifically referring to the computational abstraction used in programs such as R: A mathematical function, say f(x)=x2f(x) = x^2f(x)=x2, has an input, say x=2x = 2x=2, and delivers an output for f(2)f(2)f(2), which in this case results in 22=42^2 = 422=4 and resembles the y-coordinate of a point on that function with the respective x-coordinate value of x=2x = 2x=2. The input represents the independent variable and the output the change of the dependent variable after it was influenced by or in general simply in relation to the independent variable, xxx. The algorithmic process performed within a function is arbitrary in programming. The input could be a list of names and the output a blank data table with the respective input names set as column names. We will learn more about functions and their possibilities later below, eventually writing functions for ourselves.

Классификация текста растрового документа по признаку начертания

When highlighting the logical structure of documents, a number of properties are used, one of which is the bold style of text words. In documents, headings, defined words, and column names in tables are often highlighted in bold. This paper proposes a method for classifying text by boldness, which consists of a sequence of steps. The first step is binarization of the entire image. The purpose of this step is to separate the image pixels into text and background pixels. The second step is to evaluate each word. The result is returned a value characterizing the thickness of the main stroke of the character in the given word. At the last step, the ratings are clustered into two clusters: bold text and regular. The proposed method was implemented and tested on three data sets, and the source code was published in an open repository.

Datetime Feature Recommendation Using Textual Information

Analysis to gain new knowledge from huge amounts of data is called data science, and its widespread use is now socially important. Feature engineering, the process of extracting features from data, is one of the main tasks in data science, and since this task relies on the experience of experts, research is being conducted to automate it. In this paper, we propose a novel approach to automate feature identification from textual information in data column names. Specifically, we use techniques of natural language processing and source code analysis, for data descriptions and source codes in Python notebooks to create a knowledge database with a particular focus on datetime features. We develop a recommendation system of datetime features for newly given text information based on that knowledge database. In experiments, we confirmed the classification accuracy of the knowledge database, applied the database to actual forecasting tasks such as home price forecasting, and achieved 5.76% on average for accuracy gain.

Construction and Optimization of Co-occurrence-attribute-interaction Model for Column Semantic Recognition

PDF HTML XML Export Cite reminder Construction and Optimization of Co-occurrence-attribute-interaction Model for Column Semantic Recognition DOI: 10.21655/ijsi.1673-7288.00294 Author: Affiliation: Clc Number: Fund Project: Article | Figures | Metrics | Reference | Related | Cited by | Materials | Comments Abstract:Government data governance is undergoing a new phase of transition from ``physical data aggregation'' to ``logical semantic unification''. Thus far, long-term ``autonomy'' of government information silos, leads to a wide spectrum of metadata curation issues, such as attributes with the same names but having different meanings, or attributes with different names but having the same meanings. Instead of either rebuilding/modifying legacy information systems or physically aggregating data from government information silos, logical semantic unification solves this problem by unifying the semantic expression of the metadata in government information silos and achieves the standardized metadata governance. This paper focuses on the logical semantic unification that semantically aligns the metadata in each government information silo with the existing standard metadata. Specifically, the names of the standard metadata are abstracted as semantic labels, and the column projections of silo relational data are semantically recognized to semantically align column names with the standard metadata and ultimately achieve the standardized governance of silo metadata. The existing semantic recognition techniques based on column projection fail to capture the column order-independent features of relational data and the correlation features among attributes and semantic labels. To address the above problem, we propose a two-phase model based on a prediction phase and a correction phase. In the prediction phase, a Co-occurrence-Attribute-Interaction (CAI) model is proposed to guarantee the column order-independent property by employing the parallelized self-attention mechanism; in the correction phase, a correction mechanism is introduced to optimize the prediction results of the CAI model by utilizing the co-occurrence of semantic labels. Experiments are conducted on a government benchmark dataset and several public English datasets, such as Magellan, and the results show that the two-phase model with a correction mechanism outperforms the current optimal model in macro-average and weighted average by up to 20.03% and 13.36%, respectively. Reference Related Cited by

Forest structure and individual tree inventories of northeastern Siberia along climatic gradients

Abstract. We compile a data set of forest surveys from expeditions to the northeast of the Russian Federation, in Krasnoyarsk Krai, the Republic of Sakha (Yakutia), and the Chukotka Autonomous Okrug (59–73∘ N, 97–169∘ E), performed between the years 2011 and 2021. The region is characterized by permafrost soils and forests dominated by larch (Larix gmelinii Rupr. and Larix cajanderi Mayr). Our data set consists of a plot database describing 226 georeferenced vegetation survey plots and a tree database with information about all the trees on these plots. The tree database, consisting of two tables with the same column names, contains information on the height, species, and vitality of 40 289 trees. A subset of the trees was subject to a more detailed inventory, which recorded the stem diameter at base and at breast height, crown diameter, and height of the beginning of the crown. We recorded heights up to 28.5 m (median 2.5 m) and stand densities up to 120 000 trees per hectare (median 1197 ha−1), with both values tending to be higher in the more southerly areas. Observed taxa include Larix Mill., Pinus L., Picea A. Dietr., Abies Mill., Salix L., Betula L., Populus L., Alnus Mill., and Ulmus L. In this study, we present the forest inventory data aggregated per plot. Additionally, we connect the data with different remote sensing data products to find out how accurately forest structure can be predicted from such products. Allometries were calculated to obtain the diameter from height measurements for every species group. For Larix, the most frequent of 10 species groups, allometries depended also on the stand density, as denser stands are characterized by thinner trees, relative to height. The remote sensing products used to compare against the inventory data include climate, forest biomass, canopy height, and forest loss or disturbance. We find that the forest metrics measured in the field can only be reconstructed from the remote sensing data to a limited extent, as they depend on local properties. This illustrates the need for ground inventories like those data we present here. The data can be used for studying the forest structure of northeastern Siberia and for the calibration and validation of remotely sensed data. They are available at https://doi.org/10.1594/PANGAEA.943547 (Miesner et al., 2022).

Critical assessment of chromatographic metadata in publicly available metabolomics data repositories

IntroductionThe structural identification of metabolites represents one of the current bottlenecks in non-targeted liquid chromatography-mass spectrometry (LC–MS) based metabolomics. The Metabolomics Standard Initiative has developed a multilevel system to report confidence in metabolite identification, which involves the use of MS, MS/MS and orthogonal data. Limitations due to similar or same fragmentation pattern (e.g. isomeric compounds) can be overcome by the additional orthogonal information of the retention time (RT), since it is a system property that is different for each chromatographic setup.ObjectivesIn contrast to MS data, sharing of RT data is not as widespread. The quality of data and its (re-)useability depend very much on the quality of the metadata. We aimed to evaluate the coverage and quality of this metadata from public metabolomics repositories.MethodsWe acquired an overview on the current reporting of chromatographic separation conditions. For this purpose, we defined the following information as important details that have to be provided: column name and dimension, flow rate, temperature, composition of eluents and gradient.ResultsWe found that 70% of descriptions of the chromatographic setups are incomplete (according to our definition) and an additional 10% of the descriptions contained ambiguous and/or incorrect information. Accordingly, only about 20% of the descriptions allow further (re-)use of the data, e.g. for RT prediction. Therefore, we have started to develop a unified and standardized notation for chromatographic metadata with detailed and specific description of eluents, columns and gradients.ConclusionReporting of chromatographic metadata is currently not unified. Our recommended suggestions for metadata reporting will enable more standardization and automatization in future reporting.

An occurrence data set for invasive and naturalized alien plants in India.

India has numerous invasive alien plant species (IAPSs), which seriously impact biodiversity, ecosystem services, and economic development. The availability of reliable occurrence records of IAPSs is of great importance for their successful management, prediction of distribution across time and space, and other research and development efforts. Global databases of occurrence data, like the Global Biodiversity Information Facility (GBIF), are often not exhaustive, especially for India, and poorly represent the actual distribution of IAPSs in the country. Our objective in creating this data set was threefold: (1) to compile occurrence data for the invasive and naturalized alien plant species of India, (2) to collect spatial and temporal information associated with occurrence records, and (3) to share the data in an accessible format so every record is traceable to its sources and allow users to submit data to increase the resolution of the data set. To achieve these objectives, we extracted data from 3137 literature records and 357 herbarium sheets. For each occurrence record, we curated information for 20 variables, which were arranged in a table with the Darwin Core (DwC) terms as column names. All data were subjected to technical validation before being included in the database. A total of 12,347 occurrence records were obtained for 362 species (195 invasive aliens and 167 naturalized aliens). The number of collected occurrence records was much higher for the invasive aliens (73.7%) than for the naturalized alien species (26.3%). Our data set will supplement the GBIF data by 60.39%, and occurrence records will be added for 64 invasive and naturalized alien plant species. The data set, as a part of the larger database of the Indian Alien Flora Information (ILORA) database, is made available without any restrictions on use as long as this data paper is properly cited. We have also made provisions for users to submit occurrence-related data following a data standard. The users are encouraged to cite the original reference when using a specific data record. The data set is expected to assist a wide range of stakeholders involved in India's scientific research, policy formulation, and decision-making related to IAPSs.

Balancing coverage and specificity for semantic labelling of subject columns

Many data are published on the Web using tabular data formats (e.g., spreadsheets). One of the main challenges for their effective (re)use is their generalized lack of semantics (e.g., column names are not usually standardized, and their meaning and content are not always clear). There is a common understanding that the reuse of tabular data may be improved by annotating them with the types used in knowledge graphs. In this paper, we present a novel approach to automatically type entity columns in tabular data with ontology classes. In contrast with existing proposals in the state-of-the-art, our approach does not require external linguistic resources, lookup services, model training, building a model of the knowledge graph beforehand, or having a human in the loop.

Method of protection of database management systems against sql-identifier injection attacks

The article reviews SQL injection and SQL identifier injection attacks in database management systems, identifies their nature, the threats they pose, and the types of these attacks. A new method of protecting database management systems from SQL identifier injection attacks is also covered. Proposed solution are functions that can be added to the prepared API statements: setColumnName: uses the column name and its index as arguments and setTableName: uses the table name and its index as arguments. This method allows you to prepare operators to fill placeholders with table and column names, prevents SQL-IDIA, does not skip schema information, has no restrictions on input-based sanitation approaches. These two features help prevent database management systems from leaking confidential database information by performing a default operation when the input column or table name does not exist in the database. For example, if a column name is used in a particular function and the column name is invalid, the database management system will sort the results by the first column of the table. Only the table and column names in our advanced API were examined, as GitHub analysis showed that 96% of concatenated IDs were table and column names. In all experiments, the new setColumnName feature surpassed the implementation of dynamic whitelisting. In two experiments, the implementation of a static whitelist slightly exceeded the name function of the new set of columns. Although this special approach has little performance advantage, whitelisting approaches can add non-trivial complexity to program code and lead to erroneous results. The new setColumnName feature has successfully prevented all these attacks. Filling placeholders with column names is practical and effective compared to existing special approaches, does not create additional costs compared to the existing functions of the trained operator, and is effective against SQL identifier injection attack.

ER-SQL: Learning enhanced representation for Text-to-SQL using table contents

Text-to-SQL emerges to play an important role in interactive data analysis, which provides a friendly interface for converting natural language into relational database language (i.e., SQL). In order to translate a user’s query into an executable SQL statement, semantic parsing is essential to the transformation process. In particular, existing efforts provide some feasible solutions, and state-of-the-art models mainly adopt the sketch-based paradigm such that template values are to be filled. To this end, most methods extract values based on column representations. However, if the query contains multiple values that belong to different columns, these methods may fail to extract the values accurately. Moreover, it can be difficult to infer the right values when the query does not explicitly mention the corresponding column names. To bridge the gap, we propose a novel neural architecture, namely, ER-SQL for learning enhanced representations for Text-to-SQL. Based on pre-trained model BERT, ER-SQL uses column contents to better extract features of columns. Moreover, ER-SQL harnesses the column representations to latently reformulate the query. To verify the effectiveness of ER-SQL, comprehensive experiments demonstrate that ER-SQL achieves better results than existing models on the benchmark dataset WikiSQL, as well as on a representative Chinese dataset TableQA.

Intelligent approach to automated star-schema construction using a knowledge base

Most data-warehouse construction processes are performed manually by experts, which is laborious, time-consuming, and prone to error. Furthermore, special knowledge is required to design complex multidimensional models, such as a star schema. This predicament has motivated computer scientists to propose automation techniques to generate such models. For this reason, we present a new strategy that incorporates knowledge-based models into a framework, named the Semantic-based Star-schema Designer, that assists the automation of star schema construction. Our models provide reasoning capabilities needed by star schema designs, including those that can disambiguate heterogeneous terms, detect appropriate data types and attribute sizes, and organize data hierarchies to support online analytical processes. We also propose strategies to overcome the uncertainty arising when attribute names are not available in the data source. The names of unknown attributes are thus predicted using an arithmetic coding technique to infer column names. Our system also generates star schema from semi-structured data (e.g., comma-separated-value files and spreadsheets), which do not provide primary keys, foreign keys, or relationship cardinalities between tables. Our framework facilitates star schema construction and their relationship information without human intervention using homegrown algorithms. Experiments demonstrate that our technique predicts column names and data types that enable the effective generation of star schema better than baseline approaches.

Enhanced Natural Language Interface for Web-Based Information Retrieval

Database application is at the core of most web application systems such as web-based email, source codes repository management, public scientific data repository management, news portals, and publication repository of various fields. However, the usage of these database systems for data and information retrieval is severely limited because of lacking support for processing search queries expressed in a natural language (NL). Most web interfaces for databases today only take search queries entered in some form of logical combination of keywords or text strings, which restrict the scope and depth of what a web user really wants to search for, even though natural language based data or information retrieval has made significant advances in recent years. To overcome or at least to alleviate such limitation in web information services, we propose in this article an improved neural model based on an existing framework IRNet for NL query of databases, in which a representation of Gated Graph Neural Network (GGNN) is introduced to encode the database entities and relations. We also represent and use the database values in the prediction model to identify and match table and column names for automatic synthesize a correct SQL statement from a query expressed in a NL sentence. Experiments with a public dataset demonstrates the promising potential of our approach.

Mechanism for securing cloud based data warehouse schema

Several studies have recommended encryption of data for securing Data Warehouse (DW) stored in hostile cloud networks. Most of the published work on encrypted DWs encrypts the DW data while DW Schema, i.e., Fact and Dimension table’s name and their attributes, remain unprotected. Unencrypted DW schema attributes may reveal critical information about the data contents to the cloud administrators or attackers. In this paper, we propose a novel mechanism for encrypting column names of DW’s fact and dimension tables, which in turn protect the revelation of details of interesting columns to the cloud network. For this, we have proposed a customized method for encryption of column names of facts and dimension tables and implemented it in MySQL’s column name restrictions to test its validity. This paper also introduces an Enhanced Encryption Model for Data Warehouse, which provides a complete solution for securing data warehouse. Here, the column name gets encrypted with the help of keys from a secure host without the revelation of security details to the cloud network. It reduces the attacker’s ability to target strategically important columns such as sales figures, cost, etc. As a result, it increases attacker effort by (n – k − W)/(n − k) in case of a weighted column scenario. Here, ‘n’ is the total number of columns, ‘k’, and ‘W’ is the number of unencrypted and encrypted columns, respectively. We have also conducted a performance analysis of the proposed mechanism on the standard TPC-H database for both encryption and decryption cycles.

Guided automated learning for query workload re-optimization

Query optimization is a hallmark of database systems. When an SQL query runs more expensively than is viable or warranted, determination of the performance issues is usually performed manually in consultation with experts through the analysis of query's execution plan (QEP). However, this is an excessively time consuming, human error-prone, and costly process. GALO is a novel system that automates this process. The tool automatically learns recurring problem patterns in query plans over workloads in an offline learning phase, to build a knowledge base of plan-rewrite remedies. It then uses the knowledge base online to re-optimize queries often quite drastically. GALO's knowledge base is built on RDF and SPARQL, W3C graph database standards, which is well suited for manipulating and querying over SQL query plans, which are graphs themselves. GALO acts as a third-tier of re-optimization, after query rewrite and cost-based optimization, as a query plan rewrite . For generality, the context of knowledge base problem patterns, including table and column names, is abstracted with canonical symbol labels. Since the knowledge base is not tied to the context of supplied QEPs, table and column names are matched automatically during the re-optimization phase. Thus, problem patterns learned over a particular query workload can be applied in other query workloads. GALO's knowledge base is also an invaluable tool for database experts to debug query performance issues by tracking to known issues and solutions as well as refining the optimizer with new tuned techniques by the development team. We demonstrate an experimental study of the effectiveness of our techniques over synthetic TPC-DS and real IBM client query workloads.

Meimei: An Efficient Probabilistic Approach for Semantically Annotating Tables

Given a large amount of table data, how can we find the tables that contain the contents we want? A naive search fails when the column names are ambiguous, such as if columns containing stock price information are named “Close” in one table and named “P” in another table.One way of dealing with this problem that has been gaining attention is the semantic annotation of table data columns by using canonical knowledge. While previous studies successfully dealt with this problem for specific types of table data such as web tables, it still remains for various other types of table data: (1) most approaches do not handle table data with numerical values, and (2) their predictive performance is not satisfactory.This paper presents a novel approach for table data annotation that combines a latent probabilistic model with multilabel classifiers. It features three advantages over previous approaches due to using highly predictive multi-label classifiers in the probabilistic computation of semantic annotation. (1) It is more versatile due to using multi-label classifiers in the probabilistic model, which enables various types of data such as numerical values to be supported. (2) It is more accurate due to the multi-label classifiers and probabilistic model working together to improve predictive performance. (3) It is more efficient due to potential functions based on multi-label classifiers reducing the computational cost for annotation.Extensive experiments demonstrated the superiority of the proposed approach over state-of-the-art approaches for semantic annotation of real data (183 human-annotated tables obtained from the UCI Machine Learning Repository).