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.

In other words, your input will be a relation, and your output will be a relation.

A relation is a set of tuples. For example, if you think about the following relation/table, Students …

This relation contains 4 tuples:

1. (Alice, alice@mail.edu, Computer Science)
2. (Bob, bob@mail.edu, Math)
3. (Charlie, charlie@mail.edu, Math)
4. (David, david@mail.edu, English)

The relational algebra query language provides us with several operations that we can use to specify which tuples we want returned and which tuples we want excluded in the final relation.

An operation is either considered a unary or binary operation. Unary implies that the operation only accepts 1 operand (relation) as input, while a binary operation accepts 2 (or more) operands as input.

We will now take a look at some of the most common operations for relational algebra. I will provide you with the operation’s syntax, along with an example and some visuals to help you see why our expressions return a certain result.

This article will cover the following operations:

• Selection
• 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.

Syntax: σₚ(R)

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

How to say in English terms: “Select from relation, R, all of the tuples that satisfy condition p.

Example 1.1:

Let’s use the Students relation from above.

Our expression: σ (Students)

How to say in English terms: “Select all tuples from Students”

Our result…

Figure 1.1

Notice that we did not specify any condition p. That’s okay. We can leave p blank if we want, but let’s look at another example when p is specified.

Example 1.2:

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

How to say in English terms: “Select all tuples from Students whose major is ‘Math’ ”

Let’s look at our result…

Figure 1.2

Because we specified that we want only tuples who have Major equal to “Math,” our result only includes Bob and Charlie, since they are the only ones majoring in Math.

Example 1.3:

Let’s create an expression using a composite expression.

Our expression → σ (Major = ‘Math’ OR Major = ‘English’) (Students)

How to say in English terms: “Select all tuples from Students whose major is either ‘Math’ or ‘English’”

Let’s look at our result…

Figure 1.3

Projection

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

Syntax: π [A1, A2, …, An] (R)

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

How to say in English terms: “Only return columns [A1, A2, … , An] from relation R.”

Example 2.1:

Our expression → π Name, Major (Students)

How to say in English terms: “Return columns Name and Major from Students”

Here is our result…

Figure 2.1

Union

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

Syntax: A ∪ B

• A, B are sets
• ∪ is the union operator

How to say in English terms: “Union set A with set B” or “A union B”

Example 3.1

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

Figure 3.1

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

How to say in English terms: “Union the set of Majors with the set of Minors”

Here is our result…

Figure 3.1.1

Intersection

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

Syntax: A ∩ B

• A, B are sets
• ∩ is the Intersection operator

How to say in English terms: “Intersect set A with set B” or “A intersect B”

Example 4.1

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

How to say in English terms: “Intersect the set of Majors with the set of Minors”

Here is our result…

Figure 4.1

Set Difference

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

Syntax → A \ B

• A, B are sets
• \ is the Set Difference operator

Example 5.1

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

How to say in English terms: “Return the tuples in the Major set that are not in Minor set”

Here is our result…

Figure 5.1

Rename

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

Syntax: ρ newRelationName [newColumn oldColumn] (R)

• ρ (rho) is the symbol for the Rename operation
• 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)

Here is our result…

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.

Syntax → A * B

• * is the Natural Join operator
• A, B are relations

How to say in English terms: “Natural join relation A with relation B”

Example 7.1

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

Let’s use the following expression → Students * Advisors

And we will get the following result…

Figure 7.1

The operation links the “AName” columns from both relations and finds the instances where Student’s AName equals Advisor’s AName, then links the tuples.

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.

Syntax: A ⟕ B

• ⟕ is the Left Outer Join operator
• A, B are relations

How to say in English Terms: “Left Join relation A with relation B”

Example 8.1

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

Our expression → Advisors ⟕ Students

Here is our result…

Figure 8.1

You may notice that we have some NULL values. This is because Left Outer Join will preserve the records on the left-side relation even if they have no matching tuple in the right-side relation.

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.

Thanks for reading! 👍