Relational Algebra and SQL Query Visualisation

Objective

To construct a tool that displays graphically the inputs, intermediate results, and outputs of relational algebra (RA) and SQL queries in a manner that facilitates the learning of these languages.

Description

The RA has the important property that each operators takes as input one or more tables, and has as output a table that may be fed into any other RA operator. This simple rule makes it relatively simple to construct very complex queries from the basic set of five primitive operators. The fact that SQL is based on the RA means that it inherits this property.

The basic version of this project would allow a user of the tool developed to use the graphical use inferface to see input tables, then add RA operators, to connect the tables, and see output tables from the operators, and then use those output tables for input to new operators. The basic version would also allow a similar approach to be followed for basic SELECT FROM WHERE statements, and for the UNION and EXCEPT operators.

More complext versions of this project could follow one or more of the following themes

• Allow for the visualisation of OLAP operators, including GROUP BY, aggregates, PIVOT, ROLLUP and CUBE.
• Provide data lineage, in the sense the user can click on a data element in an output table, and see highlighted which data element(s) in input and intermediate tables where used to comput that data element.
• Extend the tool to implement the translation of queries into physical database operators (such as sort, hash join, index join, table scan, etc) and illustate the execute of those operators.

References

• 
  Relational Algebra Learning Tool
  MEng Final Year Project Report, Dept of Computing, Imperial College, 2008

  A Java based application can be downloaded from here.