Structured Query Language Statements Research Articles

Sharing Queries with Nonequivalent User-defined Aggregate Functions

This article presents Sharing User-Defined Aggregate Function (SUDAF), a declarative framework that allows users to write User-defined Aggregate Functions (UDAFs) as mathematical expressions and use them in Structured Query Language statements. SUDAF rewrites partial aggregates of UDAFs using built-in aggregate functions and supports efficient dynamic caching and reusing of partial aggregates. Our experiments show that rewriting UDAFs using built-in functions can significantly speed up queries with UDAFs, and the proposed sharing approach can yield up to two orders of magnitude improvement in query execution time. The article studies also an extension of SUDAF to support sharing partial results between arbitrary queries with UDAFs. We show a connection with the problem of query rewriting using views and introduce a new class of rewritings, called SUDAF rewritings, which enables to use views that have aggregate functions different from the ones used in the input query. We investigate the underlying rewriting-checking and rewriting-existing problem. Our main technical result is a reduction of these problems to, respectively, rewriting-checking and rewriting-existing of the so-called aggregate candidates , a class of rewritings that has been deeply investigated in the literature.

Applying self-powered sensor and support vector machine in load energy consumption modeling and prediction of relational database

This study explores the analysis and modeling of energy consumption in the context of database workloads, aiming to develop an eco-friendly database management system (DBMS). It leverages vibration energy harvesting systems with self-sustaining wireless vibration sensors (WVSs) in combination with the least square support vector machine algorithm to establish an energy consumption model (ECM) for relational database workloads. Through experiments, the performance of self-sustaining WVS in providing power is validated, and the accuracy of the proposed ECM during the execution of Structured Query Language (SQL) statements is evaluated. The findings demonstrate that this approach can reliably predict the energy consumption of database workloads, with a maximum prediction error rate of 10% during SQL statement execution. Furthermore, the ECM developed for relational databases closely approximates actual energy consumption for query operations, with errors ranging from 1 to 4%. In most cases, the predictions are conservative, falling below the actual values. This finding underscores the high predictive accuracy of the ECM in anticipating relational database workloads and their associated energy consumption. Additionally, this paper delves into prediction accuracy under different types of operations and reveals that ECM excels in single-block read operations, outperforming multi-block read operations. ECM exhibits substantial accuracy in predicting energy consumption for SQL statements in sequential and random read modes, especially in specialized database management system environments, where the error rate for the sequential read model is lower. In comparison to alternative models, the proposed ECM offers superior precision. Furthermore, a noticeable correlation between model error and the volume of data processed by SQL statements is observed. In summary, the relational database ECM introduced in this paper provides accurate predictions of workload and database energy consumption, offering a theoretical foundation and practical guidance for the development of eco-friendly DBMS.

Rapid identification of chronic kidney disease in electronic health record database using computable phenotype combining a common data model.

Rapid identification of chronic kidney disease in electronic health record database using computable phenotype combining a common data model.

A Semantic Learning-Based SQL Injection Attack Detection Technology

Over the years, injection vulnerabilities have been at the top of the Open Web Application Security Project Top 10 and are one of the most damaging and widely exploited types of vulnerabilities against web applications. Structured Query Language (SQL) injection attack detection remains a challenging problem due to the heterogeneity of attack loads, the diversity of attack methods, and the variety of attack patterns. It has been demonstrated that no single model can guarantee adequate security to protect web applications, and it is crucial to develop an efficient and accurate model for SQL injection attack detection. In this paper, we propose synBERT, a semantic learning-based detection model that explicitly embeds the sentence-level semantic information from SQL statements into an embedding vector. The model learns representations that can be mapped to SQL syntax tree structures, as evidenced by visualization work. We gathered a wide range of datasets to assess the classification performance of the synBERT, and the results show that our approach outperforms previously proposed models. Even on brand-new, untrained models, accuracy can reach 90% or higher, indicating that the model has good generalization performance.

A Natural Language Interface to Relational Databases Using an Online Analytic Processing Hypercube

