关系数据库设计

第1章  关系数据库设计

SQL语言及采用SQL语言的关系数据库系统是计算机中重要的基础技术之一。在使用任何数据库之前，都必须设计好数据库，包括将要存储的数据的类型，数据之间的相互关系以及数据的组织形式。本章将对SQL语言进行简要的介绍，并详细地介绍如何进行数据库的设计。

本章主要内容：

  • SQL简史及功能特性
  • 关系数据模型
  • 关系操作
  • 数据库设计概述
  • 概念数据库设计
  • 逻辑数据库设计
  • 完整性
  • 数据库设计的其他问题
  • 回顾数据库设计

1.1     SQL简史及功能特性

与其他编程语言类似，SQL语言也有一个从简单到复杂的发展过程。同时，所提供的功能也逐步强大。本节中简单介绍一下SQL语言的历史与功能特性。

1.1.1  SQL简史

SQL语言的历史与关系数据库的发展密切联系在一起。关系数据库的概念是由IBM的研究员Dr.E.F.Codd提出的，他在1970年6月发表了一篇题为“大型共享数据银行的关系数据模型”的论文，在该文中描述了关于数据如何在表中存储和处理的数学理论。70年代中期，IBM San Jose研究实验室把SQL作为一种新型关系数据模型的数据库语言设计出来，其含义是结构化查询语言(Structed Query Language)。SQL语言不同于C、JAVA等其他程序设计语言，它属于一种描述性的语言，描述的是检索、删除或插入何种数据，而不是说明如何去执行它，SQL的出现使得建立关系数据库成为可能。它的正式发音是ess-cue-ell(按照American National Standards Institute)，但许多人发音为sequel。在本书中，SQL的发音为sequel。

七十年代后期，IBM准备开发一个关系数据库系统：SQL/DS RDBMS，其他开发商也迅速准备开发他们的RDBMS。但是低性能的关系型数据库管理系统制约了他们的商业生存能力。在早期关系系统中，关系模型的重要问题，“数学上的有效描述和直观的显示”没有有效的处理方法，导致了大型数据库管理系统速度慢又难以使用。直到大容量、高速度的计算机出现和开发了优秀的数据检索、数据存储和数据访问方法，这些方法有效地支持了关系系统的后台处理功能，才在一定程度上解决了这个问题。

1981年，IBM发布了它的第一个基于SQL的商业产品SQL/DS。在八十年代早期，Oracle，Relational Technology等其他几个开发商也纷纷发布了各自的基于SQL的关系型数据库管理系统。到1989年，市场上出现了许多种SQL或类似于SQL的数据库管理系统，这些系统运行在从个人计算机到处理多用户计算机等各种计算机上。在市场的推动下SQL得到了普及。同时IBM公司加快了对SQL的普及，并把DB2定位为九十年代数据管理的解决方案。

关系型数据库管理系统市场的激烈竞争导致出现了各种各样的SQL实现版本，每一种版本都代表了开发商在开发完整和高效关系模型表达语言方面所做的最大努力。1986年美国国家标准化学会(American National Standard Institute，ANSI)和国际标准化组织(International Standard Organization，ISO)发布的ANSI/ISO标准正式确认SQL的标准地位，这是SQL在市场中确定地位最重要的一点。并且在1992年与1999年先后进行了修订，现在的正式版本是SQL3版本。从九十年代初起，SQL性能便开始稳步提高，处理器速度明显加快，使SQL成为事务处理程序的有效工具，随着网络的发展，SQL成为客户机/服务器结构的关键部分，通过网络实现数据库的连接与使用变得越来越普遍，现在已经可以基于Web应用程序来实现远程访问数据库。

1.1.2  SQL的功能与特性

SQL是与DBMS进行通信的一种语言和工具，将DBMS的组件联系在一起。可以为用户提供强大的功能，使用户可以方便地进行数据库的管理以及数据的操作。通过控制DBMS，SQL能提供给用户如下的功能。

  • 数据定义： SQL能让用户自己定义所存储数据的结构，以及所存储数据各项之间的关系。这样用户就可以按自己的需要方便地进行数据的组织。
  • 数据更新： SQL为用户和应用程序提供了添加、删除、修改等数据更新操作，使用户或应用程序可以向数据库中增加新的数据，删除旧的数据以及修改已有数据，有效地支持了数据库数据的更新。
  • 数据查询： SQL使用户或应用程序可以从数据库中按照自己的需要查询数据并组织使用它们。其中SQL不仅支持简单条件的检索操作，而且支持子查询、查询的嵌套、视图等复杂的检索，为用户和应用程序提供了很好的数据查询方法。
  • 数据安全： SQL能对用户和应用程序访问数据、添加数据等操作的权限进行限制，以防止未经授权的访问，有效地保护数据库的安全。
  • 数据完整性： SQL使用户可以定义约束规则，定义的规则将存在数据库内部，可以防止因数据库更新过程中的意外事件或系统错误导致的数据库崩溃。
  • 数据库结构的修改： SQL使用户或应用程序可以修改数据库的结构。

可见，SQL为用户提供了完整的数据库操作、安全及维护的各种操作，为用户提供了很好的可操作性。

SQL是一种易于理解的语言，同时又是综合管理数据的工具。作为现在数据库市场普遍应用的语言，它具有以下一些特性。

  • 确定的标准： 美国国家标准化学会(ANSI)和国际标准化组织(ISO)在1986年制订了SQL的标准，并在1989年、1992年与1999年进行了3次扩展，使得所有生产商都可以按照统一标准实现对SQL的支持，SQL语言在数据库厂家之间具有广泛的适用性。虽然在不同厂家之间SQL语言的实现方式存在某些差异，但是通常情况下无论选择何种数据库平台，SQL语言都保持相同。这就是SQL的最大优点。
  • 软件提供商的独立性： 现在所有主流的DBMS软件提供商均提供对SQL的支持，SQL标准的确立使不同的生产商可以独立地进行DBMS软件的设计，一个基于SQL的数据库和使用该数据库的应用程序能很方便地从一种DBMS系统转向另一种DBMS系统。查询、报表生成器等数据库工具能在许多不同类型的SQL数据库中使用。
  • 跨计算机系统的移植性： 基于SQL的数据库产品能够在大型计算机、个人计算机等各种计算机上运行，也支持在不同的操作系统上运行，同时也可以通过网络进行访问和管理。
  • 各大公司的支持： SQL最初由IBM研究人员发明，已经成为IBM数据库产品DB2的战略性产品，同时也得到了Microsoft公司、Oracle公司等数据库市场各大软件公司的支持，这就保证了SQL今后的发展。
  • 程序化数据库访问： SQL语句既能用于交互式访问也能用于程序化访问，这样应用程序就具有很大的灵活性，可以将这两种方式结合起来设计更好的程序。
  • 应用程序传送的支持： 在数据库发展的最初时期，SQL支持基于主机的应用程序；随着计算机技术的发展，客户机/服务器体系结构随着PC革命的到来应运而生，SQL作为优化用户相互作用的前端计算机系统和专为数据库管理设计的后端系统的桥梁，使每个系统处于最佳工作状态；随着Internet和WWW的迅速发展，SQL作为Internet数据访问标准，在应用中充当了新的角色，建立了以SQL作为应用程序和数据库连接的标准；最新出现的Java也已经引入了SQL，SQL在最新的编程语言中也有了有效的应用。
  • 数据的多视化： 可以通过使用SQL产生不同的报表和视图将数据库中的数据按用户所需的角度显示在用户面前，供用户使用，具有很大的灵活性。同时，SQL的视图功能也能提高数据库的安全性并且能满足特定用户的需要。
  • 可扩展性和对象： 面向对象编程技术的兴起，数据库市场也面临了对象技术的

入，各个SQL数据库生产商也正在扩展和提高SQL对对象的支持。

SQL的以上特性，使得SQL在个人计算机、小型计算机和大型计算机上作为管理数据的标准工具出现，占据了大部分的市场。

1.1.3  SQL3简介

SQL标准是为了在各个开发商之间达成高度的一致性来达到提高市场上所有数据库产品的通用性而制定的一个国际标准，每次该标准的更新都为SQL添加了新的特征和新的命令与功能。最新的ANSI/ISO的SQL-3即SQL99标准的特色就是提供了一系列可以处理面向对象数据类型的扩展功能。

可以通过简单地比较SQL-2和SQL-3的语句种类来感受SQL-3的新特色。在SQL-2标准中，SQL语句可以大体分为：数据定义语句(Data Definition Language, DDL)、数据操作语句(Data Manipulation Language, DML)以及数据控制语句(Data Control Language, DCL)。其中， DDL包括了处理数据库元素的命令，如CREATE和DROP；DML提供了特定的数据操作命令，如SELECT、INSERT、UPDATE和DELETE；而DCL则包括了处理权限(Permission)的命令，如GRANT和REVOKE。

