7.2 数据规范化

本节介绍几个真实的数据组织案例，来讨论关系范例，再介绍一些实用的建模技术。理解SQL的关键是理解关系范例，并能把数据规范化到关系结构中。规范化是系统分析员的工作，因为他们要把业务数据建模为适合存储在关系表中的形式。这门学科需要研究多年，有许多人开发出了自己的方法和记号。

真实场景

这个向导使用几个假想的场景，包括两个Oracle提供的内置场景HR和OE，它们常常用作试题的上下文，来演示各种SQL概念。在讨论新概念时，下面的场景会进一步演化。

汽车代理公司 Sid经营一家汽车代理公司，需要一个系统跟踪她买卖的汽车。她发现，业务下降很多，希望进入21世纪时，能创建一个网站，为库存的汽车做广告。她需要一个系统记下她买卖的汽车和这些事务的细节。

地质岩心 地球岩心的样本由本地地理研究机构收集。为了确保科学、严谨，GeoCore的开发人员决定，系统必须跟踪准确的地理位置、岩心样本的元素成分和收集日期。

订单项 订单项(OE)场景由Oracle提供为一个样本，包含了一个假想商业系统的信息，该系统跟踪产品、客户和已下的销售订单。

人力资源 人力资源(HR)场景由Oracle提供为一个样本，记录了员工、部门、办公地址和与一般HR部门的工作相关的信息。

尽管前面描述的假想场景在复杂性方面各有不同，但它们有几个共同的特性，包括潜在的数据增长最终可能摧毁基于纸质或电子表格的数据组织方案，以及需要以高效的方式操纵(插入、更新和删除)和检索数据。获得高效的数据组织设计方案(也称为数据模型)的挑战，可以通过理解如何利用要组织的数据和几个基本的数据建模技术来克服。目标是在数据存储和数据访问之间维持优化的平衡，提供长期的、节约成本的下游优势。

7.2.1 数据建模

有各种形式化的数据建模方法，例如Zachman框架和Rational Unified Process，在根本上是为了提供系统的、基于标准的方法，来表示企业中的对象。有许多记号法可用于建立实体及其关系的模型。Oracle在其计算机辅助软件工程(CASE)工具和最近的SQL Developer采用了一种流行的记号法：鸟足记号法，本章将讨论它。其他记号法，例如关系模式记号法和UML，也很流行，但必须选择一种有效的、熟悉的记号法。

逻辑建模基于概念化的对象，它把感兴趣的对象看作实体，把它们之间的交互操作看作关系。实体-关系图有许多绘制方法，每种方法都有优缺点。下面简要讨论实体-关系图及其记号。

7.2.2 实体和关系

许多Oracle专业人士都采用一种架构，该架构在关系数据库建模时包含3个建模阶段。在构思逻辑模型时，一般在一张图中表示高级结构，该结构称为实体，它包含各种属性及其关系。逻辑模型中的实体通常表示为圆角矩形，它包含属性或标识符，有时表示为“o”符号。唯一标识实体实例的属性称为主键，有时表示为“#*”符号。表示属性的数据类型可以在这个阶段确定，但一般不反映在设计中。

接着要把逻辑模型转换为关系模型，为此要把实体转换为关系，通常称为表。这里的理念是实体的实例集统一建模为一个表。属性转换为表列。实体的每个实例反映为元组或数据行，每行都有不同的属性值或列值。表中的行数就是元组的基数。通常，属性在每行中都唯一地称为唯一键，一般选择唯一键作为主键(稍后讨论)。实体之间的关系常常建模为外键，本章后面会讨论。

关系模型中的关系通常表示为矩形。在这个阶段，一般有更多的细节涉及属性的数据类型，主键和外键属性也在关系模型中分别用P和F表示。最后，通过在关系数据库中实现设计，将关系模型设计为物理模型。

鸟足记号法常常用于表示逻辑和关系数据模型中的关系。实体之间的关系可以是如下之一，并在汽车代理公司场景中展开讨论：

