關系代數運算_關系代數

關系代數運算

When working with the relational model, we have 2 groups of operations we can use.

使用關系模型時 ，我們可以使用2組操作。

The first is called relational algebra, and it’s a procedural language.

第一種稱為關系代數 ，它是一種過程語言 。

This is what SQL is based upon, and as such it is very important to learn - as SQL is the de-facto standard for working with relational databases.

這是SQL的基礎，是以學習非常重要-因為SQL是使用關系資料庫的實際标準。

The second is called relational calculus and instead of being procedural, it’s a declarative language. It’s a fundamental difference in how we interact with databases, because you don’t tell the database software what to do, you just tell it what you want, and let it sort out the details of how to do it.

第二種稱為關系演算 ，而不是程式性的，它是一種聲明性語言 。 這是我們與資料庫互動的方式的根本差別，因為您沒有告訴資料庫軟體該怎麼做 ，而隻是告訴您想要什麼 ，然後讓它整理出如何做的細節。

This is a common distinction among programming languages. In modern frontend, we say interaction with the DOM in React is declarative. Using vanilla JavaScript to modify the DOM is procedural.

這是程式設計語言之間的常見差別。 在現代的前端中，我們說與React中的DOM互動是聲明性的。 使用原始JavaScript修改DOM是過程性的。

Languages like Datalog, QBE and QUEL have relational calculus as its base. I’m not going to talk about this because I think it’s a much more niche way of doing things compared to the more practical approach followed by SQL, but you can look at it if you want.

諸如Datalog ， QBE和QUEL之類的語言都以關系演算為基礎。 我不打算讨論這個問題，因為與SQL所采用的更實際的方法相比，這是一種更利基的處理方式，但是您可以根據需要進行檢視。

Given this introduction, let’s go on with relational algebra.

有了這個介紹，讓我們繼續關系代數 。

We have 2 types of operations:

我們有2種操作類型：

• primary operations

  主要操作

• join operations

  加盟行動

關系代數中的主要運算 (Primary operations in relational algebra)

Primary operations are:

主要操作是：

• union to get data from two tables, generating a sum of the tuples, as long as the two tables have the same columns and attribute types (domain).

  隻要兩個表具有相同的列和屬性類型(域)，就可以通過并集從兩個表中擷取資料，并生成元組的和。

• difference to get data contained in the first table but not in the second table, generating a difference of the tuples, as long as the two tables have the same columns and attribute types (domain).

  差異以擷取包含在第一個表中但不包含在第二個表中的資料，進而産生元組的差異，隻要兩個表具有相同的列和屬性類型(域)即可。

• cartesian product to get data from two tables into and generate one single table that combines the data of them, based on an attribute value.

  笛卡爾乘積可将兩個表中的資料擷取并生成一個基于屬性值将它們的資料組合在一起的單個表。

• select to only extract some of the tuples (rows) contained in a table based on certain criteria.

  選擇僅基于某些條件提取表中包含的一些元組(行)。

• project to generate a new table containing only one or more attributes (columns) of an existing table

  項目以生成僅包含現有表的一個或多個屬性(列)的新表

• rename used to rename an attribute, used to prevent conflicts when multiple tables have the same name for different data

  重命名用于重命名屬性，用于防止多個表對不同資料使用相同名稱時發生沖突

關系代數中的聯接運算 (Join operations in relational algebra)

Joins are probably the most powerful operations you can perform with relational algebra. They build on top of primary operations, and they allow you to correlate data contained in different relations (tables).

連接配接可能是關系代數可以執行的最強大的運算。 它們建立在主要操作之上，并且使您可以關聯包含在不同關系(表)中的資料。

Note: I’ll soon talk about joins in practice in a DBMS, this is mostly theory.

注意：我将很快讨論DBMS在實踐中的聯接，這主要是理論上的。

We have 2 main join versions: natural join and theta join. All the other versions are extracted from those 2.

我們有2個主要的join版本： 自然聯接和theta聯接 。 所有其他版本均摘自那些2。

自然加入 (Natural Join)

Natural join correlates two relations (tables), and creates a new table based on the same values of an attribute.

自然聯接将兩個關系(表)相關聯，并基于屬性的相同值建立一個新表。

We need two relations with the same attribute name (column), first. Then if values in the attributes in relation A are unmatched in the attributes in relation B, the row is not part of the result, it’s ignored.

首先，我們需要兩個具有相同屬性名稱(列)的關系。 然後，如果關系A中的屬性值與關系B中的屬性不比對，則該行不是結果的一部分，将忽略該行。

Example:

例：

Relation A

關系A

Employee ID	Name
1	Mark
2	Tony
3	Rick

員工ID	名稱
1個	标記
2	托尼
3	裡克

Relation B

關系B

Manager Name	Employee ID
Todd	1
Albert	2

經理姓名	員工ID
托德	1個
阿爾伯特	2

We can perform a natural join to get the boss name for each employee:

我們可以進行自然加入以獲得每個員工的老闆姓名：

Employee ID	Name	Manager Name
1	Mark	Todd
2	Tony	Albert

員工ID	名稱	經理姓名
1個	标記	托德
2	托尼	阿爾伯特

Since the relations have the Employee ID attribute name in common, it is only present once in the result, not 2 times.

由于這些關系具有共同的Employee ID屬性名稱，是以在結果中僅出現一次，而不是2次。

The employee #3 present in relation A, Rick, is not included in this table, because there’s no corresponding entry in relation B.

由于在關系B中沒有相應的條目，是以關系A中存在的＃3雇員Rick不包括在此表中。

θ聯接 (Theta-join)

A theta-join allows to perform a join based on any criteria to compare two columns in two different relations, not just equality like the natural join does.

Theta-join允許根據任何條件執行連接配接以比較兩個不同關系中的兩個列，而不僅僅是自然連接配接那樣的相等性。

It performs a cartesian product of two tables, and filters the results based on the selection we want to make.

它執行兩個表的笛卡爾積，并根據我們要選擇的内容過濾結果。

等值聯接 (Equi-join)

The equi-join is a theta join, where the selection is based on equality between attribute values in the two different tables.

等值聯接是theta聯接，其中選擇基于兩個不同表中屬性值之間的相等性。

The difference with the natural join is that we can choose which attributes names (columns) we want to compare.

自然連接配接的差別在于我們可以選擇要比較的屬性名稱(列)。

We’ll talk much more about joins later when SQL is introduced, so we can use them in practice.

稍後在引入SQL時，我們将詳細讨論聯接，是以我們可以在實踐中使用它們。

翻譯自: https://flaviocopes.com/relational-algebra/

關系代數運算