Generating SQL Queries from SBVR Rules

Referential integrity quality metrics

Data mining is becoming increasingly important since the size of databases grows even larger and the need to explore hidden rules from the databases becomes widely recognized. Currently database systems are dominated by relational database and the ability to perform data mining using standard SQL queries will definitely ease implementation of data mining. However the performance of SQL based data mining is known to fall behind specialized implementation. In this paper we present an evaluation of parallel SQL based data mining on large scale PC cluster. The performance achieved by parallelizing SQL query for mining association rule using 4 processing nodes is even with C based program.

Getting intelligent insight from large amount of dataset is critical for Energy companies to optimize their operations across various business segments such as drilling, production and completion etc. The paper proposes end-to-end workflow to 1) extract data form rig and production reports and store dataset into databases 2) build a conversational generative AI enabled chatbot which is trained to answer questions related to drilling and production monitoring, queries dataset, frequently performed diagnostic analysis and can generate recommendations to improve operations. The chatbot is integrated with large language models (LLM) and machine learning models (ML) on the cloud and based on questions asked by user it provides answers in conversational settings. Chatbot is hosted in cloud and is integrated with various databases, document repositories and several machine learning model. The machine learning models are built to enable chatbot's capability to answer questions related to drilling and production analytics. Chatbot is integrated with user interface where user can type or ask questions. Using natural language process (NLP) and artificial intelligence (Al), chatbot understands intent of question and if needed asks relevant follow-up questions to provide the answer. Chatbot can also perform statistical analysis, generate SQL queries on datasets and can use those statistics to answer questions. Further if enabled, chatbot can also search information from drilling and production reports and scientific articles. Three case studies are presented. In case study#1, chatbot was integrated with operator's historical PDF drilling reports (Volve dataset), which traditionally are not easy to extract and analyze at scale. Several thousand drilling reports were extracted and stored in database. Various capabilities were added to chatbot such has Cross-documents insights and trend, for example, well progression, operation history, can be generated and displayed on user interface and further analysis can be performed in conversational manner. The dataset created was used to perform comparative analysis identifying wells having significant higher non production time (NPT) due to repair or fishing events. In this manner, chatbot can compare one well's operational statistics with other well and generate various visuals which helps identifying possible ways to improve drilling operations. Similarly, chatbot was also trained to provide answers for production diagnostics such as comparing well's relative performances and root cause identification for poor performing wells. When analyzed on test dataset chatbot was able to identify 20% uplift in production for wells supported on plunger lift. Finally, chatbot was enabled to support NLP based searches. Engineers can ask specific questions such as "provide operational log for well F4 when fishing happened and sort the result by reporting date in ascending order. Show me both SQL query and the resulted table" and chatbot will generate SQL query and resulted table. The work demonstrates that generative AI has great potential to transform the Energy industry.

The structural registration of chemically modified macromolecules is vital for the development of biopharmaceuticals. However, registration and search of such complex molecules has so far posed formidable challenges performance-wise, since today's chemistry-oriented databases do not scale well to macromolecules. As a practical consequence, macromolecules tend to be stored in protein databases with a focus on protein sequence only, and salient chemistry details are therefore lost. This article describes protein format extensions and the use of pseudoatoms for representing natural amino acids in chemical structures to allow high-performance registration and retrieval of large macromolecules. The representations include exact chemical modifications and enable lossless conversion between chemistry and sequence formats. Registration is done in parallel in both sequence and chemistry formats, and users can register and retrieve molecules in either format as they choose, resulting in what we call a BioChemformatics database. Having both sequence and chemistry formats available on-demand allows for the construction of protein SAR tables with mixed sequence and chemistry information. Likewise, searching may combine sequence and chemistry terms and be performed in standard vendor applications like MDL's ISIS/Base or in-house applications using standard SQL queries.

The paper presents concept and structure of relational database for description of noise sources investigated within the framework of the project “Development of methodologies and means for protection from environmental noise.” The database is used for collecting and storing experimental results of industrial noise measurements that were carried out for the purpose of noise mapping and noise protection planning. The database provides information about equivalent noise level and one-third octave noise spectrum in the near field of sound sources. The access to the database is possible through standard SQL queries, which is suitable for software tools used for noise reduction. The rights for submitting data are granted only to the developers, while all collected data are publicly available via the Internet.

