Query formulation is increasingly performed by systems that need to guess a user’s intent (e.g. via spoken word interfaces). But how can a user know that the computational agent is returning answers to the “right” query? More generally, given that relational queries can become pretty complicated, how can we help users understand existing relational queries, whether human-generated or automatically generated? Now seems the right moment to revisit a topic that predates the birth of the relational model: developing visual metaphors of relational queries.

Patterns.png

This lecture-style tutorial is a greatly extended tutorial from VLDB'23, and surveys the key visual metaphors developed for visual representations of relational expressions, a human endeavour that largely predates the rise of the database community. We will survey the history and state-of-the art of relationally-complete diagrammatic representations of relational queries, discuss the key visual metaphors developed in well over a century of investigating diagrammatic languages, and organize the landscape by mapping their used visual alphabets to the syntax and semantics of Relational Algebra (RA) and Relational Calculus (RC).

Slides (PDF, 515 pages, 50 MB)

Tutorial first page.png

Feedback

If you notice anything incorrect in the slides, please let me know. Via email or this anonymous feedback form. Thanks a lot!

Errata

Errors found after June 3rd 2024 will be posted here.

Funding

This work has been supported in part by the National Science Foundation (NSF) under award numbers IIS-1762268 and IIS-1956096, and was conducted in part while Wolfgang Gatterbauer and his students were attending a semester-long program on Logic and Algorithms in Database Theory and AI at Berkeley's Simons Institute for the Theory of Computing. Any opinions, findings, and conclusions or recommendations expressed in this project are those of the author(s) and do not necessarily reflect the views of the Funding Agencies.

National Science Foundation Simons Institute for the Theory of Computing

Reference

A Comprehensive Tutorial on over 100 years of Diagrammatic Representations of Logical Statements and Relational Queries
ICDE 2024
A 3h tutorial that surveys the key visual metaphors developed for visual representations of relational expressions.

Related Publications

On the Reasonable Effectiveness of Relational Diagrams: Explaining Relational Query Patterns and the Pattern Expressiveness of Relational Languages
Wolfgang Gatterbauer, Cody Dunne
Best paper honorable mention (announcement)
Proposes a semantic definition of relational query patterns, which allows us to analyze the relative pattern expressiveness of relational query languages. Also proposes "relational diagrams", a natural diagrammatic representation of tuple relational calculus.
A Tutorial on Visual Representations of Relational Queries
Wolfgang Gatterbauer
An earlier shorter version of this tutorial that surveys the key visual metaphors developed for visual representations of relational expressions inside the database community, leaving out the early history and connections to diagrammatic reasoning.
Principles of Query Visualization
Wolfgang Gatterbauer, Cody Dunne, H.V. Jagadish, Mirek Riedewald
Discusses the principles of relational query visualization and its potential for simplifying user interactions with relational data
Stratisfimal Layout: A modular optimization model for laying out layered node-link network visualizations
Sara Di Bartolomeo, Mirek Riedewald, Wolfgang Gatterbauer, and Cody Dunne
Proposes a comprehensive approach to make complicated networks (such as those with layers and grouped nodes) easier to read. The idea is to formulate various readability criteria (notably crossing minimization and bendiness reduction) as a modular and customizable constraint optimization problem.
QueryVis: Logic-based Diagrams help Users Understand Complicated SQL Queries Faster
Aristotelis Leventidis, Jiahui Zhang, Cody Dunne, Wolfgang Gatterbauer, HV Jagadish, Mirek Riedewald
SIGMOD reproducibility award (announcement)
Shows that logical diagrams automatically created from SQL queries help users understand the queries faster and with fewer errors than SQL itself. Our ultimate goal is to allow users of SQL to reason about queries in terms of "diagrammatic SQL patterns" based on first-order logic.
QueryViz: Helping users understand SQL queries and their patterns
Jonathan Danaparamita, Wolfgang Gatterbauer
Proposes QueryViz, a light-weight add-on to existing databases. Copy your SQL query into the interactive interface at http://queryviz.com and look at the visualization of the query's relational pattern.
Databases will visualize queries too
Wolfgang Gatterbauer
Describes a new human-query interaction in which users re-use existing queries as templates to compose their own queries. This interaction is made possible with new automatic query visualization tools (such as QueryViz, or now QueryVis) which help users understand SQL patterns quickly.