SQL Standard Research Articles

Specification of Row Pattern Recognition in the SQL Standard and its Implementations

Recently, the SQL standardization Committee published a specification for support of the concept called Row Pattern Recognition in SQL. That way, the focus for the members of the Committee turned back to a kernel issue of the language, after standardizing the storage and manipulation of data formats, such as XML and JSON. In this paper we discuss the specified features using several examples and show to what extent different relational database systems as well as Data Analytics tools have integrated them. At the end of the paper, we describe the main inaccuracies of the proposal and the ways how to solve them. From our point of view, the following should be modified in one of the future proposals of the standard in relation to this concept: (a) Naming of several pattern navigation operations is inappropriate and should be changed; (b) The concept of implicit definition for row pattern variables in DEFINE should be changed to explicit; (c) The set of existing functions should be extended.

The New and Improved SQL

SQL:2016 (officially called ISO/IEC 9075:2016, Information technology - Database languages - SQL) was published in December of 2016, replacing SQL:2011 as the most recent revision of the SQL standard. This paper gives an overview of the most important new features in SQL:2016.

SQL/JSON Standard: Properties and Deficiencies

Recently, a new era of application development is emerging, which is based upon the ease of access to modern compute resources, such as mobile devices. This access can be supported using JSON (Java Script Object Notation). Therefore, the support of storage and query access for JSON documents in the context of relational DBMSs is necessary. For this reason, the SQL standardization committee published a proposal called SQL/JSON. In this paper we discuss the JSON features specified in the proposal and show to what extent different relational database systems have integrated them. At the end of the paper we describe the main drawbacks of the proposal and the ways to solve them. From our point of view, the following should be specified in one of the future proposals of SQL/JSON: JSON documents should be first-class objects in SQL (native storage). Handling JSON documents as first-class objects in SQL would provide the potential for greater capability for users and for better performance. The support of modification of parts of a JSON document using the SQL UPDATE statement is necessary. Direct access of external JSON data should be supported, too.

Possible and certain keys for SQL

Driven by the dominance of the relational model and the requirements of modern applications, we revisit the fundamental notion of a key in relational databases with NULL. In SQL, primary key columns are NOT NULL, and UNIQUE constraints guarantee uniqueness only for tuples without NULL. We investigate the notions of possible and certain keys, which are keys that hold in some or all possible worlds that originate from an SQL table, respectively. Possible keys coincide with UNIQUE, thus providing a semantics for their syntactic definition in the SQL standard. Certain keys extend primary keys to include NULL columns and can uniquely identify entities whenever feasible, while primary keys may not. In addition to basic characterization, axiomatization, discovery, and extremal combinatorics problems, we investigate the existence and construction of Armstrong tables, and describe an indexing scheme for enforcing certain keys. Our experiments show that certain keys with NULLs occur in real-world data, and related computational problems can be solved efficiently. Certain keys are therefore semantically well founded and able to meet Codd's entity integrity rule while handling high volumes of incomplete data from different formats.

Performance limitations of a text search application running in cloud instances

This article analyzes the performance of MySQL in clouds based on commodity hardware in order to identify the bottlenecks in the execution of series of scripts developed on the SQL standard. The developed scripts were designed in order to perform text search in a considerable amount of records. Two types of platforms were employed: a physical machine that serves as host and an instance within a cloud infrastructure. The results show that the intensive use of a relational database presents a greater loss of performance in a cloud instance due limitations in the primary storage system that was employed in the cloud infrastructure.

Temporal data warehouse logical modelling

Temporal data warehouses (TDWs) have been developed for the management of time-varying data in dimensions. This paper presents a new approach for the logical modelling of TDWs. The novel design is based on the integration of two schemata, the star schema and the snowflake schema, to the temporal starnest schema. Time in the temporal starnest schema is not treated as another dimension but as time attributes in every temporal dimension, i.e., dimension tables dependent on time. Time manipulation functions for the treatment of time attributes are provided in the query language. The temporal starnest schema proposed in this paper expresses naturally hierarchy levels by the clustering of data in nested tables, with resulting description of aggregation levels for a dimension in a natural way. The proposed extension is applied to a TDW for accident monitoring in crossroads where the expressive power of the model is exemplified with several temporal queries expressed in a suitable extension of SQL standard.