Relational database management systems (RDBMS) are operationally similar to a dynamic language processor. They take SQL queries as input, dynamically generate an optimized execution plan, and then execute it. In recent decades, the emergence of in-memory databases with columnar storage, which use array-like storage structures, has shifted the focus on optimizations from the traditional I/O bottleneck to CPU and memory. However, database research so far has primarily focused on CPU cache optimizations. The similarity in the computational characteristics of such database workloads and array programming language optimizations are largely unexplored. We believe that these database implementations can benefit from merging database optimizations with dynamic array-based programming language approaches. Therefore, in this paper, we propose a novel approach to optimize database query execution using a new array-based intermediate representation, HorseIR, that resides between database queries and compiled code. Furthermore, we provide a translator to generate HorseIR from database execution plans and a compiler that optimizes HorseIR and generates efficient code. We compare HorseIR with the MonetDB RDBMS, by testing standard SQL queries, and show how our approach and compiler optimizations improve the runtime of complex queries.

Due to the lack of labeled data, previous research on text-to-SQL parsing mainly focuses on English. Representative English datasets include ATIS, WikiSQL, Spider, etc. This paper presents DuSQL, a larges-scale and pragmatic Chinese dataset for the cross-domain text-to-SQL task, containing 200 databases, 813 tables, and 23,797 question/SQL pairs. Our new dataset has three major characteristics. First, by manually analyzing questions from several representative applications, we try to figure out the true distribution of SQL queries in real-life needs. Second, DuSQL contains a considerable proportion of SQL queries involving row or column calculations, motivated by our analysis on the SQL query distributions. Finally, we adopt an effective data construction framework via human-computer collaboration. The basic idea is automatically generating SQL queries based on the SQL grammar and constrained by the given database. This paper describes in detail the construction process and data statistics of DuSQL. Moreover, we present and compare performance of several open-source text-to-SQL parsers with minor modification to accommodate Chinese, including a simple yet effective extension to IRNet for handling calculation SQL queries.

Text-to-SQL is a task that converts natural language questions into SQL queries. Recent text-to-SQL models employ two decoding methods: sketch-based and generation-based, but each has its own shortcomings. The sketch-based method has limitations in performance as it does not reflect the relevance between SQL elements, while the generation-based method may increase inference time and cause syntactic errors. Therefore, we propose a novel decoding method, Hybrid decoder, which combines both methods. This reflects inter-SQL element information and defines elements that can be generated, enabling the generation of syntactically accurate SQL queries. Additionally, we introduce a Value prediction module for predicting values in the WHERE clause. It simplifies the decoding process and reduces the size of vocabulary by predicting values at once, regardless of the number of conditions. The results of evaluating the significance of Hybrid decoder indicate that it improves performance by effectively incorporating mutual information among SQL elements, compared to the sketch-based method. It also efficiently generates SQL queries by simplifying the decoding process in the generation-based method. In addition, we design a new evaluation measure to evaluate if it generates syntactically correct SQL queries. The result demonstrates that the proposed model generates syntactically accurate SQL queries.

Large databases are becoming ever more ubiquitous, as are the opportunities for discovering useful knowledge within them. Evolutionary computation methods such as genetic programming have previously been applied to several aspects of the problem of discovering knowledge in databases. The more specific task of producing human-comprehensible SQL queries has several potential applications but has thus far been explored only to a limited extent. In this chapter we show howdevelopmental genetic programming can automatically generate SQL queries from sets of positive and negative examples. We show that a developmental genetic programming system can produce queries that are reasonably accurate while excelling in human comprehensibility relative to the well-known C5.0 decision tree generation system.

Text-to-SQL is a task of synthesizing SQL queries from utterances. Most existing approaches of text-to-SQL rarely utilize tables to guide the prediction of SQL query. We present a novel approach called GuideSQL which predicts tables first and uses a pruning algorithm for removing the columns which don’t belong to the predicted tables to avoid errors caused by misprediction of table-column dependencies. For reducing the prediction errors of tables, we use the top-K predicted tables to generate SQL queries and employ a string-matching algorithm to get the most reasonable one. Futhermore, a type linking mechanism is utilized to augment the relevance between utterances and schemas. On the challenging text-to-SQL benchmark SParC, we use previous query attention to get context-dependent information of SQL queries. GuideSQL obtains 36.3% question matching accuracy and 19.5% interaction matching accuracy on the dev set. With BERT augmentation, GuideSQL achieves 49.2% question matching accuracy and 31.6% interaction matching accuracy on the dev set, outperforms the previous state-of-the-art model by 2% question matching accuracy and 2.1% interaction matching accuracy.