● 1:N一对多

● N: 1 多对一

● 1:1 一对一

● M:N多对多

考虑前面介绍的Sid汽车代理公司。可以把可能的数据建模为一个实体，其中包括如下与汽车相关的属性：厂商、型号、引擎容量和颜色。也需要与汽车买卖相关的信息，所以可以添加购买日期、销售日期、销售员姓名、销售员SSN(社会安全号)、销售公司、买主的相同信息，最后是购买价格和销售价格，如图7-2所示。

图7-3显示了表中基于这个实体的样本事务数据，表CAR_DEALERSHIP包含3行、14列数据。创建表和填充数据的命令在本书后面讨论。现在，要注意几个更重要的地方。表把数据存储在行(也称为记录)中，每个数据元素都位于行和列的交叉处(也称为单元格)。表相当直观，很像电子表格。

表CAR_DEALERSHIP中的前两个记录包含如下信息：

● 一辆银色的梅赛德斯A160，引擎容量1600cc，属于SSN是12345的私人销售员Coda，该车由Sid's Cars的Sid在2013年6月1日以10 000美元的价格购买，Sid的SSN是12346。

● 一辆银色的梅赛德斯A160，引擎容量1600cc，隶属于Sid's Cars的Sid, Sid的SSN是12346，该车由Wags Auto的Wags在2013年8月1日以12 000美元的价格购买， Wags的SSN是12347。

注意数据的重复。每个记录都包含所买卖汽车的重复数据，以及买卖汽车的客户的重复数据。数据的不必要重复通常表示设计不良，因为这很浪费，而且常常需要不必要的维护。如果这种维护不仔细，这种设计会提高出错率(有时称为插入更新和删除异常)，降低数据的总体完整性。

数据库规范化是指使用多个实体及其之间的关系给数据建模，可以减少或完全消除数据冗余。理论上定义了许多类型的范式，但关系数据库设计主要考虑如下3种范式：

● 第一范式(1NF)解决了删除不必要重复的数据组的问题。图7-3中重复组的一个例子是第1、2行的前4列，其关于汽车的描述信息是重复的。可以定义一个新的Cars实体，使用Car ID主键属性，以及Make、Model、Engine Capacity和Color属性，唯一地标识特定的汽车。Car ID标识符在相关的Transactions实体中用以避免重复数据组。

● 第二范式从实体(1NF)中删除不依赖主键的属性。在前述的Cars实体中，Color属性不依赖特定的汽车。可以定义一个新的Colors实体，使用Color ID主键属性，唯一地标识特定的颜色。于是，Color ID就可以由Cars实体引用。

● 第三范式从2NF实体中删除所有独立的属性。汽车的买家和卖家都有一个唯一标识的社会安全号(SSN)。但他们的名字独立于SSN属性。所以可以定义一个新的Customers实体，使用Customer ID主键属性唯一地标识客户。其中，存储了独立的信息，例如客户的名字和公司。

注意在调整性能时，重复实体中的数据是故意的，也是可接受的。规范化多个实体中的数据时，如果多个实体实例化为必须连接在一起的多个表，Oracle服务器进程就需要从多个表中物理提取出数据，并在内存缓存中把它们连接起来，生成需要的结果集。查询或操纵规范化数据所需的额外I/O有时能证明，反规范化数据模型，会减少磁盘IO操作，因此提高性能。这在数据仓储(DWH)和决策支持系统(DSS)中很常见，但在OLTP系统中，是一个例外，而不是规则。

考虑图7-4中的逻辑数据模型。与汽车相关的数据建模为Cars实体。客户(买家和卖家)信息其实是相同的，所以客户建模为Customers实体，用Customer Type属性区分Purchasers和Sellers。买卖信息记录在Transactions实体中，而查找实体Colors跟踪不同的颜色。

