首先范式英文是什么呢?Normalization,所以如果要学习英文资料,就知道怎么搜索了。
第一范式
第一范式,英文为first normal form,检查1NF。第一范式是很容易理解的,就是列必须是原子性的,只能包含一个值,并且列不能继续拆分。如果可以拆分为两个列的,那么就不符合第一范式了。有两种不符合第一范式的情况,第一张是开发中常见的逗号隔开,比如以下用户表。
id | name | role_ids |
---|---|---|
1 | Tom | 1,3 |
2 | Lily | 2 |
3 | David | 4,5 |
为什么这种设计不好?因为查询不方便,假如我查询角色为3的用户,就必须得用like查询了,但like性能不高。
第二种情况比较少见,就是多个字段合成一个列了,举个同样的用户表例子:
id | name | address |
---|---|---|
1 | Tom | 浙江温州 |
2 | Lily | 江苏苏州 |
3 | David | 湖南娄底 |
这种设计不好得原因也是不方便查询,假如查询浙江的用户,必须得like了,所以不如拆分为两个字段,直接等号查询。
第二范式
第二范式,英文为second normal form,简称2NF,在英文资料里有一个错误的定义,曾经误导了我,他是这么说的,说第二范式必须符合两条规定:
- 表设计符合第一范式;
- 每个表的主键只能包含一列。
这篇错误的英文资料来源于Normalization in DBMS: 1NF, 2NF, 3NF and BCNF with Examples
- The table should be in the first normal form.
- The primary key of the table should compose of exactly 1 column.
也就是说啊,必须都有主键。但是这个第二范式,中英文资料存在巨大的争议。我列举下中文资料里的描述:
第二范式(确保表中的每列都和主键相关)【符合第一范式,同时非主属性完全依赖于主键】
第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关,而不能只与主键的某一部分相关(主要针对联合主键而言)。也就是说在一个数据库表中,一个表中只能保存一种数据,不可以把多种数据保存在同一张数据库表中。
来源于博客数据库设计三大范式。
但是还有一份英文资料是这么写的:
- In the 2NF, relational must be in 1NF.
- In the second normal form, all non-key attributes are fully functional dependent on the primary key
这个定义来源于知名的javapoint网站,文章链接为Second Normal Form (2NF)。
再翻阅其他资料,少数服从多数,我倾向于功能性依赖(在软考中叫函数依赖,所以我用函数依赖吧,感觉更正式一点)的定义。不过这也是一个生动的例子,不要偏信某一篇文章,要多看多思考。闲话少提,回到正题,要理解第二范式,首先要明白一个概念,函数依赖functional dependency。好,什么是函数依赖?
函数依赖还有个概念,叫决定子determinant,这个单词和线性代数里的行列式一模一样。函数依赖指的是两个列A,B,由A能确定B,那么A就是决定子,B函数依赖于A。比如user_id和user_name,user_id是决定子,user_name函数依赖于user_id。
第二范式是说所有非空字段必须完全函数依赖于主键。违反第二范式主要有那些情景呢?
- 没有主键,虽然符合第一范式,但是第一范式没有定义必须要有主键;
- 有联合主键,但是字段只和联合主键的一个子键有关联,这也是那篇英文博客错误的原因所在,他一刀切地将联合主键抽离出一张关联表,忽略了第二范式允许联合主键的场景。
对于第一种情况很少见,我就不举例子了,对于第二个场景我举个例子,以下是学生成绩表:
student_id | course_id | student_name | score |
---|---|---|---|
1 | 1 | 张三丰 | 80 |
1 | 2 | 张三丰 | 90 |
1 | 3 | 张三丰 | 70 |
可以看到student_id和course_id构成一个联合主键,score是和功能性依赖于这个联合主键的,但是student_name只依赖student_id,所以不符合第二范式。
第三范式
第三范式Third Normal Form,简写为3NF,定义如下:
- 必须符合第二范式
- 不包含任何转移性依赖
这里出现了一个新概念,转移性依赖Transitive Functional Dependency。先解释下什么是转移性依赖,转移性依赖其实就是两层函数依赖。举个例子,看看下面这张课程表,一个课程只有一个老师。
course_id | course_name | teacher_id | teacher_name |
---|---|---|---|
1 | 微积分 | 1 | 张三丰 |
2 | 数学分析 | 2 | 灭绝师太 |
3 | 抽象代数 | 3 | 赵敏 |
4 | 概率论 | 4 | 张三丰 |
一个课程只有一个老师上课,所以上面这张表种teacher_name函数依赖于teacher_id,但是teacher_id不是主键,teacher_id依赖于course_id,这就是函数依赖。
那我们再来分析这张表,这种表所有字段都是函数依赖于主键的。teacher_name是course_id唯一决定的,所以是函数依赖于这个主键,所以整张表符合第二范式。但是存在course_id-teacher_id-teacher_name的转移性依赖,也就是teacher_name转移性依赖于course_id。
那要怎么修改呢?course_id-teacher_id-teacher_name这个依赖链,直接断掉最后一个就行了,也就是抽离出一张teacher表就行了,修改后的表如下:
课程表:
course_id | course_name | teacher_id |
---|---|---|
1 | 微积分 | 1 |
2 | 数学分析 | 2 |
3 | 抽象代数 | 3 |
4 | 概率论 | 4 |
教师表:
teacher_id | teacher_name |
---|---|
1 | 张三丰 |
2 | 灭绝师太 |
3 | 赵敏 |