设计核酸检测数据库系统

设计理念

设计一个疫情之下学校做核酸检测服务的系统，设计的理念旨在呈现学生做核酸检测的整个流程，包括核酸预约、预约后核酸码的自动生成、核酸结果查询、结果更新以及阳性同学的密接人员排查。其中还有志愿者志愿时长更新的功能。

需求分析

通过了解校内核酸检测的特点和实际情况，从校内核酸检测的基本情况入手，结合要实现的功能，对系统的可行性进行分析，为提高其可行性，故做了以下数据分析。

数据需求：

*1.预约阶段需要的数据：*

学生信息：学号，组号。

核酸码信息：核酸码号。

****2.****核验签到阶段需要的数据：

学生信息：学号，组号。

志愿者信息：志愿者工号。

核酸码信息：核酸码号。

3****.采样登记阶段需要的数据： ****

学生信息：学号，组号。

医生信息：医生工号。

核酸码信息：核酸码号，采样时间。

****4.样本检测阶段需要的数据： ****

核酸码信息：核酸码号，采样时间。

检测结果信息：检测时间，检测单位，是否阴性。

查询阶段需要的数据：

学生信息：核酸码号。

检测结果信息：采样时间，检测时间，检测单位，是否阴性。

处理******需求 ******

****1.****数据录入

录入学生信息、录入志愿者信息、录入医生信息、录入核酸码信息、录入检测结果信息。

****2.数据更新/删除 ****

更新/删除学生信息、更新/删除志愿者信息、更新/删除医生信息、更新/删除核酸码信息。

****3.********数据查询 ****

查询可用学生信息、查询志愿者信息、查询医生信息、查询核酸码信息、查检测结果信息。

3.概念模型设计

（ER图）

4.逻辑模型设计

4.1 关系模式

学生（学生姓名，学号，核酸号码，组号）

Student(s_id,s_name,c-id,c_group)

核酸码（核酸号码，学号，志愿者工号，医生工号，组号，采样时间）

Covid-code(c_id,s_id,v_id,d_id,c_group,s_time)

志愿者（志愿者工号,志愿者姓名，志愿者时长）

Volunteer(v_id，v_name,v_time)

医生（医生工号,姓名）

Doctor(d_id，d_name)

检测结果（核酸号码，采样时间，结果时间，检测单位，是否阴性）

Result(s_id,s_time,，r_time,r_unit,is_neg)

标记黄色部分为每个表的主码

4.2 关系的规范化

（对转换后的关系模式符合范式的说明）

(对转换后的关系模式符合范式的说明)

首先对范式进行说明如下：

第一范式1NF

定义:属于第一范式关系的所有属性都不可再分，即数据项不可分。第一范式强调数据表的原子性，是其他范式的基础。将数据表进行不可再分，使其满足1NF.

第二范式2NF

定义: 若某关系R属于第一范式，且每一个非主属性函数依赖于任何一个候选码，则关系R属于第二范式。

将表拆分，使每个表的所有字段全部依赖于主键。对于数据表，不存在非主属性对于码的部分函数依赖，符合2NF。

第三范式3NF

将表拆分，使每个表的所有字段与主键直接相关。对于数据表，不存在非主属性对于码的传递函数依赖，符合3NF

Student(s_name,s_id,s_number,group)

属于第一范式，所有属性不可再分

Covid-code(c_id,s_id,v_id,d_id,group,s_time)

属于第一范式，所有属性不可再分

Volunteer(v_id,v_name,v_time)

属于第二范式，首先其属于第一范式，其次非主属性v_name和v_time完全函数依赖于v_id。

Doctor(d_id,d_name)

属于第二范式，首先其属于第一范式，其次非主属性d_name完全函数依赖于d_id。

Result(s_id,s_time,，r_time,r_unit,is_neg)

属于第一范式，所有属性都不可再分

5.物理模型设计

-- 核酸检测系统 

