数据库系统概论：Relation Model 关系模型

Relation

• $A_1,A_2,...,A_n$ are attributes ;

• $R=(A_1,A_2,...,A_n)$ is a **relation schema **;

• $r(R)$ is a relation instance defined over schema $R$ ;

• tuple is a row in a table ;

Relations are unordered:

• Order of tuples is irrelevant (Relations are Unordered)
• Duplicate rows removed from result, since relations are sets

Attributes

每个属性的允许值集合称为属性的域

属性值(通常)需要是原子的;

特殊值null是每个域的成员。表示值为“unknown”。

null值会导致许多操作的定义复杂化

Attribute domain (属性域): the set of allowed values for attribute.

• Attributes values are required to be atomic ;
• null is a member of every domain. Indicated that the value is “unkownn” but not empty.
• The null value causes complications in the definition of many operations.

Database Schema

Database schema: logical structure of the database

Database instance: a snapshot of the data in the database at a given instant in time.

Keys

$K$ is a superkey(超码) of $R$ if:

• $K \subset R$
• $K$ are sufficient(充足的) to identify a unique tuple of each possible relation $r(R)$

$K$ is a candidate key(候选码) if

• $K$ is superkey
• $K$ is minimal (最小了，再小就没办法决定关系 R 的元组了)

$K$ is primary key(主码) if

• $K$ is a candidate key
• $K$ is selected to be primary key by the designer.

Relational Algebra

• Not Turing-machine equivalent
• Consists of 6 basic operations

关系代数的六种基本运算

https://billc.io/2020/04/latex-relational-algebra/

Latex 符号

$\sigma$	\sigma	select
$\Pi$	\Pi	project (投影 就是对应在指定属性上的值)
$\cup$	\cup	union(并)
$-$	-	set difference (集合的差)
$\times$	\times	Cartesian product (笛卡尔积)
$\rho$	\rho	rename (重命名)

Select

selects operation selects tuples that satisfy a given selection predicate(断言，也就是筛选条件)

$$\sigma_p(r)$$

$p$ 是断言，$r$ 是元组集合（表名），意思为从 $r$ 中筛选符合 $p$ 的元组，示例：

$$\sigma_{dept\_name="Physics"}(instructor)$$

从 instructor 表中筛选 “dept_name” 为 “Physics” 的元组。

关系代数中的 select 相当于 SQL 中的 WHERE ，都是筛选条件。

Comparisons :

$$= | \neq|\geq|\leq|\gt|\lt$$

Combine several predicates: (连接多个断言)

\and | \or | \neg

Project

投影：在已知元组上过滤掉没有给出的列

A unary operation that returns its argument relation, with certain attributes left out.

The result is defined as the relation of k columns obtained by erasing the columns that are not listed

$$\Pi_{A_1,A_2,...,A_k}(r)$$

由于结果是集合所以去掉了重复的行（元组）

Duplicate rows removed from result, since relations are sets

For example:

$$\Pi_{name}(\sigma_{dept\_name="Physics"}(instructor))$$

Cartesian-Product Operation

Allows us to combine information from any two relations.

笛卡尔积：将两个关系全连接

Join Operation

$$\bowtie$$

自然连接：两个关系之间，通过共有的属性的值，使元组一一对应。

假设共有属性为 $A_i$ ，此时若 $R_1$ 中存在元组的 $A_i = a$，而关系 $R_2$ 没有元组满足这个条件，则该元组的信息将不会出现在 $R_1 \bowtie R_2$ 中。

$$r \bowtie_\theta s = \sigma_\theta(r\times s)$$

Set Operation

$\cup$

Union

• r, s must have the same arity (same number of attributes)
• The attribute domains must be compatible

$\cap$

Intersection

• r, s have the same arity
• attributes of r and s are compatible

$-$

Difference

• r and s must have the same arity

• attribute domains of r and s must be compatible

Assignment Operation

assigning parts of a relational-algebra expression to temporary relation variables(临时变量)

$$VarName \leftarrow \sigma_{dept\_name="Physics"}(instructor)$$

Rename Operation

\chi $\chi$

$$\rho_\chi(E) \\ \rho_{\chi(A_1,A_2,...,A_n)}(E)$$

Return the result of expression E under the name $\chi$