而SQL-3标准提出了一种新的语句分类方式，将它所现有的SQL语句分为7种核心的类型，而这些SQL-3标准的语句类试图使每一个类型和那些语句能更加准确并且更有逻辑性地结合在一起，在某种程度上也更易于理解。这样一套新的语句类型允许将来新产生的且不适于任何已有类型的语句能够合理地进行单独分类，因此这种做法非常有益于SQL将来的发展。SQL-3包含的语句类如下。

  • 连接语句： 使用该类语句可以开始和结束一个客户连接，如CONNECT语句和DISCONNECT语句。
  • 控制语句： 该类语句用来控制一组SQL语句的执行，如CALL语句和RETURN语句。
  • 数据语句： 使用该类语句可以直接对数据产生持续的作用，如SELECT语句、INSERT语句、UPDATE语句和DELETE语句。
  • 诊断语句： 该类语句可以提供诊断信息并指出异常或错误，如GET DIAGNOSTIC语句。
  • 模式语句： 该类语句可以操作数据库模式及其内部的元素，如ALTER语句、CREATE语句和DROP语句。
  • 会话语句： 使用该类语句可以在一次会话中控制默认操作和其他的参数，如SET语句。
  • 事务语句： 该语句用来设置一个事务处理的开始点和结束点，如COMMIT语句和ROLLBACK语句。

从SQL-3标准的这些语句类可以明显看出，与以前的SQL标准相比，SQL-3标准更适合对面向对象的数据类型进行处理。

除了上述的语句分类方式的变化，使新的方式更适合处理面向对象的数据，SQL-3标准还增加了一些新的数据库元素以增强SQL对面向对象数据类型的处理。这些新增数据库元素有角色、触发器和用户定义类型。角色是指定的一种或一组权限，是一个单独的成分，是一个对象。角色具有的分组权限功能为数据库系统的安全性管理提供了许多便利的条件；触发器是指当在指定的表上发生一个特定的操作事件时，将被调用的一个程序段；在以前的SQL标准中，所有的数据类型都是预定义的，但是，在SQL-3标准中新增的CREATE TYPE、ALTER TYPE和DROP TYPE语句应用了对象/关系概念，可以使用它们来进行用户定义类型(UDT)的操作。这3种新的数据元素将在本书的12.1.3小节中予以介绍。

之前简单讲述了SQL-3标准提供的一系列可以处理面向对象数据类型的扩展功能的特色，下面来了解一下SQL-3相对SQL-2的最重要改变—— 进一步阐述了SQL-2的一致性级别(level of conformance)。SQL-2标准定义了3类级别：Entry(入门)、Intermediate(中等)和Full(完全)，此标准的每一个较高的级别都包括了其较低一致性级别的所有特征，而开发商必须达到Entry级别才可以声明其产品符合ANSI/SQL标准。而SQL-3修订了关于一致性的基本级别，不再使用SQL-2的3个级别，而使用核心SQL-3标准(Core SQL-3)、增强SQL-3标准(Enhanced SQL-3)。其中，Core SQL-3标准包括SQL-2中的Entry级的特征集和其他级别中的一些特征以及其他一些全新的特征；而Enhanced SQL-3则对应于SQL-2的Intermediate和Full级的部分特征以及其他一些全新的特征。ANSI/SQL则要求开发商必须实现SQL-3的一致性级别的Core SQL-3级，只要是支持Core SQL-3一致性级别的数据库产品再加上SQL-3 9个附加特征包中的任意一个或多个就可以说是满足增强SQL-3标准。因为一致性级别只与开发商密切相关，不属于本书实用教程的讨论范围，所以书中不进行详细讨论。

除了上面讨论的SQL-3标准的特色，新的语句分类形式，新增的数据库元素和进一步阐述的SQL一致性级别以外，SQL-3还在多个细节上对SQL-2进行了改进。本书后面的章节中所有的内容都是基于SQL-3标准的，为了说明SQL-3与旧标准的不同，在这先举几个不同的例子，如下。

(1) 在SQL-3中，空值NULL的使用方式不同于旧标准。在SQL-3中，约束检验时，NULL的值是UNKNOWN而不是旧标准的TRUE或FALSE。

(2) 在SQL-2中，基于多表连接的视图和含有集合算符的查询的视图都不能作为INSERT、UPDATE和DELETE语句的操作对象，但是在SQL-3中可以对这几类视图进行有条件的更新操作，例如可以在两个表的UNION JOIN结果中进行这些数据的更新操作。举一个例子，假设有两个表，第一个表有5个字段，第二个表有6个字段，在它们的UNION JOIN结果中就有11列，下面进行如下操作：

  • INSERT一个新记录行

如果新行的前5列为空，后6列非空，则该操作去掉前面5个NULL，将剩余的数据存入第二个表中。

如果新行的前5列非空，后6列为空，则该操作去掉后面6个NULL，将剩余的数据存入第一个表中。

新行的前5列非空，后6列也有非空值，则该操作不能进行。

  • UPDATE操作

如果需要更新的行来自第一个表(后6列为空值)，则该操作修改第一个表中的对应行的数据。

如果需要更新的行来自第二个表(前5列为空值)，则该操作修改第二个表中的对应行的数据。

新行的前5列非空，后6列也有非空值，则该操作不能进行。

  • DELETE操作

如果需要删除的行来自第一个表(后6列为空值)，则该操作删除第一个表中的对应行。

如果需要删除的行来自第二个表(前5列为空值)，则该操作删除第二个表中的对应行。

新行的前5列非空，后6列也有非空值，则该操作不能进行。

(3) 下面举两条语句，在SQL-2中不合法，但是在SQL-3中是合法的。

SELECT语句中使用的ORDER BY子句后的表列存在，但是不在SELECT清单中：

SELECT name

           FROM Employee

           ORDER BY id

在SELECT清单和ORDER BY子句中同时使用未命名的表达式：

SELECT salary*12

           FROM Employee

           ORDER BY salary*12

1.2  关系数据模型

关系数据模型的设计是关系数据库设计中重要也是基础的一步。关系数据模型的设计主要包含两个方面：数据结构的设计以及键与外部键的定义。本节将介绍这两个方面设计的定义与应用。

1.2.1  数据结构

每一个系统都有其自己的数据结构。关系数据库管理系统组织并构造数据，以使数据能被用户或应用程序检索与更新。由DBMS提供的数据结构和访问方法被称为DBMS的数据模型。数据模型决定了DBMS的性能及其适应的范围。

在关系数据库系统中，关系是关系数据模型的核心。关系数据库系统中最主要的数据结构就是关系。在实际表示中，关系可以用一个表来直观地表示，通常表是以一种矩形数据行/列的形式表示。表的每一列表示关系的一个属性，每列的名字即为一个属性名，每一行表示一个记录代表一个物理实体。关系数据库中，所有的数据都是通过表来进行存储的，可以说如果没有表数据就无法进行存储和表示。

表1-1给出了一个关系模式“工作人员(名字，职员ID，生日，性别，工资)”的实例。表的每一行表示一个物理实体—— 工作人员。表中有6行，代表6个工作人员。例如，第一行记录的是工作人员“林志千”的信息。工作人员表的每一列代表一个工作人员的一个数据项，记录工作人员的一部分信息。例如，职员ID列用来存储每个工作人员的职员ID，生日列用来存储每个工作人员的生日。第一行的职员ID列记录了工作人员“林志千”的职员ID1001，生日列记录了工作人员“林志千”的生日1962年11月25号。第二行记录的是工作人员“李明”的信息。第二行的职员ID列记录了工作人员“李明”的职员ID1002，生日列记录了工作人员“李明”的生日1976年5月23号。

表1-1  关系模式实例

姓    名

职员ID

生    日

性    别

工    资

林志千

1001

1962-11-25

男

6000.00

李明

1002

1976-05-23

男

5000.00

李大平

1003

1978-12-20

男

3000.00

林国荣

1004

1977-06-14

男

4000.00

刘燕

2002

1971-12-26

女

3000.00

魏箐

3003

1977-05-17

女

4000.00

 

关系数据模型中数据结构的核心规则是：所有数据都必须在表中所有关系模型中呈现给用户。它提供了数据表达和存储之间的一级抽象概念，使数据具有独立性，无论是用哪种关系数据库产品，不论其采用的是什么物理存储方式，查询和处理数据的方式都是相同的。

具体来说，数据的独立性表现在两个重要的方面：物理独立性和逻辑独立性。

各开发商之间，甚至同一开发商的不同版本之间的物理存储体系结构都是不相同的。物理独立性是指用户所看到的数据与数据的物理存储是完全独立的，即可以改变或重新安排物理存储而不会影响到数据的使用和逻辑数据库的设计(逻辑数据库的设计将在1.6节中详细介绍)。

逻辑独立性指的是，可以改变表、行和列之间的相互关系，即对数据结构进行修改而不会削弱应用程序特别是查询的功能。

1.2.2  键与外部键

每个表表示的是现实世界中的数据集合，表中有多个列名，需要一个或一组属性来表示表中元组的惟一性，接下来将介绍两个基本概念。

  • 候选键：包含独立的标识数据库中每一行值的任意一个列或一组列。
  • 主键：独立的标识存储在表中的每个记录或潜在记录的一组列(一组中可能只包含

一个值)。

一个表中可能具有多个候选键，这时可以选择一个作为该表的主键，即主键是从候选键中选出，剩下的候选键称为备用键。候选键中的属性称为键属性，其他属性则称为非键属性。主键中的属性称为主属性，其他属性则称为非主属性。

判断一组值是否具有候选键的资格有两条严格的规则：

  • 表中任意两个记录在他们的由候选键组成的列的组合中都不具有相同的值，即惟一性规则(uniqueness property)。
  • 作为候选键的列的组合中，没有一个列的子集表现出惟一性，即不可简化性规则(irreducability property)。

