数据库设计（一）

---

前言

文章标记颜色说明：
注意事项
重点
重点
标记
  

数据库设计是计算机科学中非常重要的一个领域，它涉及到如何组织和优化数据以支持应用程序的需求。一个良好的数据库设计可以提高系统的性能、可靠性和可维护性。

• 在数据库设计中，需要考虑多个关键方面。首先，需要明确定义数据的结构和关系，包括实体、属性和它们之间的关系。这可以通过使用实体关系模型（Entity-Relationship Model）来进行建模和表示。

• 其次，需要选择适当的数据库管理系统（DBMS）来存储和管理数据。常见的DBMS包括关系型数据库管理系统（RDBMS）如MySQL、Oracle和SQL Server，以及非关系型数据库（NoSQL）如MongoDB和Redis。不同的DBMS有不同的优势和适用场景，在设计阶段需要考虑到系统的需求和约束条件。

• 有效的数据库设计还需要了解和应用一些基本的规范和原则。其中，范式化（Normalization）是一种重要的设计方法，用于消除冗余和提高数据的一致性。范式化涉及到将数据分解成更小、更简洁的结构，并通过关系（关联）将它们重新组合在一起。此外，数据库设计中还需要考虑性能优化、索引设计、安全性、备份和恢复等方面。

• 最后，数据库设计也需要关注数据访问和查询的效率。这包括选择合适的数据查询语言（如SQL）和编写高效的查询语句，以及合理利用索引、分区和缓存等技术来优化数据的检索和操作。

总之，数据库设计是一个复杂而关键的过程，需要综合考虑多个因素。合理的数据库设计可以提高系统的稳定性、性能和可维护性，从而实现数据的有效管理和利用。在本文中，我们将介绍一些关键的数据库设计知识点，帮助你理解和应用数据库设计的基本原则和技巧。

数据库设计

实体关系模型

实体Entity：一张表就是一个实体

关系Relationship：实体与实体之间的关系

实体关系模型也称为ER模型。

用图形表示ER模型时，这个图称为ER图。

用矩形表示实体，用椭圆形表示实体的属性，用菱形表示实体之间的关系，用直线连接各个图形。

实体之间的关系

一对一

实体A与实体B之间唯一对应。

如一个国家有一个领导人；一个人对应一个配偶。

ER图

在数据库中创建表的过程

方式一：使用一张表实现。

国家实体的某个属性和领导人实体的某个属性需要添加唯一约束。

序号(主键)	国家名(唯一约束)	人口数量	国土面积	领导人编号(唯一约束)	领导人	性别	生日
1	A	xx亿	xxx万平方公里	1001	xxx	男	xxx
2	B	234万	xxx万平方公里	1002	xxx	女	xxx

方式二：使用两张表实现(推荐)

各自保存实体的属性，选择其中一张表添加额外的一列，关联另一张表的主键，这一列还要添加唯一约束。

• 领导人表

  领导人编号(主键)	领导人	性别	生日
  1001	xxx	男	xxx
  1002	xxx	女	xxx

  create table leader(
  	id int not null primary key auto_increment,
      name varchar(50) not null,
      birthday date ,
      sex char(1) not null
  )
  

• 国家表

  序号(主键)	国家名(唯一约束)	人口数量	国土面积	领导人编号(唯一约束)
  1	A	xx亿	xxx万平方公里	1001
  2	B	234万	xxx万平方公里	1002

  create table country(
  	id int not null primary key auto_increment,
      name varchar(50) not null,
      poplulation bigint not null,
      area double not null,
      leader_id int not null unique,
      foreign key leader_id references leader(id) 
  )
  

方式三：使用三张表实现

两张表用于保存两个实体的信息，第三张表用于表示对应关系

• 领导人表

  领导人编号(主键)	领导人	性别	生日
  1001	xxx	男	xxx
  1002	xxx	女	xxx

• 国家表

  序号(主键)	国家名(唯一约束)	人口数量	国土面积
  1	A	xx亿	xxx万平方公里
  2	B	234万	xxx万平方公里

• 关系表

  id	国家编号(唯一约束)	领导人编号(唯一约束)
  9001	1	1001
  9002	2	1002

一对多/多对一

一对多：一个实体A对应多个实体B，一个实体B不能对应多个实体A。

如一个人可以有多辆车，一辆车不能被多个人拥有。

多对一：多个实体B对应一个实体A，多个实体A不能对应一个实体B。

如多个学员对应一个训练他们的教官，多个教官不能对应一个学员。

ER图

在数据库中创建表的过程

• 创建主表(一)

  id	name	level
  9001	李伟	二级士官
  9002	赵铭	二级士官

  create table coach(
  	id int not null primary key auto_increment,
      name varchar(20) not null,
      level varchar(20) not null
  )
  

• 创建从表(多)

  id	name	phone	coach_id
  1001	a	123	9001
  1002	b	12312	9001
  1003	c	1123	9002

  create table student(
  	id int not null primary key auto_increment,
      phone varchar(20) not null,
      name varchar(20) not null,
      coach_id int not null,
      foreign key coach_id references coach(id)
  )
  

多对多

一个实体A可以对应多个实体B，一个实体B也可以对应多个实体A。

如一个学生可以学习多门课程，一门课程可以对应多个学习它的学生。

如一个医生对应多个病人，一个病人可以看多个医生。

ER图

在数据库中创建表的过程

• 创建学生表

  id	name	phone
  1001	a	123456
  1002	b	123565

  create table student(
  	id int not null primary key auto_increment,
      name varchar(20) not null,
      phone varchar(20)
  )
  

• 创建课程表

  id	name	credit
  c001	高等数学	8
  c002	大学英语	6

  create table course(
  	id varchar(10) not null primary key ,
      name varchar(20) not null,
      credit int not null
  )
  

• 体现多对多关系的表：成绩表

  id	学号	课程号	成绩
  1	1001	c001	88
  2	1001	c002	86
  3	1002	c001	68
  4	1002	c002	72

  create table score(
      id int not null primary key auto_increment
  	s_id int not null,
      c_id varchar(20) not null,
      cj int not null,
      foreign key s_id references student(id),
      foreign key c_id references course(id)
  )
  

总结

一对一：创建各自的实体表，在任意一张表中添加另一张表的主键字段，将其设置为唯一

一对多/多对一：先创建主表(一)，再创建从表(多)，在从表中添加主表的主键字段，外键可选添加

多对多：创建各自的实体表，再创建第三张表:“关系表”，在关系表中添加两个实体表中的主键字段，外键可选添加

练习

“医院信息管理系统”

科室模块：CURD

医生模块：CURD

病人模块：CURD

病历模块：哪个医生在什么时间诊断了哪个病人，记录诊断结果

• 科室表

  create table dept(
  	id int not null primary key auto_increment,
      name varchar(20) not null,
      phone varchar(20) not null
  )
  

• 医生表

  create table doctor(
  	id int not null primary key auto_increment,
      name varchar(20) not null,
      phone varchar(20) not null,
      dept_id int not null
  )
  

• 病人表

  create table patient(
  	id int not null primary key auto_increment,
      name varchar(20) not null,
      phone varchar(20) not null,
      age int not null,
      address varchar(20)
  )
  

• 诊断记录表

  create table diagnosis(
      id int not null primary key auto_increment,
  	dr_id int not null,
      p_id int not null,
      diagnosis_time datetime not null,
      diagnosis_result text not null
  )
  

数据库设计规范

数据库设计的规范，简称为范式NF。

范式分为第一范式1NF，第二范式2NF，第三范式3NF，BC范式BCNF，第四范式4NF，第五范式5NF共六种。

这六种范式的级别越高(第一到第五越来越高)，表示数据库设计的结构越规范。

每一个高等级的范式都包含了低等级的范式。
通常设计数据库时，只需满足3NF即可。

如有该原始表。

黄色背景称为联合主键。由学号和科目一起区分每一条记录。暂时这张表不满足任何范式。

当前表如果要做增删改查的操作，会涉及到的问题

• 如果要加入一个新的系别，就要添加学生信息、系主任
• 如果要删除"刘定宇"，他所占的系别也会被删除
• 如果将"王海"的系别改为会计，相应的系主任也要修改
• 当前表中有大量冗余数据

第一范式1NF

数据表中的每一列都是不可分割的原子项。

关系型数据库中，起码要满足1NF，才能创建表。

上表中的"联系方式"列，可以分为"手机"和"QQ"两列，不满足原子性，不满足1NF。

根据1NF修改

第二范式2NF

在满足1NF的基础上，消除部分依赖。

对于联合主键而言，每一个非主属性字段都需要完全依赖于主属性，而不是只依赖其中的一部分。

在上图中，无法用学号当主键，需要学号和科目组合为联合主键，通过联合主键才能得到分数。

除了学号和科目外，其他字段都是非主属性字段，分数完全依赖于联合主键，其他字段部分依赖于联合主键，所以对其进行拆分。

第三范式3NF

在满足2NF的基础上，消除传递依赖。

在上图中，系主任是通过学号–>系别–>系主任获取，系主任传递依赖于学号，消除这个传递依赖

最终根据实体关系模型进行优化，体现对应关系

名词解释

• 主键/主码/主属性

  • 用于区分每条记录的一个字段。
  • 通常选择能唯一确定且几乎不会更改的字段作为主键。如果没有，自定义一个id列作为主键

• 联合主键

• 如果一个字段不能唯一区分每条记录，而需要多个字段一起才能区分，这些字段组成了联合主键

• 完全依赖

  • 如果能通过A和B得到C，A和B都不能单独得到C时，称为C完全依赖于A和B。

    如(学号+科目)联合主键–>分数，分数完全依赖于联合主键

    其他字段完全依赖于学号

• 部分依赖

  • 通过A和B可以得到C，单独通过A或B也能得到C，称为C部分依赖于A和B。

    如(学号+科目)联合主键–>姓名，其实只通过学号也能得到姓名，称为姓名部分依赖于联合主键

• 传递依赖

  • 如果通过A得到B，通过B得到C，称为C传递依赖于A。

    如学号–>系–>系主任。其实只通过系也能得到系主任，称为系主任传递依赖于学号。

---