【数据库原理与SQL Server应用】Part13——数据库设计

四口鲸鱼爱吃盐

已于 2022-11-06 17:28:31 修改

阅读量3.2k

点赞数 5

分类专栏：数据库文章标签：数据库 sql sqlserver database

于 2022-11-06 09:54:26 首次发布

本文链接：https://blog.csdn.net/qq_60090693/article/details/127567418

版权

数据库专栏收录该内容

10 篇文章 2 订阅

订阅专栏

【数据库原理与SQL Server应用】Part13——数据库设计

一、关系规范化理论的引入
二、关系模式的规范形式
三、关系模式的规范化
四、数据库系统设计

一、关系规范化理论的引入

在针对一个实际应用来进行数据库的设计时，首先遇到的一个问题就是：
应该如何构造一个适合于它的数据模式，即如何构造它的逻辑结构。

因为关系模型有严格的数据理论基础，人们就以关系模型为背景来讨论上述问题，这就形成了关系规范化理论。

实际上，该理论对于一般的数据库设计理论均有指导意义。

1.1 问题的提出

关系模型的五元组定义：
$R (U, D, D OM, F)$ 在这个定义中： $R$ 是关系名称； $U$ 是属性名集合； $D$ 是 $U$ 中属性所来自的域的集合； $D OM$ 是属性到域的映射； $F$ 是属性 $U$ 上的数据依赖集合。

在这个关系模型定义中，D和DOM对数据库设计关系不大，因此可将上述关系模式简化为一个三元组。

关系模型的三元组定义：
$R < U, F >$ 当且仅当 $U$ 上的一个关系 $r$ 满足 $F$ 时，称为关系模式 $R$ 的一个关系。

对于一个关系模式，如何来评价它的优劣呢？

在这里插入图片描述

对上述关系的观察可以发现，这个关系模式存在如下问题：

数据冗余大
例如：每一名教师的姓名和住址重复出现，重复次数与选修该课程的所有学生数目相同。这将浪费大量的存储空间。并且，由此引出一系列的操作异常。
更新异常(Update Anomalies)
由于数据冗余，当更新数据库中的数据时，系统要付出很大的代价来维护数据库的完整性。否则会面临数据不一致的危险，例如，某课程因某种原因更换教师后，系统必须修改选修该课程学生的每一个元组。 ‘
插入异常(Insertion Anomalies)
如果一名年青教师刚分配来，还未承担课程教学任务，则就无法把这名教师的信息存入该表中。
删除异常(Deletion Anomalies)
上学期学生选修课程学习完成后，在删除这些学生信息的同时，也会把相应教师的信息也一起丢失了。

鉴于以上种种问题，可以说，SELECTS关系模式不是一个好的模式。
一个“好”的模式应当不会发生插入异常、删除异常和更新异常，数据冗余应尽可能少。

为什么会发生这样的问题呢？
这是因为在这个关系模式中的数据依赖存在某些不好的性质。

1.2 从数据依赖到函数依赖

一个关系模式之所以会产生以上诸如数据冗余、操作异常等问题，是由存在于模式中的某些数据依赖引起的。
规范化理论正是在对数据依赖进行分析研究的基础上，通过一定方法来消除其中不合适的数据依赖，以改造关系模式，进而解决在关系模式中的插入异常、删除异常、更新异常和数据冗余的问题。

1.2.1 数据依赖（Data Dependency）

规范化理论要研究关系模式中的数据依赖。
所谓数据依赖，即指实体属性值之间相互联系和相互制约的关系，是数据内在的性质，是语义的体现。
数据依赖性也是关系数据库设计理论的中心问题，研究中发现数据依赖有多种类型，而函数依赖和多值依赖是最其中重要的两种数据依赖。

1.2.2 函数依赖（Functional Dependency）

函数依赖是关系属性之间联系中广泛存在的一种数据依赖，其定义如下：
设关系模式R(U)，X和Y是属性集U的子集，对于R(U)中任意一个可能关系r中的两个元组 t、s，若有 $t [X] = s [X]$ ，则有 $t [Y] = s [Y]$ ，就称X函数决定Y，或Y函数依赖于X。记为： $X \to Y$ 。
在上述定义中： t[X]表示元组t在属性集X上的值，其余依此类推。

函数依赖示例
设有关系模式 $R (A, B, C, D)$ 的一个关系r如下表所示：
在这里插入图片描述

在该表属性A、B之间，存在：
当 $t [A] = s [A]$ 时，有： $t [B] = s [B]$
例如，该表中第一个元组t，当A属性取a1时，B属性取b1；则在第二个元组s在A，B属性列上取值相对应，因此，可以说在此关系上存在 $A \to B$ 。
从函数依赖的定义和以上示例中可知，如果对于关系模式R任意一个可能的关系r，其两个属性组X、Y之间的联系满足以下条件：即在r中不存在两个元组在X上的属性值相等，而在Y属性值不等的情况，则就说明X与Y属性组之间存在函数依赖。