例如，在表1-1中可以看列组{姓名，职员ID，生日}和列{职员ID}，二者均可独立地标识表中的每一行，都表现出惟一性；但是列{职员ID}显然是列组{姓名，职员ID，生日}的子集，即列组中的子集{职员ID}表现出惟一性，所以列组{姓名，职员ID，生日}不满足不可简化性，因此列组{姓名，职员ID，生日}不是候选键。在表“工作人员(姓名，职员ID，生日，住址，性别)”中，由于生日和姓名都有可能重复，所以{职员ID}是惟一的一个主键，“职员ID”是这个主键的主属性，表中其他的属性称为非主属性。

注意：

键必须满足惟一性和不可简化性规则！

下面介绍一下外部键的概念，假设X是表A中的一个属性集合，若X同时是另一个表B的主属性，则称X是表A关于表B的外部键。

例如，与表“工作人员(姓名，职员ID，生日，住址，性别，所属部门号)”相应的有一个表“部门(部门名称，部门号，部门所在地)”，在表“部门(部门名称，部门号，部门所在地)”中一般来说一家公司内各部门的部门号都是惟一的，因此可以把“部门号”作为一个主键，同时“部门号”是表“工作人员(姓名，职员ID，生日，住址，性别，所属部门号)”中的一个属性，所以“部门号”是表“工作人员(姓名，职员ID，生日，住址，性别，所属部门号)”关于表“部门(部门名称，部门号，部门所在地)”的外部键。

1.3  关 系 操 作

对所有的关系数据库来说，有大量的操作方式可以使用，这些实用的操作方式能让用户看到所需要的数据。

由前面介绍的物理独立性和数据独立性可以让用户不必担心数据的具体存储位置，也不必关心如何才能找到所需的数据。这些，数据库管理系统可以帮用户完成。SQL允许不指定任何关于数据存储位置和寻找方式的细节就可以表示需要的内容。

为了便于本节的介绍，下面就先给出关于SELECT语句的简单语法：

SELECT select_list

           FROM table_list

           WHERE search_conditions

在SELECT子句中指定要返回的表的属性，在FROM子句中指定从哪个表中检索数据，WHERE子句则指定检索条件。

下面介绍关系数据库中定义的3个具体的操作方式。

注意：

现在不必在意SQL语法的使用，关键是要理解所述操作的概念。

1.3.1  选择

选择操作是从表中返回特定行的集合。选择操作也可以称为限制，因为它限制了返回行集合的标准。限制条件只适合于被返回的行，不适合包括在查询结果中的列。要指定限制条件，可以将条件写在WHERE子句中。

例如，如果只需要返回有关于部门号小于5的部门的信息，可以使用下面的语句：

SELECT *

FROM Department

WHERE dnumber < 5

下面是选择结果：

Results

dnumber                dname                  mgrid                            mgrstrdate

————              ————            ————            ——————

1                          开发部          1001           2001-11-3

2                          市场部          2001           2002-3-1

3                          人事部          3001           2001-4-5

4                          技术部          4001           2001-1-15

 [4rows]

1.3.2  投影

投影操作限制了查询返回的列，允许用户列出所要查看的列。投影操作只允许返回表中各列的具体的子集，是通过将具体的列清单传递给SELECT语句来实现的，即在SELECT列表中列出需要查看的列的名称。

例如，要查看部门的部门号、部门负责人ID号、部门负责人开始工作时间这3个列的信息，可以使用投影操作：

SELECT dname,  mgrid,  mgrstrdate

           FROM Department

下面是投影结果：

Results

dnumber          mgrid            mgrstrdate

————          ————         ——————

1                 1001             2001-11-3

2                 2001             2002-3-1

3                 3001             2001-4-5

4                 4001             2001-1-15

5                 5001             2002-6-5

6                 6001             2002-3-10

[6rows]

用户可以同时使用选择和投影来查询所要查看的表的列和行。选择用来限制返回的记录数目，而投影用来列出要查看的具体的字段。

例如，只想知道部门号小于5的部门的负责人的职员ID号和开始工作时间，可以同时使用选择和投影操作：

SELECT mgrid,  mgrstrdate

           FROM Department

           WHERE dnumber < 5

 

Results

mgrid              mgrstrdate

————           ——————

1001               2001-11-3

2001               2002-3-1

3001               2001-4-5

4001               2001-1-15

[4rows]

1.3.3  连接

连接操作是结合多个表的关系操作，为了查询特定数据而将两个或更多个表连接起来形成新的报表或视图，以便用户能够比较和对比数据库不同表中的数据，为用户提供了更多的功能和灵活性。可以通过连接来发现数据之间的关系，找出数据之间的联系，而不仅仅局限于数据库中已有的一些关系。

连接操作是通过在WHERE子句中设定特定的条件，或者使用JOIN关键字通过表之间相关的列来连接不同表中的数据，用户可以在其中设定想指定的条件。详细的连接操作的实现将在第7章中进行介绍。

例如，想知道各部门的部门负责人的具体情况，每个工作人员的情况都存储在Employee表中，Employee表中也包含了工作人员的职员ID号，但是谁是部门负责人并没有记录在Employee表中，而是记录在Department表中，如图1-1所示。

 

图 1-1  在Employee表和Department表中的列

在Employee表中的dno就是工作人员所在部门号，而Department表中也记录了部门的部门号dnumber，所以可以用连接这两个表来实现用户想实现的操作，如图1-2所示。

 

图 1-2  在Employee表和Department表中的共享列

由于共享列的存在，可以使用下面的语句来实现上面的连接操作：

SELECT name, sex, id, salary, dno

           FROM Employee,  Department

           WHERE Employee.dno = Department.dnumber

FROM子句表示要连接的两个表，WHERE子句指明这些表中的行在两个表中部门号相等时进行连接。

下面是连接结果：

Results

name            sex      id           salary              dno

—————      ———   ——        ————         ———

林志千          男         1001       6000.00          1

陈广海          男         2001       5000.00          2

张宇            男         3001       5000.00          3

张峰            男         4001       7000.00          4

李志深          男         5001       5000.00          5

魏成            男         6001       5000.00          6

[6rows]

读者可能有疑问，为什么不首先将这些列放置在同一个表中而要使用连接操作呢？这是因为一致性(即最小冗余性)、方便性及性能的要求，表中的列数必须有限制。在下面的章节中将介绍表中列的确定准则。

前面说过可以将选择和投影操作结合起来使用。同样，也可以将选择、投影和连接操作结合起来使用。

例如，只想知道部门号小于5的部门负责人的名字和工资，就必须将3个操作结合起来，可以使用下面的语句：

SELECT name, salary

           FROM Employee, Department

           WHERE Employee.dno = Department.dnumber

           AND Employee.dno < 5

下面是连接结果：

Results

name              salary 

————          —————

林志千             6000.00

陈广海             5000.00

张宇               5000.00

张峰               7000.00

[4rows]

1.4  数据库设计概述

在详细介绍如何进行数据库的设计之前，我们先介绍一下有关数据库模型准则及数据库设计过程，使读者对数据库设计有一个大致的了解。

1.4.1  数据库模型准则

E.F.Codd博士于1970年在一篇名为“一种存储大型共享数据的关系模型”的历史性论文中提出了数据库模型定义的12条准则。

  • 信息准则： 关系型数据库(包括表和列名)中所有的信息都必须清楚地用表中的数值来表示。
  • 确保访问准则： 必须保证关系数据库中的每一个数值都可用表名、主键和列名的组合来访问。
  • 空值准则： DBMS对空值(表示未知或不可使用数据，不同于其他任何一种数据类型，如字符串、数字等数据类型)应提供系统支持。空值与默认值不同，它独立于任何数据类型。
  • 数据字典准则： 应支持主动的、在线的关系型数据字典。在逻辑上，数据库的描述及其内容都被表示为表的形式，并能用数据库语言进行查询。
  • 广泛的数据子语言准则： 具有统一的数据子语言，同时至少有一种支持语言，该语言应具有严格、统一的语法格式，而且必须支持数据定义、视图定义、数据操作、完整性规则、授权和事务处理。
  • 视图更新准则： 所有理论上可更新的视图，在实际操作中也可以被系统更新。
  • 集合准则： DBMS应该不仅仅支持集合级上的检索，而且支持集合级上的插入操作、更新操作与删除操作。
  • 物理数据准则： 物理数据必须具有独立性，即当数据的存储结构或数据的物理存取方法改变时，应用程序和其他特殊程序在逻辑上应保持不受影响。
  • 逻辑数据准则： 逻辑数据应具有独立性，即当表的结构改变时，应用程序和其他特殊程序应尽可能地保持在逻辑上不受影响。
  • 数据完整性准则： 数据库语言必须能够定义完整性规则，这些规则必须存储在联机数据字典中，不能被忽略。
  • 分布准则： 应支持分布的独立性，当首次引入分布式数据或数据重新分布时，应用程序和其他特殊程序在逻辑上应不受影响。
  • 无损害准则： 绝不能发生用一种低级的语言绕过数据库语言定义的完整性规则的事件。

在实现关系数据库模型时必须满足上述12条标准。

准则一基本上是有关数据库信息的定义。

准则二强调表中主键的重要性，要求能通过表名正确访问表，通过列名访问列，而主键值能区分表中的行。

准则三强调空值的处理。