把这个设计概念化为4个相互关联的实体，有几个优点。第一，数据已规范化，没有重复的数据。有多个实体。每个实体都跟踪一个结构，如Cars、Customers、Colors和Transactions，便于数据维护。可以添加新颜色，每种颜色都有唯一的代码。购买新车时，这些颜色在一个地方定义和维护，可以用一种颜色描述多辆汽车。还可以为轮胎、安全系统、跟踪设备或音视频插件定义实体，提高这个模型的复杂度。还可以改善为每辆汽车收集的细节，例如车辆标识号(VIN)和引擎号，或者每个客户的地址和银行信息，但这个假想的场景用于演示几个概念，显然没有进一步改进，就不能用于产品应用程序场景。

主键

图7-4中的每个实体都有一个主键属性，唯一地标识一个元组或数据行，在该主键属性名的旁边用“#*”表示。Car ID主键的每个值在实体中都是唯一的。多个行不能有相同的主键值。同样，Color ID唯一地标识Colors实体中的每一行，Customers实体中的Customer ID和Transactions实体中的Transaction ID也是如此。

关系

图7-4中链接各个实体的线称为关系。鸟足记号法可以表达实体间关系的基数——一对一、一对多、多对一和多对多。鸟足记号法用多个“足”明确演示了位于关系中“多”端的实体，而“一”端的实体只有一足。一对一关系中的属性是相同的，而多对多关系表示，实体A中的多个元组与实体B中的多个元组具备相同的属性值。一对一和多对多关系并不常见，有时表示关系模型中的缺陷。给关系实体建模时，一对多和多对一关系很常见。它们在主从关系中关联两个实体中的属性。例如，在Cars和Colors实体(其顺序很重要)之间的关系中， Cars实体中的许多记录都有相同的Color。许多汽车可能有相同的Color ID属性，这表示它们有相同的颜色。在这个关系中，Colors实体是主实体或查找实体，而Cars实体是从实体。在Cars和Colors实体之间的关系中，一个Color可以关联多个Car。所以，是一对多还是多对一关系仅考虑观察方向，它完全取决于观察该关系的方向。鸟足记号法表示的其他关系说明，一辆车可以买卖多次，这就是Cars和Transactions实体之间是一对多关系的原因，一个客户可以进行多次交易(例如买卖许多车)。

参照完整性和外键

这些关系引入了参照完整性的概念，该概念会确保关系中“一”端的实体中的属性A必定唯一，而“多”端实体中的属性B的值必定是属性A描述的唯一值集合中的一个，从而保证数据的一致性和完整性。属性B称为外键，因为它对属性A具有参照依赖性。考虑基于Color ID属性的Colors - Cars关系。参照完整性确保，Cars实体中每个元组的Color ID属性值都必须与Colors实体中的一个Color ID属性实例相同。这个保证对关系建模非常重要，因为根据Color ID属性连接Colors 和Cars实体，允许Colors.Color(这是句点记号)属性匹配Cars实体中的一个相关元组。Cars实体中的Color ID属性是与唯一键关联的外键，而唯一键就是Colors实体中的Color ID属性，Color ID属性正巧是主键。实体中的外键常常基于关联实体的主键，但这并不是什么硬性规则。

图7-4中的逻辑模型一般会演化为关系模型，其中带有更多的数据类型细节、更清晰的主键和外键，如图7-5所示。

关系模型可以设计为一个物理模型，其中创建了实际的表和其他数据库结构(参见本章后面)。图7-3中的样本数据传入物理模型中，而物理模型是从前面描述的关系模型中创建的，这些样本数据生成了4个数据集，如图7-6所示。

Transactions数据集中的前两行数据可以解释为：

● 与TX ID 100相关的交易描述了，Sid的汽车代理公司于2013年6月1日以10 000美元的价格从Customer ID为2的客户手里购买了Car ID为1的汽车。查找Customer ID为2的客户，会发现他从SSN为12345的私人卖家那里买了这辆车。而查找Car ID为1的汽车，会发现它是一辆2001-A160梅塞德斯，其Color ID为1，该颜色进一步解析为银色。

