• 20 hours
  • Medium

Free online content available in this course.

course.header.alt.is_video

course.header.alt.is_certifying

Got it!

Last updated on 4/19/21

Filter a relation using projection and restriction

Log in or subscribe for free to enjoy all this course has to offer!

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.

Example of certificate of achievement
Example of certificate of achievement