database 什么是数据库术语中的关系？

What is a relation in database terminology?

提问by Meir

When someone refers to a relation in a database course, what does that mean?

当有人在数据库课程中提到关系时，这意味着什么？

回答by Shannon Severance

Amazingly, "relation" in "relational" databases does notrefer to the foreign key relationship of one table to another. "A relation is a data structure which consists of a heading and an unordered set of tuples which share the same type," according to Wikipedia on 'Relation (database)'.

令人惊讶的是，“关系”数据库中的“关系”并不是指一个表与另一个表的外键关系。“关系是一种数据结构，它由一个标题和一组共享相同类型的无序元组组成，”根据维基百科关于“关系（数据库）”的说法。

In SQL RDBMSes (such as MS SQL Server and Oracle] tables are permently stored relations, where the column names defined in the data dictionary form the "heading" and the rows are the "tuples" of the relation.

在 SQL RDBMS（如 MS SQL Server 和 Oracle）中，表是永久存储的关系，其中数据字典中定义的列名形成“标题”，行是关系的“元组”。

Then from a table, a query can return a different relation:

然后从表中，查询可以返回不同的关系：

create table t (x number primary key, y number not null);

Table created.

SQL> insert into t values (1, 10);

1 row created.

SQL> insert into t values (2, 20);

1 row created.

SQL> select x from t;

         X
----------
         1
         2

select x from treturned a relation with fewer columns, tuples of fewer elements, than the base table had. And select x, y from t where x = 1will return a relation with fewer tuples than the base table:

select x from t返回一个比基表具有更少列、更少元素的元组的关系。并且select x, y from t where x = 1将返回比基表少元组的关系：

SQL> select x, y from t where x = 1;

         X          Y
---------- ----------
         1         10

An example using inner join:

使用内连接的示例：

SQL> create table s (x number primary key, words varchar2(100) not null);

Table created.

SQL> insert into s values (1, 'Hello World!');

1 row created.

SQL> insert into s values (3, 'Will not show');

1 row created.

SQL> select t.x, t.y, s.words
  2  from t
  3  inner join s
  4      on t.x = s.x;

         X          Y WORDS
---------- ---------- ---------------
         1         10 Hello World!

Conceptually, t inner join s on t.x = s.xgoes through the following steps:

从概念上讲，t inner join s on t.x = s.x经过以下步骤：

1. Take the cartesian product of sand t, which is to take each row of sand combine it with each row of tresulting in a tuple with size of s* size of ttuples or rows, each with all the columns from both sand tmuch like the results of:

   SQL> select * from s, t;

        X WORDS                    X          Y
   

   ---

        3 Will not show            1         10
        3 Will not show            2         20
        1 Hello World!             1         10
        1 Hello World!             2         20
   

1. 取sand的笛卡尔积t，即取 的每一行s并将其与 的每一行组合，t从而得到一个大小为 s* t元组或行大小的元组，每个元组都包含来自两者的所有列，s并且t非常类似于：

   SQL> select * from s, t;

        X WORDS                    X          Y
   

   ---

        3 Will not show            1         10
        3 Will not show            2         20
        1 Hello World!             1         10
        1 Hello World!             2         20
   

(Or select * from s cross join tin the SQL-92 syntax) From the cartesian product containing four tuples/rows with four columns on s.x = t.xtrims the tuples down to one, still with four columns:

（或select * from s cross join t在 SQL-92 语法中）从包含四个元组/四列行的笛卡尔积on s.x = t.x将元组修剪为一，仍然有四列：

SQL> select *
  2  from t
  3  inner join s
  4      on t.x = s.x;

         X          Y          X WORDS
---------- ---------- ---------- ---------------
         1         10          1 Hello World!

And select t.x, t.y, s.wordsshaves one column off of the relation.

并select t.x, t.y, s.words从关系中删除一列。

Notethat the above describes a conceptual or logical model of what is going on. Databases come with query optimizers that are designed to give the results as if all the logical steps had been followed, but manage to skip steps, in the physical implementation of the work and to use supporting physical structures, such as indexes, that are not part of the relational model.