Structured Query Language (SQL) is commonly used in Relational Database Management Systems (RDBMS) and is currently one of the most popular data definition and manipulation languages. Its core functionality is implemented, with only some minor variations, throughout all RDBMS products. It is an effective tool in the process of managing and querying data in relational databases. This paper describes a method to effectively automate the conversion of a data query from a Natural Language Query (NLQ) to Structured Query Language (SQL) with Online Analytical Processing (OLAP) cube data warehouse objects. To obtain or manipulate the data from relational databases, the user must be familiar with SQL and must also write an appropriate and valid SQL statement. However, users who are not familiar with SQL are unable to obtain relevant data through relational databases. To address this, we propose a Natural Language Processing (NLP) model to convert an NLQ into an SQL query. This allows novice users to obtain the required data without having to know any complicated SQL details. The model is also capable of handling complex queries using the OLAP cube technique, which allows data to be pre-calculated and stored in a multi-dimensional and ready-to-use format. A multi-dimensional cube (hypercube) is used to connect with the NLP interface, thereby eliminating long-running data queries and enabling self-service business intelligence. The study demonstrated how the use of hypercube technology helps to increase the system response speed and the ability to process very complex query sentences. The system achieved impressive performance in terms of NLP and the accuracy of generating different query sentences. Using OLAP hypercube technology, the study achieved distinguished results compared to previous studies in terms of the speed of the response of the model to NLQ analysis, the generation of complex SQL statements, and the dynamic display of the results. As a plan for future work, it is recommended to use infinite-dimension (n-D) cubes instead of 4-D cubes to enable ingesting as much data as possible in a single object and to facilitate the execution of query statements that may be too complex in query interfaces running in a data warehouse. The study demonstrated how the use of hypercube technology helps to increase system response speed and process very complex query sentences.

TransJoin: An Algorithm to Implement Division Operator of Relational Algebra in Structured Query Language

Division operator is one of operators in Relational Algebra which is not implemented directly in SQL (Structured Query Language) standard. Therefore it is not be able to retrieve division query results with an SQL statement in the current relational database management systems (RDBMS). Database programmers have to create a complicated query to perform the task. It is the only relational algebra operators which is not implemented in ANSI SQL standard. This research aimed to study and design an algorithm named TransJoin (transformation and join) to implement it in SQL. TransJoin works to relation P(x, y) divided by Q(y) resulted R(x) through grouping and transforming each y attribute becomes P’(x, y’) and Q’(y’) and each x value is a single tuple with y’ is a composite value of y. Then, result R(x) is resulted of joining P’(x, y’) and Q’(y’) relations by matching y’ attribute. TransJoin was implemented in an open source RDBMS SQLLite. TransJoin tested and delivered valid results by comparing of traditional SQL queries and our proposed SQL queries with various data. The research showed that our algorithm is much more efficient by consuming 0.078 milliseconds compared contrasted the traditional query in 1,974.08 milliseconds for 9,991 tuples.

Structured Query Language Learning: Concept Map-Based Instruction Based on Cognitive Load Theory

Structured query language (SQL) is difficult to master because the execution process of SQL statements is invisible. When learning to construct an SQL query, learners must visualise the evolution process of the intermediate datasets of the SQL statement in working memory, which may burden learners' cognitive load and consequently jeopardise learning outcomes. This study describes the execution process of SQL statements by using concept maps to improve learners' understanding of SQL. An empirical experiment was conducted using two database courses, namely concept map-based and conventional instruction, to examine the relationship between concept maps and the understanding of SQL from a cognitive load theory perspective. The experimental results demonstrated the superiority of concept map-based instruction over conventional instruction because concept map-based instruction reduces extraneous load but increases germane load. Concept map construction facilitated learner engagement and promoted meaningful learning. Studying the instructors' concept maps helped learners follow the cognitive structures used by instructors to perform SQL queries, and enabled them to perceive the execution process of SQL queries relatively easily. These results potentially help educators understand the learning difficulties caused by the declarative nature of SQL and motivating researchers to resolve the inherent problem by considering learners' cognitive processes.

A Information Retrieval Based on Question and Answering and NER for Unstructured Information Without Using SQL

In today’s world, the availability of information in the form of unstructured data is in abundance. The unstructured information received is more often than not in the form of natural language text. For any defense establishment, the spy data or any sensitive information received may be best utilized when the information can be extracted efficiently and easily. The proposed model is applicable wherever the influx of text-heavy (unstructured data) is high like the information from the world wide web, documents related to a particular domain, or any other source where the information is in the form of natural language. The proposed Natural Language Information Interpretation and Representation System (NLIIRS) accepts the information in the form of natural language text, processes the information and allows the user to retrieve information by rendering questions in natural language. The questions thus asked by the user are responded by NLIIRS in the form of factoid or phrase based answers. In comparison to the conventional question and answering systems the proposed NLIIRS uses the advantages of both named entity recognition as well as sequential pattern matching based answer search technique. The proposed technique helps us to avoid the use of structured query language (SQL) at the back-end for information processing, storage and extraction. The conversion of user query to SQL statements and also storing the unstructured text in the form of relation tables can be avoided by using NLIIRS. By using this approach in our novel text processing algorithm, after every execution step, the pattern matching and extraction process of the answers to the queries becomes concise and faster. The whole system has been designed on natural language tool kit of Stanford University which helped us to generate parts of speech tag, tokenize the data, and forming tree structure. The novel text processing algorithm utilizes the lemmatizer, stemmer and ne_chunker to prepare the text for information retrieval via Q&A. The advantage of this system is that it does not need training. This system will enable the user to retrieve any information of his/her choice from the available unstructured information.