准则四要求数据库能自我描述，即数据库应该包含一定的系统表(数据字典)，系统表可以描述数据库自身的结构。

准则五要求数据库支持关系数据语言，如SQL。通过SQL语言实现DBMS要求的数据库的创建、数据的输入与更新等。

准则六支持视图的使用，在实际使用过程中，视图提供了很大的灵活性，可以用来方便地完成特殊功能。

准则七强调对集合的支持，能进行集合运算：交、并、差等。

准则八与准则九强调了应用程序与物理数据、逻辑数据之间的独立性，以保证低层数据库结构发生改变时，不会影响应用程序对数据的操作。

准则十强调数据库的完整性，要求保证添加和修改数据时确保数据及数据之间的正确性。

准则十一要求能对分布数据进行处理。

准则十二限制了其他访问数据库的方法，以保证数据库结构不受破坏。

1.4.2  数据库设计过程

数据库的设计中有许许多多需要考虑的因素，如数据库的背景、应用环境等方面都需要有深入的了解，只有一个对所有这些因素都很了解的数据库设计专家，他设计的数据库才能易于使用和维护，并且具有高效和一致的特征。虽然这样只对数据库设计过程有一个概要的了解，但是仍然有助于读者了解和掌握SQL，使读者可以很好地分析数据间的相互关系，在使用SQL进行报表的生成、子查询及视图等操作时，可以更好地进行操作。

在开始介绍设计方法之前，先介绍好的数据库及差的数据库的一些品质，这将有助于读者更好地掌握数据库的设计。

一般来说，好的数据库应该满足以下条件。

  • 便于检索所需要的数据： 在大型的数据库中，由于表的增加，多表中的数据很难读取和理解，数据之间的关系无法在计算机屏幕或输出页中完整地显示，因此好的数据库应该便于用户检索所需要的数据。
  • 具有较高的完整性、数据更新的一致性： 好的数据库应该能防止不一致信息的引入，避免因各种问题而出现的数据的不完整和数据之间的不一致。
  • 使系统具有尽可能良好的性能。

当然，这3种品质之间也会有一些妥协。

例如，将数据分割存储在多个表中对数据完整性的支持较好，但对数据进行检索或更新时需要进行更多的操作，不仅麻烦，而且对性能有害；若将数据都存储在一个表中，虽然进行数据检索或更新变得比较方便，性能也较好，但是又会降低数据库的完整性。

因此在设计数据库时应该平衡它们中的每一项，使之适合应用程序。当然好数据库最根本就是能提供用户高满意度而且能可靠地满足要求。

相对而言，设计得不好的数据库或多或少与好的数据库的品质相反，在数据的检索、数据的完整性、数据更新的一致性及良好的性能上都具有较大的缺陷。还有一些具体的特征可以帮助用户判断什么是设计得不好的数据库：

  • 需要多次输入相同的数据，或需要输入多余的数据。
  • 返回不正确的查询结果。
  • 数据之间的关系难以确定。
  • 表或列的名称不明确。

在数据库的设计中，应尽量保证设计的数据库具有好的特征，同时应尽量避免具有上述一些不好的特征。

下面将详细地介绍数据库的设计过程。

(1) 信息的收集：研究和考虑所要建立的数据库的信息环境，对数据库应用领域中各种信息要求和操作要求进行详细地分析，了解应用领域中数据项、数据项之间的关系和所有的数据操作的详细要求，了解哪些因素对响应时间、可用性和可靠性有较大的影响等各方面的因素。

这些就需要设计人员与相关人员进行各种交流，了解数据库需要完成的功能，数据项的定义等。例如，以本书使用的简单的数据库示例为例，设计人员需要与有关人员进行交流以确定部门与工程、工作人员和部门、工作人员与工程之间的关系，同时确定每个数据项，如工作人员，应该包含哪些数据等，只有完成这些必要的信息收集后，数据库的设计才能顺利地进行下去。

(2) 确定数据：这是数据库设计最基本而且最重要的工作，在收集到所需的全部相关信息后，将所有找到的数据的类型和这些类型的属性进行明确的定义，同时需要仔细地考虑属性与数据类型的关系，应该放置于哪个数据类型中。

(3) 建立实体-关系模型：按照定义的数据类型和属性创建实体和实体属性列表。实体形成表，如“工作人员”就是一个实体，属性则为表中的列，如对应于实体“工作人员”属性包含“姓名”、“职员ID”等。

在定义完实体和属性后，就要确保每个实体都由一个或一组属性惟一地标识表中的任何记录，即确定每个表的主键。例如在“工作人员”表中，属性“职员ID”就可以惟一地标识“工作人员”中的任何记录，因此将属性“职员ID”确定为该表的主键。

接下来就应该确定实体之间的关系，确定是一对一、一对多还是多对多的关系。例如一个部门有多个工作人员，但每个工作人员只属于一个部门，因此部门与工作人员之间是一对多关系。一般来说是根据现实中的情况来确定实体之间的关系，从而形成实体-关系模型。有关内容将在本章的1.5节中进行详细介绍。

(4) 进行规范化：按照规范化准则对数据进行整体分析，以找到逻辑上的错误，更正任何违背了规范化形式的做法，使数据符合规范化的要求。

(5) 编写组建数据库的SQL代码，同时使用查询等操作进行一些检测，有关测试的具体内容将在第11章中介绍。

(6) 根据测试结果对已完成的数据库进行检查，在觉得有问题或需要完善的地方进行必要修改和完善。

上面介绍的是数据库设计的一般过程，如果读者有更好的方法，可以按照自己的方法。记住：方法并不是最主要的，重要的是结果。

1.5  概念数据库设计

概念数据库的设计是进行具体数据库设计的第一步，概念数据库设计的好坏直接影响到逻辑数据库的设计，影响到整个数据库的好坏。

1.5.1  概述

概念数据库的设计应该极易于转换为逻辑数据库模式，又容易被用户所理解。概念数据库设计中最主要的就是采用实体-关系数据模型来确定数据库的结构。

数据是表达信息的一种重要的量化符号，是信息存在的一种重要形式。数据模型则是数据特征的一种抽象。它描述的是数据的共性，而不是描述个别的数据。一般来说，数据模型包含两方面内容。

(1) 数据的静态特性：主要包括数据的基本结构、数据间的关系和数据之间的相互约束等特性。

(2) 数据的动态特性：主要包括对数据进行操作的方法。

在数据库系统设计中，建立反映客观信息的数据模型，是设计中最为重要的，也最基本的步骤之一。数据模型是连接客观信息世界和数据库系统数据逻辑组织的桥梁，也是数据库设计人员与用户之间进行交流的共同基础。概念数据库中采用的实体-关系模型，与传统的数据模型有所不同。实体-关系模型是面向现实世界，而不是面向实现方法的，它主要是用于描述现实信息世界中数据的静态特性。而不涉及数据的处理过程。但由于它简单易学，且使用方便，因而在数据库系统应用的设计中，得到了广泛应用。

实体-关系模型可以用来说明数据库中实体的等级和属性。以下是实体-关系模型中的重要标识：

  • 在数据库中存在的实体
  • 实体的属性
  • 实体之间的关系

下面就介绍一下实体-关系数据模型中的3个基本要素，即实体、关系和属性的有关基本知识。

1.5.2  实体

实体是实体-关系模型的基本对象，是现实世界中各种事物的抽象。凡是可以相互区别开并可以被识别的事、物、概念等对象均可认为是实体。在本书示例的简单的Company数据库中，基本的实体列表如下：

  • 工作人员
  • 部门
  • 工程项目
  • 工程项目接收方

在绘制实体-关系图(E-R图)时，实体出现在矩形中。如图1-3所示。

 

图1-3   表示实体的E-R图

一般来说，每个实体都相当于数据库中的一个表。

上面介绍的实体都是强实体，每个实体都有自己的键。但是在实际领域中，经常存在一些实体，它们没有自己的键，这样的实体称为弱实体。弱实体中不同的记录有可能完全相同，难以区别，这些值依赖于另一个实体(强实体)的意义，必须与强实体联合使用。有关弱实体的具体问题，本书不作具体介绍，有兴趣的读者可以参考相关数据库设计书籍。

在创建了实体之后，就可以标识各个实体的属性了。

1.5.3  属性

每个实体都有一组特征或性质，称为实体的属性。实体的属性值是数据库中存储的主要数据，一个属性实际上相当于表中的一个列。

下面来看看“工作人员”(Employee)实体。这个实体具有哪些属性呢？对每个工作人员来说，都具有名字(name)、生日(birthday)、住址(address)、目前所在城市(city)、性别(sex)。同时在公司中每个工作人员还有职员ID号(id)、工资(salary)和所属部门号(dno)等属性。所以关于“工作人员”Employee的属性如下：

  • 名字(name)
  • 生日(birthday)
  • 住址(address)
  • 目前所在城市(city)
  • 性别(sex)
  • 职员ID号(id)
  • 工资(salary)
  • 所属部门号(dno)

实体“部门”Department包含的属性如下：

  • 部门名称(dname)
  • 部门号(dnumber)
  • 部门领导ID号( mgrid)
  • 部门领导开始领导工作日期(mgrstrdate)

实体“项目”Project包含的属性如下：

  • 工程名称(pname)
  • 工程项目号(pnumber)
  • 工程项目负责人ID号(pmgrid)
  • 工程预算(budget)
  • 工程预计总收入(gross)
  • 工程开始时间(pstrdate)
  • 工程预计结束时间(penddate)