Possible and certain SQL keys

Driven by the dominance of the relational model, the requirements of modern applications, and the veracity of data, we revisit the fundamental notion of a key in relational databases with NULLs. In SQL database systems primary key columns are NOT NULL by default. NULL columns may occur in unique constraints which only guarantee uniqueness for tuples which do not feature null markers in any of the columns involved, and therefore serve a different function than primary keys. We investigate the notions of possible and certain keys, which are keys that hold in some or all possible worlds that can originate from an SQL table, respectively. Possible keys coincide with the unique constraint of SQL, and thus provide a semantics for their syntactic definition in the SQL standard. Certain keys extend primary keys to include NULL columns, and thus form a sufficient and necessary condition to identify tuples uniquely, while primary keys are only sufficient for that purpose. In addition to basic characterization, axiomatization, and simple discovery approaches for possible and certain keys, we investigate the existence and construction of Armstrong tables, and describe an indexing scheme for enforcing certain keys. Our experiments show that certain keys with NULLs do occur in real-world databases, and that related computational problems can be solved efficiently. Certain keys are therefore semantically well-founded and able to maintain data quality in the form of Codd's entity integrity rule while handling the requirements of modern applications, that is, higher volumes of incomplete data from different formats.

Shortest Path Computing in Relational DBMSs

This paper takes the shortest path discovery to study efficient relational approaches to graph search queries. We first abstract three enhanced relational operators, based on which we introduce an FEM framework to bridge the gap between relational operations and graph operations. We show new features introduced by recent SQL standards, such as window function and merge statement, can improve the performance of the FEM framework. Second, we propose an edge weight aware graph partitioning schema and design a bi-directional restrictive BFS (breadth-first-search)over partitioned tables, which improves the scalability and performance without extra indexing overheads. The final extensive experimental results illustrate our relational approach with optimization strategies can achieve high scalability and performance.

Achieving Data Privacy through Secrecy Views and Null-Based Virtual Updates

We may want to keep sensitive information in a relational database hidden from a user or group thereof. We characterize sensitive data as the extensions of secrecy views. The database, before returning the answers to a query posed by a restricted user, is updated to make the secrecy views empty or a single tuple with null values. Then, a query about any of those views returns no meaningful information. Since the database is not supposed to be physically changed for this purpose, the updates are only virtual, and also minimal. Minimality makes sure that query answers, while being privacy preserving, are also maximally informative. The virtual updates are based on null values as used in the SQL standard. We provide the semantics of secrecy views, virtual updates, and secret answers (SAs) to queries. The different instances resulting from the virtually updates are specified as the models of a logic program with stable model semantics, which becomes the basis for computation of the SAs.

The SQL StandarD: How it Happened

Donald R. Deutsch joined the US government agency now called the National Institute of Standards and Technology (NIST) in the last 1970s and the X3H2 Database Standards Committee (DSC) in Washington DC in April 1978. Initially chartered only to develop a standard Data Definition Language (DDL) for network DBMSs, in time the DSC became the epicenter of US and international SQL standardization. Deutsch became the DSC vice chair at the second meeting and chair at the committee's 11th meeting in May 1980. Throughout his career that included (in addition to NIST) GE, Sybase, and now Oracle, he has continued to chair the DSC. From this perspective, this article describes how the SQL standard happened.

The place of the stored procedures in the cloud databases

The software as a service model ensures not only cloud applications, but also cloud databases. In this paper we analyze the impact on stored procedures of the switch of the existing application to cloud applications and cloud databases. We demonstrate that the stored procedures and cloud applications have common advantages and we emphasize also other advantages of the stored procedures. As a contribution we propose a list of improvements to stored procedures that can be considered in the new versions of the SQL standard and implemented by the database management systems providers. For the moment the SQL standard does not include any feature related to the cloud databases, so we expect that once again the software industry will be one step forward to the SQL standard.

