Relational Model¶

A relational database is a collection of one or more relations, which are based on the relational model.

Example

Structure of Relational Databases¶

Basic Structure¶

设集合 $D_{1}, D_{2}, \dots, D_{n}$ ，关系 $r$ 是笛卡尔积 $D_{1} \times D_{2} \times \dots \times D_{n}$ 的一个子集。

Attribute Types¶

设关系 $r$ 为元组 $(a_{1}, a_{2}, \dots, a_{n})$ ，其中 $a_{i}$ 是属性， $D_{i}$ 是属性的域。每个属性都有名称。

关系理论第一范式

Attribute values are (normally) required to be atomic. 属性值是原子的，即无法分割。例如复合属性或者多值属性都不能成为关系的属性。

$null$ 属于每个域。

Concepts about Relation¶

A relation is concerned with two concepts: relation schema and relation instance. 关系包含两个概念：关系模式和关系实例。

Relation Schema: describes the structure of the relation.

Example

Student_schema = (sid: string, name: string, sex: string, age: int, dept: string)

or

Student_schema = (sid, name, sex, age, dept)

Relation Instance: corresponds to the snapshot of the data in the relation at a given instant in time.

The Properties of Relation¶

The order of tuples is irrelevant. 无序
No duplicated tuples in a relation. 无重复
Attribute values are atomic. 属性是原子的

Database¶

A database consists of a collection of relations.

Key¶

$K \subseteq R$

Superkey: $K$ is a superkey of $R$ if the values of $K$ are sufficient to identify a unique tuple in $R$ . $K$ 是 $R$ 的超键，如果 $K$ 的值足以唯一标识 $R$ 中的一个元组。
Candidate Key: $K$ is a candidate key of $R$ if $K$ is a minimal superkey of $R$ . $K$ 是 $R$ 的候选键，如果 $K$ 是 $R$ 的最小超键。
Primary Key: A candidate key defined by the database designer. 主键是数据库设计者定义的候选键，通常用下划线标识。

Foreign Key¶

Assume relations $r$ and $s$ : $r (\underset{―}{A}, B, C)$ , $s (\underset{―}{B}, D)$ . $B$ in relation $r$ is a foreign key referencing $s$ , and $r$ is a referencing relation, and $s$ is a referenced relation.

参照关系中外码的值必须在被参照关系中实际存在, 或为 $null$ 。

Query Language¶

Pure languages:

Relational Algebra: a procedural query language. 过程式查询语言，SQL 的基础。
Tuple Relational Calculus: 元组关系演算
Domain Relational Calculus: 域关系演算

Fundamental Relational-Algebra Operations¶

Six basic operators:

Selection: $σ$ 选择
Projection: $Π$ 投影
Union: $\cup$ 并
Set Difference: $-$ 差
Cartesian product: $\times$ 笛卡尔积
Rename: $ρ$ 重命名

Selection¶

$σ_{p} (r) = {t ∣ t \in r \land p (t)}$

$r$ is a relation
$p$ is a predicate in the form of <attribute> op <attribute> or <constant>, where op is a comparison operator.

Example

Projection¶

$Π_{A_{1}, A_{2}, \dots, A_{k}} (r)$

$r$ is a relation
$A_{i}$ is an attribute of $r$

The result is defined as the relation of $k$ columns obtained by erasing the columns that are not listed. 结果是通过删除未列出的列而获得的 $k$ 列关系。并且进行去重。

Example

Union¶

$r \cup s = {t ∣ t \in r \lor t \in s}$

$r$ and $s$ are relations
$r$ and $s$ must have the same number of attributes. $r$ 和 $s$ 必须有相同数量的属性。
The attribute domains must be compatible. 属性域必须兼容。

Example

Set Difference¶

$r - s = {t ∣ t \in r \land t \notin s}$

$r$ and $s$ are relations
Similar to Union, $r$ and $s$ must have the same number of attributes and the attribute domains must be compatible.

Example

Cartesian Product¶

$r \times s = {(t_{1}, t_{2}) ∣ t_{1} \in r \land t_{2} \in s}$

$r$ and $s$ are relations
If $r$ and $s$ are not disjoint, then rename will be used to avoid ambiguity between the attributes with the same name. 如果 $r$ 和 $s$ 有相同名称的属性，则将使用 重命名 来避免具有相同名称的属性之间的歧义。

