Converting SQL queries into relational algebra

Abstract We present an algorithm for converting a semantically meaningful SQL query into an equivalent algebraic expression. The relational algebra we employ consists of the following operators: union, intersection, difference, Cartesian product, selection, and projection. The SQL queries we consider can have an arbitrary level of nesting but are restricted in three ways. We assume that they do not contain an ORDER BY or a GROUP BY clause, all SELECTs are in fact SELECT DISTINCTs, and that no aggregate functions are used. The first two assumptions are made in order to remain faithful to the definition of a relation as a set of rows. The last assumption is needed since there is no standard for incorporating aggregate functions into a relational algebra. The research results in this paper will be useful in implementing an SQL user interface for database management systems that internally employ relational algebra. They can also be used in optimizing the evaluation of SQL queries.

[1]  R. Ageloff.  A primer on SQL , 1988 .

[2]  M. W. Blasgen,et al.  Storage and Access in Relational Data Bases , 1977, IBM Syst. J..

[3]  John Miles Smith,et al.  Optimizing the performance of a relational algebra database interface , 1975, CACM.

[4]  Patrick A. V. Hall,et al.  Optimization of a Single Relation Expression in a Relational Data Base System , 1976, IBM J. Res. Dev..

[5]  S. B. Yao,et al.  Optimization of query evaluation algorithms , 1979, ACM Trans. Database Syst..

[6]  Georg Gottlob,et al.  Translating SQL Into Relational Algebra: Optimization, Semantics, and Equivalence of SQL Queries , 1985, IEEE Transactions on Software Engineering.

[7]  W. S. Luk,et al.  ELFS: English language from SQL , 1986, TODS.

[8]  Alfred V. Aho,et al.  Efficient optimization of a class of relational expressions , 1979, TODS.

[9]  Mohammad Dadashzadeh.  On estimation of the cost of relational query evaluation plans (optimization, dbms) , 1985 .

[10]  C. J. Date.  A Guide to the SQL Standard , 1987 .

[11]  J. D. Uiiman,et al.  Principles of Database Systems , 2004, PODS 2004.

[12]  Eugene Wong,et al.  Decomposition—a strategy for query processing , 1976, TODS.

[13]  Irving L. Traiger,et al.  A history and evaluation of System R , 1981, CACM.

[14]  Won Kim,et al.  On optimizing an SQL-like nested query , 1982, TODS.

[15]  Donald D. Chamberlin,et al.  SEQUEL 2: A Unified Approach to Data Definition, Manipulation, and Control , 1976, IBM J. Res. Dev..

[16]  C. J. Date.  A guide to DB2 , 1984 .

[17]  C. J. Date.  An Introduction to Database Systems, Volume II , 1980 .

[18]  Gultekin Özsoyoglu,et al.  Extending relational algebra and relational calculus with set-valued attributes and aggregate functions , 1987, TODS.

[19]  Anthony C. Klug.  Equivalence of Relational Algebra and Relational Calculus Query Languages Having Aggregate Functions , 1982, JACM.

[20]  Alfred V. Aho,et al.  Universality of data retrieval languages , 1979, POPL.

[21]  Antonio L. Furtado,et al.  An algebra of quotient relations , 1977, SIGMOD '77.

[22]  Donald D. Chamberlin,et al.  Implementation of a structured English query language , 1975, SIGMOD '75.

[23]  Donald D. Chamberlin,et al.  Access Path Selection in a Relational Database Management System , 1989 .

[24]  Matthias Jarke,et al.  Query Optimization in Database Systems , 1984, CSUR.