Relational Algebra in Databases: Understanding Database Operations

Relational algebra in databases is defined as a procedural query language. In this model, data retrieval does not occur randomly but is carried out through a structured and logical system of operators. In this article, Viettel IDC takes an in-depth look at the nature of relational algebra and explores the core operations that drive it.

What Is Relational Algebra in Databases?

Relational algebra was first introduced in 1970 by Edgar F. Codd, widely regarded as the “father of database management systems.” It is considered a fundamental theoretical foundation for modern database systems.

Relational algebra in databases is classified as a Procedural Query Language (PQL). The key characteristic of a procedural query language is that, when performing a query, users or developers must explicitly specify two elements:

1. What to do: Identify the data that needs to be retrieved.

2. How to do it: Specify the process, method, or algorithm the system should use to locate and retrieve that data from the database.

In other words, if data were a treasure, relational algebra would not simply allow you to say, “I want the treasure.” You would also need to provide the map that explains how to reach it.

Types of Operations in Relational Algebra

In relational algebra, data-processing operators are divided into two main groups:

- Basic Operations

- Derived Operations

Note: One of the core characteristics of relational algebra is closure. When any operation is applied to one or more relations (input tables), the result is always another relation (output table). This makes it possible to construct complex queries by combining multiple operations.

Basic Operations of Relational Algebra

Below are six fundamental operations in relational algebra. These core tools are used to perform most data retrieval operations.

Before exploring them in detail, let us establish two sample relations for the examples below: STUDENT and EMPLOYEE.

The STUDENT table contains Student ID, Name, and Age.

The EMPLOYEE table contains Employee ID, Name, and Age.

1. Selection (σ)

The Selection operation is performed using the selection operator, represented by the Greek letter sigma (σ). It is used to retrieve tuples (rows) from a relation that satisfy a specified condition. In relational algebra, Selection is a unary operation.

Notation: σP(R)

Where:

- σ: Represents the Selection operation

- R: Represents the Relation (table)

- P: Represents a logical formula or condition

For example, suppose we want to retrieve all rows from the STUDENT table where Age = 20:

σAge=20(STUDENT)

The result is:

2. Projection (π)

In relational algebra, the Projection operation is performed using the projection operator, represented by pi (π). It is used to retrieve specific attributes, or columns, from a relation. This operation is also referred to as a form of vertical partitioning.

Notation: πA(R)

Where:

- π: Represents the Projection operation

- R: Represents the Relation

- A: Represents the list of attributes to retrieve

For example, suppose we want to retrieve the names of all students in the STUDENT table:

πName(STUDENT)

The result is:

As shown above, although the name Giang appears twice in the original table, duplicate values are automatically removed from the result.

For multiple attributes, separate them with commas:

πStudentID,Name(STUDENT)

The result is:

3. Rename (ρ)

The Rename operation is represented by the Greek letter rho (ρ). As its name suggests, it is used to rename the resulting relation.

Notation: ρ(R, S)

Where:

- R: The new relation name

- S: The original relation name

For example, suppose we retrieve student names from the STUDENT table and want to rename the resulting relation to STUDENT_LIST:

ρ(STUDENT_LIST, πName(STUDENT))

The resulting relation is named STUDENT_LIST:

4. Union (∪)

In relational algebra, the Union operation is represented by the union symbol (∪). Similar to set theory, it returns all tuples from both relations.

However, the two relations must be union-compatible, meaning they must have compatible sets of attributes.

Notation: R ∪ S

Where:

- R: The first relation

- S: The second relation

For example, suppose we want to retrieve all names from both the STUDENT and EMPLOYEE tables:

πName(STUDENT) ∪ πName(EMPLOYEE)

The result is:

Duplicate values are removed, and the names from both relations are combined.

5. Set Difference (-)

The Set Difference operation returns the tuples that exist in one relation but not in another.

It is represented by the minus sign (-).

Notation: R - S

Like the Union operation, Set Difference requires the two relations to be union-compatible.

For example, suppose we want to find the names of people who are students but not employees:

πName(STUDENT) - πName(EMPLOYEE)

The result is:

The names Cuong and Dung are excluded because they also appear in the EMPLOYEE table.

6. Cartesian Product (×)