● 与TX ID 101相关的交易描述了，Car ID为1的汽车于2013年8月1日以12 000美元的价格卖给了Customer ID为4的客户，该客户可解析为Wags Auto中SSN为12347的Wags。

根据前面在单实体设计中提供的描述，把样本数据组织到4实体设计中，就不会丢失任何信息。但是，还可以得到更多好处。数据没有重复，简洁、精确，在买卖新汽车、新客户与Sid的汽车代理公司进行交易时，更便于数据的维护。

7.2.3 行和表

关系范例把数据建模为二维表。表由许多行组成，每一行又包含一组列。在表中，所有的行都具有相同的列结构，但在一些行中，一些列可以没有值。表的一个例子是员工列表，每个员工都用一行表示。行中的列可能是员工号、姓名和该员工所在的部门代码。当前没有分配给任何部门的员工，其部门代码列为空。再用另一个表表示部门，每个部门占据一行，其列是部门代码和部门名。

关系表遵循限制和定义数据的规则。在列的级别上，每一列都必须是某种数据类型，例如数字、日期时间或字符。字符数据类型是最常用的，因为它可以接受任何类型的数据。在行的级别上，每一行通常都必须有唯一标识特性。它可以是一列的值，例如前面例子中的员工号和部门号，该列值在不同的行上不能重复。还有定义表之间链接的规则，例如每个员工都必须分配一个部门代码，以匹配部门表中的一行。表7-1～表7-4是表格数据定义的例子(该数据和结构子集取自Oracle提供的样本模式SCOTT)。

看看表7-1和表7-2中DEPT和EMP表的布局，其二维结构非常清楚。每一行都有固定的长度，每一列也都有固定的长度(在必要时用空格填满)，行用换行符界定。表7-3显示了DEPT表中按DEPTNO排列的行，但这是可选的，而不是设计出来的：关系表对其行的顺序没有特别的要求。表7-4显示，部门号10有一个员工，部门号40没有员工。用关系模型修改数据通常非常高效。新员工可以追加到员工表中，修改行中的DEPTNO值，员工就可以从一个部门调到另一个部门。

考虑另一种结构，其中数据根据层次结构来存储。因技术的原因，层次模型在关系模型之前就开发出来了。在计算时代的早期，存储设备缺乏维护许多独立文件的能力，而许多关系表都需要这个能力。注意在Oracle数据库中，这个问题通过从逻辑存储(表)中抽象出物理存储(文件)的方式避免了。表和文件之间没有直接的连接，肯定不是一对一的映射。实际上，许多表可以存储在几个文件中。

层次结构在一个单元中存储所有相关的数据。例如，部门记录可能包含该部门的所有员工。层次范例可以非常快速、空间的利用非常高效。一次文件访问，就可以检索出满足查询所需的所有数据。前面列出的员工和部门可以按如下方式进行层次化的存储：

在这个布局例子中，行和列的长度都是可变的。列用逗号界定，行用换行符界定。如果查询沿着层次结构导航，数据检索通常非常高效。如果知道某员工的部门，就可以很快找到该员工。如果不知道，检索可能会比较慢。如果对数据的修改需要移动数据，就可能出问题。例如，要把员工7566, JONES从RESEARCH移动到SALES，就需要在数据库上付出极大的努力，因为要完成数据的移动，需要删除一行中的数据，再把它插入另一行。注意在这个例子中，部门可以没有员工(OPERATIONS部门)，但员工绝对不能没有部门。如果一个业务规则指定，所有员工必须在一个部门里，这就很好；但如果没有这个规则，就不那么妙了。

对于许多类型的数据，关系范例在许多方面的效率都很高，但它不适合所有应用程序。一般规则是，在给系统建模时，关系分析应是第一个采用的方法。只有证明关系分析不适合，才应转而采用非关系结构。关系模型非常高效的应用程序包括几乎所有OLTP和DSS系统。关系范例可以应对其硬件需求和开发应用程序所需的技能，但如果数据合适，这就是最通用的模型。例如，维护索引(索引维护表之间的链接)的要求可能会有问题，在索引内部和列所在的表中维护索引数据的多个副本所需的空间也可能有问题。尽管如此，关系设计在大多数情况下，仍是最优模型。

