数据库入门--关系模型与三范式
blurhead
基础知识
范式
数据库表设计的参考标准, 主要有三大范式
范式之间是层层递进的关系,满足更高一级的范式必定满足低一级的范式
其他的范式还有BCNF,4NF,5NF等, 一般来说设计的时候满足三范式就够了
- 1NF
- 数据库中的所有字段都是不可再分的基本数据项, 关系数据库的基础, 不满足1NF的数据库不是关系数据库
- 2NF
- 非主属性 1完全函数依赖 2于 主码 3, 不能 部分函数依赖 4于主码
- 3NF
- 非主属性 都要与 主码 直接相关, 而不是间接相关, 消除 传递函数依赖 5
注意事项:
第二范式和第三范式的区别在于有没有分出两张表
实际应用中一般只能满足1NF, 2NF, 为了性能很难满足3NF
案例分析
下面介绍一下, 设计表的时候如何一步步演化到第三范式
下面是一张学生成绩表
| 学生 | 系 | 课名 | 分数 |
| 1022211101 , 李晓明 | 经济系 , 王强 | 高等数学 | 95 |
这张表明显不符合第一范式, 因为学生和系都是可以拆分的
拆分之后, 就满足第一范式了
| 学号 | 姓名 | 系名 | 系主任 | 课名 | 分数 |
| 1022211101 | 李晓明 | 经济系 | 王强 | 高等数学 | 95 |
| 1022211101 | 李晓明 | 经济系 | 王强 | 大学英语 | 85 |
| 1022211101 | 李晓明 | 经济系 | 王强 | 普通化学 | 75 |
| 1022211102 | 张丽 | 经济系 | 王强 | 高等数学 | 80 |
| 1022211102 | 张丽 | 经济系 | 王强 | 大学英语 | 82 |
| 1022211102 | 张丽 | 经济系 | 王强 | 普通化学 | 82 |
现在所有字段都是最小的数据项, 满足第一范式
这张表中主格是(学号, 课名), 非主属性为(姓名, 系名, 系主任, 分数)
系名和系主任只完全函数依赖于学号, 部分函数依赖于主格, 不满足2NF
现在存在的问题
- 数据冗余
- 每一个学生的姓名,系名,系主任这些数据重复多次
- 插入异常
- 一个系没有学生,则无法将系名与系主任的数据单独添加
- 删除异常
- 删除一个系中所有学生的数据,导致这个系也不存在了
- 修改异常
- 如果有学生需要转系,需要修改该学生所有数据中系与系主任的信息
为了解决这些问题,必须在第一范式的基础上拆分表来满足第二范式
- 成绩表
| 学号 | 课名 | 分数 |
| 1022211101 | 高等数学 | 95 |
| 1022211101 | 大学英语 | 85 |
| 1022211101 | 普通化学 | 75 |
| 1022211102 | 高等数学 | 80 |
| 1022211102 | 大学英语 | 82 |
| 1022211102 | 普通化学 | 82 |
主格:(学号, 课名), 非主属性(分数,), 满足3NF
- 学生表
| 学号 | 姓名 | 系名 | 系主任 |
| 1022211101 | 李晓明 | 经济系 | 王强 |
| 1022211102 | 张丽 | 经济系 | 王强 |
主格:(学号), 非主属性(姓名, 系名, 系主任), 满足2NF
但是系主任完全函数依赖于系名, 系名完全函数依赖于学号, 存在传递函数依赖, 不满足3NF
现在数据冗余和修改异常的问题得到了改善, 但是插入异常和删除异常依然存在
这时候就需要继续提高标准, 对学生表继续拆分, 使学生表满足第三范式
- 学生表
| 学号 | 姓名 | 系名 |
| 1022211101 | 李晓明 | 经济系 |
| 1022211102 | 张丽 | 经济系 |
主格:(学号), 非主属性:(姓名, 系名), 满足3NF
- 院系表
| 系名 | 系主任 |
| 经济系 | 王强 |
主格:(系名), 非主属性:(系主任), 满足3NF
这时候可以看到数据冗余减少到了最少, 同时删除,修改,插入异常都消除了
约束
- 主键约束 (Primay Key Coustraint)
- 唯一性, 非空性
- 唯一约束 (Unique Constraint)
- 唯一性, 建议不为空
- 默认约束 (Default Constraint)
- 字段的默认值, 可以为null
- 外键约束 (Foreign Key Constraint)
- 两表间的关系约束.
- 非空约束 (Not Null Constraint)
- 字段不能为null
- 检查约束 (Check Constraint)
- 限制字段的值范围
主键一般是自增长的id, 唯一约束一般是业务上的唯一约束;
编码
字符编码(Character Set)和排序规则(Collation)
可以在服务器, 数据库, 表, 字段四个层面设置字符编码
优先级: 字段 > 表 > 数据库 > 服务器
一个Character Set对应多个Collation, 不同的Collation性能和准确性有差异
索引
锁
事务
规范
以mysql举例说明, 参考了阿里云和腾讯云社区的文章
命名
主要包括字段和表的命名, 未作特殊说明的表示对于表和字段都适用
- 采用26个字母以及下划线, 单词之间统一用下划线分割, 一般不用数字
- 字段名和表名全部小写, 禁止出现大写
- 慎用数据库关键字, 如name, 最好结合其他单词使用, 如username
- 名称要易于理解, 不宜过长, 最好不要超过三个单词
- 单数形式表示名词, 如employee, 而不是employees
- 必须有描述信息, 说明表和列的含义与用途
- 表名使用名词, 字段可以使用名词和动宾短语
- 尽量避免字段名中包含表名
- 字段的名称中不要包含数据类型
- 字段命名使用完整名词, 不要使用缩写
- 日期类型字段推荐以“DATE”结尾的名字命名
- 时间类型的字段推荐以“TIME”结尾的名字命名
- 明细表命名推荐使用主表加dtl(detail缩写)来表示
类型
- 字段最好都要有默认值, 不要为null, null字段查询难以优化, null字段的复合索引无效
- 用尽量少的存储空间来存储, 优先级int> varchar, char, varchar(10) > varchar(100)
- 固定长度的字段使用char, 可变长度的字段使用varchar
- 主键使用int类型自增长的ID, int长度不能设置太短
编码
- 使用utf8字符集同时考虑是否大小写敏感
评论