A Shallow Parsing Approach to Natural Language Queries of a Database

The performance and reliability of converting natural language into structured query language can be problematic in handling nuances that are prevalent in natural language. Relational databases are not designed to understand language nuance, therefore the question why we must handle nuance has to be asked. This paper is looking at an alternative solution for the conversion of a Natural Language Query into a Structured Query Language (SQL) capable of being used to search a relational database. The process uses the natural language concept, Part of Speech to identify words that can be used to identify database tables and table columns. The use of Open NLP based grammar files, as well as additional configuration files, assist in the translation from natural language to query language. Having identified which tables and which columns contain the pertinent data the next step is to create the SQL statement.

Introduction to the RE’14 special issue

This special issue of the Requirements Engineering Journal (REJ) contains extended versions of selected papers from the 22nd IEEE International Requirements Engineering Conference (RE’14). This continues a long-standing collaboration between RE and REJ to invite the authors of the best papers presented at the conference to submit extended papers to reach the wider journal readership. As program chair of RE’14, it is my pleasure to introduce these papers to you. The conference was held at the Blekinge Institute of Technology in Karlskrona, Sweden, August 25–29, 2014, with Tony Gorschek as general chair, Sarah Gregory and Marjo Kauppinen as industry co-chairs, and Juergen Borstler as organizing chair. Three hundred people registered for the main conference, and over 420 registered in total for the conference and its associated tutorials, doctoral symposium, and workshops. The conference featured stirring keynotes by Anthony Wasserman, Annie Anton, and Anthony Finkelstein. RE’14 also offered a variety of panels, mini-tutorials, lightning-round poster and tool demonstrations, an Industry Lab, and multiple social activities to foster new ideas, uses, and collaborations in requirements engineering. The theme of RE’14 was innovation. Innovation in requirements engineering explores new ideas and breakthroughs in the area. Innovation through requirements engineering describes how its practices, processes, models, methods, and frameworks enable and support innovation in product development. RE’14 received 115 submissions to its research track by authors from 30 countries. Following review by three members of the program committee and an online discussion among the reviewers of each paper, the program board met and used the reviews and discussion to select 31 research papers for presentation at the conference, an acceptance rate of 27 %. I extend my thanks to the program committee and the program board for their service, and to the authors for their submissions. The high quality of the authors’ papers provided the strong technical program for RE’14, and that is reflected in the papers offered here. The three papers published in this issue are extended versions of the work invited from the best of those RE’14 research-track papers. Each of these extended papers underwent rigorous, additional review by three reviewers in accordance with the REJ review process before acceptance to the special issue. The first paper is ‘‘TiQi: Answering Unstructured Natural Language Trace Queries’’ by Piotr Pruski, Sugandha Lohar, William Goss, Alexander Rasin, and Jane ClelandHuang. Developers often find it hard to specify complex trace queries, which limits their access to the traceability information they need. In response to this problem, the authors have developed TiQi, a process to automatically translate unstructured spoken or written natural language queries into executable SQL (structured query language) statements. TiQi is supported by a database query mechanism as well as by domainand project-specific elements. Experimental results show that users prefer written natural language queries over spoken queries. In experiments on two datasets, TiQi’s accuracy rate in translating queries is shown to vary from 47 up to 93 %. Two additional experiments evaluated the performance of TiQi’s ambiguity resolution and what the sample size of the trace query collection needs to be to construct a & Robyn R. Lutz rlutz@iastate.edu

Research on Improving Data Query Speed Methods

In order to improve data query speed, there are four methods: optimization of SQL (Structured Query Language) statements and reasonable use of index, temporary tables and views are researched. Practice demonstrates these four methods can improve query speed and database performance.

Information Retrieval using Natural Language Interfaces