许多软件发布商都发布了遵循关系范例的数据库管理系统(但精确程度各不相同), Oracle不是唯一的一个。 IBM也许是第一家为其提供主要资源的公司，但其产品(后来集成到DB2中)多年来都没有移植到非IBM平台上。Microsoft SQL Server是另一个受运行平台限制的关系数据库。而Oracle数据库从其第1版开始，就总是可以移植到所有主流平台上。因此，Oracle占据了关系数据库管理系统(RDBMS)市场的领先位置。

注意一个术语：与习惯于使用Microsoft产品的人们讨论关系数据库时，可能会出现混淆。SQL是一种语言，SQL Server是一个数据库，但在Microsoft里，术语SQL常常用于指代上述两者。

7.2.4 创建演示模式

整本书中有许多SQL代码的示例。这些示例使用Oracle提供的两个演示模式：HR模式(这是模拟简单人力资源应用程序的样本数据)和OE模式(它模拟一个更复杂的订单录入应用程序)。

当创建数据库时可以创建这些模式；这是数据库配置助手(Database Configuration Assistant)提供的选项。如果它们不存在，稍后可以通过运行数据库Oracle Home中的某些脚本来创建它们。

7.2.5 用户和模式

在Oracle中，数据库用户是可以登录数据库的人。数据库模式是数据库中一个用户拥有的所有对象。这两个术语常常互换使用，因为用户和模式之间是一对一关系。注意尽管仍有CREATE SCHEMA命令，但它实际上并没有创建模式——只是创建模式中的对象的快捷方式。用CREATE USER命令创建用户时，模式最初创建为空。

模式用于存储对象。它们可以是数据对象，例如表，也可以是编程对象，例如PL/SQL存储过程。用户登录用于连接数据库，访问这些对象。默认情况下，用户可以访问其模式下的对象，不能访问其他用户的对象，但大多数应用程序修改了这个规则。一般情况下，一个模式用于存储其他用户访问的数据(即使这些用户不拥有这些数据，但有使用权即可访问)。实际上，很少有用户在自己的模式下拥有对象，也没有创建它们的权限。他们拥有的访问权(受到严格控制)，仅能访问另一个模式下的对象。这些对象由运行应用程序的所有用户使用，应用程序的数据存储在该模式中。相反，拥有数据存储模式的用户实际上永远不能登录，其模式的唯一作用是保存其他用户使用的数据。

数据对象不能独立于模式而存在。换言之，所有表都必须有一个拥有者。拥有者是表所在模式的用户。表的唯一标识符(或其他模式对象)是用户名，后跟对象名。两个同名(结构或内容可能不同)的表不能存在于一个模式中，但可以存在于不同的模式中。如果对象没有存在于它自己的模式中，要访问它，用户就必须用它所在的模式名限定其名称。例如， HR.EMPLOYEES是用户HR的模式中的表EMPLOYEES。除非有同义词，否则一个连接为HR的用户只能引用没有模式名限定的EMPLOYEES来访问它。同义词是一个结构，使对象能由其他用户访问，而无须把其模式名作为前缀。

7.2.6 HR和WEBSTORE模式

HR演示模式由7个表组成，通过主键到外键的关系链接在一起。图7-7以实体-关系图的形式显示了这些表之间的关系。

图7-7中显示的两种关系可能不是很好理解。首先，从EMPLOYEES到EMPLOYEES有多对一的关系。这就是所谓的自引用外键(self-referencing foreign key)。这意味着多名员工可能连接到一名员工，因为多名员工可能有一名经理，但经理也是一名员工。这种关系由列manager_id实现(作为employee_id的外键)，而employee_id是表的主键。

