数据库设计三大范式与BCNF

Posted on

概念

  1. 实体（entity）：就是实际应用中要用数据描述的事物，一般是名词。
  2. 字段（fields）：就是一项数据，也就是我们平常所说的“列”。
  3. 记录（record）：一个实体的一个实例所特有的相关数据项的集合，也就是我们平常所说的“行”。
  4. 键（key）：可唯一标识一条记录的一个字段或字段集，有时翻译为“码”。

  5. 主键（primary key）：用于唯一标识一个表中的一条记录的键。每个主键应该具有下列特征：

    • 唯一的。
    • 最小 的（尽量选择最少键的组合）。
    • 非空。
    • 不可更新的（不能随时更改）

  6. 外键（foreign keys）：对连接父表和子表的相关记录的主键字段的复制。

  7. 依赖表（dependent table）：也称为弱实体（weak entity）是需要用父表标识的子表。
  8. 关联表（associative table）：是多对多关系中两个父表的子表。
  9. 实体完整性：每个表必须有一个有效的主 键。
  10. 参照完整性：没有不相匹配的外键值。

名词解释

函数依赖：
通俗描述：描述一个学生的关系，可以有学号(SNO),姓名(SNAME),系名(SDEPT)等几个属性。由于一个学号只对应一个学生，一个学生只在一个系学习。因此当学号确定之后，姓名和该学生所在系的值也就唯一被确定了，就像自变量x确定之后，相应的函数值f(x)也就唯一地被确定了一样，称SNO函数决定SNAME和SDEPT，或者说SNAME，SDEPT函数依赖于SNO，记为：SNO -> SNAME， SNO -> SDEPT.

严格定义：设R(U)是属性集U上的关系模式。X，Y是U的子集。若对于R(U)的任意一个可能的关系r,r中不可能存在两个元组在X上的属性值相等，而在Y上的属性值不相等，则称X函数确定Y或者Y函数依赖于X。记为X->Y。

（如果不知道“关系”、“属性集”等定义，自己看大学教材去。这里的定义摘自萨师煊&王珊《数据库系统概论》第三版）

完全函数依赖：
在R(U)中，如果Y函数依赖于X,并且对于X的任何一个真子集X’，都有Y不函数依赖于X’， 则称Y对X完全函数依赖。否则称Y对X部分函数依赖。

举个例子就明白了。假设一个学生有几个属性

SNO 学号
SNAME 姓名
SDEPT 系
SAGE 年龄
CNO 班级号
G 成绩

对于(SNO,SNAME,SDEPT,SAGE,CNO,G)来说，G完全依赖于(SNO, CNO), 因为(SNO,CNO)可以决定G，而SNO和CNO都不能单独决定G。

而SAGE部分函数依赖于(SNO,CNO),因为(SNO,CNO)可以决定SAGE，而单独的SNO也可以决定SAGE。

传递函数依赖：
在R(U)中，如果X->Y, Y->Z, 则称Z对X传递函数依赖。

候选键：
(又称候选码，候选关键字,码 ，candidate key)：

设K是一个R(U)中的属性或属性集合(注意可以是属性集合，也即多个属性的组合)，若K完全函数确定U，则K为R的候选键(Candidate key);

通俗地说就是，能够确定全部属性的某个属性或某组属性，称为候选键。若候选键多于一个，则选定其中一个作为主键。

主属性：
包含在任何一个候选键中的属性，叫做主属性(Prime attribute),不包含在任何候选键中的属性称为非主属性或非键属性或非关键字段。

例子：
在(SNO, CNO, G)中，SNO和CNO这俩合起来就是一个候选键，因为每个元组只要确定了SNO和CNO，则其它所有属性都可以根据SNO和CNO来确定。而SNO和CNO就都是“主属性”，G是“非主属性”。由于此例中只有一个候选键，于是只能选择(SNO, CNO)作为主键。

在(SNO,SDEPT, SNAME)中，SNO是一个候选键，因为只要SNO确定了，其它所有属性也都确定了，如果保证没有重名的话，则SNAME也是一个候选键，于是可以选SNO或者SNAME之一作为候选键。如果不能保证没有重名，就不能把SNAME当成候选键，于是就只有SNO能够做主键。

范式：
第一范式：
指数据库表的每一列都是不可分割的基本数据项
在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。

