oracle中dbms_DBMS中的关系代数

oracle中dbms

In DBMS relation algebra define mathematical operations to work on relation or table. Basically, we use all the relational algebra operations to perform different operations between two different table to solve different types of problems and to find the best result. So, now today here we are going to discuss these all basic operations of relational algebra.

在DBMS中， 关系代数定义数学运算来处理关系或表 。 基本上，我们使用所有关系代数运算在两个不同的表之间执行不同的运算，以解决不同类型的问题并找到最佳结果 。 因此，今天在这里，我们将讨论关系代数的所有这些基本运算 。

There are nine operations in relational algebra operations:

关系代数运算中有九种运算：

联合，交叉点，减号 (Union, Intersection, Minus)

All these operations are known to set operation. These operations work as same as they work in mathematics to solve the problems. These are binary operation; for this operation, the relation must be union-compatible means their table structure must be the same. They must have the same number of the field with same name, data type, size, domain. In union operation, it retrieves all possible rows and columns data present in two or more tables. But, the rows and columns data cannot be the same means if any rows and columns value are same then it can be written at the single time, not multiple time.

所有这些操作都是已知的设置操作 。 这些运算的作用与解决问题的数学运算相同。 这些是二进制操作； 对于此操作，关系必须是联合兼容的，这意味着它们的表结构必须相同。 它们必须具有相同名称，数据类型，大小，域的相同编号字段。 在联合操作中，它将检索两个或多个表中存在的所有可能的行和列数据。 但是，行和列的数据不能相同，这意味着如果行和列的值相同，则可以一次写入，而不能多次写入。

The intersection is also a set operation; it is similar to the mathematics intersection operation. This operation is used to retrieve different data between two or more table.

相交也是一个设置操作； 它类似于数学相交运算。 此操作用于检索两个或多个表之间的不同数据。

Minus operation is also similar to the mathematics difference set operation. In this, we find the difference between the two table.

减号运算也类似于数学差异设置运算。 在此，我们找到了两个表之间的区别。

Here, is some example of union, intersection and minus operations for knowing how this operation works and how we use this operation to retrieve the data from the multiple tables. Suppose we have these two following table:

这里是联合，交集和减号运算的一些示例，用于了解此运算的工作方式以及我们如何使用此运算从多个表中检索数据。 假设我们有以下两个表：

Table 1: (Name "A1")

表1 ：(名称“ A1”)

S. No.	S. Name	Course
1	X	C#
2	Y	Java
3	Z	Python

Table 2: (Name "A2")

表2 ：(名称“ A2”)

S. No.	S. Name	Course
1	X	C#
2	Y	Java
3	A	C
4	Z	Python

Union ( A1 U A2) :

联盟(A1 U A2)：

S. No.	S. Name	Course
1	X	C#
2	Y	Java
3	Z	Python
4	A	C

Intersection ( A1 ∩ A2) :

交点(A1∩A2)：

S. No.	S. Name	Course
1	A	C

Minus ( A2 – A1) :

减号(A2 – A1)：

S. No.	S. Name	Course
4	A	C

选择和投影 (Selection and projection)

These are unary operations. They work on the single relation.

这些是一元运算 。 他们致力于单一关系。

Selection is used to retrieve tuples/row from a relation on the basis of the condition. If the condition is true, then tuple (row) will be retrieved while if the condition is false then the row will not return any value. For each row (tuple) condition will be evaluated individually. Symbol for row query in selection is 'σ'.

选择用于根据条件从关系中检索元组/行。 如果条件为true，则将检索元组(行)，而如果条件为false，则该行将不返回任何值。 对于每一行(元组)，将分别评估条件。 选择中用于行查询的符号为'σ' 。

In projection we will retrieve columns; we just need to specify a column name. Projection is possible as user’s choice means the projection column may differ from table column order. Symbol for column query in projection 'π'

在投影中，我们将检索列； 我们只需要指定一个列名即可。 可以进行投影，因为用户的选择意味着投影列可能与表格列的顺序不同。 投影“π”中用于列查询的符号

Here, is some example is shown below. let’s see how these operations are used to retrieve the data. We take same A1 and A2 tables for this operation:

在这里，下面是一些示例 。 让我们看看如何使用这些操作来检索数据。 我们对该操作采用相同的A1和A2表 ：

Selection ( σ ) : - Command: - σ Course = 'java' (A1)

选择(σ)：-命令：-σ课程='java'(A1)

S. No.	S. Name	Course
2	Y	Java

Projection ( π ) : - Command: - π S. Name (A1)

投影(π)：-命令：-πS.名称(A1)

S. Name
X
Y
Z

Combination of Selection ( σ ) and Projection ( π ):

选择(σ)和投影(π)的组合：