In Database Management System (DBMS) is collection of interrelated data and set of programs to access and modify the data. In Relational DBMS (RDBMS), data is organized inform of tables. In order to retrieve information from the database, the end user requires the knowledge of database query language such as Structured Query Language (SQL). However, not every user is able to write SQL queries as they may not aware of structure of the database and they needs to learn SQL. So, there is a need for non-expert user to query relational databases in their natural language such as English. In this paper, we propose s Natural language Interfaces to Relational Databases (NLIRDB) to access the data in easier manner. The end user can submit the request in natural language. The query in natural language is then translated into equivalent SQL statement and finally SQL statement will be executed to extract desired information from the database. The proposed method allows non-expert user to access information from database.

Data-mining based SQL injection attack detection using internal query trees

Detecting SQL injection attacks (SQLIAs) is becoming increasingly important in database-driven web sites. Until now, most of the studies on SQLIA detection have focused on the structured query language (SQL) structure at the application level. Unfortunately, this approach inevitably fails to detect those attacks that use already stored procedure and data within the database system. In this paper, we propose a framework to detect SQLIAs at database level by using SVM classification and various kernel functions. The key issue of SQLIA detection framework is how to represent the internal query tree collected from database log suitable for SVM classification algorithm in order to acquire good performance in detecting SQLIAs. To solve the issue, we first propose a novel method to convert the query tree into an n-dimensional feature vector by using a multi-dimensional sequence as an intermediate representation. The reason that it is difficult to directly convert the query tree into an n-dimensional feature vector is the complexity and variability of the query tree structure. Second, we propose a method to extract the syntactic features, as well as the semantic features when generating feature vector. Third, we propose a method to transform string feature values into numeric feature values, combining multiple statistical models. The combined model maps one string value to one numeric value by containing the multiple characteristic of each string value. In order to demonstrate the feasibility of our proposals in practical environments, we implement the SQLIA detection system based on PostgreSQL, a popular open source database system, and we perform experiments. The experimental results using the internal query trees of PostgreSQL validate that our proposal is effective in detecting SQLIAs, with at least 99.6% of the probability that the probability for malicious queries to be correctly predicted as SQLIA is greater than the probability for normal queries to be incorrectly predicted as SQLIA. Finally, we perform additional experiments to compare our proposal with syntax-focused feature extraction and single statistical model based on feature transformation. The experimental results show that our proposal significantly increases the probability of correctly detecting SQLIAs for various SQL statements, when compared to the previous methods.

Natural Language Statement to SQL Query Translator

is an organized collection of data. It is created to store large amount of data and retrieve it efficiently in less amount of time. To retrieve data from the database Structured Query Language (SQL) is used. SQL has its own syntax of query. To retrieve correct data from the database this query should be written in proper or correct syntax. Thus users should have sufficient knowledge of SQL to retrieve data. In this paper a light weight technique of converting a natural language statement into equivalent SQL statement is highlighted which when executed on database provides us with the accurate results. The main advantage of this technique is the users which are unknown to the syntax of SQL can also use the system and retrieve data.

The Design and Implementation of an Automated System for Logging Clinical Experiences Using an Anesthesia Information Management System

Residents in anesthesia training programs throughout the world are required to document their clinical cases to help ensure that they receive adequate training. Current systems involve self-reporting, are subject to delayed updates and misreported data, and do not provide a practicable method of validation. Anesthesia information management systems (AIMS) are being used increasingly in training programs and are a logical source for verifiable documentation. We hypothesized that case logs generated automatically from an AIMS would be sufficiently accurate to replace the current manual process. We based our analysis on the data reporting requirements of the American College of Graduate Medical Education (ACGME). We conducted a systematic review of ACGME requirements and our AIMS record, and made modifications after identifying data element and attribution issues. We studied 2 methods (parsing of free text procedure descriptions and CPT4 procedure code mapping) to automatically determine ACGME case categories and generated AIMS-based case logs and compared these to assignments made by manual inspection of the anesthesia records. We also assessed under- and overreporting of cases entered manually by our residents into the ACGME website. The parsing and mapping methods assigned cases to a majority of the ACGME categories with accuracies of 95% and 97%, respectively, as compared with determinations made by 2 residents and 1 attending who manually reviewed all procedure descriptions. Comparison of AIMS-based case logs with reports from the ACGME Resident Case Log System website showed that >50% of residents either underreported or overreported their total case counts by at least 5%. The AIMS database is a source of contemporaneous documentation of resident experience that can be queried to generate valid, verifiable case logs. The extent of AIMS adoption by academic anesthesia departments should encourage accreditation organizations to support uploading of AIMS-based case log files to improve accuracy and to decrease the clerical burden on anesthesia residents.