实体“项目接收方”Paccepter包含的属性如下：

  • 接收方名称(accepter)
  • 工程项目号(pnumber)
  • 接收方所在城市(city)
  • 预计接收时间(accedate)

在绘制E-R图中，属性由椭圆包围，在属性和它所属的实体间使用直线进行连接，以实体Department为例进行示例，如图1-4所示。

 

图1-4  包含属性的Department的E-R图

对于每个实体，都有其确定的主属性(实体中的主属性实际上相当于表中的主键)。以Employee实体为例，在其属性中可以以name和birthday的组合作为主属性，即表Employee的主键。因为在现实生活中，一家公司内部有两个员工同名而且同时生日的情况极其少见。这个组合是惟一可以确定实体Employee中的每个记录的。但是仅用职员ID就可以惟一地确定实体Employee的每个记录，因为在一家公司中每个职员的ID都是不相同的，也可以以职员ID作为主属性，且肯定不可能出现重复。在有更好的主属性可选择的情况下，应该尽量选择更好的主属性，最好是创建一个单独的属性作为主属性。

在实体Department中可以选择dnumber作为主属性，而在实体Project中，每个项目的名称肯定是不会重复的，因此选择pnumber作为主属性，在实体Paccepter中也选择pnumber作为主属性。

在绘制E-R图中，主属性在属性下加下划线来说明。以实体Department为例进行示例，如图1-5所示。

 

图1-5  定义了主属性的Department的E-R图

有关SQL中主键定义的具体实现，将在第3章中介绍。

注意：

在数据库设计中，选择和设置列作为主键是一个关键步骤。

1.5.4  一对一关系

在数据库中，一个表与另一个表之间存在一对一关系是可能的，通常情况下，当一对一关系在两个表中存在时，为了简便起见一般将两个表合成一个表。

例如，数据库中详细记录了每个员工的过去表现，以往工作经历等信息，可以将其也放在Employee表中，但是这些信息不常使用，可以将其存放在一个单独的表中，以便在进行普通的查询时不必访问这些信息，以提高查询速度。这样这两个表就具有了一对一的关系。

注意：

一般来说，当对数据库的设计不是非常熟练时，应当尽量避免使用一对一关系。

1.5.5  一对多关系

数据库的数据之间往往具有较多的联系，一对多关系也是比较多的一种。在上面的设计中已经为Company数据库设计了4个实体，每个实体的属性都已确定，主属性也进行了标识。

但是，一个数据库中仅仅有这4个实体是不够的，为了实际应用必需发现实体之间的联系，表示出数据间的重要关系。

下面将给出一对多关系的定义，再看一看Company数据库中存在的一对多关系。

在一个表中一个记录可以与另一个表的若干记录相对应时就说两个表之间存在一对多关系。数据间的一对多关系通常简记为1：N。

在Company数据库中，部门与工作人员，项目与工作人员之间都具有一对多关系。每个部门都包含多个工作人员，每项工程也由多个工作人员参加。以工程实体与工作人员实体为例来介绍一对多关系的实体-关系图的画法，如图1-6所示。

 

图1-6  一对多关系的E-R图

1.5.6  多对多关系

一对一和一对多关系并不足以涵盖数据库中的所有关系，例如部门与项目间并不一定仅仅是一对一或一对多关系，一个部门可以同时进行一个或多个项目，而一个项目可能也需要多个部门的配合才能很好地完成。也就是说，部门与项目间存在着多对多的关系。数据之间的多对多关系通常简记为N：N。图1-7显示了多对多关系的实体-关系图。

 

图1-7  多对多关系的E-R图

1.5.7  E-R图的绘制

实体-关系图是表现实体-关系模型的图形工具，简称E-R图。首先介绍与E-R模型各种成分相对应的基本图形符号，然后以Company数据库为例，给出一个完整的数据库的E-R图。图1-8给出了在E-R图中使用的各种元素的图形符号。图1-9给出了Company数据库的E-R图。

 

图1-8  E-R图中使用的各种元素的图形符号

 

图1-9  Company数据库的完整E-R图

在Company数据库中：

实体之间的关系以菱形表示，关系中各方面的表通过直线与菱形中的关系名称相连接，例如项目与部门之间的关系标为p_d；实体与关系相连的直线旁都根据关系的属性标注有“1”或“N”，例如工作人员与部门之间为多对一关系，因此在工作人员与关系e_d的连线旁标记“N”，而在部门与关系e_d的连线旁标记“1”。

在建立了实体-关系模型后数据库的设计就剩下最后的一个步骤了。E-R图为读者的数据库提供了一个不错的蓝图，设计过程的最后一步使用规范化对数据进行处理获得对数据库完整性的控制权。

1.5.8  回顾

在进入下一节之前，先回顾一下概念数据库的设计，其中主要是实体-关系模型的建立。

简要总结一下实体-关系模型建立的步骤：

(1) 对需求进行分析，从而确定系统中所包含的实体。

(2) 分析得出每个实体所具有的属性。

(3) 保证每个实体有一个主属性，该主属性可以是实体的一个属性或多个属性的组合。主属性必须能惟一地描述每个记录。

(4) 确定实体之间的关系。

经过这些步骤后，读者就可以绘制出E-R图。之后可以再看看数据库的需要，判断是否获取了所需的信息，是否有遗漏信息等，读者可以再对E-R图进行修改，添加或删除实体与属性。

1.6  逻辑数据库设计

在逻辑数据库的设计中主要任务是将概念数据库转化为逻辑数据库及关系模式的规范化。本节中也将主要介绍这两项内容，有关逻辑数据库设计的其他内容，有兴趣的读者可以自己参考一些资料。

1.6.1  将实体和关系转化为关系模式

实体-关系模型建立后，就可以将实体、实体间的关系等模型结构转变为关系模式，即生成数据库中的表，并确定表的列。下述讨论由实体-关系模型生成表的方法。

1. 实体转化为表

对实体-关系模型中每个实体，建立一个表，表的列就是实体所具有的属性，主属性就作为表的主键，在表中也用下划线标记表的键。如上面建立的实体-关系模型中，所有实体转化为表，如图1-10所示。

 

图1-10  实体转化成的表

2. 一对一关系的变换

一对一关系是为了加快查询时的速度，而将一个表中的列分散存储到两个表中。一对一关系的变换比较简单，不需要再建立一个表，直接将一个表的主键作为外部键添入另一个表中即可。

例如，工程实体和工程接收方实体间具有一对一关系，一项工程对应一个接收方，因此只需将表Project的主键dnumber加入表Paccepter中即可。不过，在设计实体时，按照实际情况，已经考虑到实体Paccepter中应该含有记录标识工程的属性，在表Paccepter中已经包含了dnumber列，这时就不需要再次加入了。

3. 一对多关系的变换

一对多关系的变换也不需要再建立一个表，设表A与表B之间是1：N关系，则变换时将表A的主键作为外部键添加到表B中即可。

例如，在Company数据库中，工程实体与工作人员实体之间是一对多关系，而在实体属性设计时并没有考虑到这个关系，所以在工作人员实体中并没有记录工作人员现在参与的工程项目号，在关系变换中就应该把这个属性加入到实体工作人员中。因此表Employee中增加了一个列pno。

可能读者会有一个疑问：既然每项工程都有多个工作人员参与，为什么不能在表Project中增加一个workid来记录每项工程的参与人员呢？假设在表Project中增加了workid属性，在表Employee中删除pno属性，那么表Project与表Employee之间通过工作人员的编号联系在一起，如图1-11所示。

 

图1-11  表Employee和表Project之间的联系

这样看起来也可以很好地实现查找参与某项工程的所有工作人员的操作。但是这样会产生一个严重的问题—— 数据冗余。这样设计后，对每个工程，都必须在表Project中记录所有参与的工作人员，而这时把pname、pmgrid、budget等其他的属性都进行了重复的存储。如果改变了其中的一个属性，如pmgrid，那么必须对所有参与该工程项目的每个工作人员的记录都进行更改，大大增加了修改的复杂性。数据冗余引起的后果是非常严重的，很容易导致错误的产生。

所以最好使用表Employee中的pno与表Project中的主键pnumber进行联系如图1-12所示，这时属性pno为相对于表Project中主键pnumber的外部键。可以通过连接的逻辑操作来连接两个表，以查询参与某项工程的工作人员。

 

图1-12  通过外部键联系

采用主键与外部键进行联系之后，就不存在上面数据更新复杂的情况了。例如，修改某个项目的负责人，可以在Project表中修改pmgrid列的值即可，Project表中没有存储每个参与该项目的工作人员的信息，就不必修改这么多记录了。这时，如果需要查找参与项目的每个工作人员的ID列的值，就需要通过Project表中主键pnumber与对应Employee表中外部键pno进行连接，以查找符合条件的工作人员记录。可见，在处理一对多关系时，一般应通过一对主键和外部键来表示。

当然采用主键和外部键的处理方式也会出现一些其他问题。例如，如果表Project中的一个记录的删除导致一个pnumber的删除，那么表Employee中参与该项工程的工作人员记录中的pno列应该怎么处理呢？从逻辑上说，外部键pno指向一个不存在的主键值是不允许的，须要保持数据更新的一致性。而在现实操作中这种情况是很普遍的，要如何进行处理呢？这取决于用户的业务规则，必须在设计中包含主键与外部键一致性的考虑。例如，在从表Project中删除一个记录时，系统应能自动在表Employee中进行相应的修改，可以将表Employee中相应记录的pno修改为NULL。