Command: - πS.Name,σCourse = 'C#' (A1 U A2)

命令：-πS.Name，σCourse='C＃'(A1 U A2)

S. Name	Course
X	C#

产品，加入，划分，重命名 (Product, Join, division, rename)

Suppose, we have two relation R1 and R2 having tuple T1 and T2 and attribute A1 and A2 respectively; then perform Cartesian product (R1 X R2, T1 X T2, A1 X A2) between them is known as a product.

假设我们有两个关系R1和R2，分别具有元组T1和T2以及属性A1和A2； 然后执行它们之间的笛卡尔积 (R1 X R2，T1 X T2，A1 X A2)称为乘积 。

Join is the most powerful operation is relational algebra. It allows us to retrieve data from multiple tables by establishing a relationship between them. Join is a projection of selection of product of two table.

连接最强大的运算是关系代数。 它允许我们通过在多个表之间建立关系来检索它们。 联接是两个表的产品选择的投影。

Type of joins

联接类型

1. Inner Join: The ta join, Equijoin, natural join

   内部联接： ta 联接 ，等联接，自然联接

2. Outer join: Left outer join, right outer join, full outer join, self-join

   外连接：左外连接，右外连接，完全外连接，自连接

The division is similarly performing as same as division set operation in mathematics between tuple (row) and attribute (column).

分割类似地执行作为相同的元组(行)和属性(列)之间的数学除法设定操作。

Rename as the name specify it is used to rename the tuple or name of the relation (ρ(R (Ā),A1).

重命名为名称，它用于重命名关系的元组或名称(ρ(R(Ā)，A1) 。

Here, is some example is shown below related to these operations take a look.

这里，下面是一些与这些操作有关的例子 。

Table (A1) :

表(A1)：

E. No.	E. Name	Salary	Dept. No.
1	A	5000	10
2	B	4000	10

Table (A2) :

表(A2)：

Dept. No.	D. Name	Location
10	IT	Mumbai
20	Sales	Delhi
30	HR	Hyderabad

Product of two table (A1 X A2) :

两张桌子的乘积(A1 X A2)：

E. No.	E. Name	Salary	Dept. No. (A1)	Dept. No. (A2)	D. Name	Location
1	A	5000	10	10	IT	Mumbai
1	A	5000	10	20	Sales	Delhi
1	A	5000	10	30	HR	Hyderabad
2	B	4000	10	10	IT	Mumbai
2	B	4000	10	20	Sales	Delhi
2	B	4000	10	30	HR	Hyderabad

Join in two table:

加入两个表：

Display employees working in city 'Mumbai'.

显示在“孟买”城市工作的员工。

Now, we create its query using selection and projection.

现在，我们使用选择和投影创建其查询。

ΠE. Name

E。 名称

σA1.DeptNo. =A2.DeptNo. ^ location = 'Mumbai'

σA1。部门编号 = A2。部门编号 ^ location ='孟买'

If the value of (A1) Dept. No. and (A2) Dept. No. is equal then the E. Name will be.

如果(A1)部门编号和(A2)部门编号的值相等，则E.名称将相同。

A1 X A2

A1 X A2

E. Name
A
B

Division:

师：

A1 / A2:

A1 / A2：

E. No	E. Name	Salary	D. Name	Location
1	A	5000	IT	Mumbai
1	A	5000	Sales	Delhi
1	A	5000	HR	Hyderabad
2	B	4000	IT	Mumbai
2	B	4000	Sales	Delhi
2	B	4000	HR	Mumbai

Rename: -ρ(R (Ā),A1 )

重命名：-ρ(R(Ā)，A1)

Operation	Min	max
A1 U A2	T2 V T1	T1 + T2    (A1 - > T1, A2 -> T2)
A1 ∩ A2	0	T1 V T2
A1 – A2	0	T1

Product	T1 * T2	T1 * T2
σa=10	0	T1 
πa(A1)	T1	T1 

Here, A1 & A2 are attribute (column) and T1 & T2 are tuple (row).

在此，A1和A2是属性(列)，T1和T2是元组(行)。

Conclusion

结论

In this article, we have learned all the different operations of relation algebra which we are used to retrieving the data from the two or more tables. I hope you all understand the concepts behind these operations. In the next article, we will learn more about the DBMS. Stay tuned for the next article. Have a nice day! HappyLearning!

在本文中，我们学习了关系代数的所有不同操作，这些操作用于从两个或更多表中检索数据。 希望大家都了解这些操作背后的概念。 在下一篇文章中，我们将了解有关DBMS的更多信息。 请继续关注下一篇文章。 祝你今天愉快！ 快乐学习！

翻译自: https://www.includehelp.com/dbms/relational-algebra.aspx

oracle中dbms