细说数据库范式

2019-11-03 08:36:26

字体：大中小

来源：转载

供稿：网友

理论性的东西，往往容易把人人都看得懂的东西写成连鬼都看不懂，近似于主任医生开的药方。从前学范式的时候，把书中得概念翻来覆去看，看得痛心疾首深恶痛绝，再加上老师深切误导，最后一塌糊涂。借助网络资源，自己写了一篇，自己是看懂了，希望对大家也有所帮助，有错误帮忙指正。

数据库范式（Normal forms）：是用于规范关系型数据库设计，以减少谬误发生的一种准则。

1NF(first normal form)：

Table faithfully rePResents a relation and has no repeating groups.

数据库表必须如实地展现“关系”，并且不允许有“重复组”出现。

这样的概念真是令人痛心疾首，我们只好再搬出1NF的的作者之一Chris Date的解释：

1. There's no top-to-bottom ordering to the rows.

（任意两行没有特定的顺序关系。不存在一个特定的理由要某一行必须在另一行之前。）

2. There's no left-to-right ordering to the columns.

（任意两列没有特定的顺序关系。）

3. There are no duplicate rows.

（不允许存在重复的行。如果一张表没有Unique Key，事实上它是违反1NF的。）

4. Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else).

（不允许出现空值Null，这一点不同作者是有争议的。事实上我们常常违背这点。）

5. All columns are regular [i.e. rows have no hidden components such as row IDs, object IDs, or hidden timestamps].

（不允许存在隐藏字段。不知道Oracle的Rowid属不属于这个？）

有人从第四点的“one value”大肆挖掘，于是我们就见到了书上这样的定义：“如果一个关系模式R的所有属性都是原子的，即不可再分的基本数据项，则RÎ1NF”。

这一点被认为是1NF的核心，“关系模式R”↔“表”，“属性” ↔ “列”，下面是一种与1NF不一致的情况，通常这是一类很明显的设计缺陷：

ID
Artist
FavoriteColor
……

1
Babyface
Blue,Yellow
……

2
Sting
Green
……

对上例我们不能把它拆分成FavoriteColor1、FavoriteColor2……因为首先我们不能确定该拆分成几列；其次FavoriteColor1与FavoriteColor2在结构、含意方面都是相同的，这实际上也是一类“repeating group”；同时这种设计会导致某些查询困难，比如“有哪些艺人喜欢黄色？”

解决方案是将表拆分成两个：

ID
Artist
……

1
Babyface
……

2
Sting
……

ID
FavoriteColor

1
Blue

1
Yellow

2
Green

总结：

对1NF最核心的 “原子性”，违反此规范的可能性：接近于0%。不过，网上很多帖子说在关系型数据库中根本不可能违背1NF，我认为这是不对的。

2NF(second normal form):

No non-prime attribute in the table is functionally dependent on a part (proper subset) of a candidate key.

不存在非主属性对任一候选键的部分函数依赖。

如果解释完下面几个概念，这个定义就可以读懂了：

Superkey：超级键（L）,如果属性或属性组合能唯一标识一条记录，则它是一个Superkey。

Candidate key：候选键，当Superkey只包含一个属性时，则它是一个候选键；当Superkey包含一组属性时，仅当这一组属性不包含另一Superkey时，它是一个候选键。换句话说，候选键是“纯净的”、最小化的Superkey。

Non-prime attribute：非主属性，未在任何候选键中出现的属性，即为非主属性。

举例来说，对表{First_name,Last_name,Address}，假定全名不重复，则：

Superkey：

{First_name,Last_name}

{First_name,Last_name,Address}

Candidate key:

{First_name,Last_name}

Non-prime attribute：

Address

浅白版：“2NF针对的是复合候选键（即键包含的字段个数>1）的情况，非主属性不能只依赖于复合候选键中的一部分字段。”显然，如果是非复合候选键，如果它符合1NF，那么它一定符合2NF。

假设有这样一张涉及艺人与唱片公司的关系表：

Artist

艺人
Company

唱片公司
DurationYears

签约总年数
CompAddr

公司住址

Babyface
Solar
4
Indiana

Babyface
Laface
2
Indiana





显然，{Artist,Company}为可以作为一个候选键，DurationYears在这没有问题，但CompAddr是违反2NF的，它只依赖于候选键的一部分（依赖于Company），这是违反2NF的，为了消除这种情况，我们可以：

Artist

艺人
CompID

唱片公司
DurationYears

签约总年数

Babyface
1
4

Babyface
2
2

ID
Company

唱片公司
CompAddr

公司住址

