7.5. How are Relations Different from Other Data Representations?

Relations are just one way to represent data stored in a table. In practice, data scientists encounter many other types of data tables, like spreadsheets, matrices, and dataframes. In this section, we’ll compare and contrast the relations with other representations to explain why relations have become so widely used for data analysis. We’ll also point out scenarios where other representations might be more appropriate.

7.5.1. Relations and Spreadsheets

Spreadsheets are computer applications where users can enter data in a grid and use formulas to perform calcuations. One famous example today is Microsoft Excel, although spreadsheets date back to at least 1979 with VisiCalc [Grad, 2007]. Spreadsheets make it easy to see and directly manipulate data. These properties have make spreadsheets highly popular—by a 2005 estimate, there are over 55 million spreadsheet users compared to 3 million professional programmers in industry [Scaffidi et al., 2005].

Relations have several key advantages over spreadsheets. Writing SQL code in a computational notebook like Jupyter naturally produces a data lineage. Someone who opens the notebook can see the input files for the notebook and how the data were changed. Spreadsheets do not make a data lineage visible; if a person manually edits data values in a cell, it is difficult for future users to see which values were manually edited or how they were edited. Relations can also handle larger datasets than spreadsheets; users can use SQL systems to work with huge datasets that would be very hard to load into a spreadsheet.

7.5.2. Relations and Matrices

A matrix is a two-dimensional array of data used primarily for linear algebra operations. In the example below, \( \mathbf{X} \) is a matrix with three rows and two columns.

\[\begin{split} \begin{aligned} \mathbf{X} = \begin{bmatrix} 1 & 0 \\ 0 & 4 \\ 0 & 0 \\ \end{bmatrix} \end{aligned} \end{split}\]

Matrices are mathematical objects defined by the operators that they allow. For instance, matrices can be added or multiplied together. Matrices also have a transpose. These operators have very useful properties which data scientists rely on for statistical modeling.

One important difference between a matrix and a relation: when treated as a mathematical object, matrices can only contain numbers. Relations can contain numbers too, but dataframes can also have other types of data like text. This makes relations more useful for loading and processing real-world data which may contain all kinds of data types.

Note

Data scientists refer to matrices not only as mathematical objects, but also as program objects as well. For instance, the R programming language has a matrix object, while in Python we could represent a matrix using a two-dimensional numpy array. Matrices as implemented in Python and R can contain other data types besides numbers, but lose mathematical properties when doing so. This is yet another example of how domains can refer to different things with the same term.

7.5.3. Relations and Dataframes

Dataframes are one of the most common ways to represent data tables in general purpose programming languages like Python and R. (We cover dataframes in the Working With Dataframes Using pandas chapter of this book.) Dataframes share many similarities with relations; both use rows to represent records and columns to represent features. Both have column names, and data within a column have the same type.

One key advantage of dataframes is that they don’t require rows to represent records and columns to represent features. Many times, raw data don’t come in a convenient format that can directly be put into a relation. In these scenarios, data scientists use the dataframe to load and process data since dataframes are more flexible in this regard. Often, data scientists will load raw data into a dataframe, then process the data into a format that can easily stored into a relation.

One key advantage that relations have over dataframes is that relations are used by relational database systems like PostgreSQL 1 that have highly useful features for data storage and management. Consider a data scientist at a company that runs a large social media website. The database might hold data that is far too large to read into a pandas dataframe all at once; instead, data scientists use SQL queries to subset and aggregate data since database systems are more capable of handling large datasets. Also, website users constantly make updates to their data by making posts, uploading pictures, and editing their profile. Here, database systems let data scientists reuse their existing SQL queries to update their analyses with the latest data rather than having to repeatedly download large CSV files.

For a more rigourous description of the difference between dataframes and relations, see [Petersohn et al., 2020].


1

https://www.postgresql.org/about/