The Cartesian Product is represented by the multiplication symbol (×). Given two relations, R and S, the Cartesian Product combines every tuple in R with every tuple in S.

Notation: R × S

For example:

STUDENT × EMPLOYEE

A portion of the resulting table would look like this:

The process continues until every row in the STUDENT table has been combined with every row in the EMPLOYEE table.

Derived Operations in Relational Algebra

Derived operations, also known as extended operations in relational algebra, include three main types:

- Intersection

- Division

- Join

1. Intersection (∩)

The Intersection operation returns the tuples that appear in both relations.

The two relations must be union-compatible, meaning they must have the same number and compatible types of attributes.

Notation: R ∩ S

For example, to find names that appear in both the STUDENT and EMPLOYEE tables:

πName(STUDENT) ∩ πName(EMPLOYEE)

The result is:

2. Division (÷ or /)

The Division operation is a powerful relational algebra operation used to solve queries involving the concept of “all.”

Notation: R ÷ S

It means: Find the entities in relation R that are associated with every entity in relation S.

To make this easier to understand, consider the following example.

ENROLLMENT (R) — A list of students and the subjects they have registered for:

REQUIRED_SUBJECTS (S) — A list of subjects that students are required to complete:

The question is: Find students who have registered for all subjects listed in the REQUIRED_SUBJECTS table.

ENROLLMENT ÷ REQUIRED_SUBJECTS

The result is:

3. Join

A Join is a binary operation that combines two or more relations. To better understand how it works, consider the following two tables.

The EMPLOYEE table contains employee information, including Employee ID, Name, City, and Experience.

The DEPARTMENT table contains department information and minimum experience requirements.

Before examining Join operations in relational algebra, it is useful to consider the Cartesian Product of these two tables. Visualizing the Cartesian Product makes it easier to understand how a Join works, since a Join can conceptually be understood as a Cartesian Product followed by condition-based filtering.

a. Theta Join (θ)

A Theta Join combines two relations based on a general condition, represented by the Greek letter theta (θ).

The condition can use comparison operators such as:

>, <, >=, <=, or =

Notation: R ⋈θ S

For example, suppose we want to find combinations where an employee's experience is greater than or equal to the department's minimum experience requirement:

EMPLOYEE ⋈EMPLOYEE.Experience >= DEPARTMENT.MinExperience DEPARTMENT

The result includes only the rows that satisfy:

Experience >= Minimum Experience

b. Equi Join

An Equi Join is a special case of a Theta Join in which the condition uses only the equality operator (=).

For example, we can join the EMPLOYEE and DEPARTMENT tables where the Employee ID in one table is equal to the Employee ID in the other:

EMPLOYEE ⋈EMPLOYEE.EmployeeID = DEPARTMENT.EmployeeID DEPARTMENT

The result is:

c. Natural Join (⋈)

A Natural Join does not require an explicitly defined comparison condition. Instead, it automatically identifies attributes or columns with the same name and compatible domains in both relations and uses them to establish the connection.

It also automatically removes duplicate columns from the result.

Natural Joins are commonly used with related columns, including those involving foreign keys.

For example, a Natural Join between EMPLOYEE and DEPARTMENT automatically recognizes Employee ID as the common column:

EMPLOYEE ⋈ DEPARTMENT

The result is:

Conclusion

Relational algebra is a theoretical model that serves as the backbone and foundation of modern SQL. Through this article, Viettel IDC hopes to provide you with a clearer understanding of query languages and help you work more effectively with databases.

To efficiently transform relational algebra concepts from theory into practical applications, businesses need a powerful and reliable data environment. Viettel Database Service (vDBS) is a comprehensive cloud database service that supports a wide range of popular database management systems, including MySQL, PostgreSQL, and MongoDB, enabling query operations to be executed with high performance.

With vDBS, you can easily provision, operate, and scale your database systems in just a few minutes without having to worry about managing complex physical infrastructure.

Explore the service and register today:

https://viettelidc.com.vn/en/viettel-database-service

For consultation and information about Viettel’s services, you can contact Viettel IDC directly through the following channels:

- Hotline: 1800 8088 (toll-free)

- Fanpage: https://www.facebook.com/viettelidc

- Website: https://viettelidc.com.vn