表的设计
在设计表之前,我们需要从需求中获得实体(实体就是一张张表),实体的属性就是表中的字段(列),然后确定实体与实体之间的关系,最后使用 SQL 语句去创建具体的表
在设计表的时候我们会遵守一些规则,这些规则叫做三大范式(范式是描述数据关系的模型),下面我们来了解一下三大范式。
第一范式
第一范式是关系型数据库的一个基本要求,如果不满足第一范式就不是关系型数据库。
第一范式要求表里的字段不能继续拆分
举个例子:
我们定义一个学生表,但是你只是给了一个字段——学生,可是这个学生范围很广泛,有学生的姓名,性别,年龄,班级,学号等等,说明学生这个字段是可以拆分的,所以你最开始定义的表是不符合第一范式的。
如果一张表里的所有的字段都无法拆分那就满足第一方式,还是学生表为例子:现在创建了学号,姓名,性别,年龄这四个字段组成学生表那就符合第一范式,因为每一个字段都是不可分的。
在定义表的时候,对照数据中的数据类型,将每一个字段都可以用一个数据类型表示,那么当前这个表就天然满足第一范式
第二范式
第二范式在满足第一范式的基础上,存在于复合主键的情况下,不存在非关键字段对任意候选键的部分函数依赖
简单来说,对于由两个或者多个关键字段(即复合主键)决定一条记录的情况的时候,如果一行数据中非主键的字段与复合主键的部分字段存在关系,就说明存在部分函数依赖,不满足第二范式
现在举个例子:
假设我有一张学生成绩表,表里有一下这些字段:
这张表设计是存在问题的,首先我们可以假设将学号设置为主键,然后通过学号我们是可以确定这个学生的姓名性别年龄以及班级的,但是再往后看,我们会发现通过课程名称我们应该是可以找到课程的学分,这时候这个课程的成绩到底是由什么决定的,那应该就是学生和课程共同决定的(学生参加这个课程的考试之后就会有成绩),那这时候我们就可以将学号和课程名称定义为复合主键,在这张表中姓名和课程学分就是非关键字段,但是却与候选键发生了函数依赖(也就是存在关系),那么就会违反第二范式。
以上面的学生成绩表为例:
将表进行拆分,学生表(学生学号,姓名,性别,年龄与班级),课程表(课程编号、名称,课程学分),还有一张课程成绩表(学号,课程编号,成绩)。
如果一张表没有复合主键,那么这张表一定满足第二范式。
如果不满足第二范式有什么后果?
- 造成数据冗余:就像上面的表,本来我需要查看的是学生考了多少分,但是你还告诉我学生的性别,年龄,班级甚至课程学分我都不想知道,这就是数据冗余,并且很浪费空间,应该让每一张表都有自己特定的数据进行保存,而不是全部塞进去
- 更新可能会出现异常:假设你要修改课程对应的学分,那你是不是要把每一个都有这个课程的数据行都要进行修改,不仅费时费力,万一数据库服务器突然坏了一秒钟,你之前更新的数据还不一定会保存下来。
- 插入异常,如果你要新增一门新课程,那你怎么往这张表插入数据,设计表的时候有些列不能为空的,那你是不是还要伪造哪些空数据,或者你等到有人考了这门成绩出来以后再加入数据库中(黄瓜菜都凉了)。
- 删除异常:假设所有参加 JavaEE 课程的同学都毕业了,但是JavaEE 的新学生还没这么快考试,那你如果删除这些毕业的人的数据的时候,也就连JavaEE 的课程信息也删除了。
如何避免第二范式:
在设计复合主键的时候,一定要考虑清楚其他字段会不会与复合存在部分函数联系。
第三范式
第三范式在第二范式的基础上,不存在非关键字段对任一候选键存在传递依赖。
以下面的学生表为例:
这张表主键定义为学号,但是我们可以 从学号得知学院名称 再得知学院的地址和电话,由于学院名称是非关键字段,所以存在了传递关系。
那该如何设计这张学生表?
设计成两张表,一个学院表(学院编号,学院名称,学院电话,学院地址)
一张学生表(学号,姓名,学院编号)设计成主外键键关系即可。
表的关系
一对一
举个例子:一个中国公民只有一个身份证
如何设计表:
创建两张表,分别记录中国公民的个人信息(姓名,电话,现居地址),身份证信息(身份证号,姓名,年龄,性别)
然后我们可以再公民表中设计一个外键(身份证号)。
createtable idcard(
id bigintprimarykeycomment'身份证号',
name varchar(20)notnullcomment'姓名',
gender varchar(1)notnullcomment'性别',
age intnotnullcomment'年龄');createtable person(
id bigintprimarykeyauto_incrementcomment'编号',
name varchar(20)notnullcomment'姓名',
address varchar(100)comment'现居地址',foreignkey(id)references idcard(id));
一对多(多对一)
举例:一个班级存在很多个学生,这是一对多的关系
同理,很多个学生都在一个班级,这是多对一的关系
如何设计表:
还是一样,先创建不同的实体表,再去建立联系
演示:创建班级表(班级编号,班级人数)
再创建学生表(学生学号,姓名,年龄,所属班级)
最后确定关系,我们可以在学生表添加一个班级外键:
createtable class(
id bigintprimarykeycomment'班级编号',
num intcomment'人数');createtable student (
id bigintprimarykeyauto_incrementcomment'学生学号',
name varchar(50)notnullcomment'姓名',
age intcomment'年龄',foreignkey(id)references class(id));
多对多
举个例子:学生与课程的关系,一个学生可以选择很多个课程,一个课程同时也可以包含很多个学生。
如何设计?
首先分别创建好实体表,一张学生表(学生学号,姓名,年龄) 一张课程表(课程编号,课程名称,课程学分)
然后确定关系发现是多对多,那就再创建一张表——关系表(关系表自身的编号,学生学号,课程编号)
createtable student (
id bigintprimarykeyauto_incrementcomment'学生学号',
name varchar(50)notnullcomment'姓名',
age intcomment'年龄');createtable course(
id bigintprimarykeyauto_incrementcomment'课程编号',
name varchar(20)notnullcomment'课程名称',
score decimal(2,1)comment'学分');createtable student_course (
id bigintprimarykeycomment'自身编号',
student_id bigintnotnullcomment'学生学号',
course_id bigintnotnullcomment'课程编号',foreignkey(student_id)references student(id),foreignkey(course_id)references course(id));
我们还可以在这张关系表中添加一个成绩的字段。
没有关系
对于没有关系的表,我们直接设计即可,不需要考虑其与其他的关系(因为本来就没有关系)
版权归原作者 熵减玩家 所有, 如有侵权,请联系我们删除。