请注意，上面描述了正在发生的事情的概念或逻辑模型。数据库带有查询优化器，旨在提供结果，就好像所有的逻辑步骤都已遵循，但设法跳过工作的物理实现中的步骤，并使用支持物理结构，例如索引，不属于的关系模型。

Views are relation definitions that do not store the relation, but define a relation based on other relations, eventually with tables at the bottom. (Except for materialized views, that precompute and store a relation based on other relations.)

视图是关系定义，不存储关系，而是基于其他关系定义关系，最终在底部带有表。（除了物化视图，它根据其他关系预先计算和存储关系。）

回答by Larry Lustig

I can see that other respondents are giving you strict definitions of what can truly be called a "relation" and I don't dispute their correctness. In common usage, however, when someone refers to a "relation" in a database course they are referring to a tabular set of data either permanently stored in the database (a table) or derived from tables according to a mathematical description (a view or a query result).

我可以看到其他受访者对真正可以称为“关系”的东西给出了严格的定义，我不怀疑他们的正确性。然而，在通常的用法中，当有人在数据库课程中提到“关系”时，他们指的是永久存储在数据库中的表格数据集（表）或根据数学描述（视图或查询结果）。

回答by Rhys

Put simply, a "relation" is a table, the heading being the definition of the structure and the rows being the data.

简而言之，“关系”是一个表，标题是结构的定义，行是数据。

回答by Ned Batchelder

There are so far four answers here, and they are all the same, and all wrong. The term "relational" refers to the fact that the records in a table model a mathematical relation.

到目前为止，这里有四个答案，它们都是相同的，而且都是错误的。术语“关系”是指表中的记录模拟数学关系这一事实。

回答by Francis Niu

Practicality, a "Relation" in relational model can be considered as a "Table" in actual RDBMS products(Oracle, SQL Server, MySQL, etc), and "Tuples" in a relation can also be considered as "Rows" or "Records" in a table. The only difference between them is that Relation is a set of tuples and Table is a bag of records. As a set, relation disallows duplicate elements(tuples) and all tuples in it are unordered, but the records in table may be repeated and are always in a particular sequence for both physical storage and human-readable display.

实用性，关系模型中的“Relation”在实际的RDBMS产品（Oracle、SQL Server、MySQL等）中可以看作是“表”，关系中的“元组”也可以看作是“行”或“记录” "在一张桌子上。它们之间的唯一区别是 Relation 是一组元组，而 Table 是一组记录。作为一个集合，relation 不允许重复元素（元组），并且其中的所有元组都是无序的，但是表中的记录可能会重复，并且对于物理存储和人类可读的显示总是处于特定的顺序。

And there are two similar terms which often cause confusion and misunderstanding in database area. Please notice them: the "Relationship" in E/R model and the "Relation" in relational model is absolutely different. When converting an E/R model into a relational model, both entities and relationships in the former are represented (with a little different structure) as relations(tables) in the latter. And the association("reference" or "relationship" also be used) between tables, actually is known as foreign key, is still different with the relationship between entities.

并且有两个相似的术语在数据库领域经常引起混淆和误解。请注意它们：E/R 模型中的“关系”和关系模型中的“关系”是完全不同的。将 E/R 模型转换为关系模型时，前者中的实体和关系都表示（结构略有不同）为后者中的关系（表）。而表之间的关联（也可以使用“引用”或“关系”），实际上称为外键，与实体之间的关系还是有区别的。

More precisely, you may want to distinguish a relation and a relation variable (relvar). A relation is an abstract structure which contains a set of attributes, and a relvar is the dataset status in a particular moment of this relation. The first one can be considered as the table definition with columns, and the second one is dataset in this table. (Think about Type vs Variable in C or any other procedural programming language and Class vs Object in OOP.)

更准确地说，您可能想要区分关系和关系变量 (relvar)。关系是包含一组属性的抽象结构，相关变量是该关系在特定时刻的数据集状态。第一个可以认为是带有列的表定义，第二个是该表中的数据集。（想想 C 或任何其他过程编程语言中的类型与变量以及 OOP 中的类与对象。）