几种特定的函数依赖

非平凡函数依赖
设关系模式R(U)，X、Y是U的子集，若X →Y，且Y不是X的子集，X →Y称为非平凡函数依赖。
平凡函数依赖
设关系模式R(U)，X、Y是U的子集，若X →Y，且Y是X的子集，X →Y称为平凡函数依赖。
对于任意一个关系模式，平凡函数依赖总是存在的，因此，它不反映新的语义。所以，在以后的讨论中，如不特别指明，总是指非平凡函数依赖。
完全函数依赖
设在关系模式R(U)中，如果X→Y，并且对于X的任何一个真子集Z，都不存在Z→Y ，则称Y完全函数依赖于X。
部分函数依赖
设在关系模式R(U)中，如果X→Y，并且对于X的一个真子集Z，存在Z→Y 成立，则称Y部分函数依赖于X。
传递函数依赖
设在关系模式R(U)中，X、Y、Z是U的子集，如果X→Y，Y→Z成立，并且Z-X、Z-Y、Y-X不为空，则称Z传递函数依赖于X。

码：

码也称为键，其在关系模式中是一个重要概念。
在前面章节中已给出码的直观的定义，即码是能惟一地区分不同元组的标识符。现在，按照函数依赖的概念，给出码的形式化定义如下：
设K为R<U,F>中的属性或属性组合,若K完全决定U,则K为R的候选码(Candidate key)。其中被选定的一个候选码称为主码。
包含在任何一个候选码中的属性称为主属性(Prime attribute)，余者称为非主属性(Nonprime attribute)。非主属性也称为非码属性（Non-key attribute）。
同理，关于外码的形式化定义如下：
关系模式R中属性或属性组X并非R的码，但X是另一个关系模式的码，则称X是R的外部码(Foreign Key)

二、关系模式的规范形式

关系模式的优与劣，用什么标准评价？
这个标准就是模式的 范式（Normal Forms，简记为NF） 。

范式指的是符合某一种级别的关系模式的集合，满足最低要求的叫第一范式，简称为1NF 。
在第l范式基础上，进一步满足一些要求的称为第二范式，简称为2NF。其余以此类推。
范式的种类与数据依赖（FD）有着直接的联系，基于FD的范式有1NF、2NF、3NF、BCNF等。

通常把某关系模式R为第n范式简记为 $R \in n NF$ 。

2.1 第一范式（1NF）

定义：
如果关系模式R的=每个关系r的属性值都是不可分的原子值==，那么称R是第二范式（first normal form，简记为1NF）。记为 $R \in 1 NF$ 。

满足1NF的关系称为规范化的关系。关系数据库研究的关系都是规范化的关系。
1NF是关系模式应具备的最起码的条件。不满足第l范式的数据库模式不能称为关系数据库。

2.2 第二范式（2NF）

定义：
如果关系模式R∈1NF，并且每个非主属性完全函数依赖于码，那么称R是第二范式(2NF)。
记为 $R \in 2 NF$ 。

实际上，从上述关于2NF的定义可看到，对于一个满足1NF基础的规范化关系，去除部分函数依赖后，该关系模式即符合2NF 。

如果数据库模式中每个关系模式都是2NF，则称数据库模式为2NF的数据库模式。

很显然，如果关系模式 $R \in 1 NF$ ，并且R的码是单个属性，那么 $R \in 2 NF$ ，因为它不可能存在非主属性对码的部分函数依赖。

2.3 第三范式（3NF）

定义：
如果关系模式 $R \in 1 NF$ ，且每个非主属性都不传递依赖于R的码，则称R是第三范式。
记为 $R \in 3 NF$ 。

由3NF的定义可以证明，若 $R \in 3 NF$ ，则在R中的每一个非主属性即不部分依赖于码，也不传递依赖于码。因此很显然，若R是3NF关系模式，那么R也是2NF的关系模式。

关于3NF还可证明：设关系模式R，当R上每一个函数依赖X→Y满足下列三个条件之一时：

$Y \in X$ （即 $X \to Y$ 是一个平凡的FD）；
$X$ 是 $R$ 的超键；
$Y - X$ 是主属性。
则关系模式R就是3NF模式。

2.4 Boyce-Codd范式（BCNF）

满足BCNF的关系模式具有如下3个性质：

所有非主属性都完全函数依赖于每个候选码。
所有主属性都完全函数依赖于每个不包含它的候选码。
没有任何属性完全函数依赖于非码的任何一组属性。

2.5 多值依赖