--   1.建表语句
--       1.1 创建数据库
drop database if exists Covid;
create database Covid;
show create database covid;
--       1.2 创建表
use Covid;
create table student(s_id char(8) primary key,
s_name varchar(50),c_id char(8),c_group int);

create table covid_code(c_id char(8) primary key,
s_id char(8),v_id char(8),d_id char(8),c_group int,
s_time datetime);
-- 注意核酸采样时间和下面的检测时间需要包含日期和时间，单位用datetime

create table volunteer(v_id char(8) primary key,
v_name varchar(50),v_time int comment"单位h");

create table doctor(d_id char(8) primary key,
d_name varchar(50));

create table result(r_id char(8) primary key,
s_id char(8),s_time datetime,r_time datetime,
r_unit varchar(8),is_neg varchar(50));

show tables;
--       1.3 添加外键约束
-- 建立级联cascade约束 
-- 预约关系 
alter table student add constraint book foreign key
 (c_id) references covid_code(c_id)
 on delete cascade
 on update cascade;
-- 查询关系 
alter table student add constraint select_ foreign key
 (c_id) references result(r_id)
 on delete cascade
 on update cascade;
-- 样本检测 
alter table covid_code add constraint test foreign key
 (c_id) references result(r_id)
 on delete cascade
 on update cascade;
 -- 核酸签到 
 alter table covid_code add constraint attend foreign key
 (v_id) references volunteer(v_id)
 on delete cascade
 on update cascade;
 -- 采样登记 
 alter table covid_code add constraint sign_in foreign key
 (d_id) references doctor(d_id)
 on delete cascade
 on update cascade;
 
select * from information_schema.key_column_usage;

--    2.数据录入 
-- 注意先插入主表的数据，再插入从表的数据，因为有外键约束 
set sql_safe_updates=0;

desc doctor;
insert into doctor(d_id,d_name)
values("1","李救死"),
("2","王扶伤"),
("3","刘大白"),
("4","张天使");
select * from doctor;

desc volunteer;
insert into volunteer(v_id,v_name,v_time)
values("1","王劳动",12),
("2","冯学时",30),
("3","习抗疫",45),
("4","张新",0);
select * from volunteer;

desc result;
insert into result(r_id,s_id,s_time,r_time,r_unit,is_neg)
values("001","20047088","2022-05-23 13:00:00","2022-05-24 13:00:00","松江第一人民医院","阴性"),
("002","20057019","2022-05-23 14:00:00","2022-05-24 14:00:00","松江第一人民医院","阴性"),
("003","20058032","2022-05-23 14:03:00","2022-05-24 14:00:00","松江第一人民医院","阳性"),
("004","20056009","2022-05-24 14:00:00","2022-05-25 13:00:00","松江第二人民医院","阴性");
select * from result;

desc covid_code;
insert into covid_code(c_id,s_id,v_id,d_id,c_group,s_time)
values("001","20057019","1","1","1","2022-05-23 14:00:00"),
("002","20058032","1","1","1","2022-05-23 14:03:00"),
("003","20047088","3","4","4","2022-05-23 13:00:00"),
("004","20056009","4","2","2","2022-05-24 14:00:00");
select * from covid_code;

desc student;
insert into student(s_id,s_name,c_id,c_group)
values("20057019","覃菜","002","1"),
("20058032","杨羊","003","1"),
("20047088","刘瑶","001","4"),
("20056009","杨数数","004","2");
select * from student;
--    3.索引
create index index_stu_name on student (s_name asc);
create index index_v_name on volunteer(v_name asc);
create index index_d_name on doctor(d_name asc);
--    5.触发器
--      采样时间update触发器  
drop trigger if exists test_update;
delimiter $$
create trigger test_update after insert
on result for each row
begin
if(new.r_id is not null)then
  insert into covid_code(c_id,s_id,v_id,d_id,c_group,s_time) 
  values(new.r_id,new.s_id,floor(rand()*4),floor(rand()*4),floor(rand()*4),now());
end if;
end;
$$
delimiter ;

delete from student where s_id="20057032";
insert into student(s_id,s_name,c_id,c_group)
values("20057032","康小薇","002","2");

delete from result where r_id="005";
insert into result(r_id,s_id)
values("005","20057032");

delete from covid_code where c_id="005";
select * from covid_code;

--    6.存储过程
--        6.1 新增存储过程  志愿者帮忙登记，志愿者时长增加 
drop procedure if exists up_v_time;
delimiter $$
create procedure up_v_time(in vol_id char(8))
modifies sql data
begin
update volunteer set v_time=v_time+1 where v_id=vol_id;
end;
$$
delimiter ;

call up_v_time("2");
select * from volunteer where v_id="2";
--        6.2 查询存储过程，查询阳性同学的组员、登记志愿者和采样医生信息 
drop procedure if exists positive_mijie;
delimiter $$
create procedure positive_mijie(in stu_id char(8))
reads sql data
begin
declare y_group int;
declare y_v char(8);
declare y_d char(8);
declare y_time datetime;
select c_group into y_group from covid_code where s_id=stu_id;
select s_time into y_time from covid_code where s_id=stu_id;
select v_id into y_v from covid_code where s_id=stu_id;
select d_id into y_d from covid_code where s_id=stu_id;
set sql_safe_updates=0;

drop temporary table if exists info_mijie;
create temporary table info_mijie(m_name varchar(50),title varchar(50),m_time datetime);

insert into info_mijie(m_name,title,m_time)
values((select s_name from student where c_group=y_group and s_id!="20058032"),"学生",y_time);

insert into info_mijie(m_name,title,m_time)
values((select v_name from volunteer where v_id=y_v),"志愿者",y_time);

insert into info_mijie(m_name,title,m_time)
values((select d_name from doctor where d_id=y_d),"医生",y_time);
end;
$$
delimiter ;

call positive_mijie('20058032');
select * from info_mijie;

--    7.视图
--        7.1 学生组别分布
drop view if exists view_group;
create view view_group as 
    select s_name as 学生姓名,
    c_group as 组别 from student 
    order by c_group asc;
    
select * from view_group;
--        7.2 建立视图，查看核酸检测阳性情况及检测机构分布
drop view if exists view_pos;
create view view_pos as
     select s_name as 学生姓名,s_id as 学号,s_time as 检测时间,r_unit as 检测机构
     from student natural join  result
     where result.is_neg="阳性";
     
select * from view_pos;
--    8.自定义函数
--        8.1 输入学号，返回检测结果 
drop function if exists sid_select;
delimiter $$
create function sid_select(stu_id char(8)) returns varchar(50)
reads sql data
begin
declare stu_result varchar(50);
select is_neg into stu_result from student s left join result r
on s.c_id=r.r_id
where s.s_id=stu_id;
return stu_result;
end;
$$
delimiter ;

select sid_select("20057019");
--        8.2 建立自定义函数，输入志愿者工号，返回已登记的同学姓名
drop function if exists sign_select;
delimiter $$
create function sign_select(vol_id char(8)) returns varchar(50)
reads sql data
begin
declare stu_name varchar(50);
select s_name into stu_name from student s left join covid_code c
on s.s_id=c.s_id
where c.v_id=vol_id;
return stu_name;
end;
$$
delimiter ;

select sign_select("03");
--        8.3 建立自定义函数，输入医生工号，返回已检测的同学姓名
drop function if exists test_select;
delimiter $$
create function test_select(doc_id char(8)) returns varchar(50)
reads sql data
begin
declare stu_name varchar(50);
select s_name into stu_name from student s left join covid_code c
on s.s_id=c.s_id
where c.d_id=doc_id;
return stu_name;
end;
$$
delimiter ;

select test_select("2");

5.3 针对具体应用场景的数据操纵和查询

5.3.1 输入学号，查询核酸检测结果

select sid_select("20057019");