第二范式：
数据库表中不存在非关键字段对任一候选键的部分函数依赖，也即所有非关键字 段都完全依赖于任意一组候选关键字。

2NF的违例只会出现在候选键由超过一个字段构成的表中，因为对单关键字字段不存在部分依赖问题。

例子：(学号, 姓名, 年龄, 课程名称, 成绩, 学分)

候选键只有一个，就是(姓名，课程名称)，则主键就是(姓名，课程名称)

存在如下决定关系：

1：(学号, 课程名称) → (姓名, 年龄, 成绩, 学分)
2：(课程名称) → (学分)
3：(学号) → (姓名, 年龄)

其中，姓名、年龄、学分是部分依赖于主键的，而成绩是完全依赖于主键的，存在部分依赖关系，所以不满足第二范式。

这会造成如下问题

  1. 数据冗余：
    同一门课程由n个学生选修，”学分”就重复n-1次；同一个学生选修了m门课程，姓名和年龄就重复了m-1次。
  2. 更新异常：
    若调整了某门课程的学分，数据表中所有行的”学分”值都要更新，否则会出现同一门课程学分不同的情况。
  3. 插入异常：
    假设要开设一门新的课程，暂时还没有人选修。这样，由于还没有”学号”关键字，课程名称和学分也无法记录入数据 库。
  4. 删除异常：
    假设一批学生已经完成课程的选修，这些选修记录就应该从数据库表中删除。但是，与此同 时，课程名称和学分信息也被删除了。很显然，这也会导致插入异常。

问题就在于存在非主属性对主键的部分依赖

解决办法：把原表(学号, 姓名, 年龄, 课程名称, 成绩, 学分)分成三个表：

学生：Student(学号, 姓名, 年龄)；

课程：Course(课程名称, 学分)；

选课关 系：SelectCourse(学号, 课程名称, 成绩)。

第三范式:
在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式

出现传递依赖A->B->C，即主键A可以确定出某一非关键字段B，而B又可以确定出C，这意味着C依赖于一个非关键字段B。因此第三范式又可描述为：表中不存在可以确定其他非关键字的非键字段

例子：表：(学号, 姓名, 年龄, 所在学院, 学院地点, 学院电话)

该表中候选字段只有“学号”，于是“学号”做主键。由于主键是单一属性，所以不存在非主属性对主键的部分函数依赖的问题，所以必然满足第二范式。但是存在如下传递依赖

(学号) → (所在学院) → (学院地点, 学院电话)

学院地点和学院电话传递依赖于学号，而学院地点和学院电话都是非关键字段，即表中出现了“某一非关键字段可以确定出其它非关键字段”的情况，于是违反了第三范式。

解决办法：

把原表分成两个表：

学生：(学号, 姓名, 年龄, 所在学院)；

学院：(学院, 地点, 电话)。

BCNF：
BCNF意味着在关系模式中每一个决定因素都包含候选键，也就是说，只要属性或属性组A能够决定任何一个属性B，则A的子集中必须有候选键。BCNF范式排除了任何属性(不光是非主属性，2NF和3NF所限制的都是非主属性)对候选键的传递依赖与部分依赖。

例子：

例子二：

假设仓库管理关系表为StorehouseManage(仓库ID, 存储物品ID, 管理员ID, 数量)，且有一个管理员只在一个仓库工作；一个仓库可以存储多种物品。这个数据库表中存在如下决定关系：

(仓库ID, 存储物品ID) →(管理员ID, 数量)

(管理员ID, 存储物品ID) → (仓库ID, 数量)

所以，(仓库ID, 存储物品ID)和(管理员ID, 存储物品ID)都是StorehouseManage的候选关键字，表中的唯一非关键字段为数量，它是符合第三范式的。但是，由于存在如下决定关系：

(仓库ID) → (管理员ID)

(管理员ID) → (仓库ID)

仓库I是决定因素，但仓库ID不包含候选键(candidate key,也就是候选码，简称码)。

同样的，管理员ID也是决定因素，但不包含候选键。

所以该表不满足BCNF。

3NF和BCNF是在函数依赖的条件下对模式分解所能达到的最大程度。一个模式中的关系模式如果都属于BCNF，那么在函数依赖范围内，它已经实现了彻底的分离，已消除了插入和删除的异常。3NF的“不彻底”性表现在可能存在主属性对键的部分依赖和传递依赖。