We illustrate the idea of visualizing logical SQL patterns with the help of 9 queries over following 3 schemas of sailors reserving boats, students taking classes, and actors playing in movies:
The following table of 9 SQL queries are instances of 3 logical patterns over above 3 schemas:
- Find sailors who reserved NO red boat (students who took no art class | actors who played in no movie by Hitchcock).
- Find sailors who reserved ONLY red boats (students who took only art classes | actors who played only in movies by Hitchcock).
- Find sailors who reserved ALL red boats (students who took only all classes | actors who played in all movies by Hitchcock).
The following table represents the 9 queries as diagrams. Notice how logical similarities of queries across rows (thus across different schemas) become apparent despite using very different real world relations and attributes. In tuple relational calculus such queries are equivalent up to renaming of attributes and relation names. This similarity in logical pattern is not as readily visible in SQL.
Online Demonstration
Feel free to play with Online QueryVis, an online demo from EDBT 2011 which allows you to quickly visualize your queries (see the SQL grammar that QueryVis currently supports).