A natural language user interface for fuzzy scope queries.

This paper presents a two-agent framework to build a natural language query interface for IC information system, focusing more on scope queries in a single English sentence. The first agent, parsing agent, syntactically processes and semantically interprets natural language sentence to construct a fuzzy structured query language (SQL) statement. The second agent, defuzzifying agent, defuzzifies the imprecise part of the fuzzy SQL statement into its equivalent executable precise SQL statement based on fuzzy rules. The first agent can also actively ask the user some necessary questions when it manages to disambiguate the vague retrieval requirements. The adaptive defuzzification approach employed in the defuzzifying agent is discussed in detail. A prototype interface has been implemented to demonstrate the effectiveness.

Rule-Based Model for Traffic Accident Visualization and Analysis

This research develops and tests a computerized model for intersection accident data visualization and analysis. The model implemented a rule-based visualization module that automatically constructs the collision diagram according to traffic flow conflicts, where accident characteristics can be analyzed either individually or collectively. A data acquisition module was developed for accessing accident databases maintained in state transportation and public safety departments. Using a local database management system as part of the model, the user can select to analyze any accident data through Structured Query Language statements. In addition, this model has implemented a data analysis and graphing module for comparing similarities and identifying differences in accident data and for presenting the results of the analysis through charts and tables. The model was tested with accident data obtained from the Ohio Department of Public Safety in a number of scenario studies, and some of them were presented in this paper as examples. Because the model is very time efficient and user friendly, it can be developed into an effective engineering tool for field applications.

Using an anesthesia information management system to prove a deficit in voluntary reporting of adverse events in a quality assurance program.

A deficit is suspected in the manual documentation of adverse events in quality assurance programs in anesthesiology. In order to verify and quantify this, we retrospectively compared the incidence of manually recorded perioperative adverse events with automatically detected events. In 1998, data of all anesthetic procedures, including the data set for quality assurance of the German Society of Anaesthesiology and Intensive Care Medicine (DGAI), was recorded online with the Anesthesia Information Management System (AIMS) NarkoData4 (Imeso GmbH). SQL (Structured Query Language) queries based on medical data were defined for the automatic detection of common adverse events. The definition of the SQL statements had to be in accordance with the definition of the DGAI for perioperative adverse events: A potentially harmful change of parameters led to therapeutic interventions by an anesthesiologist. During 16,019 surgical procedures, anesthesiologists recorded 911 (5.7%) adverse events manually, whereas 2966 (18.7%) events from the same database were detected automatically. With the exception of hypoxemia, the incidence of automatically detected events was considerably higher than that of manually recorded events. Fourteen and a half percent (435) of all automatically detected events were recorded manually. Using automatic detection, we were able to prove a considerable deficit in the documentation of adverse events according to the guidelines of the German quality assurance program in anesthesiology. Based on the data from manual recording, the results of the quality assurance of our department match those of other comparable German departments. Thus, we are of the opinion that manual incident reporting seriously underestimates the true occurrence rate of incidents. This brings into question the validity of quality assurance comparisons based on manually recorded data.

On workload characterization of relational database environments

A relational database workload analyzer (REDWAR) is developed to characterize the workload in a DB2 environment. This is applied to study a production DB2 system where a structured query language (SQL) trace for a two-hour interval and an image copy of the database catalog were obtained. The results of the workload study are summarized. The structure and complexity of SQL statements, the makeup and run-time behavior of transactions/queries, and the composition of relations and views are discussed. The results obtained provide the important information needed to build a benchmark workload to evaluate the alternative design tradeoffs of database systems.< <ETX xmlns:mml="http://www.w3.org/1998/Math/MathML" xmlns:xlink="http://www.w3.org/1999/xlink">&gt;</ETX>

OS/2 Query Manager overview and prompted interface

Operating System/2™ (OS/2™) Query Manager provides a user interface for both novice and sophisticated database users of the OS/2 Database Services. It offers defaults and standard options for the novice user. Prompting provides access to the database without requiring extensive knowledge of Structured Query Language (SQL), yet it also allows the advanced user to completely customize screens and reports. Direct keying of SQL statements is allowed as a fast path for the knowledgeable SQL user. Functions of OS/2 Query Manager are described, including details of the user interface.