Temporal features in SQL:2011

SQL:2011 was published in December of 2011, replacing SQL:2008 as the most recent revision of the SQL standard. This paper covers the most important new functionality that is part of SQL:2011: the ability to create and manipulate temporal tables.

Relational approach for shortest path discovery over large graphs

With the rapid growth of large graphs, we cannot assume that graphs can still be fully loaded into memory, thus the disk-based graph operation is inevitable. In this paper, we take the shortest path discovery as an example to investigate the technique issues when leveraging existing infrastructure of relational database (RDB) in the graph data management. Based on the observation that a variety of graph search queries can be implemented by iterative operations including selecting frontier nodes from visited nodes, making expansion from the selected frontier nodes, and merging the expanded nodes into the visited ones, we introduce a relational FEM framework with three corresponding operators to implement graph search tasks in the RDB context. We show new features such as window function and merge statement introduced by recent SQL standards can not only simplify the expression but also improve the performance of the FEM framework. In addition, we propose two optimization strategies specific to shortest path discovery inside the FEM framework. First, we take a bi-directional set Dijkstra's algorithm in the path finding. The bi-directional strategy can reduce the search space, and set Dijkstra's algorithm finds the shortest path in a set-at-a-time fashion. Second, we introduce an index named SegTable to preserve the local shortest segments, and exploit SegTable to further improve the performance. The final extensive experimental results illustrate our relational approach with the optimization strategies achieves high scalability and performance.

From regular expressions to nested words

There is growing interest in query language extensions for pattern matching over event streams and stored database sequences, due to the many important applications that such extensions make possible. The push for such extensions has led DBMS vendors and DSMS venture companies to propose Kleene-closure extensions of SQL standards, building on seminal research that demonstrated the effectiveness and amenability to efficient implementation of such constructs. These extensions, however powerful, suffer from limitations that severely impair their effectiveness in many real-world applications. To overcome these problems, we have designed the K*SQL language and system, based on our investigation of the nested words , which are recent models that generalize both words and trees. K*SQL extends the existing relational sequence languages, and also enables applications from other domains such as genomics, software analysis, and XML processing. At the same time, K*SQL remains extremely efficient, using our powerful optimizations for pattern search over nested words. Furthermore, we show that other sequence languages and XPath can be automatically translated into K*SQL, allowing for K*SQL to be also used as a high-performance query execution back-end for those languages. Therefore, K*SQL is a unifying SQL-based engine for sequence and XML queries, which provides novel optimization techniques for both.

Fundamentals of Object Databases: Object-Oriented and Object-Relational Design

Object-oriented databases were originally developed as an alternative to relational database technology for the representation, storage, and access of non-traditional data forms that were increasingly found in advanced applications of database technology. After much debate regarding object-oriented versus relational database technology, object-oriented extensions were eventually incorporated into relational technology to create object-relational databases. Both object-oriented databases and object-relational databases, collectively known as object databases, provide inherent support for object features, such as object identity, classes, inheritance hierarchies, and associations between classes using object references. This monograph presents the fundamentals of object databases, with a specific focus on conceptual modeling of object database designs. After an introduction to the fundamental concepts of object-oriented data, the monograph provides a review of object-oriented conceptual modeling techniques using side-by-side Enhanced Entity Relationship diagrams and Unified Modeling Language conceptual class diagrams that feature class hierarchies with specialization constraints and object associations. These object-oriented conceptual models provide the basis for introducing case studies that illustrate the use of object features within the design of object-oriented and object-relational databases. For the object-oriented database perspective, the Object Data Management Group data definition language provides a portable, language-independent specification of an object schema, together with an SQL-like object query language. LINQ (Language INtegrated Query) is presented as a case study of an object query language together with its use in the db4o open-source object-oriented database. For the object-relational perspective, the object-relational features of the SQL standard are presented together with an accompanying case study of the object-relational features of Oracle. For completeness of coverage, an appendix provides a mapping of object-oriented conceptual designs to the relational model and its associated constraints.

