A better SQL in 11 lines of code

A Better SQL in 11 Lines of Code

Prela is a new query language being developed at UCLA RePL. The language is quite different from SQL, but its key ideas are very simple. In this short tutorial, we will build a toy version of Prela in Python to understand its core principles. By the end of this tutorial, you will know how the following query works:

You can probably already guess what it's doing: the query finds every movie produced by an American company and has a character name in its title, and outputs the title along with the alias for each cast member. Note that the equivalent query in SQL spans over 20 lines.

The first special thing about Prela is that there are only binary relations, i.e., tables with two columns. That may sound very limiting at first, but it's easy to "binarize" a wide table with multiple columns. Suppose we have a table of movies:

ID title year 646 The Godfather 1972 478 Seven Samurai 1954 583 Casablanca 1942

We can decompose the 3-column table into 3 binary relations, each mapping the row number to the column value:

= Rel([( 646 , 0 ), movieRel([(), ( 478 , 1 ), ), ( 583 , 2 )]) )]) = Rel([( 0 , "The Godfather" ), titleRel([(), ( 1 , "Seven Samurai" ), ), ( 2 , "Casablanca" )]) )]) = Rel([( 0 , 1972 ), yearRel([(), ( 1 , 1954 ), ), ( 2 , 1942 )]) )])

Tip This tutorial uses snip to connect code cells into a notebook-like environment, changes made in one cell are reflected in later cells.

The movie , title , and year relations above represent the ID , title , and year columns of the original table, respectively. Note how the row number comes first in title and year , but second in movie (which is also not called ID ). The reason for this will become clear later.

The motivation for focusing on binary relations is that they generalize functions. Functions are powerful because they compose, making them the building blocks of programs. A function maps every input to a unique output, where as a binary relation can map an input to multiple different outputs. In a sense, a binary relation can be viewed as a nondeterministic function, and we can compose them just like how we compose functions.

... continue reading