A Formalization of SQL with Nulls

SQL is the world's most popular declarative language, forming the basis of the multi-billion-dollar database industry. Although SQL has been standardized, the full standard is based on ambiguous natural language rather than formal specification. Commercial SQL implementations interpret the standard in different ways, so that, given the same input data, the same query can yield different results depending on the SQL system it is run on. Even for a particular system, mechanically checked formalization of all widely-used features of SQL remains an open problem. The lack of a well-understood formal semantics makes it very difficult to validate the soundness of database implementations. Although formal semantics for fragments of SQL were designed in the past, they usually did not support set and bag operations, nested subqueries, and, crucially, null values. Null values complicate SQL's semantics in profound ways analogous to null pointers or side-effects in other programming languages. Since certain SQL queries are equivalent in the absence of null values, but produce different results when applied to tables containing incomplete data, semantics which ignore null values are able to prove query equivalences that are unsound in realistic databases. A formal semantics of SQL supporting all the aforementioned features was only proposed recently. In this paper, we report about our mechanization of SQL semantics covering set/bag operations, nested subqueries, and nulls, written the Coq proof assistant, and describe the validation of key metatheoretic properties

Troubles with Nulls, Views from the Users

International audienceIncomplete data, in the form of null values, has been extensively studied since the inception of the relational model in the 1970s. Anecdotally, one hears that the way in which SQL, the standard language for relational databases, handles nulls creates a myriad of problems in everyday applications of database systems. To the best of our knowledge, however, the actual shortcomings of SQL in this respect, as perceived by database practitioners, have not been systematically documented, and it is not known if existing research results can readily be used to address the practical challenges. Our goal is to collect and analyze the shortcomings of nulls and their treatment by SQL, and to re-evaluate existing research in this light. To this end, we designed and conducted a survey on the everyday usage of null values among database users. From the analysis of the results we reached two main conclusions. First, null values are ubiquitous and relevant in real-life scenarios, but SQL's features designed to deal with them cause multiple problems. The severity of these problems varies depending on the SQL features used, and they cannot be reduced to a single issue. Second, foundational research on nulls is misdirected and has been addressing problems of limited practical relevance. We urge the community to view the results of this survey as a way to broaden the spectrum of their researches and further bridge the theory-practice gap on null values

Metamorphic testing framework for SQL queries with null values

Trabajo de Fin de Máster en Ingeniería Informática, Facultad de Informática UCM, Departamento de Sistemas Informáticos y Computación, Curso 2019/2020La falta de información dentro de las bases de datos relacionales expresada mediante valores nulos presenta un problema a la hora de garantizar la calidad de los datos y de las consultas SQL sobre esos datos. Esto ocurre debido a que existen múltiples interpretaciones de los valores nulos y en muchos casos las consultas no consideran que se puedan producir valores nulos como resultado de su evaluación, o bien no reflejan en el código la interpretación correcta de dichos valores. Los valores nulos han estado presentes en las bases de datos desde prácticamente las primeras implementaciones de sistemas de gestión de bases de datos relacionales, pero la implementación del estándar SQL plantea múltiples problemas. Al realizar consultas sobre una base de datos que maneja valores nulos, los resultados pueden no ser los esperados, ya sea por omisión de resultados (falsos negativos) o por resultados incorrectos (falsos positivos). Por esta razón, en este trabajo se propone una herramienta que analice diferentes consultas SQL y permita al desarrollador detectar posibles errores en aquellas consultas que tengan valores nulos utilizando un marco de testing metamórfico. Después de estudiar la bibliografía relacionada sobre pruebas de bases de datos, esta parece ser la primera propuesta que aplica relaciones metamórficas a consultas SQL con valores nulos.The lack of information within relational databases expressed by null values poses important problems when trying to ensure the quality of the data and of the SQL queries evaluated on that data. This occurs because there are multiple interpretations of null values and in many cases the queries either do not consider that null values can occur as a result of their evaluation, or they do not reflect the correct interpretation of these values in the code. Null values have been present in databases since the first implementations of relational database management systems, but the implementation of the SQL standard can generate multiple problems. When querying a database that handles null values, the results may not be produced as expected, either due to the omission of results (false negatives) or incorrect results (false positives). For this reason, this work proposes a tool that analyzes different SQL queries and allows the developer to detect possible errors in those queries that have null values using a metamorphic testing framework. After studying the related literature on database testing, this appears to be the first proposal of the application of metamorphic relationships to SQL queries on null values.Depto. de Sistemas Informáticos y ComputaciónFac. de InformáticaTRUEunpu

The teaching of Relational On-Line Analytical Processing (ROLAP) in Advanced Database courses.

Permission to make digital or hard copies of all or part of this work for personal or classroom use is granted without fee provided that copies are not made or distributed for profit or commercial advantage and that copies bear this notice and the full citation on the first page. To copy otherwise, to republish, to post on servers or to redistribute to lists, requires prior specific permission. © 2012 Higher Education AcademyDatabases are taught as a core subject at undergraduate and postgraduate levels in computer science degrees. The subject is often divided into conceptual database design and database technologies including physical database design. One of the problems with teaching the topic of ROLAP and Data Warehousing is the difficulty of providing practical exposure to the students. The approach taken in this work is intended to help students apply their knowledge learnt throughout the subject and gain some practical understanding leading to its application and implementation which furthermore provides the basis for analysis and evaluation at the level of critical analysis that would be expected on an advanced level course and at the highest level of learning taxonomies. In our work we have reverse engineered a large Oracle sample dataset and adapted it for use in the teaching of ROLAP. We have also produced our own smaller sample dataset for simplification and understanding of complex queries involving Slicing, Dicing , Pivoting, Rollup and Cube operations. In our examples ROLAP operations and their alternative SQL approaches are performed to provide multi-dimensional results

A dubiety-determining based model for database cumulated anomaly intrusion

The concept of Cumulated Anomaly (CA), which describes a new type of database anomalies, is addressed. A typical CA intrusion is that when a user who is authorized to modify data records under certain constraints deliberately hides his/her intentions to change data beyond constraints in different operations and different transactions. It happens when some appearing to be authorized and normal transactions lead to certain accumulated results out of given thresholds. The existing intrusion techniques are unable to deal with CAs. This paper proposes a detection model, Dubiety-Determining Model (DDM), for Cumulated Anomaly. This model is mainly based on statistical theories and fuzzy set theories. It measures the dubiety degree, which is presented by a real number between 0 and 1, for each database transaction, to show the likelihood of a transaction to be intrusive. The algorithms used in the DDM are introduced. A DDM-based software architecture has been designed and implemented for monitoring database transactions. The experimental results show that the DDM method is feasible and effective