5.1数据库分析与设计
5.1.1数据库分析与设计概述
数据库设计是指对于给定的一个应用环境（DBMS、系统软件、操作系统和硬件环境），构造最优的数据库模式，使之能够有效地存储和索取数据，满足各种用户的应用需求。数据库设计存在不同的方法，包括传统的设计方法、Barker方法、改进的设计方法等。
不同的设计方法有不同的侧重点，不过总体来说一定包括以下3个基本阶段。
1.需求信息的收集
此阶段主要是对企业现有业务的了解，搜集业务数据，构建现有业务模型。
2.数据建模
通过可视化方式分析业务数据，将业务模型转换为物理模型。
3.数据库设计和规范化
选择合理的范式，对数据库进行规范化处理。
5.1.2数据库设计的基本阶段
仿照软件生存期，把数据库应用系统从开始规划、设计、实现、维护到最后被新的系统所代替称为一个生命周期，包括以下几个阶段。
1）规划阶段
对于数据库系统，特别是大型数据库系统，规划阶段是十分必要的。规划的好坏将直接影响到整个系统的成功与否。规划阶段需要完成以下内容：
①定义任务描述：对于要设计的数据库整体目标的概括描述，包括数据库的目标、数据库的使用者、客户如何访问数据库等。
②制订工作计划：包括设计小组的建立、基本的标准和命名规范、所使用的开发环境等。
③制订工作进度和各阶段成果：定义不同的设计阶段完成的目标和时间期限。
2）需求描述和分析阶段
这个阶段是计算机人员和用户双方共同收集数据库所需要的信息内容和用户对处理的要求，并以相应的文档形式确定下来，作为以后开发和验证系统的依据。
3）概念设计阶段
概念设计的目标是产生反映企业组织信息需求的数据库概念结构，即概念模式。概念模式是从现实世界中抽象出来的实体类型及实体间联系，能够充分反映现实世界中实体之间的联系，又是各种基本数据类型的共同基础，易于向关系模式转换。
（1）ER图的概念和组成
实体关系图（Entity Relationship Model，ER）用来描述概念模式，是直观表示概念模式的有力工具。在ER图中有下面4个基本部分：
①矩形框，表示实体类型，实体指的是客观存在且可以互相区别的东西。它可以是具体的对象，如一辆汽车，也可以是抽象的事件，如一次购物等。
②菱形框，表示联系类型，即实体之间的联系。
③椭圆形框，表示实体类型和联系类型的属性。
④直线，联系类型与其涉及的实体类型之间以直线连接，用来表示它们之间的联系，并在直线端部标注联系的种类（1∶1、1∶N或M∶N）。
对于不同的设计工具，ER图的图形表示会有所不同，不过ER图的基本元素是相同的，即都包括实体、实体联系和属性。
（2）ER图设计和实现
设计ER图的步骤如下：
①确定实体类型。
②确定联系类型。
③把实体类型和联系类型组成ER图。
④确定实体类型和联系类型的属性，加入实体和联系的具体属性。
⑤把所有实体和联系类型连接在一起，组成一个完整的ER图。
4）逻辑设计阶段
逻辑设计的任务是把概念设计阶段得到的全局ER模式转换成关系模式。
（1）ER图到关系模式的转换
ER图到关系模式的转换包括两种情况，一种是实体的转换，一种是关系的转换，下面具体说明。
①每一个实体类型转换为一个关系表，实体的属性即为关系的属性。
例如，部门实体转换为关系表。
部门（部门名称，部门地址，电话）
②实体间是1∶1的关系，将一个表的主键和联系类型中的属性放在另一个表中。其中主键作为另一个表的外键。
例如，一个部门有一个经理，部门和经理是1对1的关系，即一个部门只有一个经理。
将经理编号和聘用时间放在部门表里面：
部门（部门编号，部门名称，部门地址，电话，经理编号，聘用时间）
经理（经理编号，姓名，性别，出生日期）
③实体间是1∶N的关系，将关系是1的表中的主键作为外键放在N端的表中。联系类型的属性也放在N端的表中。
例如部门和职工是1∶N关系：
部门（部门编号，部门名称，部门地址，电话）
职工（职工编号，姓名，性别，出生日期，部门编号，聘用时间）
④实体间是M∶N的关系，将联系类型也转换为关系表，其属性为两实体表的键加上联系类型的属性，键为两端实体键的组合。
例如，一个职工可以承担多个项目，一个项目需要多个职工，他们之间是多对多的关系。
项目（项目编号，项目名称）
施工（项目编号，职工编号，开始时间）
职工（职工编号，姓名，性别，出生日期）
（2）规范化
把ER图转换为关系模式（表）后，要对关系模式进行规范化。规范化的目的是减少乃至消除关系模式中存在的各种异常，改善完整性、一致性和存储效率。具体地讲，规范化设计的过程就是按不同的范式，将一个二维表不断分解成多个二维表并建立表之间的关联，最终达到一个表只描述一个实体或者实体间的一种联系的目标。目前遵循的主要范式包括1NF、2NF、3NF、BCNF、4NF和5NF等几种，一般以规范到3NF为准。
①第一范式（1NF）的应用
如果关系模式r的属性值都是不可分的原子值，那么称r是第一范式（first normal form，1NF）的模式。
1NF的具体要求是：
多值字段（multivalued field）必须被移动到另一个表里；
每个字段必须是原子型的（atomic），或者说要尽量小；
每个字段都必须有一个关键字（key）；
重复的值必须被移动到另一个表里。
下面举例说明1NF的具体实施。现有一张存储书籍信息的表Books，包括以下字段：
{Title，Author，ISBN，Price，Publisher，Category}
应用1NF的第一步是确保表没有包含多值字段，所谓多值字段，是指字段能够保存一个以上可能的条目。最初的表有两个地方会违反这一规则：Author（作者）和Category（分类）。许多书都有多个作者，所以Author字段就会出现问题。类似的，一本书可以被归入多个类别。例如《金银岛（Treasure Island）》可以被归为儿童读物、冒险类、经典类及其他类等。
更正这个问题的方法是把出现多值的字段移到另一个表里，新创建两个新表：Authors和Categories，如下所示：
Books：{Title，ISBN，Price，Publisher}
Authors：{Author}
Categories：{Category}
应用1NF的第二步是要求每个字段都必须是原子型的，这就是表示每个字段必须保存可能会有的最小数据元素。这条规则有助于搜索和排序。Author字段就存在问题了，因为一个名字可以包含名和姓，所以将Author分解为FirstName和LastName两个字段。
Authors：{FirstName，LastName}
应用1NF的第三步是要求每个表都必须有一个关键字段（key）。关系模型不允许表包含有重复记录。所以，一个表中的关键字的值必须是唯一的。大多数关系数据库管理系统（RDBMS）都会提供一类计数或者自动编号的数据类型，它会为每条记录分配一个连续的数值，通常使用这样的字段作为表的关键字。当然了，也可以选择表中有意义的字段和字段组合作为关键字，这样便于用有意义的方式查询到记录。以下用自动编号的字段作为关键字：
Books：{BookID，Title，ISBN，Price，Publisher}
Authors：{AuthorID，FirstName，LastName}
Categories：{CategoryID，Category}
应用1NF的第四步是删除重复值。在没有录入真实数据之前，要确定是否满足这一要求是有难度的。在遇到重复输入字段值的时候，就要考虑把这个字段移动到一个新的表里。
例如，Books表中的Publisher（出版商）字段，会一次又一次地输入相同的出版商，为了满足1NF，必须把这个字段移动到另一个表中：
Books：{BookID，Title，ISBN，Price，Publisher}
Authors：{AuthorID，FirstName，LastName}
Categories：{CategoryID，Category}
Publishers：{PublisherID，Publisher}
②第二范式（2NF）的应用
如果关系模式R（U，F）中的所有非主属性都完全依赖于任意一个候选关键字，则称关系R是属于第二范式的。第二范式实际上是消除非主属性对于主关键字的部分依赖。
2NF有以下要求：
为重复应用到多条记录的字段建立独立的表；
通过一个foreignkey来关联这些表。
下面举例来说明2NF的实施。
例：选课关系SCI（SNO，CNO，GRADE，CREDIT），其中SNO为学号，CNO为课程号，GRADEGE为成绩，CREDIT为学分。关键字为组合关键字（SNO，CNO）。
在应用中使用以上关系模式有以下问题。
数据冗余。假设同一门课由40个学生选修，学分就重复40次。
更新异常。若调整了某课程的学分，相应的元组CREDIT值都要更新，有可能会出现同一门课学分不同。
插入异常。如计划开新课，由于没人选修，没有学号关键字，只能等有人选修才能把课程和学分存入。
删除异常。若学生已经结业，则从当前数据库删除选修记录；若某些门课程新生尚未选修，则此门课程及学分记录无法保存。
原因：非关键字属性CREDIT仅函数依赖于CNO，也就是CREDIT部分依赖组合关键字（SNO，CNO）而不是完全依赖。
解决方法：分成两个关系模式SC1（SNO，CNO，GRADE），C2（CNO，CREDIT）。
新关系包括两个关系模式，它们之间通过SC1中的外关键字CNO相联系，需要时再进行自然连接，恢复了原来的关系。
③第三范式（3NF）的应用
如果关系模式R（U，F）中的所有非主属性对任何候选关键字都不存在传递信赖，则称关系R是属于第三范式的。
例：S1（SNO，SNAME，DNO，DNAME，LOCATION）各属性分别代表学号，姓名，所在系，系名称，系地址。
关键字SNO决定各个属性。由于是单个关键字，没有部分依赖的问题，肯定是2NF。但这关系肯定有大量的冗余，有关学生所在的几个属性DNO，DNAME，LOCATION将重复存储、插入、删除和修改时也将产生类似以上异常的情况。
原因：关系中存在传递依赖造成的。即SNO—>；DNO。而DNO—>；SNO却不存在，DNO—>；LOCATION，因此关键字SNO对LOCATION的函数决定是通过传递依赖SNO—>；LOCATION实现的。也就是说，SNO不直接决定非主属性LOCATION。
解决目的：每个关系模式中不能留有传递依赖。
解决方法：分为两个关系S（SNO，SNAME，DNO）和D（DNO，DNAME，LOCATION）。
（3）非规范化
非规范化（也称为反规范化）设计的基本思想是，现实世界并不总是依从于某一完美的数学化的关系模式。强制性地对事物进行规范化设计，形式上显得简单化，内容上趋于复杂化，更重要的是导致数据库运行效率减低。非规范化要求适当地降低甚至抛弃关系模式的范式，不再要求一个表只描述一个实体或者实体间的一种联系。其主要目的在于提高数据库的运行效率。非规范化处理的主要技术包括增加冗余或派生列，对表进行合并、分割或增加重复表。
一般认为，在下列情况下可以考虑进行非规范化处理。
①大量频繁的查询过程所涉及的表都需要进行连接。
②主要的应用程序在执行时要将表连接起来进行查询。
③对数据的计算需要临时表或进行复杂的查询。
非规范化是用适当的冗余信息来减少系统开销，用空间代价来换取时间代价。
例如：一个订单信息表Order Detail里面记录了投递员信息、收款员信息、物品信息、价格策略、客户信息……这些信息分别在投递员信息表、收款员信息表、物品信息表、价格策略表、客户信息表……中存放。如果按照规范化的要求，Order Detail查询时就必须要与这么多个表进行连接或者嵌套查询。如果Order Detail表中的数据量是在百万级的，那么一次查询所需要的时间可能会有好几个小时。事实上，只要在设计时保证数据的逻辑有效性，很多信息都可以直接冗余在OrderDetail表中，这些冗余的数据能够极大地提高了查询效率，从而减少了CPU和I/O操作。
