【分享】医疗场景下的数据库构建实践

案例：医院人事系统

每个科室有多名医生，每名医生只能在一个科室中工作。每名医生可以参加多个医药科研项目，每个医药科研项目可以由多名医生参加。每个医药科研项目由一名医生担任总负责人，一名医生可以负责多个医药科研项目。每名医生参加某医药科研项目，都有“项目职责”和“每年投入几个月”这两个信息。其他相关信息包括：
科室：科室编号、科室描述
医生：工号、姓名、性别、出生日期、进入医院工作日期、职称
医药科研项目：项目编号、项目名称、项目简述

1. ER图绘制

省略了部分属性，用下划线标注了主键。其中，由于将职责这一实体认定为弱实体，因此没有标注主键，但在具体实现时，会通过添加工号和项目编号这两个属性来将职责和医生和项目对应起来）：

2. 将ER图转换为关系模式，注明关系的属性、主键和外键。

Department(Dept dept_number, Dept dept_description, Dept doct_num)  #科室编号，科室描述，医生数量
Doctor(Dct doct_id, Dct doct_name, Dept dept_number, Dct doct_gender, Dct doct_birthday, Dct doct_work_began_date, Dct doct_title) 
#医生编号，医生姓名，科室编号，性别，出生日期，进入医院工作日期、职称
Duty(Dct doct_id, Proj proj_id, Duty proj_duty, Duty worktime) #医生编号，项目编号，项目职责，每年投入几个月
Project(Proj proj_id, Proj proj_name, Proj proj_description, Proj proj_leader) #项目编号、项目名称、项目简述，负责人姓名

3. 建立的数据库中定义成视图、存储过程、触发器或函数

通过华为云GaussDB for MySQL平台进行数据库搭建。

首先，点击控制台，选择区域和项目：

接着，在页面左上角单击服务列表，选择“数据库 > 云数据库 GaussDB”。进入云数据库 GaussDB信息页面：

在左侧导航栏选择GaussDB > 实例管理，就可以进入GaussDB信息页面。

通过个人账号按需购买GaussDB数据库，然后等待数据库初始化后登陆，测试连接成功后进入数据库。

【1】视图

功能描述：显示所有担任项目负责人的医生的姓名，工作年限，职称，所在科室

SQL代码：

CREATE view proj_leader as
SELECT `user019db`.`project`.`proj_name` AS `proj_name`,`user019db`.`project`.`proj_leader` AS `项目领导人`,timestampdiff(YEAR,`user019db`.`doctor`.`doct_work_began_date`,now()) AS `工作年限`,`user019db`.`doctor`.`doct_title` AS `职称`,`user019db`.`doctor`.`dept_number` AS `科室编号` 
FROM (`user019db`.`project` join `user019db`.`doctor`) 
WHERE (`user019db`.`project`.`proj_leader` = `user019db`.`doctor`.`doct_name`)

华为云GaussDB执行效果：

【2】存储过程

功能描述：

创建存储过程UpdateDoctorTitle，该存储过程更新doctor表中的职称（doct_title），根据医生的入职时间做不同的更新操作，使用游标：

1. 对入职时间在2010-01-01之前（含）的，如果之前的职称是副主任医师则设置医生的职称（doct_title）为主任医师；

2. 对入职时间在2018-01-01之前（含）的，如果之前的职称是实习医师则设置医生的职称（doct_title）为普通医生；

SQL代码：

CREATE PROCEDURE `UpdateDoctorTitle`()
BEGIN
DECLARE d_id INT;
DECLARE work_date DATE;
DECLARE title VARCHAR(255);
DECLARE cur CURSOR FOR SELECT a.doct_work_began_date, a.doct_title, a.doct_id FROM doctor a;
DECLARE exit HANDLER FOR NOT FOUND CLOSE cur;
OPEN cur;
REPEAT
FETCH cur INTO work_date, title, d_id;
IF(work_date<='2010-01-01' and title = '副主任医师') THEN SET title = '主任医师';
ELSEIF(work_date<='2018-01-01' and title = '实习医师') THEN SET title = '普通医生';
END IF;
UPDATE doctor SET doct_title=title WHERE doct_id=d_id;
UNTIL 0 END REPEAT;
END

华为云GaussDB执行效果：

【3】触发器

功能描述：

当有新增的医生后，科室内的医生数量对应更新

SQL代码：

DELIMITER $$
CREATE DEFINER=`DB_USER019`@`%` TRIGGER `UpdateDoctNum` AFTER INSERT ON `doctor` FOR EACH ROW begin
    UPDATE department SET doct_num=(SELECT count(*) FROM doctor WHERE new.dept_number=dept_number) WHERE dept_number=new.dept_number;
end $$
DELIMITER ;

华为云GaussDB执行效果：