Publication: Comprobación de equivalencia de consultas SQL
Loading...
Official URL
Full text at PDC
Publication Date
2022
Advisors (or tutors)
Editors
Journal Title
Journal ISSN
Volume Title
Publisher
Citations
Exportar
Abstract
LearnSQL [1], actual juez de aprendizaje de SQL en desarrollo en nuestra facultad, ejecuta una consulta frente a una o varias bases de datos y comprueba que el resultado sea el mismo que la solución oficial proporcionada por el profesor. Esta manera de comprobar si una solución es correcta, pese a ser sencillo de implementar, presenta varios problemas, como la necesidad de realizar varias conexiones a la base de datos por cada envío recibido o, sobre todo, la limitación en los resultados, ya que dos consultas pueden devolver los mismos resultados para numerosas BD concretas pero no ser equivalentes para todas las posibles.
Este proyecto consiste en el desarrollo de una biblioteca Python que reciba dos consultas SQL y calcule su equivalencia, haciendo, en primer lugar, una transformación a su forma canónica y, en segundo lugar, comprobando que estas coincidan en su totalidad.
El trabajo consta de dos partes: la traducción de sentencias SQL a diccionarios Python, y la aplicación de las reglas del álgebra relacional [2] a estas. Todo ello tiene un fin último: la implementación de una biblioteca Python que traduzca las sentencias SQL a formato JSON [3], desarrolle las reglas mencionadas anteriormente sobre el diccionario, y verifique que la sentencia con los datos introducidos es equivalente o distinta a la sentencia a comparar. El alcance definido de nuestro trabajo será el del conjunto completo de las consultas SQL conjuntivas, ya que el problema de saber si dos consultas SQL son equivalentes es indecible, trabajando sobre el subconjunto de consultas SQL conjuntivas se pueden automatizar las comprobaciones.
LearnSQL, current SQL learning judge in development at our faculty, runs a query against one or more databases and checks that the result is the same as the official solution provided by the professor. This way of checking if a solution is correct, despite being simple to implement, has several problems, such as the need to make several connections to the database for each message received or, above all, the limitation in the results, since two queries can return the same results for numerous specific databases but not be equivalent for all possible ones. This project consists in the development of a Python library that receives two SQL queries and calculates their equivalence, firstly transforming them to their canonical form and secondly checking that they coincide in their entirety. The work consists of two parts: the translation of SQL statements to Python dictionaries and the application of relational algebra rules to them. All this has an ultimate goal: the implementation of a Python library that translates the SQL statements into JSON format, develops the previously mentioned rules about the dictionary and verifies that the statement with the entered data is equivalent or different from the statement to be compared. The defined scope of our work will be that of the complete set of conjunctive SQL queries, since the problem of knowing if two SQL queries are equivalent is inconclusive, working on the subset of conjunctive SQL queries, the checks can be automated.
LearnSQL, current SQL learning judge in development at our faculty, runs a query against one or more databases and checks that the result is the same as the official solution provided by the professor. This way of checking if a solution is correct, despite being simple to implement, has several problems, such as the need to make several connections to the database for each message received or, above all, the limitation in the results, since two queries can return the same results for numerous specific databases but not be equivalent for all possible ones. This project consists in the development of a Python library that receives two SQL queries and calculates their equivalence, firstly transforming them to their canonical form and secondly checking that they coincide in their entirety. The work consists of two parts: the translation of SQL statements to Python dictionaries and the application of relational algebra rules to them. All this has an ultimate goal: the implementation of a Python library that translates the SQL statements into JSON format, develops the previously mentioned rules about the dictionary and verifies that the statement with the entered data is equivalent or different from the statement to be compared. The defined scope of our work will be that of the complete set of conjunctive SQL queries, since the problem of knowing if two SQL queries are equivalent is inconclusive, working on the subset of conjunctive SQL queries, the checks can be automated.
Description
Trabajo de Fin de Grado en Ingeniería Informática e Ingeniería del Software, en la Facultad de Informática UCM, Departamento de Sistemas Informáticos y Computación, Curso 2021/2022.