4. 多对多关系的变换

在一对多关系的处理中，采用了主键与外部键的方法。多对多关系比一对多关系复杂得多，应该采用什么方法进行处理呢？数据库设计中必须创建一个称为连接表(connecting table)的特殊表来表达两个实体之间的关系，连接表的列包含其连接的两个表的主键列，同时包含一些可能在关系中存在的特定的列。例如，每个参加工程的部门都发给奖金，参加不同工程的不同部门的奖金不同，而参加同一项工程的部门之间得到的奖金也不相同，奖金既不属于部门表，也不属于工程表，那么奖金就属于连接表的属性。也就是说，在Company数据库中，仅仅有表Department、表Project是不够的，还要使用一个表来表示它们之间的关系，如图1-13所示。

 

图1-13  多对多关系的连接表

注意：

创建的连接表dep_pro代表了表Department和表Project之间多对多的关系，也是一个单独的表，但是它本质上是一种连接而不是一个单独的实体！

当数据库Company的用户想知道某个项目是由哪些部门开发时，就可以编写一个以表Department和表Project为基础的连接查询操作，使用连接表进行查询。

与一对多关系类似，连接表中也存在键与外部键。对于连接表dep_pro来说，其属性dnumber是表Department的主键，所以连接表中的属性dnumber是表Department的外部键。属性pnumber是表Project的主键，所以连接表中的属性dnumber是表Project的外部键。

读者可能有这样的疑问：既然连接表也是一个表，那么连接表中的主键是什么呢？由于表Department和表Project之间是多对多的关系，所以在连接表dep_pro的记录中属性dnimber与属性pnumber都将可能有重复值出现。因此属性dnimber与属性pnumber都不可能单独作为连接表的主键。但是，在连接表dep_pro中，属性dnimber与属性pnumber二者的组合是惟一的，也就是说，对连接表dep_pro任何一个记录中，属性dnimber与属性pnumber的值均相等的情况是不会出现的。所以，可以用属性dnimber与属性pnumber的组合来作为连接表dep_pro的主键。

注意：

除非用户能够绝对肯定两个实体之间的关系不是一对一或一对多关系，否则误认为二者之间是多对多关系而创建连接表就会误入歧途。对数据库设计的初学者来说，多对多关系尤其要注意！

在完成这些变换后，就形成了一个数据库基本的表和表中所含的列，在Company数据库中就包含了5个表：表Employee、表Project、表Department、表Paccepter和表dep_pro。其中表dep_pro是一个连接表。

1.6.2  函数依赖

在介绍关系模式的规范化之前，先介绍一下函数依赖的概念。

函数依赖在现实生活中普遍地存在，也是数据库设计中一个重要的问题。比如描述公司中一个工作人员的关系，可以有职员ID(id)、名字(name)、所属部门号(dnumber)等属性。由于公司中一个职员ID(id)只对应一个工作人员，一个工作人员只属于一个部门。因此，当职员ID(id)的值确定之后，名字(name)及所属部门号(dnumber)的值也就被惟一地确定了。这说明名字(name)和部门号(dnumber)都对职员ID(id)具有函数依赖。这就是函数依赖的定义。对于第一个属性集合在实际中的每一组可能值来说，如果有且仅有一组值依赖于第二个属性集合中的项目，那么就是该集合中的值对另一个集合中的值具有函数依赖。

属性之间的这种依赖关系类似于数学中的函数。因此，可以说职员ID(id)函数的确定靠名字(name)和所属部门号(dnumber)，或者说名字(name)和所属部门号(dnumber)函数得依赖于职员ID(id)。有一个专门的符号“→”来表示函数依赖。上述的函数依赖可以表示为：

{id } → {name}, {id} → {dnumber}

由于函数依赖关系的存在，使得若数据库设计不合理会出现各种异常。例如，建立了一个工作人员表，其属性包括职员ID(id)、名字(name)、所属部门号(dnumber)、所属部门名称(dname)、所属部门领导ID号(mgrid)。从现实世界中可以知道：

(1) 一个部门有多名工作人员，但一个工作人员只属于一个部门；

(2) 一个部门只有一个部门领导。

从中可以得出这样的依赖关系：{id} → {dnumber}，{dnumber} → {mgrid}。这个表将存在如下3个异常。

  • 插入异常： 如果新成立一个部门，暂时没有工作人员，由于函数依赖{id} → {dnumber}，{dnumber} → {mgrid}的存在，因此无法把这个部门及其部门信息存入数据库中；
  • 更新异常： 如果由于人事调整，一个部门暂时没有工作人员，则在修改工作人员所属部门的信息后，把这个部门及部门信息也丢失了；
  • 数据冗余： 一个部门有多名工作人员，所以部门的信息重复存储，重复次数与该部门的工作人员人数相同，造成了存储空间的极大浪费；同时，如果更换了部门领导，则系统必须修改在该部门工作的每个工作人员记录中的信息，要付出很大的代价来维护数据库的完整性。

因此，在设计中必须将部门信息从工作人员表中分离出来。

可见，在数据库设计时遇到哪些值应该放在哪个表中这个具体的问题时，应当总是记着函数依赖的概念。这就是为什么在最初进行设计时，必须仔细考虑数据库中的数据，考虑处理过程中真正的值的原因。

一般来说，表中所有不属于候选键的列都必须对候选键具有函数依赖。如果其中的一列与候选键不具有函数依赖，那么候选键就不可能在所有的情况下都独立地标识每一个记录，也就不能称为候选键了。

1.6.3  无损分解

无损分解是规范化过程中需要用到的主要原理。因此，介绍规范化之前先介绍一下无损分解。

无损分解是指，在不损失任何存储在表中的数据的情况下，将表分解成两个或多个表。使用无损分解，表和表所含数据中所有的改动都是可逆的，即可以从分解出的表及其所含数据恢复初始表和表中数据。因为无损分解过程中所有的数据都保存下来了。

下面举例说明怎样用无损分解来将一个表分解成两个表，及怎样是有损分解。例如，将地址中省份名称和所在城市名称分成两个列存储，建立项目地址表，表中包含属性工程项目号(pnumber)、所在省份(province)、所在城市(city)，其中主属性为工程项目号(pnumber)。在这个表中具有函数依赖关系：{pnumber} → {province}，{pnumber} → {city}。

 

图1-14 分解表的两种方法

在图1-14中，第一种分解方法将项目地址表分解成两个表，两个表中都包含了原表中的主键pnumber；第二种分解方法中，主键pnumber只在第一个表pnum_city中存在，而第二个表中不包含主键pnumber。

在第二种方法中产生了数据丢失。第二种方法的表pro_city只包含了province和city，丢失了pnumber和province之间的关系。由于在同一个省和城市中可能有多个项目同时进行，那么在表pro_city中出现了相同的记录。而且同名城市可能在不同的省中存在，所以表pnum_city与表pro_city之间没有办法可靠地将项目和它所在的省连接起来。

而在第一种方法中并不会产生这种情况。第一种方法的两个表中都包含了pnumber。pnumber为主键，对于不同的项目，其项目号肯定是不同的，这样无论是多个项目在同一个省和城市中同时进行，还是不同省之间存在同名的城市，两个表中都可以正确地保存数据。同时两个表通过pnumber可以很顺利地连接起来。

那么为什么第二种分解会产生这种情况呢？在上一节中介绍了函数依赖，一般来说，确定了主键后，表中所有其他的列都必须对主键具有函数依赖。非主键列和主键列之间的关系是重要的信息。在分解表的时候，不能破坏函数依赖关系。如果破坏了主键列和非主键列之间的函数依赖关系，表就不能在无损分解的基础上进行分解了。

决定表的分解是否正确有一种简单的测试方法：在分解之后，看看两个表示是不是可以结合在一起重新生成原来的表。

1.6.4  规范化准则

规范化是数据库设计中一个重要过程，可以通过它来提出数据库中冗余的数据。当然，在正确使用时，实体-关系模型可以在最大程度上避免冗余。但是，在确定数据库之前，仍然需要进行规范化处理以确保数据库遵从适当的范式。一般来说，范式是一组数据设计标准。数据可以遵循很多范式，但是使用最多的还是5种范式。本书中将介绍第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、第四范式(4NF)这4种范式，这对于绝大多数数据库来说已经足够了。所以第五范式(5NF)和Boyce-Codd(BCNF)范式不在本书介绍范围内，感兴趣的读者可以自己参考相关资料。

规范化为数据库提供了许多好处，具有很多优点：

  • 大大减少了数据冗余
  • 改进了数据库整体组织
  • 增强了数据的一致性
  • 增加了数据库设计的灵活性

范式从第一种到第五种是按顺序提高的，每一种范式都隐含满足所有低级范式的标准。例如，用户使用第三范式，那么数据也将同时满足第一范式和第二范式。

按照规范化准则进行规范，通常是重新构架表的集合(一般来说，是将一个表分为两个表或多个表)，以便成功地满足规范化的要求。按照规范化准则分开表的主要优点是减少数据冗余。当然使表分开，必然在分开后的表中存在相同的列，这样才能通过连接操作将它们重新连接起来。

