写给女朋友的 SQL 教程——数据模型

Zhou Yang — Sun, 24 Jan 2021 05:01:07 +0000

本文中以斜体表示的定义部分内容可能相对晦涩，可以参照后续解释进行理解。

数据库的作用是存储数据，而数据是来自于现实世界中的，所以数据库中描述的往往是对现实世界数据特征的抽象。这种抽象的方式被称为数据库的数据模型。关系模型即是其中一种抽象方式。

层次模型
一个数据模型首先要能够反映现实世界中的关联关系，这样才能够针对这种关系设计数据库结构。其次，当现实世界中的关系发生变化时，数据模型也要有随之改变的能力。在关系模型出现之前，存在层次模型（Hirearchical Model）和网状模型（Network Model）两个数据模型。层次模型是数据库中最早出现的数据模型，它定义了：只有一个根结点；根以外的结点有且只有一个双亲结点。也就是说，层次模型是一棵树。

图1，层次模型

针对图1的层次模型来讲，它的最高的层次是 College，College 和 Department、Infrastructure 之间存在归属关系，在层次模型中将这种关系用线连起来。它们之间的联系是父子之间一对多的关系。也就是说，一个结点只能归属于另一个结点。所以在层次模型中，只能处理一对多的实体联系。

想象这样一种情况：学生张三修了双学位，而恰好这两个专业属于不同的两个学院，那么层次模型应该如何构造？也就是说，层次模型难以表达多对多的结构。

另外，在 Department 和 Students 之间加入 Class 结点，这显然是合理的。如果用户在设计数据库结构时没有考虑周到，导致后续对结构进行更改，这势必会对整体结构造成很大的影响。

网状模型
在层次模型中，线用来表示层级之间的归属关系。但是在现实世界中，许多事物之间的联系是不存在层次关系的。把这种层级关系转换成事物之间的关联关系，同样用线将它们连起来，这样就可以得到一个网式的结构，称为网状模型。

图2，网状模型

在网状模型中，一个实体可以与多个实体存在联系，这种联系可以是任何方式的，包括归属关系。假设图2描述的是一种组织架构上的关系，D1 在属于 C2 的同时，也可以属于 C3，这样就能够解决层级模型难以解决的问题：无法表达多对多的关系。网状模型能够表达现实世界中的复杂关系。但是随着网状模型的规模扩大，整个模型的结构越来越复杂，每次对一个数据的修改势必会影响许多相关数据。

关系模型
既然网状模型能够解决多对多的问题，只是结构较为复杂，如果将网状模型按照一个个实体拆分，并附加上它们之间的关联关系，是否能够在表达多对多关系的同时简化结构呢？

按照上述思路，每个实体单独拆分，同时实体间的关系也可以看作一个描述它们之间关系的实体。图3描述了一个从 Supplier 购买 Chemical 的关系。想象一次采购的过程：可以同时采购多种 Chemical，每种 Chemical 有各自的 Supplier；同时可能在一个 Supplier 采购了多种 Chemical；这次购买要记录购买的数量、总价和时间；每个 Chemical 和 Supplier 的详细信息当然也要记录下来。按照 Chemical, Supplier 这两个实体，以及它们之间的关系（Order）拆分，就可以得到图3的实体——关系模型。

图3，从 Supplier 采购 Chemicla 的 E-R 图

关系模型设计完成后，用户发现忘记记录采购者的信息，这该怎么办？在该模型中，只需要加入采购者实体，并将其与 Order 关联起来，不需要对其他实体进行修改。可以看出，关系模型降低了实体间的耦合度，使得结构的可维护性大大增强。

在 E-R 图（Entity Relationship Diagram）中，方形框代表实体，椭圆形框代表实体或关系的属性，菱形框代表实体间的联系，实体、属性、联系之间用实线连接起来。

在实际关系型数据库中，实体和联系都被设计为一张表，表中包含实体或联系的属性。

根据图3的 E-R 图，可以设计出下面三张表：表1描述了每个 Chemical 的属性，以及一个能够唯一表示它的 chemical_id

表1，Chemical 表

chemical_id	name	formula
1	盐酸	HCL
2	硫酸	H2SO4

表2和表1类似，每个 Supplier 也都有能够唯一描述它们的 supplier_id

表2，Supplier 表

supplier_id	name	contact
1	大海化工	123
2	森林药业	456

Order 表用于表达对一项 Chemical 采购的信息，它包含了用于记录 Chemical 和 Supplier 的 chemical_id 和 supplier_id，这样就能够通过这张表找到实际购买的 Chemical 和这个 Chemical 对应的 Supplier。同时还要记录这一项购买的数量和花费。

表3，Order 表

order_id	supplier_id	chemical_id	amount	totalcost
1	2	1	3	300
2	1	2	2	200

此时用户发现了一个问题：一次购买了多种 Chemical 时，Order 表并没有记录这次购买的总价。难道要用户逐一计算吗？这时可以增加一个 Order_Total 表，用于记录每次采购对多种 Chemical 的 Order 关系，同时在 Order 表中增加一列用于标示它属于哪次购买。

表4，Order_Total 表

order_total_id	total	order_time
1	500	2020-12-12

表5，修改后的 Order 表

order_id	supplier_id	chemical_id	order_total_id	amount	totalcost
1	2	1	1	3	300
2	1	2	1	2	200