Example

$r$ 和 $s$ 不相交：

$r$ 和 $s$ 相交：

Rename¶

$ρ_{X} (E)$ : rename the expression $E$ as $X$ .
$ρ_{X (A_{1}, A_{2}, \dots, A_{n})} (E)$ : rename the expression $E$ as $X$ and the attributes as $A_{1}, A_{2}, \dots, A_{n}$ .

$ρ$ returns a relation that is identical to $E$ except that the relation is renamed as $X$ . $ρ$ 返回一个与 $E$ 相同的关系，只是关系被重命名为 $X$ 。

Additional Relational-Algebra Operations¶

Four additional operators:

Set Intersection: $\cap$ 交
Natural Join: $⋈$ 自然连接
Division: $\div$ 除
Assignment: $\leftarrow$ 赋值

Set Intersection¶

$r \cap s = {t ∣ t \in r \land t \in s} = r - (r - s)$

$r$ and $s$ are relations
$r$ and $s$ must have the same number of attributes and the attribute domains must be compatible.

Example

Natural Join¶

$r ⋈ s$

$r$ and $s$ are relations
Consider each pair of tuples $t_{1} \in r$ and $t_{2} \in s$ , if $t_{1}$ and $t_{2}$ have the same value on each attribute in $R \cap S$ , then add a tuple to the result relation with the remaining attributes from $t_{1}$ and $t_{2}$ . 如果 $t_{1}$ 和 $t_{2}$ 在 $R \cap S$ 上的每个属性上具有相同的值，则将一个元组添加到结果关系中，该元组具有来自 $t_{1}$ 和 $t_{2}$ 的剩余属性。

Example

Theta Join Operation

$r ⋈_{θ} s = σ_{θ} (r \times s)$

Division¶

$r \div s = {t ∣ t \in Π_{R - S} (r) \land \forall u \in s, t \times u \in r}$

$r$ and $s$ are relations on schema $R a n d S$

Example

Assignment¶

$X \leftarrow E$

Assignment is always made to a temporary relation.

Example

Write $r \div s$ as:

\begin{aligned} t e m p 1 & \leftarrow Π_{R - S} (r) \\ t e m p 2 & \leftarrow Π_{R - S} (t e m p 1 \times s - Π_{R - S, S} (r)) \\ r e s u l t & \leftarrow t e m p 1 - t e m p 2 \end{aligned}

Extended Relational-Algebra Operations¶

Generalized Projection: $Π_{A_{1}, A_{2}, \dots, A_{n}} (r)$ where $A_{i}$ is an expression. 广义投影
Aggregation Functions: 聚合函数

Generalized Projection¶

$Π_{A_{1}, A_{2}, \dots, A_{n}} (r)$ where $A_{i}$ is an expression.

Example

Given a relation $credit_info (customer_name, limit, current_balance)$ , we can use the following expression to calculate the available credit for each customer:

Π_{customer_name, limit - current_balance} (credit_info)

Aggregation Functions¶

$_{G_{1}, G_{2}, \dots, G_{n}} g_{F_{1} (A_{1}), F_{2} (A_{2}), \dots, F_{n} (A_{n})} (r)$

$r$ is a relation
$A_{i}$ is an attribute of $r$
$F_{i}$ is an aggregation function, such as
- avg: average value
- sum: sum of values
- count: number of tuples
- max: maximum value
- min: minimum value
$G_{i}$ is a grouping attribute(can be empty)

Example

Info

Result of aggregation does not have a name. 聚合的结果没有名称，需要重命名或者在聚合操作中指定名称。

Example:

_{branch-name} g_{sum (balance) a s sum-balance} (account)

Modification of the Database¶

Deletion
Insertion
Updating

Deletion¶

$r \leftarrow r - E$

$r$ is a relation
$E$ is a relational-algebra expression

Insertion¶

$r \leftarrow r \cup E$

Updating¶

$r \leftarrow Π_{F_{1}, F_{2}, \dots, F_{n}} (r)$

$r$ is a relation
$F_{i}$ is an attribute of $r$ or an expression which gives the new value of the attribute