那么，有的读者可能觉得疑惑，这样不是造成了数据的复制产生数据冗余了么？其实不然，可控制的复制和出现数据冗余不是一回事。以2.5.2节中建立的包含属性职员ID(id)、名字(name)、所属部门号(dnumber)、所属部门名称(dname)、所属部门领导ID号(mgrid)工作人员表为例，由上面的讨论知道该表中存在严重的数据冗余，即部门信息的重复存储，将部门单独作为一个表分开后，工作人员中只存储所属部门号(dnumber)，当然新的部门表中也存储了部门号(dnumber)，这时部门的其他详细信息在工作人员表中就不必进行多次存储，每个部门的详细信息只在部门表中存储一次即可，大大减少了数据冗余。同时，两个表通过部门号(dnumber)相连，在主键和外部键(在新的部门表中部门号作为主键)之间保持一定的复制关系是为了保持数据的完整性。

下面对每个范式作出解释。

1. 第一范式

第一范式规定表的每个列的值都是不可分的简单数据项。在任何一个数据库中，第一范式都是一个最基本的要求。

所有的表都必须符合第一范式，所以一对多关系就必须用多个表来表示，而不能用一个表来表示。而一对一关系可以用一个表来表示。如果一列对主键具有函数依赖那么该列中的值与相对应的主键之间就具有一对一关系。

第一范式的规定非常地清楚，而且读者也已经对数据关系有所了解，就不进行详细的解释了。

2. 第二范式

第二范式有两项要求：

  • 所有表必须符合第一范式；
  • 表中每一个非主键列都必须完全函数依赖于主键。

下面举一个非第二范式的例子来解释第二范式的使用。假设没有学习实体-关系建模过程，而建立了工作人员(Employees)表，包含属性职员ID(id)、职员所在城市(city)、职员所在城市简介(cintroduce)、工程号(pnumber)、工程接收方(Paccepter)。该表的主键由职员ID(id)和工程号(pnumber)组成，因为工程接收方(Paccepter)不能由职员ID(id)惟一确定。即建立表Employees(id，city，cintroduce，pnumber，Paccepter)。

该表中具有的函数依赖关系十分混乱：city列只对id具有函数依赖，并不随pnumber的改变而改变；cintroduce列只对city列具有函数依赖，而city列只对主键{id，pnumber}中的id具有依赖关系，所以cintroduce列并不依赖于pnumber；而Paccepter只依赖于pnumber。其中的函数依赖关系不符合第二范式的要求。

表的不完全函数依赖关系将导致很多问题的发生。

  • 添加问题： 如果要添加一个工作人员，而新的工作人员暂时还没有分配进具体的工程中，这样的记录无法添加入表Employees中，因为添加时必须给定主键的值，而这时主键的一部分值为空，导致无法添加记录；在公司中并不是每个工作人员都一定参与一项工程，如某些负责行政的人员等，这时要想添加这些工作人员的记录就必须同时添加工程接收方(Paccepter)这样不相干的数据。
  • 删除问题： 删除时删除了多于需要删除的信息。比如，从表中删除一个工作人员记录时，同时删除了该工作人员所在城市的简介。
  • 更新问题： 如果某个工作人员从一项工程转向另一项工程，本来只需要修改pnumber，但表中还含有列Paccepter，所以必须同时修改Paccepter。这样更新时，必须同时更新多余的信息。
  • 冗余问题： 一般来说，一项工程都有很多工作人员参与工作，这样在每个工作人员记录中都必须重复存储pnumber和Paccepter列，不仅冗余度大，而且当数据更新时必须无遗漏地修改每个参与该工程的人员中的pnumber和Paccepter，也造成了修改的复杂化。

表中出现上述问题的原因是对主键的部分依赖，或对非主键的依赖。那么该怎样解决这些问题呢？解决方法就是在遵守无损分解的条件下，将表分解成多个表，从而消除不符合第二范式要求的依赖的情况。

由于Paccepter列只对主键{id，pnumber}中的pnumber具有依赖关系，即对主键只有部分依赖，所以Paccepter列必须从Employees表中删除，且创建只包含pnumber列和Paccepter列的新表Projects；而cintroduce列只对city列具有函数依赖，而city列只对主键{id，pnumber}中的id具有依赖关系。所以cintroduce不用从表中删除。上述分解过程符合无损分解的条件，是正确的分解。这样就将Employees表分解成2个表：

Employees(id，city，cintroduce ，pnumber)

Projects(pnumber，Paccepter)

这两个表都符合了第一范式的条件，同时满足非主键对主键的完全函数依赖，所以都满足第二范式的要求。

3．第三范式

第三范式有两项要求：

  • 所有表必须符合第二范式。
  • 表中每一个非主键列都传递地依赖于主键。

这里解释一下什么叫传递地依赖于主键。例如，建立了工作人员(Employees)表，包含属性职员ID(id)、职员所在城市(city)、职员所在城市简介(cintroduce)。该表的主键是职员ID(id)。在表中cintroduce列依赖于city列，而city列又依赖于主键id列。即{id} → {city}，{city} → {cintroduce}。可知id列传递的确定cintroduce列。

传递依赖的存在也将会导致问题的出现。

  • 添加问题： 不能单独添加新的城市和城市介绍，而必须添加一个新的工作人员才能添加城市和城市介绍的信息。
  • 删除问题： 同样存在删除时删除多余信息的问题。比如，删除一个工作人员记录时，同时也删除了该工作人员所在城市的简介。
  • 更新问题： 更新问题仍然存在。例如，如果一个工作人员从一座城市搬到另一座城市，本来只需要修改city列，但表中还含有cintroduce列，所以必须同时修改cintroduce列。这样更新时，必须同时更新多余的信息。
  • 冗余问题： 一般来说，会有很多工作人员同时在一座城市，这样在每个工作人员记录中都必须重复存储city列和cintroduce列，也导致了冗余问题的出现。

表中出现上述问题的原因是对主键依赖的传递。那么该怎样解决这些问题呢？解决的方法同样是在遵守无损分解的条件下，将表分解成多个表，从而消除传递依赖的情况。由于cintroduce列是通过city列传递地依赖于id列，所以将introduce列从Employees表中删除。从而建立两个新表：

 

Employees(id，city)

Cities(city，cintroduce)

新建这样两个表后，原来表中存在的添加、删除、更新和冗余的问题都很好地解决了。

当然，这只是在本例中达到了基本的要求，并不代表第三范式可以解决所有表的问题，在其他表中第三范式仍然可能还有一些问题，这里就不举例说明了，有兴趣的读者可以参考相关资料。

4. 第四范式

第四范式禁止了主键列和非主键列之间的一对多关系。这里举一个例子对第四范式进行解释。

建立一个工作人员(Employees)表，包含属性职员ID(id)、喜欢的运动(sports)和喜欢的水果(fruits)。主键为id，sports列和fruits列都依赖于主键，而sports列和fruits列之间并没有依赖关系，因此Employees表中不存在传递依赖。所以Employees表符合第三范式的要求。

在Employees表中，一个职员可以有多种喜欢的运动，也可以有多种喜欢的水果。表1-2所示为Employees表中的一些示范内容。

表1-2  Employees表中的示范内容

id

sports

fruits

1001

篮球

香蕉

1001

足球

 

1003

乒乓球

苹果

1005

 

梨

1005

游泳

桃

1005

 

香蕉

 

在同一表中具有这样两个不相关的列，与主键存在多对一关系，使得表中存在冗余现象和不对称情况，因为不知道每个人的喜好情况，也就无法知道哪列的值多，哪列的值少，使得表格中出现了很多不规则的空白。

因为列之间的不相关性，所以可以将两个列分解到两个表中，来消除冗余现象和不对称情况。分解后的表如下：

em_sports(id，sports)

em_fruits(id，fruits)

分解后上面Employees表的示范内容在下面两个新表中存放如表1-3和表1-4所示。

 

 

 

表1-3  Employees表中的示范内容

id

sports

1001

篮球

1001

    足球

1003

乒乓球

1005

 

1005

游泳

1005

 

表1-4  Employees表中的示范内容

id

fruits

1001

香蕉

1005

苹果

1005

梨

1005

桃

1005

香蕉

 

可以使用更高的范式对表进行进一步规范化处理。但是由于内容较深，本书不作介绍，有兴趣的读者可以自己参考资料。

1.6.5  回顾

本节中介绍了逻辑数据库设计中主要的两个任务：

  • 将实体和关系转化为表
  • 对表进行规范化处理

第一个任务将建立好的实体-关系模型转化成数据库中实际的表，并确定表包含的列和主键，这个过程相对比较简单，只要参照实体-关系模型按照规定的方法进行转化就可以很好地完成。

第二个任务比较复杂，因而对每个范式分别作了比较详细的介绍，进行规范化时，要按照从第一范式到第五范式的由低级到高级的顺序进行规范化。多数情况下数据库规范化处理为第三范式，这对绝大部分数据库来说已经基本满足了需求。进一步规范化是可选的。不过，是否对数据库进行进一步规范化处理取决于性能。更高级的规范化会导致性能的下降。在数据库投入使用时，应当时刻考虑性能的要求，如果性能不可接受那么就不能进行更高级的规范化。

1.7  完  整  性

