数据库设计

设计选择

在设计时,我们必须确保避免两个主要的缺陷:

更大还是更小:

设计异常

不符合范式的关系

ER模型

扩展

约束

映射基数

参与约束

实体的码是一个足以分区每个实体的属性集,同样,码也可以用于唯一标识联系

从实体集中删除冗余属性

当决定好实体集后,必须挑选合适的属性

ER图

基本结构

复杂的属性

比如Address

角色

通过在菱形和矩形之间的连线上进行标注来表示角色

非二元的联系集

即一个联系连接了两个以上的实体

批注 2020-03-08 205525

继承关系

批注 2020-03-08 205614

弱实体集

转换为关系模式

具有简单属性的强实体集表示

比如实体集student,有三个属性:ID、name、credit,可以转换成如下关系模式:

student(ID,name,credit)

具有复杂属性的强实体集的表示

比如student有一个属性address,又有子属性city,street,那么可生成关系模式:

student(ID,name,credit,city,street)

弱实体集的表示

设A为一个弱实体集,B为A所依赖的一个强实体集。那么可以创建一个关系模式:

B(a1,a2,a3,x),其中a1,a2,a3为B的属性,x为B到A的外键约束

联系集的表示

设R为联系集,a1,a2...an为参与R的实体集构成的属性集合,b1,b2...bn为R的属性,则R的属性为:

{a1,a2..an}∪{b1,b2,...bn}

如何选取主码:

模式冗余

一般情况下,连接弱实体集与其所依赖的强实体集的联系集模式是冗余的。

模式的合并

ER设计问题

用实体集还是用属性

什么构成实体集,什么构成属性?这个问题要根据现实情况进行回答。

用实体集还是用联系集

一个原则是:当描述发生在实体间的行为时采用联系集

二元还是n元联系集

数据库中的联系通常都是二元的。

一些非二元的联系可以通过拆分分为二元联系,但是这样做,有时并不那么自然

联系属性的布局

属性放到哪里,是实体集还是联系集?这也是要根据实际情况进行决定

扩展的E-R特性

特化

自顶向下的,可以看做OOP当中父类转换成子类的这么样一个过程

概化

同上,类似于OOP中的向上转型

属性继承

高层实体集的属性可以被底层实体集继承

概化上的约束

数据库设计者可以决定哪些实体能成为给定低层实体集的成员,条件可以如下:

聚集

E-R模型的一个局限性在于它不能表达联系间的联系。聚集是一种抽象,它把联系视为高层实体,这样就可以表达联系之间的联系了

转换为关系模式

概化的表示

聚集的表示

聚集的主码是定义该聚集的联系集的主码

其他建模方式表达

范式

范式最重要的就是保证数据之间的关联一致性及控制数据冗余,这种保证会影响数据库的性能 所以在大数据量、高并发的场景下提倡反范式以此来提升性能

原子域与第一范式

一个域是原子的,如果该域的元素被认为是不可分的单元,我们称一个关系模式R属于第一范式。 简单来说:所有关系模式数据库都符合第一范式

第二范式

每个非主属性完全函数依赖于键码,说人话就是数据行的每个属性都可以由主键查询得到

函数依赖

码和函数依赖

一个关系满足需求定义的现实世界约束,称为关系的合法实例

BC范式

属于BC范式的条件是: 对于F+中所有形如a→b的函数依赖(a ⊆ R,b⊆R ),下面至少有一项成立:

BC范式就是要求一个表中的每个属性都只跟主键有关,而不跟其他属性有关

BCNF和保持依赖

由于设计使得函数依赖的强制实施在计算很困难,因此称这个设计不是保持依赖的

第三范式

属于第三范式的条件,下面至少一项成立:

满足BC范式的关系模式一定满足第三范式,第三范式与 BC 范式的区别在于,BC 范式的依赖是可以推导的,而第三范式依赖只是直接有关:第三范式就是要求一个表中的每个属性都只跟主键直接有关,而不跟其他属性有关

其他范式

ER模型和规范化

属性和联系的命名

为性能去规范化

有些范式可能需要在修改或者查询数据时进行更多的操作,这会影响性能,常见去范式提高性能的方式:

  1. 信息冗余,如在订单表存放购买人信息
  2. 物化视图,虽然还有冗余,但是一致性的维护是由数据库来进行

函数依赖理论

函数依赖集的闭包

逻辑蕴含: A → B,B → h 那么 A → H被逻辑蕴含

Amstrong 公理

一组关于函数依赖的基本规则,它们可以用来推导出一个关系模式中所有的函数依赖

属性集的闭包

如果 a → B,我们称属性B被a函数确定

正则覆盖

无损分解

R1,R2是R的分解,如果用R1,R2替代R没有信息损失,则该分解是无损分解

保持依赖

分解算法

BCNF分解

3NF分解

BCNF和3NF的比较

应用函数依赖进行数据库设计的目标:

使用多值依赖的分解

多值依赖

函数依赖有时成为相等依赖 多值依赖成为元组产生依赖 设R为关系模式,让a ⊆ R 且 b ⊆ R 多值依赖 a -> -> b在R上成立的条件是:

4NF分解

数据库设计的其他方面

显式声明约束的优点:

缺点:

在数据更新时,执行约束会在性能上带来潜在的高代价

使用需求:查询、性能

主要的两个度量方法:

时态数据建模

时态数据时具有关联时间段的数据 快照快照是指一个特定时间点上该数据的值,除了可以将每行记录关联一个时间段来代表有效性之外,也有专门的时序数据库用来解决这种需求,金融系统用的很多

授权需求

不同的用户与组织能看到数据会受到不同的限制,这块是认证授权需要干的

数据流、工作流

工作流表示一个流程中的数据和任务的组合

当用户在执行工作流中的任务时,工作流会与数据库系统进行交互,除了工作流操作的数据之外,数据库还可以存储工作流自身的数据

数据库加密

数据库设计的其他问题

数据库设计要求设计者可以预先估计一个组织将来的需求,设计出的模式在需求发生变更时只要做最少的改动即可满足要求。

实践中的一些问题

没有唯一键约束

业务上具有唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引

如果没有唯一索引,很容易会被插入重复数据

执行delete没带查询条件

执行删除操作时,要开启事务,同时要先查询核对影响的行数和数据准确性,再执行删除操作 ;执行删除操作后再次核实,如果情况不对立即回滚

表结构修改没有兼容老数据

比如增加了一个非null字段

时效性要求极高的场景查了备库

备库存放的数据可能不是最新的数据

悬停时间较长的事务被kill

表新增了供查询的字段,却没建索引,导致慢查询

数据库技术选型

MySQL 数据库设计

MySQL 数据库设计规范

命名规范

数据库:

表:

字段:

字段类型规范

索引规范

范式规范

设计原则

核心原则:

字段原则:

索引原则:

SQL原则: