The Relational Model

As we've discussed, most popular databases use the relational model. We should understand what this model is before learning to write SQL.

Every database has a database model which dictates how it structures and organizes its data. The relational model is a database model where groups of data are stored in relations. "Relation" is a technical term for table. Most people prefer to say "table," but they are interchangeable. In the relational model, you can also do transformations on tables. The set of transformations you can do is called relational algebra.

Relations (Tables)

A relation is another way of saying "table". It has rows and columns, but they call rows "tuples". So, to say it in the jargon of the relational model, relations are collections of tuples. Tuples are sets of attributes, which are values with a name and a type. The attributes are the individual values for tuples that go in each column. For simplicity, we usually say "table" instead of "relation," and "row" instead of "tuple," but you can interchange these.

Let's look at an example of a table. Suppose you are an engineer working on a dating app called "Teender". Your database might have a table named daters, like this:

Each row (tuple) represents 1 thing in the collection. For the daters table, 1 row is 1 dater — 1 person looking for love.

Because there are 3 columns, each row has 3 attributes, which have a names and types seen in the header of the table. The name is how you reference that column (attribute), and the type tells you what kind of value it is and what you can do with it. All values in a column must have that same type.

VARCHAR

We can see that each dater has a name and eye_color which are VARCHAR types. VARCHAR stands for CHARACTER VARYING, or a string of varying number of characters. Basically, it's text.

INTEGER

Each dater also has an height_cm which is an INTEGER. This is a number without a fraction, a "whole number".

Relational Algebra

Tables don't do much on their own. What makes the relational model powerful is transformations on these tables. The set of transformations you can do on tables is called relational algebra.

Just like how elementary algebra (like in math class) is operations with numbers as inputs and outputs, relational algebra is operations with relations tables as inputs and outputs. It lets you slice, dice, and combine tables to produce meaningful results which are also tables.

What are some examples of operations we can do? Let's look at some examples on the dater table from above.

• Getting the name for each dater, outputting a table with 1 column.

• Getting users whose name starts with "F", outputting a table with 2 rows.

• Calculating the average height of all daters. It outputs just 1 value, and yet it's still a table. It just has 1 row and 1 column.

What Does Relational Algebra Look Like?

Relational algebra looks a bit different than the elementary algebra you did in school. When you read academic papers on relational algebra, you'll usually see them represented with math-like formulas and Greek letters.

However, you don't need to worry about understanding that, because all relational databases use SQL to express this instead of Greek letters. When writing SQL, we are writing words that look more like English. That formula looks like this in SQL:

SELECT name
FROM daters
WHERE eye_color = 'blue' AND height_cm > 170;

Even as a total beginner, you can probably figure out what this is doing. We'll get more into the specifics shortly. First, let's break down a query like that into its parts in the next section.