在 Chemical 和 Supplier 中，存在着一列用于唯一标示这一行，这样的列通常被设计为主键。在联系表（Order）中，除了唯一标示的列，还存在用于关联其他表的列（supplier_id, checmical_id, order_total_id），这些列的通常是其他表中的主键，这种其他表中的主键的列通常被设置为外键。

一行的属性（列）必须能够唯一区分这一行的。也就是说每个关系（表）中没有两行是完全一致的。一个或多个属性组合在一起能够唯一区分这一行，这样的组合叫做 superkey。
一个 superkey 可能包含许多非必需的属性，去掉这些属性同样能够唯一标示一行，去掉无用的属性后的属性组合叫做 candidate key。candidate key 是 superkey 的一个子集。
一行可能有多种 candidate key 选择，用户可以选择其中一个作为这张表的主键（primary key）
一个表 r1 中的属性中可能包含另一个表 r2 中的主键，这个属性在 r1 上被称为参照 r2 的外键（forign key）在数据库中，主键和外键约束都是用户手动加上去的。即使表数据之间有着约束和关联关系，数据库中也可以不施加此约束。在实际生产环境中，数据库很少使用外键约束[1]。

在表5中，order_id 是 Order 的主键，supplier_id 是 Order 表中 Supplier 的外键。

在上篇文章中提到过，SQL 是在关系模型诞生之后才被创造出来的结构化查询语言。它是一个声明式（Declarative）语言，声明式的含义是描述目标的性质，也就是说“要什么样的结果”。而与之对应的是称之为命令式（Imperative）语言，它描述了每一步应该如何去做。在层次模型中，查询数据首先要明确其父结点；而网状模型是一种导航式的结构，不仅要说明对数据做什么，还要说明操作的路径。当一个网状模型极为庞大时复杂的结构对用户来说是一个巨大的负担。得益于 SQL 的出现，用户只需要告诉数据库要什么，而怎么去做只需交给数据库来实现。

[1] https://draveness.me/whys-the-design-database-foreign-key/

写给女朋友的 SQL 教程——历史和关系模型

Zhou Yang — Sat, 23 Jan 2021 11:40:28 +0000

学习新概念经常陷入的误区之一是：先生硬地记下，而不是尝试理解其含义。读书时老师经常讲“要理解着记忆”，这个思想在此处也同理。一个新概念必然是为了解决一个问题而出现的，所以在介绍新概念之前，要首先了解其诞生的历史背景。

1970年代，IBM 的工程师从 Edgar F. Codd 的关系模型[1]中得到灵感，设计了一个用于操作他们的半结构化数据库 System R[2]的语言：SEQUEL (Structured English Query Language)，由于商标原因后改称为 SQL。这也是经常有人将 SQL 读作 [siːkwəl] 的原因。SQL 先后在 System/38, SQL/DS, DB2 等基于 System R 的数据库上大放异彩，这让Oracle (Relational Software, Inc.) 从中得到了灵感。1979年，Oracle 发布了他们的第一个基于 SQL 的商用数据库：Oracle V2。SQL 在商用关系型数据库上的成功使得其成为了事实标准，ANSI 和 ISO 也随后相继采用 SQL 作为标准。

SQL 是 Structured Query Language 的缩写，从字面上翻译过来是“结构化查询语言”。“查询”和“语言”两词容易理解，那么何为“结构化”？既然 SQL 为操作关系模型而设计的，那么要解释“结构化”就首先要解释什么是“关系模型”。

关系模型认为数据都是以关系的形式存在的。想象一张标准的 Excel 表，每一行代表着一条记录（元组），每一列代表着这条记录中的一个属性（字段）。在关系模型中，将这张表的结构称为关系模式 (relation schema), 表的值被称为关系。关系模式是对关系的描述。想象一张学生信息的表格，每个学生都要填写其姓名、学号、专业班级等信息，这些信息就是这个关系的属性；每个学生信息都是表格中的一行记录/元组。在学生填写表格之前。需要在表头给出每一列所需要填写的信息，表头中对每一列给出的约束就是关系模式。学生填写的一行行数据的集合是就是关系，关系是关系模式在某一时刻的内容。关系模式规定了关系的结构，SQL 即是被设计用来对这种结构进行查询的语言。

表1，学生表格的一种结构

姓名	学号	专业
张三	222016XXXX	软件工程

SQL 在成为结构化查询的事实标准后，其使用的范围已经不局限于 RDBMS 领域。比如在大数据领域中，几乎所有组件都离不开 Google 提出的“三驾马车”。在开源实现中，HDFS 和 MapReduce 这两驾马车组成了 Hadoop 的核心。但是 Hadoop 有非常陡峭的学习曲线，颇有“不食人间烟火”的高冷意味。既然 SQL 的通用性这么强，能否通过 SQL 来实现 Hadoop 的使用？于是 Hive 应运而生了。Hive 是一个数据仓库，它能够将 Hive SQL (一种类似 SQL 的语言[4])转换成 MapReduce 任务，从而提交到 Hadoop 集群上。

[1] https://en.wikipedia.org/wiki/Relational_model

[2] https://en.wikipedia.org/wiki/IBM_System_R

[3] 这里的数据库模式对应三级模式中的概念模式

[4] ISO 规定了 SQL 标准，各数据库采用不同了语法的方言

DEV Community: Zhou Yang

写给女朋友的 SQL 教程——数据模型

写给女朋友的 SQL 教程——历史和关系模型