Confidence bounds for sampling-based group by estimates

Sampling is now a very important data management tool, to such an extent that an interface for database sampling is included in the latest SQL standard. In this article we reconsider in depth what at first may seem like a very simple problem—computing the error of a sampling-based guess for the answer to a GROUP BY query over a multitable join. The difficulty when sampling for the answer to such a query is that the same sample will be used to guess the result of the query for each group, which induces correlations among the estimates. Thus, from a statistical point-of-view it is very problematic and even dangerous to use traditional methods such as confidence intervals for communicating estimate accuracy to the user. We explore ways to address this problem, and pay particular attention to the computational aspects of computing “safe” confidence intervals.

Advancements in SQL/XML

Since we last wrote about SQL/XML in [2], the first edition of that new part of the SQL standard has been officially published as an international standard [1], commonly called SQL/XML:2003. At the time of that earlier column, SQL/XML was just entering its first official ballot, meaning that (possibly significant) changes to the text were expected in response to ballot comments submitted by the various participants in the SQL standardization process.

SQL:2003 has been published

SQL:2003 has finally achieved final publication as an International Standard, replacing SQL:1999. SQL:2003 is popularly believed to be largely a “bugfix release” of the SQL standard — except, of course, for the SQL/XML work on which we have previously reported. However, as you will learn from this and future columns, there are many compelling new features in the 2003 edition of the SQL standard. We are pleased that three of the more active SQL proposal writers have joined forces to present several of those new features in this month’s column. Krishna Kulkarni is, among other responsibilities, the formal International Representative for the INCITS H2 Technical Committee for Database. Jan-Eike Michels is a frequent USA representative to the corresponding international group, ISO/IEC JTC1/SC32/WG3. Fred Zemke is widely acknowledged as a principle expert in many areas of the SQL standard and also a regular USA representative to WG3. In the coming months, we will provide information about even more of SQL:2003’s new features.

SQL multimedia and application packages (SQL/MM)

Regular readers of this column will have become familiar with database language SQL -- indeed, most readers are already familiar with it. We have also discussed the fact that the SQL standard is being published in multiple parts and have even discussed one of those parts in some detail[l].Another standard, based on SQL and its structured user-defined types[2], has been developed and published by the International Organization for Standardization (ISO). This standard, like SQL, is divided into multiple parts (more independent than the parts of SQL, in fact). Some parts of this other standard, known as SQL/MM, have already been published and are currently in revision, while others are still in preparation for initial publication.In this issue, we introduce SQL/MM and review each of its parts, necessarily at a high level.

SQL/SDA: a query language for supporting spatial data analysis and its Web-based implementation

An important trend of current GIS development is to provide easy and effective access to spatial analysis functionalities for supporting decision making based on geo-referenced data. Within the framework of the ongoing SQL standards for spatial extensions, a spatial query language, called SQV/SDA, has been designed to meet such a requirement. Since the language needs to incorporate the important derivation functions (e.g., map-overlay and feature-fusion) as well as the spatial relationship and metric functions, the functionality of the FROM clause in SQL is developed in addition to the SELECT and WHERE clauses. By restructuring the FROM clause via a subquery, SQL/SDA is well-adapted to the general spatial analysis procedures using current GIS packages. Such an extended SQL, therefore, stretches the capabilities of previous ones. The implementation of SQL/SDA on the Internet adopts a hybrid model, which takes advantage of the Web GIS design methods in both the client side and server side. The client side of SQL/SDA, programmed in the Java language, provides a query interface by introducing visual constructs such as icons, listboxes, and comboboxes to assist in the composition of queries, thereby enhancing the usability of the language. The server side of SQL/SDA, which is composed of a query processor and Spatial Database Engine (SDE), carries out query processing on spatial databases after receiving user requests.