Relational Algebra for Databases and Query Languages

unDraw.co

Introduction

This article will introduce you to the basics of relational algebra and help you become familiar with reading and writing algebraic expressions. I would like this to be a guide for you to continually reference when you are dealing with relational algebra and some of its basic operations. Hopefully I have written this article in such a way that will simplify relational algebra and allow you to confidently understand some of its concepts.

What is relational algebra?

Relational algebra is a procedural query language that takes relations and conditions as input, performs one or several operations (queries) on those relations, and outputs another relation after satisfying the expression.

  1. (Bob, bob@mail.edu, Math)
  2. (Charlie, charlie@mail.edu, Math)
  3. (David, david@mail.edu, English)
  • Projection
  • Union
  • Intersection
  • Set Difference
  • Rename
  • Natural Join
  • Left Outer Join

Selection

The selection operation allows you to retrieve rows from a relation. You could select every row from a relation, or select only rows that satisfy a certain condition.

  • p represents expressions and conditions we want to specify (i.e. =, <, >, ≠, ≥, ≤)
  • R represents the relation on which we are performing this operation

Example 1.1:

Let’s use the Students relation from above.

Figure 1.1

Example 1.2:

Our expression σ (Major = ‘Math’) (Students)

Figure 1.2

Example 1.3:

Let’s create an expression using a composite expression.

Figure 1.3

Projection

The projection operation allows you to return specific columns from a relation, instead of returning every single one.

  • [A1, A2, … , An] are the columns that you want returned
  • R is the relation you are getting a projection from

Example 2.1:

Our expression → π Name, Major (Students)

Figure 2.1

Union

The Union operation allows you to union two sets of relational sets.

  • is the union operator

Example 3.1

Let’s add a new column (Minor) to our Students relation…

Figure 3.1
Figure 3.1.1

Intersection

The Intersection operation returns only the shared instances between two sets.

  • is the Intersection operator

Example 4.1

Our expression → π Major (Students) ∩ π Minor (Students)

Figure 4.1

Set Difference

The Set Difference operation returns tuples in set A that are NOT in set B

  • \ is the Set Difference operator

Example 5.1

Our expression → π Major (Students) \ π Minor (Students)

Figure 5.1

Rename

The Rename operation allows you to rename a column in your relation.

  • newRelationName is the name you want to give to the relation that will be generated
  • newColumn is the name of the new column
  • oldColumn is the old column name that you want to rename
  • R is a relation

Example 6.1

Our expression → ρ User [FirstName Name, Major2 Minor] (Students)

Figure 6.1

Natural join

The Natural Join operation will compare two relations, match attributes with the same name, and return tuples whose values match each other. Only tuples that satisfy the expression will appear in the result. There will be no null values.

  • A, B are relations

Example 7.1

For this example, let’s change our schema a little bit…

Figure 7.1

Left Outer Join

The Left Outer Join operation returns all combinations of records between two relations that are equal by some related attribute, but it also preserves those tuples from the LEFT relation that don’t have any matches.

  • A, B are relations

Example 8.1

Let’s use the same schema as the example above…

Figure 8.1

Conclusion

There you have it, some guidelines and examples of the most common relational algebra operations! These rules make up the foundation for common database query languages such as SQL. Keep an eye out for my next article on relational calculus.

Software Engineer at IBM — more about me at https://cernera.me/

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store