In the preceding chapters, we saw that the relational model is used to represent data. We introduced the concepts of relation and foreign key.
Now we move on to the manipulation part! In the relational model, everything having to do with manipulating relations is called relational algebra. Perhaps the word algebra gives you pause, bringing back memories of old math classes. Well, let me assure you: relational algebra is a lot more fun!
Without further delay, let’s tackle the first two operations of relational algebra: projection and restriction. These are unary operations, which means that they are defined on a single relation. Let’s take another look at our apple relation:
identifier | weight | diameter | color |
1 | 151 g | 8.3 cm | red |
25 | 182 g | 7.5 cm | red |
16 | 140 g | 5.9 cm | red |
Filter a table using the projection operation
In our apple example, projection of the apple relation on its attributes diameter and color yields a new relation containing only two attributes—diameter and color—and contains the same tuples as the apple relation.
Here is the result of this projection:
diameter | color |
8.3 cm | red |
7.5 cm | red |
5.9 cm | red |
Filter a table using the restriction operation
With projection, we chose a subset of all available columns. Restriction does the same thing, only for rows.
That’s actually the job of a database: to store dynamic data that is inserted, deleted, and modified as the application is used!
Since the rows we want to select (or exclude) are dynamic, we need to establish a condition that will allow us to restrict the rows.
Here’s an example: restriction of the apple relation according to condition C: diameter > 6 cmresults in a relation containing all of the tuples that meet condition C.
The result of this restriction is a relation with two rows:
identifier | weight | diameter | color |
1 | 151 g | 8.3 cm | red |
25 | 182 g | 7.5 cm | red |
A restriction can also involve more than one column, for example diameter > 2 * weight. Granted, this example doesn’t make sense, but for now just know that it is possible.
Notations
A number of notations can be used to describe the operations we are learning here. Let’s agree on one per operation.
For the two operations just described, we can use the following notations:
apple_proj = Projection (apple, weight, color)
apple_restr = Restriction(apple, weight > 141 g)
And there you have it! So far, it’s pretty simple, wouldn’t you agree? Stay tuned; there’s more in the next chapter!
Summary
The projection operation extracts columns from a table and excludes others.
The restriction operation filters for the rows of a table that meet a specific condition.