数据库应用对数据一般都具有一定的限制，这种限制称为完整性。数据库的完整性是保证数据库正确的关键。广义上说，数据库完整性包括数据库中数据的准确性和一致性。理想情况下，数据库软件提供了检查数据完整性的各种方法，但遗憾的是，目前大多数关系数据库系统对数据库的完整性支持并不够充分。

关系数据库系统应该保证输入的值符合其规定的数据类型，并保证值在系统支持的范围内。不同的关系数据库系统可能提供了不同的数据类型，但所有的关系数据库系统都检查输入的值，并且拒绝不符合定义的数据类型的值或者不在系统支持范围内的值。例如，不希望将一个工作人员的生日输入为：

1977.5.2

而将另一个工作人员的生日输入为：

3/3/1977

数据必须准确且在数据库中一致存储，这样才能从数据库中检索出正确的数据，才能在数据间作出正确比较。

一般来说，关系数据库系统都支持3种完整性：

  • 域约束
  • 实体完整性约束
  • 关联完整性约束

1.7.1  域约束

域是逻辑相关的值的集合，从域中可以得出特定列的值。

例如，在Company数据库中：

  • 工作人员的生日、部门领导开始工作的日期等与时间相关域的值必须按照特定的时间格式存放，而不能不用统一格式。
  • 职员ID、部门的部门号等域的值必须是0到9之间的数字的值的集合，不能有字符等别的值。
  • 工作人员名字、部门的名称等域的值必须属于字符集合。
  • 对于工作人员的性别，该列中的值必须局限于男、女。

域约束的具体实现，可以在CREATE TABLE语句中使用CHECK约束进行表示。在下一章创建数据库时，将具体介绍域约束的实现。

1.7.2  实体完整性约束

假设X是一个表的主键，则X不能接收空值，即单列主键的值不能为空，复合主键的任何列也不能接收空值。例如，在Employee表中，职员ID(id)为该表的主键，那么在数据库的任何记录中，职员ID(id)列的值都不能为空。这样的约束称为实体完整性约束。

实体完整性约束来源于关系模型本身的要求，而不是来源于任何特殊的应用程序的要求。实体完整性约束不同于数据库完整性规定的其他约束方式。因为表中的任何一个记录都是通过主键进行区分，所以主键有不能为空值的要求。

实际上，实体完整性的问题就是设计问题。在创建表时，可以通过指定主键来保证实体的完整性。即可以通过在CREATE TABLE语句中创建一个惟一的列索引或者使用PRIMARY KEY并指定NOT NULL来实现。

1.7.3  关联完整性约束

关联完整性约束关心的是逻辑相关的表中值与值之间的关系。

假设X是一个表A的主键，在表B中是外部键，那么若K是表B中一个外部键值，则表A中必然存在在X上的值为K的记录。例如，部门号(pnumber)是Department表的主键，而在Employee表中工作人员所属部门号(pno)是相对于Department表的外部键，对于Employee表的任何记录，其所包含的所属部门号(pno)的值在Department表记录的pnumber列中必然存在一个相同的值。这样的约束称为关联完整性约束。

关联完整性约束说明，任何表的一个记录只能通过外部键与另一个表中存在的记录相关联。在数据库中，关联完整性约束就是要求通过确保主键的值和外部键的值相匹配而保持数据库的一致性。

那么数据库系统是怎样维护外部键和对应主键之间值的匹配的呢？第3章创建数据库中详细介绍了在CREATE TABLE语句中的PREFERENCES和FOREIGN KEY用来保证关联完整性的实现。它们建立了一种检查机制，即如果外部键与主键不对应，那么就不能添加外部键。

但是这只是确保关联完整性中主键与外部键之间惟一对应的问题。数据库中还存在主键更新的问题。例如，由于部门调整，要修改一个部门的部门号，那么这种改变破坏了原先数据库中Employee表与Department表之间的连接。

对主键更新的问题有两种处理方法。第一种方法是在数据库中禁止主键值的更改，保证主键值不会发生变化，这种方法显然不是非常合理。

第二种方法是数据库系统自动更新所对应的外部键的值。例如，对部门号的修改将引起系统自动对Employee表中工作人员所属部门号(pno)作相应的修改，确保主键部门号修改后主键和外部键之间的匹配，而无需用户的干预。

为了在数据库完整性约束被违背时能够及时地执行必要的操作以保证完整性约束的有效性，数据库研究人员提出了触发器技术。触发器技术把说明性技术和过程性技术紧密地结合在一起。触发器的条件是说明性的，而执行的处理是过程性的。触发器比在CREATE TABLE中使用约束条件以加强关联完整性提供了更多的灵活性，既可以用来定义显式约束，也可以用来定义隐含约束。

一个触发器用来定义一个条件出现时需要执行的操作。SQL语言提供了一个创建触发器的语句CREATE TRIGGER。本书不介绍详细的使用方法，具体的使用方法可以参考用户所使用的软件(如ORACLE等)中的介绍。

1.8  数据库设计的其他问题

上面介绍了数据库设计的主要部分，这些可以帮助用户设计出基本符合要求的数据库，但是在数据库的设计中还有许多其他的问题需要考虑。

  • 索引： 索引是数据库实现的重要组成部分，对于数据的检索、存储等有很大的影响。关于索引的创建将在第3章创建数据库中介绍。
  • 安全性： 在数据库中安全性是一个非常重要的问题。必须保证数据库数据的安全性，特别是在Internet迅速发展的今天，有效地保障数据库数据的安全对用户来说是非常重要的。将在第12章中介绍数据库的安全性。
  • 数据字典： 数据字典用来存储数据库中存储的数据的描述信息和数据库管理系统需要的控制信息，数据字典与数据库管理软件密切相关。在本书中不详细介绍有关数据字典的内容。
  • 物理数据库设计： 每个数据库管理系统都提供很多种存储结构和存储方法供数据库设计人员选择，物理数据库设计的好坏对数据库的性能有很大的影响。在本书中也不作详细的介绍。

对于数据库设计的一些其他问题，这里进行一些简要的介绍，相关的详细内容用户可以自己参考相关书籍。

1.9  回顾数据库设计

下面回顾一下Company数据库设计的过程。

首先，确定Company数据库包含的四个实体：工作人员实体、部门实体、工程项目实体和工程项目接收方实体。并确定了每个实体包含的属性。通过分析实体之间的关系，从而可以确定实体-关系模型。实体-关系方法对事务设计来说是比较直观的，实体-关系模型中每个实体都可以对应于数据库中的表。由实体之间的关系知道，工作人员实体和工程项目实体之间是多对一关系因此我们在工作人员表中添加了一个新的pno列；部门实体和工程项目实体之间是多对多关系，在把实体-关系模型转化为表时，要为多对多关系建立一个新表dep_pro(dnumber，pnumber)。

虽然正确地使用实体-关系模型后确定的表基本上可以在最大限度上避免数据冗余，在布置数据库之前还是应该使用介绍的范式对表进行检查，保证数据库遵从适当的范式。

在图1-15中给出最终数据结构图表。图表中每一个方框都表示一个表，表名和列名都放在表中，表名用黑体表示。连接方框的线上标出表与表之间的关系。

 

图1-15  Company数据库结构图表

用双向箭头表示两表之间的一对一关系，并在线上方标记1：1；两表之间单箭头表示两表之间的多对一关系，例如，部门与工作人员之间的关系是一对多关系，一个部门有多个工作人员，但一个工作人员只属于一个部门，所以箭头位于Employee表那一端，并在线上方标记N：1。

当然真正公司数据库远比本书中的Company示例数据库复杂得多。但是这个示例就足以用来学习SQL的基本知识，如查询、更新、视图等。在本书中将以Company数据库为例介绍SQL基本用法。

当然数据库设计中还有许多需要考虑的问题如物理数据库的设计实现问题等，用户可以翻阅资料进行更深入的学习。

在设计好数据库之后，就可以创建表并向表中添加一些数据，然后通过查询、更新、视图等操作来测试Company数据库的设计，从而找出数据库设计中存在的问题，并加以修改和完善。

1.10  小    结

本章中首先介绍了关系数据模型的数据结构和键、外部键的概念，接着介绍关系数据模型的三个主要操作：选择、投影与连接。

接下来，通过示例数据库Company详细地讨论了数据库设计的过程、数据库设计中要考虑的一些问题和数据库设计过程中主要的内容，包括实体-关系模型的建立、实体-关系图的绘制、实体-关系模型到表的转化、规范化准则的使用等。

最后介绍了数据库完整性使用，简要介绍了数据库设计中的一些其他问题，并对数据库的设计作了一个简要的回顾。

下一章将对SQL进行一些基础性的介绍。

1.11  上 机 练 习

1. 编写一个从Project表中选出由职员ID号为1001员工主管的项目记录的SQL语句。

2. 在上题基础上限制查看的列，仅查看工程项目号，项目负责人ID号，预计总收入三列的内容，编写SQL语句。

3. 编写使用连接操作实现查找项目负责人具体信息的SQL语句。

1.12  习    题

1. 简述主键与外部键的区别。

2. 数据库中表之间关系有几种，简述其区别。

3. 指出函数依赖中可能存在的异常，简述在设计中应该注意的问题。

4. 简述规范化准则中四种范式的含义。

5. 简述完整性约束中三种约束的含义。

 

posted @ 2012-06-27 22:06  gzm2012  阅读(1237)  评论(0)  编辑  收藏  举报