1
Solar
Indiana

2
Laface
Indiana

总结：

对于2NF，如果关系中的候选键只包含一个属性，可以直接略过。

在考虑2NF的过程中，不要把几个无关的实体的属性杂揉放在一个关系中，比如Artist是一个实体、Company是一个实体，它们可以有一系列的关联表（也是实体），但在关联表中尽量不要引入前两个实体的无关属性。

3NF(Third normal form)

Every non-prime attribute is non-transitively dependent on every key of the table.

不存在非主属性对任一键（候选键）的传递依赖。

传递依赖，你可以顾名思义，这里就不再引入定义了，举个例子，有下面一张表：

Tournament

赛事
Year

年份
Winner

冠军
Winner Date of Birth

冠军生日

Indiana Invitational
1998
Al Fredrickson
21 July 1975

Cleveland Open
1999
Bob Albertson
28 September 1968

Des Moines Masters
1999
Al Fredrickson
21 July 1975

Indiana Invitational
1999
Chip Masterson
14 March 1977

这里的候选键为{Tournament,Year}，显然有这样的决定关系：

{Tournament,Year}→Winner

{Tournament,Year}→Winner→Winner Date of Birth

其中第二条就属于违反3NF的情况，因为Winner Date of Birth依赖于Winner而不是直接依赖于候选键。这种情况下，可以将Winner,Winner Date of Birth单独作为一张表，这里不赘述。

总结：

我觉得大多数人凭借直观感觉，就可使设计的关系符合3NF，所以这些理论，你只需要姑且读之。

BCNF(Boyce-Codd normal form)（Boyce与Codd是该范式的两名作者。）

Every non-trivial functional dependency in the table is a dependency on a superkey.

表中的任何非平凡函数依赖，都必须是对superkey的依赖。

non-trivial functional dependency：非平凡函数依赖，如果存在一个决定关系x→y，且y并非x的子集，则叫着y非平凡函数依赖于x。

BCNF与3NF的最大区别是它并不仅针对非主属性（non-prime attribute）来说，它发生的时候常常是表中根本不存在非主属性，以至于它不可能违反2NF或3NF。而BCNF的出现就是为了扩大“打击面”。

于是BCNF的主旨是：补充对发生在主属性（prime attribute）身上的函数依赖的约束，因为对于非主属性的约束已经在3NF中完成了。

例子，使用关系表描述学生、课程、教师的关系（假定一名教师只负责一门课程，一门课程则可以由多位教师负责）：

Student

学生
Course

课程
Teacher

教师

S1
C1
T1

S1
C2
T2

S2
C1
T1

S2
C2
T3

S2
C3
T2

候选键：

{Student,Course}

{Student,Teacher}

因此这里不存在非主属性，而在主属性的函数依赖中，存在Teacher→Course，这属于违反BCNF的情况。

可是，问题是这个表看起来还挺正常的啊？！它的毛病在于，我们无法阻止类似最后一行这样的数据插入，而这会导致与前提“一名教师只负责一门课程”违背。所以我们还是需要将它拆分：

Student

学生
Teacher

教师

S1
T1

S1
T2

S2
T1

S2
T3

Teacher

教师
Course

课程

T1
C1

T2
C2

T3
C2

这样，在“Teacher-Course”表中，借助主键的帮助，最后可以避免违背“一名教师只负责一门课程”这个前提。

那么，如果没有这样一个前提，是初的设计是否符合BCNF？目前看来是的。

真实的情况可能更为复杂，下面这个更接近于我的一些经历：

1）学生需要学习多门课程

2）一门课程可能有多位教师负责

3）一位教师可能负责多门课程

4）某一班级的某一课程对应的教师是固定的（一位）

据此，为了描述学生、课程、教师三者的关系，从这一团乱麻中最早跳出来的大概是这样的表：

Student

学生
Class

班级
Course

课程
Teacher

教师









候选键：

{Student,Course}

我们可以明显地看到Student→Class违反了2NF，于是：

Student

学生
Class

班级





Class

班级
Course

课程
Teacher

教师







从这两张表，仔细考虑，即便我们通过Class关联两张表，还是无法得出学生与课程的关系（只能得出可供该学生选择的课程），所以我们需要再添加一张表：

Student

学生
Course

课程





最后大概是这么三张表，可能还有其它的方案，这里只是举例说明，就不纠缠了。

在BCNF之后，还有4NF，5NF，DKNF，6NF，等什么时候有空了再看看是什么东东。

上一篇：SQL Server系统视图

下一篇：Microsoft SQL Server 2008 Express版本