一、设计范式,Normal Format
设计范式就是设计数据表的规范,如果遵循这个设计规范,则设计出来的数据表是合理而且不会有数据冗余。设计范式有3个,分别是NF1,NF2,NF3。3个范式依次严格。
1、第一范式,NF1,字段的原子性
字段是不能拆分的。
老师姓名 | 老师性别 | 班级名称 | 上课教室 | 课程周期 | |
赵老师 | 男 | 计算机1 | 100 | 2020-09-1 | 2024-06-15 |
钱老师 | 男 | 计算机2 | 101 | 2020-09-1 | 2024-06-15 |
孙老师 | 男 | 计算机3 | 102 | 2020-09-1 | 2024-06-15 |
因为关系型数据库就是由二维表及其之间的联系组成。二维表不能实现这种结构,课程周期字段它是可以再被拆分的,不是原子的。
需要对课程周期字段进行拆分,满足字段的原子性要求,从而达到满足NF1
老师姓名 | 老师性别 | 班级名称 | 上课教室 | 开课日期 | 结课日期 |
赵老师 | 男 | 计算机1 | 100 | 2020-09-1 | 2024-06-15 |
钱老师 | 男 | 计算机2 | 101 | 2020-09-1 | 2024-06-15 |
孙老师 | 男 | 计算机3 | 102 | 2020-09-1 | 2024-06-15 |
满足第一范式是很容易做到的。
2、第二范式,NF2,消除部分依赖
老师姓名 | 老师性别 | 班级名称 | 上课教室 | 开课日期 | 结课日期 |
赵老师 | 男 | 计算机1 | 100 | 2020-09-1 | 2024-06-15 |
钱老师 | 男 | 计算机2 | 101 | 2020-09-1 | 2024-06-15 |
孙老师 | 男 | 计算机3 | 102 | 2020-09-1 | 2024-06-15 |
老师性别字段是依赖于老师姓名字段
上课教室、开课日期、结课日期3个字段是依赖于班级名称字段的
在这种情况下,请问哪个字段可以代表整条记录?
解决访求:为数据表添加一个ID字段(主键),此ID字段就能够代表整条记录,所有的字段现在可以依赖于ID字段了。就消除了部分依赖。
ID | 老师姓名 | 老师性别 | 班级名称 | 上课教室 | 开课日期 | 结课日期 |
1 | 赵老师 | 男 | 计算机1 | 100 | 2020-09-1 | 2024-06-15 |
2 | 钱老师 | 男 | 计算机2 | 101 | 2020-09-1 | 2024-06-15 |
3 | 孙老师 | 男 | 计算机3 | 102 | 2020-09-1 | 2024-06-15 |
此时,数据表就是满足第二范式的要求了。
3、第三范式,NF3,消除传递依赖
消除传递依赖的办法就是对数据表进行拆分
老师实体信息表
老师ID | 老师姓名 | 老师性别 |
1 | 赵老师 | 男 |
2 | 钱老师 | 男 |
班级实体信息表
班级ID | 班级名称 | 上课教室 | 开课日期 | 结课日期 |
12 | 计算机1 | 100 | 2020-09-1 | 2024-06-15 |
21 | 计算机2 | 101 | 2020-09-1 | 2024-06-15 |
18 | 计算机3 | 102 | 2020-09-1 | 2024-06-15 |
上课实体信息表
ID | 老师ID | 班级ID |
1 | 1 | 12 |
2 | 2 | 21 |
3 | 3 | 18 |
不同的依赖部分拆分成不同的表,从而消除了传递依赖现象,满足了第三范式。
不满足第三范式的一个重要特征就是有数据的冗余现象
4、举个例子
一、把下面的数据表修改成满足关系数据库设计范式的结构
会员姓名 | 性别 | 商品名称 | 商品单价 | 购买数量 | 总费用 | |
商品费用 | 送货费用 | |||||
张飞 | 男 | 手机贴膜 | 15.00 | 1 | 15.00 | 8.00 |
- 将数据表修改成满足1NF
会员姓名 | 性别 | 商品名称 | 商品单价 | 购买数量 | 商品费用 | 送货费用 |
张飞 | 男 | 手机贴膜 | 15.00 | 1 | 15.00 | 8.00 |
对不满足字段的原子性的字段进行拆分
- 将数据表修改成满足2NF
要求:消除部分依赖
通过增加主键的方法以满足第二范式
ID | 会员姓名 | 性别 | 商品名称 | 商品单价 | 购买数量 | 商品费用 | 送货费用 |
5 | 张飞 | 男 | 手机贴膜 | 15.00 | 1 | 15.00 | 8.00 |
- 将数据表修改成满足3NF
要求:消除传递依赖。部分字段通过依赖的字段而依赖于主键
对数据表进行拆分
不满足第三范式的一个特征就是有数据冗余。
会员实体信息表
会员ID | 会员姓名 | 性别 |
26 | 张飞 | 男 |
商品实体信息表
商品ID | 商品名称 | 商品单价 |
17 | 手机贴膜 | 15.00 |
订单实体信息表
订单ID | 会员ID | 商品ID | 购买数量 | 商品费用 | 送货费用 |
5 | 26 | 17 | 1 | 15.00 | 8.00 |
- 将修改好的数据表在数据库中进行创建,并填充数据
第一个表会员表
注意主键自动增量字段要给出unsigned属性,如果没有,可以插入id为负的记录!
枚举类型字段定义时,不要使用下面的方式
原因:枚举选项有索引,第一个选项的索引值是1,第二个是2,现在的定义容易造成混乱。
建议改成 gender enum(‘1’,’2’) 或直接使用男女,male,female更好。
价格字段一定要用decimal
二、在原来数据库管理程序基础上,增加建库,删除库,
建库需要再获取字符集信息
在创建数据库时,不需要指定存储引擎的!应该是在建表时指定。
删除数据库要进行确认
二、实体间的联系
实体:就是指具体的数据表。
关系型 数据库是由二维表及其之间的联系构成的数据组织。
实体间的联系分为:一对多,多对多,一对一
1、一对多,多对一
这是实体的一种联系方式
2、多对多
我们要想表达出多对多的情况,必须建立一个中间表
实体的联系就通过中间表变成了:一对多加上多对一
3、一对一
比如以内容为主的网站经常有文章上传,内容字段通常会用text的类型,网站设计数据表时经常会将这个字段拆分出去,以扩展表的方式和文章关联
另一个例子,会员表在网站上经常会进行拆分
三、外键约束,foreign key
外键:当B表中的字段是A表中的主键时,B表中的这个字段就是一个外键。
A表可以叫主表。B表可以叫从表。
外键功能只有innodb存储引擎支持。建议在建数据表明确指定存储引擎类型。
约束:
当删除A表中的数据时,如果它是在B表中存在的,则约束不能删除。
当在B表中插入数据时,如果外键字段的值不是A表中存在的值,则约束不允许插入。
1、创建外键
先创建主表
再创建从表,同时创建外键
格式:foreign key (从表中的外键字段的字体名) references 主表名 (主表的主键字段名)
提示,有两个情况要关注
- 创建外键时,如果不指定约束名称,系统会自动分配一个
- 外键字段会自动添加索引
2、查看外键的约束
- 不能够删除或修改主表中被从表正在使用的记录的主键值
- 不能够在从表中添加数据,外键字段的值是主表中不存在的
总结:增加外键可以有效保护数据的合法性。
3、删除外键
格式:alter table 表名 drop foreign key 约束名;
总结:
- 删除外键要使用约束名
- 外键删除后,索引仍然会保留
4、手工添加外键
格式:alter table 从表名 add foreign key (从表中外键字段名) references 主表名 (主表的主键字段名);
如果从表中有非法的数据,则外键无法创建成功。
5、外键的联动方式
外键下,有3 种联动方式
Strict,严格方式,默认的方式。不允许删除及修改
Cascade,级联方式。删除或修改时,从表跟随主表一起变化
Set null,设置为NULL方式,修改删除时从表的外键字段数据设置为null
可以单独为修改或删除指定联动方式
下面的设置也是比较合理一种设置
On update cascade
On delete set null
提示:要注意从表的外键字段,是否有not null的约束,如果有在严格方式没影响,但是增加set null则会失败!
测试修改
测试删除
我是小咖
发个邀请:
如果你正好想学习php,可以与我一起交流,我的VX:feilueze333。下面资料免费赠送。