定义：
设关系模式R（U），X，Y，Z是U的子集，且Z=U-X-Y。当且仅当R的任一关系r，r在（X,Z）上的每一个值对应一组Y的值，这组值仅仅决定于X而与Z值无关，则称Y多值依赖于X，记为：X→→Y 。
若X→→Y，而Z=Φ（为空），则X→→Y称为平凡的多值依赖；否则称X→→Y为非平凡的多值依赖

多值依赖具有下列性质：

多值依赖具有对称性。即若X→→Y，则X→→Z，其中Z=U-X-Y。
多值依赖具有传递性。即若X→→Y，Y→→Z，则X→→Z-Y
函数依赖可以看作是多值依赖的特殊情况。即若X→Y，则X→→Y。这是因为当X→→Y时，对于X的每一个值x，Y有一个确定的值y与之对应，所以X→→Y。

2.6 第四范式（4NF）

定义：
关系模式R∈1NF，如果对于R的每个非平凡多值依赖 $X \to\to Y$ （Y不是X的子集，XY未包含R的全部属性），X都含有候选码，则称R是第四范式，记为 $R \in 4 NF$ 。

根据定义，对于每一个非平凡的多值依赖X→→Y, X都含有候选码，于是就有X→Y，所以4NF所允许的非平凡的多值依赖实际上是函数依赖。4NF所不允许的是非平凡且非函数依赖的多值依赖。

因此若一个关系模式是4NF，则其必为BCNF。

三、关系模式的规范化

对1NF关系进行投影，消除原关系中非主属性对码的部分函数依赖，将1NF关系转换为若干个2NF关系。
对2NF关系进行投影，消除原关系中非主属性对码的传递函数依赖，从而产生一组3NF关系。
对3NF关系进行投影，消除原关系中主属性对码的部分函数依赖和传递函数依赖（也就是说，使决定属性都成为投影的候选码），得到一组BCNF关系。
对BCNF关系进行投影，消除原关系中非平凡且非函数依赖的多值依赖，从而产生一组4NF关系。
对4NF关系进行投影，消除原关系中不是由候选码所蕴含的连接依赖，即可得到一组5NF关系。

上述规范化步骤，会改进规范化程度低的关系可能存在的插入异常、删除异常、修改复杂和数据冗余等问题，并将其转换成高级范式。

但并不意味着规范化程度越高的关系模式就越好。特别是在对实际应用进行设计时，必须对现实世界的实际情况和用户应用需求作进一步分析，以确定一个合适的、能够反映现实世界的模式。

换句话说，以上的规范化步骤可以在其中任何一步终止。

四、数据库系统设计

一般而言，数据库设计是对于给定的应用环境，设计并优化数据库逻辑模式和物理结构，并据此建立数据库及其应用系统，使其能有效地存储数据，满足用户的应用需求。

数据库设计的目标是为用户和应用系统提供一个信息基础设施和高效率的运行环境。以满足用户对信息管理的要求以及对数据操作的要求。

数据库设计有其自身的特点：
- 重技术、注重管理、强调基础数据
- 结构设计和行为设计相结合

数据库设计过程：
在这里插入图片描述

在这里插入图片描述

4.1 需求分析

需求分析是整个设计过程的基础，需求分析就是要分析用户的要求。这包括对数据及其处理的要求，对数据完整性、安全性的要求。

因此，这一阶段主要任务是通过对现行的手工系统或已有的计算机系统进行调查和分析，以确定企业对即将建立的数据库应用系统的信息要求和处理要求。

在本阶段调查分析结果的准确与否将直接影响后面各个阶段的设计，并影响到设计结果是否合理和实用。

在这一阶段的工作成果，即其所收集的基础数据（用数据字典来表达）和一组数据流程图（Data Flow Diagram）则是下一步进行概念设计的基础。

4.1.1 需求分析方法

跟班作业。通过亲身参加业务工作以了解业务活动情况；
开座谈会。通过与用户座谈以了解业务活动情况及用户需求；
询问。通过询问业务相关负责人、操作人员、用户以了解所要调查的问题；
设计用户调查表或请用户填写。通过这种方法以了解业务活动或用户需求；
查阅记录。通过对原系统中有关数据记录的查阅来了解业务活动及数据处理要求。

4.1.2 数据流图

数据流图（Data Flow Diagram 简称DFD）是系统处理模型的主要组成部分，它摆脱了具体的物理细节，在逻辑上精确地描述了系统中数据和处理的关系，详尽表示了系统的功能、输入、输出和数据存储等。

4.1.2 数据字典

数据字典（Data Dictionary 简称DD）是对系统中各类数据的详细描述，是各类数据属性的清单。

是进行详细的数据收集和数据分析所获得的主要成果。数据字典中的内容在数据库设计过程中还要不断修改、充实和完善。

4.2 概念结构设计

根据需求分析阶段形成的所要建立的新系统需求分析说明书，把用户的信息需求抽象为信息结构即概念模型的过程就是概念结构设计。