Following are corresponding terms between relation theory and database practice:

以下是关系理论与数据库实践的对应术语：

Relation             <-->  Table
Tuple                <-->  Record, Row
Attribute            <-->  Column, Field
Domain of attribute  <-->  Datatype of column

回答by Quassnoi

These articles may be of interest to you:

您可能对这些文章感兴趣：

• What is a relational database?
• What is entity-relationship model?

• 什么是关系数据库？
• 什么是实体关系模型？

In simple English: relationis data in tabular format with fixed number of columns and data type of each column.

简单的英语：relation是表格格式的数据，具有固定的列数和每列的数据类型。

This can be a table, a view, a result of a subquery or a function etc.

这可以是表、视图、子查询的结果或函数等。

回答by Rose Perrone

A relation is a table, which is a set of data. A table is the result of a query.

关系是一个表，它是一组数据。表是查询的结果。

Why is a table called a relation? In short, because all the values in the table can be defined by a relation in the sense of set theory.

为什么将表称为关系？简而言之，因为表中的所有值都可以通过集合论意义上的关系来定义。

A table contains a set of data. All the elements in a set are defined by a relation. In set theory, relations are often denoted xRy, where xis related to yby the relation R. For example, (2) R (-2)where the relation Ris xis the negative of y. The set of all negative numbers is defined by this relation R, where the domain is all positive numbers, and the range is all negative numbers.

一个表包含一组数据。集合中的所有元素都由关系定义。在集合论，关系常常表示xRy，其中x涉及到y的关系R。例如，(2) R (-2)其中的关系R是x是负y。所有负数的集合由这个关系定义R，其中域都是正数，范围都是负数。

We could also have the binary relation: ('Boston') R (American Cities)where the relation Ris defined as xcan be defined by y.

我们也可以有二元关系：('Boston') R (American Cities)在关系R中定义为x可以通过定义y。

We could also have the binary relation: ('Mango') R (Fruit)where the relation Ris defined as xis this type of food. And so, xis a value in the domain (the input) of a relation, and yis a value in the range (the output) of a relation.

我们也可以有二元关系：('Mango') R (Fruit)关系R被定义为x这种类型的食物。因此，x是关系域（输入）中y的值，是关系范围（输出）中的值。

A database table of all citizens in New York may be represented as

纽约所有公民的数据库表可以表示为

Citizen(Social_Security_Number, Name, Home_Address).

Here, the relation in the sense of set theory, is xRywhere the relation Ris defined as xis a citizen living in y, where we define yas New York.

在这里，集合论意义xRy上的关系，是将关系R定义为x居住在 的公民y，我们将其定义y为纽约。

A query can also return a new relation (that is, it returns a set of data defined by a new relation). If we want to query the database to find all citizens that have the last name, 'Perrone', we would define our result set based on another relation xRy, namely, x is a citizen living in New York with the last name, y, where we define yas 'Perrone'.

查询还可以返回一个新关系（即它返回由新关系定义的一组数据）。如果我们要查询数据库以查找姓氏为 'Perrone' 的所有公民，我们将基于另一个关系 xRy 定义我们的结果集，即 x 是居住在纽约的姓氏为 的公民y，其中我们定义y为“Perrone”。

回答by SRowe

A relation is a set of unique tuples, where a tuple consists of an entity ID value which is RELATED TO (identifies) one or more attributes. It is NOT a table, which is a different level entirely (implementation rather than design).

关系是一组唯一的元组，其中元组由与（标识）一个或多个属性相关的实体 ID 值组成。它不是一张桌子，完全是一个不同的层次（实现而不是设计）。

I can't make this definition any shorter without leaving something out, but it is so short as to be merely a list of terms. If I make it longer, I will probably confuse the main point which is: "what does the word relationmean in this context? What is being related?"

我不能在不遗漏某些内容的情况下使这个定义更短，但它是如此简短，以至于仅仅是一个术语列表。如果我延长它，我可能会混淆主要观点：“关系这个词在这种情况下是什么意思？什么是相关的？”