需要解释的第二种关系是DEPARTMENTS和EMPLOYEES之间的关系，它是双向的。一个部门对多名员工的关系说明在一个部门中可能有许多员工，EMPLOYEES的department_id列是相对于DEPARTMENTS的主键department_id列的外键。一名员工对多个部门的关系表示一名员工可能是几个部门的经理，由DEPARTMENTS中的manager_id列实现，它是相对于EMPLOYEES中的主键employee_id列的外键。

表7-5显示了HR模式中各表的列，使用前面讲述的符号来表示主键(#)、外键(\)以及列是可选的(o)还是强制的(*)。

这些表分别如下所示：

● REGIONS包含表示主要地区的行。

● COUNTRIES包含表示每个国家的行，可以将一个国家分配给一个地区。

● LOCATIONS包含单个地址，可将一个地址分配给一个国家。

● DEPARTMENTS包含表示各部门的行，可将一个部门分配给一个地址和一位经理(必须作为员工存在)。

● EMPLOYEES包含表示各员工的行，必须为每个员工分配一项工作，并且可将他分配给一个部门和一位经理，经理本身必须是员工。

● JOBS列出组织中所有可能的工作。多名员工可能有相同的工作。

● JOB_HISTORY列出员工以前从事的工作，由employee_id和start_date唯一标识；一名员工不可能同时从事两份工作。每个工作历史记录都针对一名员工，当时他只有一份工作，是一个部门中的成员。

该HR模式将在本书各章的许多练习和示例中使用，要求必须是可用的。

OE模式比HR模式复杂得多。表结构也复杂许多；它们包含的列定义为嵌套表、用户定义的数据类型，和XML数据类型。所使用的对象在使用它们时解释。

7.2.7 演示模式的创建

如果使用的数据库专门用于研究SQL考试，就应该已经创建了演示模式。在创建数据库时，DBCA把它们显示为选项。创建数据库后，模式可能需要解锁，设置其密码。默认情况下，账户是锁定的，这表示无法登录它。下面的命令在SQL *Plus或SQL Developer上执行，可以使用密码hr和oe登录为用户HR和OE：

        alter user hr account unlock identified by hr;
        alter user oe account unlock identified by oe;

只有用具有数据库管理员权限的用户身份(例如SYSTEM)连接数据库，才能执行这些alter user命令。

如果在创建数据库时没有创建模式，那么通过运行安装在数据库Oracle Home中的脚本就可以创建它们。如果这些脚本不存在，就可以从Oracle 上下载并安装Oracle Database Examples软件。必须以具有SYSDBA特权的用户身份从SQL*Plus或者SQL Developer运行这些脚本。在运行时，脚本会提示需要某些值。例如，在Linux系统上，先从操作系统提示符下启动SQL*Plus：

        sqlplus / as sysdba

这一连接有许多可选参数，但如果数据库与SQL*Plus 在同一台计算机上运行，上面的语句通常就会起作用。然后从SQL>提示符调用脚本：

        SQL> @? /demo/schema/human_resources/hr_main.sql

“? ”字符是一个变量，SQL*Plus会将其扩展成Oracle Home目录的路径。脚本会提示需要输入HR口令、默认表空间、临时表空间、SYS口令和脚本运行的日志文件的目标。默认表空间和临时表空间的典型值是USERS和TEMP，但必须已经创建这些值。脚本运行之后，就可以作为新的HR用户连接到数据库。要验证这一点，请运行下面的语句：

        SQL> show user;

你会发现当前用户作为HR连接到数据库；然后运行：

        SQL> select table_name from user_tables;

你会看到HR模式中7个表的列表。

要创建OE模式，可使用相同的过程，指定脚本：

        ?/demo/schema/order_entry/oe_main.sql

在Windows上创建模式的过程是相同的，只有路径界定符不同——大多数操作系统使用斜杠，而Windows使用反斜杠。所以Windows HR创建脚本的路径是：

        @? \demo\schema\human_resources\hr_main.sql

注意运行这些模式创建脚本，会先删除已有的模式。删除模式意味着，删除其中的所有项，再删除用户。这不应出问题，除非模式用于某些需要保留的开发工作。