概念结构设计是整个数据库设计的关键。概念结构设计将现实世界中的客观对象首先抽象为独立于具体机器，独立于具体DBMS的信息结构。

目前常用的ER方法，即用E-R图来描述现实世界的概念模型。

如前所述，描述概念设计的有力工具是实体-联系（E-R）模型，因此，概念结构设计就归结为E-R模型、方法的分析和设计。

4.2.1 概念结构设计方法

设计概念结构通常有 自项向下、自底向上，逐步扩张和混合策略 等4类方法。

自顶向下是先定义全局概念结构框架，然后逐步细化的方法。
自底向上是先定义各局部应用的概念结构，然后合并集成的方法。
逐步扩张是根据应用的业务逻辑，先定义核心概念结构，然后逐步向外扩充，形成完整的反映应用需求的概念结构的方法。
混合策略则是自顶向下与自底向上方法的结合。

在概念结构设计的阶段，经常采用的策略是自底向上方法。
即自项向下地进行需求分析，然后再自底向上地设计概念结构。

自底向上设计概念结构的方法通常又可以为两步：

抽象数据并设计局部视图；
集成局部视图，得到全局的概念结构。

因此，使用E-R方法设计概念模型一般要经过三个步骤：

设计用户分E-R图。
合并用户分E-R图构成总体 E-R图。
对总体E-R图进行优化。

局部视图设计：
概念结构设计的第一步就是对需求分析阶段收集到的数据按照E-R模型的要求进行分类、组织，形成实体、实体的属性，标识实体的码，确定实体之间的联系类型(1：l，1：n，m：n)，设计分E-R图。
在这里插入图片描述

具体做法是：

选择局部应用；
设计分E-R图。

为了简化E-R图的处置，在给定的应用环境中，可遵循以下基本准则来划分实体和属性：

属性不需要进一步描述；
除与它所描述的实体之外，不再与其他实体具有联系。

符合以上准则的数据项，可作为属性。现实世界的事物能作为属性对待的，尽量作为属性对待。

总体E-R图设计：

总体E-R图即全局视图，其设计就是分E-R图的综合，即所谓视图的集成。；
视图集成有两种方式，一是多个分E-R图一次集成，二是用累加方式一次集成两个分E-R图；
前者操作起来比较复杂，而后者因为每次只集成两个分E-R图，复杂程度较低；
通常，视图集成的具体做法是选出最大的一个分E-R图作为基础，将其他分E-R图逐一合并上去。

集成局部的分E-R图时都需要两个步骤：

合并以消除各分E-R图之间的冲突，形成初步E-R图；
优化以消除冗余信息，使其保持最小冗余度。其中，冗余数据可用分析的方法加以消除。而冗余的联系还可用规范化方法来消除。这样，生成基本E-R图。

4.3 逻辑结构设计

4.3.1 逻辑结构设计的任务

本阶段的任务是把概念结构设计阶段设计好的全局概念模式 转换成 与所选用的具体的DBMS所支持的数据模型相符合的 逻辑结构。

换言之，逻辑设计是要把概念模式转换成DBMS能处理的模式。

因此，逻辑设计，就是采取一定的策略，按照若干准则将（E-R图）概念模型转换为关系数据库管理系统所能接受的一组关系模式，并利用规范化的理论和方法对这组关系模式进行处理。

根据上述逻辑结构设计阶段任务可知，当从E-R图向关系模式转换时，需要做以下两项工作：

确定数据依赖；
构造关系模式。

完成逻辑结构设计阶段的任务，有不同的方法：

按相同左部的原则将函数依赖集 $F$ 分为 $n$ 组，得 $F_{i}（1≤i≤n）$ ，为每组所涉及的属性 $U_{i}$ 构造一个关系模式， $F_{i}$ 则为该关系模式的函数依赖集。
按照规则直接将E-R图转换为一组关系模式。

4.3.2 逻辑结构设计的步骤

逻辑结构设计步骤如下：
1. 将E-R图转换为关系模型
将E-R图转换为适当的模型。由于现在常用的DBMS都是基于关系模型的关系数据库，所以，通常只需要将E-R图转换为关系模型即可。

将E-R图转换为关系模型一般应遵循的原则是：一个实体转换为一个关系模式，实体名转换为关系名，实体属性转换为关系属性。

由于实体之间的联系分为一对一、一对多和多对多3种，所以实体之间的联系转换时，则有不同的情况：

一个一对一联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并。
如果转换为一个独立的关系模式，则与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性。
如果与某一端实体对应的关系模式合并，则需要在该关系模式的属性中加入另一个关系模式的码和联系本身的属性。
一个一对多联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并。
合并转换规则与一对一联系一样。
一个多对多联系转换为一个独立的关系模式。
与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性。