Bryce Ostler, Utah Valley University
Engineering
Much of SQL’s power derives from SQL’s declarative rather than procedural nature: a programmer describes the result desired rather than how to produce the result. Systems using SQL must translate SQL’s declarative language into a procedural language in order to execute queries. Relational Algebra (RA) is a procedural language that SQL can be transformed into and executed on a computer using a RA engine. Optimizations are applied to RA code to improve the performance of a translated query. The author of this abstract will present a simple RA engine written in Python and how it has been used as part of a Database Theory course.
An RA engine can be composed using only six operations: 1) Select, 2) Project, 3) Natural Join, 4) Union, 5) Intersection, and 6) Difference. These six simple operations can form a single expression to solve such SQL queries as:
“SELECT * FROM parts”
or the even more complex query:
“SELECT pcity FROM parts WHERE pcity NOT IN (SELECT scity FROM suppliers INTERSECT SELECT jcity FROM projects INTERSECT SELECT pcity FROM parts) UNION SELECT scity FROM suppliers WHERE scity NOT IN (SELECT scity FROM suppliers INTERSECT SELECT jcity FROM projects INTERSECT SELECT pcity FROM parts) UNION SELECT jcity FROM projects WHERE jcity NOT IN (SELECT scity FROM suppliers INTERSECT SELECT jcity FROM projects INTERSECT SELECT pcity FROM parts) “
Using RA and SQL together, rather than using SQL alone, in a Database course can provide students with a richer environment for learning how to query a Database by providing a deeper insight into how a Database Management System goes about executing a query. Additionally, understanding both RA and SQL provides insights into solving difficult problems as some problems are more easily solved using RA than SQL while others are more easily solved with SQL.