Modern databases contain an enormous amount of information stored in a structured format. This information is processed to acquire knowledge. However, the process of information extraction from a Database System is cumbersome for non-expert users as it requires an extensive knowledge of DBMS languages. Therefore, an inevitable need arises to bridge the gap between user requirements and the provision of a simple information retrieval system whereby the role of a specialized Database Administrator is annulled. In this paper, we propose a methodology for building an Intelligent Querying System (IQS) by which a user can fire queries in his own (natural) language. The system first parses the input sentences and then generates SQL queries from the natural language expressions of the input. These queries are in turn mapped with the desired information to generate the required output. Hence, it makes the information retrieval process simple, effective and reliable.

This paper describes the specification language associated with the Validation and Verification System (VVS) currently used verify portions of the data from the Space Shuttle Measurement and Stimulus data base. This base contains generic information about each of the Space Shuttles as well as specific data about each mission such as sensor calibrations, telemetry, etc. VVS is built around a simple and straightforward language used by engineering analysts write logically true expressions about the data. The VVS Interpreter processes the expressions, verifies the data, and reports any discrepancies. Consequently, data verification is removed from the hands of programmers and is made available analysts who can worry about what data checks make rather than how they are programmed. The language is based on the Predicate Calculus. It is applicable both relational and hierarchical data structures and contains facilities for a wide variety of validations such as data format and syntax checks, numeric type checking, range checks, and conditional verification. The language also includes numeric and suing operators and functions, field decomposition (sub-ficld) specification, uscrdefined functions, pre-conditions, and quantif~ed expressions. VVS accepts as input a user-created data file containing verification statements. Each statement is a logically [rue expression about the contents of the data base. Statements are written in free form and use the semicolon as a statement terminator. VVS requires a Data Base Description (DBD) lile which lists the relations (or hierarchy) and their contents in an ordered manner. Each field must have a name, a start position in the record, and a length. Also, key fields and jomed relations must Copyright Q 1993 by Leal Associates. Published by !he American Institute of Aeronautics and Astronautics, Inc. with permission. be specified. No data types are declared since VVS assumes that all data items are of type character. Numeric conversion does not occur until a numeric operator or function is used. Thus, expressions may be written which extract portions of alphanumeric fields and use them as arguments numeric operators or functions. Operators are infix with standard precedence. Numbers can be signed or unsigned integers, fixed point, or exponential type. Character strings are enclosed within double quote marks. VVS contains a standard set of built-in arithmetic, string, and logical operators for constructing expressions. The arithmetic operators are the following: + Addition Subtraction x Multiplication / Division * * Exponentiation / / Root The following arithmetic comparison operators are available for forming numeric verifications: Equal Not equal lo Less than Greater than Less than or equal Greater than or equal Not less than Not greater than Approximately equal The approximately equal to operator is true when the absolute value of the difference between its left and right arguments is less than a user-defined threshold. The only string manipulation operator is A which is concatenation. However, strings can be compared with the following operators:

A Java based development tool for building portable chemical information systems is presented. The system contains applets for constructing web-based interfaces and classes that add structure handling to relational databases. Custom applications built with JChem can combine SQL and structural queries.

Text-to-SQL parsing is the task of converting natural language questions into executable SQL queries, a significant branch of semantic parsing, which has gained increasing attention in recent years. This technology lowers the barrier for people to access databases, enhancing the convenience and availability of data. However, the primary challenge for text-to-SQL parsing lies in domain adaptation, which concerns whether the model can be applied to new databases and effectively align natural language questions with the corresponding tables or columns within the database. To address these issues, research has introduced SRSQL (Syntax and Relation-Augmented Query Generation), which incorporates syntax information and predefined relationships into the model, effectively utilizing syntactic dependencies and pattern linking to improve performance. Using a Transformer-based decoder, SRSQL generates SQL queries in the form of Abstract Syntax Trees (AST), significantly boosting prediction accuracy. Experimental results show that SRSQL outperforms comparative models, particularly on challenging benchmarks like Spider and Spider-SYN.

In the Big Data era, data-intensive and data-driven approaches are affording the paradigm change. In this data deluge, finding relevant and appropriate information is of great importance for achieving the demanding goals posed by business and society. However, traditional database search is limited by the assumption of query criteria as hard constraints. This provides results without personalization and flexibility, and often leads to information overload, i.e., non-focused results that are not informative to users. There is also often relevance misalignment, which occurs when the system only relies on consensus relevance. To overcome these problems, we present, in this work, a novel approach for (semi) automatically learning database queries based on Semiotics and Computational Intelligence techniques, in which user perception and intention are considered for interactively producing tailored queries that facilitate personalized data exploration and retrieval. In this sense, we present an empirical analysis to assess the effectiveness of our approach in learning SQL queries in a Query By Example scenario. The obtained results confirm that our approach is capable of generating SQL queries based on a single user-defined example without requiring any database-specific knowledge such as query language or database schema and structure.