Relational algebra

When using the relational model, we can use 2 sets of operations. Relational algebra is one of them.

When usingRelational model, We can use 2 sets of operations.

The first one is calledRelational algebra,This is anprogramming language.

This is the foundation of SQL, so learning is very important-because SQL is the de facto standard for using relational databases.

The second one is calledRelational calculusNot proceduralDeclarative language. This is the fundamental difference in the way we interact with the database, because you will not tell the database softwarewhat to do, You just have to say itwhat do you want, And then sort out the specific operation method.

This is a common difference between programming languages. In the modern front end, we say that the interaction with the DOM in React is declarative. Modifying the DOM using raw JavaScript is procedural.

Language like thisdata record,Qualified Education BureauwithQuillBased on relational calculus. I am not going to discuss this issue because it is a more niche approach than the more practical approach adopted by SQL, but you can check it out as needed.

With this introduction, let's continueRelational algebra.

We have 2 types of operations:

  • Main operation
  • Franchise action

Main operations in relational algebra

The main operations are:

  • allianceAs long as the two tables have the same column and attribute type (domain), you can get data from the two tables and generate the sum of tuples.
  • the differenceAs long as the two tables have the same column and attribute type (domain), you can get the data contained in the first table but not in the second table, resulting in a difference in tuples.
  • Cartesian ProductObtain the data in the two tables according to the attribute value and generate a single table that combines their data.
  • selectOnly certain tuples (rows) contained in the table are extracted based on certain conditions.
  • projectGenerate a new table containing only one or more attributes (columns) of an existing table
  • RenamedUsed to rename attributes to prevent conflicts when multiple tables have the same name on different data

Join operations in relational algebra

Connection is probably the most powerful operation that relational algebra can perform. They are built on the main operation and allow you to correlate data contained in different relationships (tables).

Note: I will soon discuss the connection of DBMS in practice, which is mainly theoretical.

We have 2 main addition versions:Natural connectionwithθ connection. All the other versions are extracted from those 2.

Naturally join

Naturally joinAssociate two relationships (tables) and create a new table based on the same value of the attribute.

First, we need two relationships with the same attribute name (column). Then, if the attribute value in Relation A does not match the attribute in Relation B, the row is not part of the result and the row will be ignored.

example:

Relationship A

Employee ID Name
1 mark
2 Tony
3 Rick

Relationship B

Manager's name Employee ID
Todd 1
Albert 2

We can perform natural connection to get the name of each employee's boss:

Employee ID Name Manager's name
1 mark Todd
2 Tony Albert

Since these relationships have a common Employee ID attribute name, they only appear once in the results, not twice.

Since there is no corresponding entry in relationship B, employee #3 Rick who exists in relationship A is not included in this table.

Theta-join

Theta-join allows to perform joins based on any conditions to compare two columns in two different relationships, not just for equality like natural joins.

It performs the Cartesian product of two tables and filters the results based on the choices we want to make.

Equi-join

Equivalent joins are theta joins, where selection is based on the equality between attribute values in two different tables.

The difference with natural join is that we can choose the attribute name (column) to compare.

We will talk more about joins when we introduce SQL later, so we can use them in practice.


More database tutorials: