Database Theory in Action: Database Visualization

Bertin first described the data-to-visual (or pixel) mapping, where tuples map to marks (e.g., points), and data attributes map to the mark’s visual channels [2]. For instance, Figure 1(a) visualizes a table $T(id,a,b)$ as a scatter plot $V_T$ where $T.a$ and $T.b$ are mapped to the x and y axes (ignore the red edges). Wilkinson’s Grammar of Graphics [8] extended this mapping with scales, mark types, facets, and more to develop a “graphical grammar” that defines a design space of data visualizations, which forms the foundation of most data visualization systems including nViZn [7], ggplot2 [6], Tableau [5], and vega-lite [4].

However, this theory is predicated on a single-table data model, which does not model most data such as hierarchical JSON, RDF graphs, and relational databases. Thus, the user is expected to ”prepare” their data (extract, transform, filter, join) into a single table before visualization. This is a major burden, cannot guarantee that the visualization faithfully reflects the data, and makes it impossible for existing formalisms to express seemingly common designs such as node-link diagrams, parallel coordinates, tree diagrams, nested visualizations, nor tree maps.

For instance, Figure 1 connects the points in $V_T$ based on the edges table $E(id,s,t)$. In existing approaches, the user first computes $E^{\prime }=T⨝E⨝T$, then maps $E^{\prime }$ as links in $V_{E^{\prime }}$. However, the edges only appear to connect the points. If the user jitters the points or applies a force-directed layout algorithm, then the positions of the points and edges become inconsistent because the visualization is unable to maintain their logical relationship (Figure 1(b)). Furthermore, single-table formalisms and libraries cannot support the force-directed layout algorithm because it needs to reason about two tables. As a consequence, the user needs to do more “preparation”: run the layout to compute pixel coordinates of the nodes in a new table $T^{\prime }$, derive a new edges table $E^{\prime \prime }=T^{\prime }⨝E⨝T^{\prime }$, and draw the points and links. This breaks the data-to-pixel mapping abstraction because $T^{\prime }$ and $E^{\prime \prime }$ are tables of pixel coordinates, and the grammar is merely used for rendering because all of the layout logic was applied during preparation.

We denote a table by its schema $T(a_1,\mathrm{\dots },a_n)$, where attribute $T.a_i$ has domain $T.{𝔻}_i$, and use capitalization to denote a set of attributes $T.X$. We underline a key $\underset{¯}{T.X}$, and assume every table has primary key $\underset{¯}{T.id}$. A foreign key relationship $C(S.X,T.Y)$ specifies that a subset of attributes $S.X\subseteq S_S$ refer to attributes $T.Y\subseteq S_T$ of another table¹¹1Codd’s definition [3] is more strict, and states that $S.X$ is not a key and $T.Y$ is a primary key. We relax this definition in order to model relationships as used in practice. : $\forall s\in S\exists t\in T,s.X=t.Y$. 1-1 relationships are when both $S.X$ and $T.Y$ are keys, denoted $C(\underset{¯}{S.X},\underset{¯}{T.Y})$; N-1 relationships denoted $C(S.X,\underset{¯}{T.Y})$. N-M relationships are modeled using an intermediate relation $W(X,Y)$ with constraints $C(W.X,\underset{¯}{S.X})$ and $C(W.Y,\underset{¯}{T.Y})$.

We now introduce a simple formalism to describe graphical grammars that visually map an input table $T$ to an output view $V$ (a visual mapping). The notation focuses on data attributes, marks, mappings between data and mark properties, and scales. A visualization may have multiple views, often laid out next to each other, or super-imposed as layers.

In database parlance, the view $V$ is a projection of data attributes to mark properties (often called a visual channel) that is indexed by the table’s keys. The notation $a\stackrel{𝑠}{\to }v$ specifies an aesthetic mapping from data attribute $a$ to mark property $v$ using a scale function $s$. Named scale functions can be referenced in multiple views; by default each mapping uses a different scale function. For instance, the following creates a scatterplot that uses linear scales to map values of $a$ to pixel positions from $10$ to $100$ along the x axis (similarly for $b\stackrel{s_y}{\to }y$, and the primary key $id$ using an identity function ($s_{id}$).

$V$ is a table of marks with schema $V(type,\underset{¯}{id},x,y,\mathrm{\dots })$ that contains its mark type, keys, and mark properties. $V$ has a 1-1 relationship with $T$ via the constraint $C(\underset{¯}{V.id},\underset{¯}{T.id})$, so it preserves the same relationships that $T$ has with other tables. We will use a simplified notation that omits the primary key mapping, table prefixes, and scale functions, unless they are important to an example. For instance, the following is equivalent to the above example: $V=\{T\to point,a\to x,b\to y\}$.

We first examine the existing single-table formalism from the perspective of preserving attribute domain and key constraints. Attribute domains are preserved by drawing axes or legends to illustrate how the data attribute’s domain maps to the range of the visual channel. Without these visual metadata, marks are simply objects floating in space, and the user cannot interpret the visually encoded data.

To understand key constraints, let us revisit the scatterplot in the previous section: $V=\{T\to point,id\to id,year\to x,price\to y\}$. Te preserve the key constraint $\underset{¯}{T.id}$, we must be able to distinguish different rows in the visualization. Is the constraint trivially satisfied because $\underset{¯}{V.id}=\underset{¯}{T.id}$? Unfortunately no, because the visualization is not a set or bag of rows in an N-dimensional space – its marks are spatially positioned in a 2D space. Thus, different marks may appear indistinguishable and thus violate the constraint. This is commonly called “overplotting”.

What are ways to address this constraint violation? One common approach is to change the constraints by e.g., grouping by the spatial attributes ($T.(year,price)$) to render a heatmap where the spatial attributes are the key, or reducing opacity achieves a similar goal but groups at the pixel granularity. A second approach is to change the specification by e.g., mapping $id\to x$ and $year\to color$ to remove overlaps. The third is to perturb the layout by e.g., jittering the points to reduce overlaps. This variety of interventions is possible because the violations are visual and low dimensional. This contrasts with data cleaning, which resolves violations by changing data values.

We define a faithful database visualization as a mapping where 1) each table maps to one or more views; in a given view, 2) each row maps to one mark and 3) each attribute maps to a mark property; and 4) each constraint is preserved in the views. Single-table mappings are sufficient to express (1-3), so we now shift focus to (4) using the series of examples in Figure 2. Starting with the database $D_0$ in Figure 2(a), we different decompositions to illustrate how the data model affects the structure of the visualization, and several ways to visually preserve foreign keys.