SQL server数据库期末大作业

• 传染病动态监测系统

背景：

当前社会面临着传染病的严重挑战，特别是近年来新冠肺炎疫情的爆发，更加凸显了传染病监测的必要性和重要性。

传染病是一种可以通过人际接触、空气、食物水源等途径在人与人间传播的疾病。尤其在如今全球化、城市化背景下，传染病的传播速度和范围都得到了极大的加速和扩大。传染病的预防、控制和管理愈加显得重要。而传染病的监测是控制传染病的重要环节。

目的：

1. 监测系统：通过现代科技手段建立起来的监控系统，紧密结合现有医疗体系，及时、准确地监测疾病传播动态，掌握疫情信息和趋势。

2.信息传播：建立起来的信息传播系统能够及时、全面地发布疫情信息，提供前沿的疫情动态。

3.公共卫生体系：监测系统与公共卫生体系建立起连接，促进公共卫生体系的协调发展，先进的监测系统可以借助公共卫生体系推动疾病预防与治理。

4.实现地区数据存储，通过地区映射出传染病的发生和传播的记录，包括地区和名称。

5.实现患者信息存储，包括患者的姓名、年龄、性别、联系方式、地址、登记时间等。

6.实现医院信息存储，包括医院的名称和地址。

7.实现病毒信息存储，病毒的名称、类型、基因型、基因组序列和记录时间等。

通过数据库建立一个传染病监测系统，对传染病的重点人群、重点区域、重点疫情进行监测，分析传染病疫情的发生、传播规律，及时发现或预测传染病的扩散和流行趋势，为卫生部门制定针对性的措施和政策提供参考。

业务逻辑关系：

该系统包含主要业务流程如下：

( 疫情监测：通过各种途径收集各地的疫情信息，记录疫情发生地、时间、人数等信息。

高风险区域监测：每隔一段时间对疫情高发区域进行监测，了解该地区病人数量和病情变化趋势。

重点人群监测：对高风险人群（如医护人员、密切接触者等）进行监测，及时发现病例。

干预措施记录：记录疫情监测中采取的各种干预措施的类型和效果。

数据分析和报告：根据数据库信息分析疫情发展的规律，生成各种图表和报告。 )

数据关系：

在该系统中，主要涉及以下几个实体：

• 地区 (Region) 实体： - 地区ID (RegionID) - 主键，唯一标识地区- 地区名称 (Name) - 地区的名称.
• 风险监测实体（risk_monitor），疫情高风险区域的信息：（视图查询：查询风险区域，风险区域所在病例数 - 风险监测ID（主键）- 风险区域ID（外键，引用地区表）- 监测人员- 监测时间- 监测内容
• 医院(Hospital)实体: - 医院ID(HospitalID) - 主键，唯一标识医院- 医院名字(Name) - 医院的名称- 地址(Address) - 医院的地址
• 病毒 (Virus) 实体（视图查询：查询同一种病毒发生的传播事件和传播类型，传播事件是否结束，再查看有几个病例）- 病毒ID (VirusID) - 主键，唯一标识病毒- 名称 (Name) - 病毒的名称- 种类 (Type) - 病毒的种类，如RNA病毒或DNA病毒- 基因型 (Genotype) - 病毒的基因型- 基因序列(GenomeSequence)- 收录时间 (RecordTime) - 病毒记录的时间
• 病例 (Case) 实体 - 病例ID (CaseID) - 主键，唯一标识病例- 病毒ID (VirusID) - 外键，指向病毒实体中的对应病毒- 感染症状(Symptoms)- 状态 (Status) - 病例的状态，如确诊或疑似- 发病时间 (OnsetTime) - 病例的发病时间- 录入时间 (RecordTime) - 病例记录的时间- 患者ID (PatientID) - 外键，指向患者实体中的对应患者- 地区ID (RegionID) - 外键，指向地区实体中的对应地区- 医院ID(HospitalID) - 外键，指向医院实体中的对应医院
• 患者 (Patient) 实体 - 患者ID (PatientID) - 主键，唯一标识患者- 姓名 (Name) - 患者的姓名- 年龄 (Age) - 患者的年龄- 性别 (Gender) - 患者的性别- 联系方式 (Contact) - 患者的联系方式- 地址 (Address) - 患者的地址- 注册时间 (RegisterTime) - 患者的注册时间
• 传播事件 (TransmissionEvent) 实体 - 传播事件ID (TransmissionEventID) - 主键，唯一标识传播事件- 病例ID (CaseID) - 外键，指向病例实体中的对应病例- 传播类型 (TransmissionType) - 传播类型，如密切接触或聚集感染- 发生地区ID (OccurrenceRegionID) - 外键，指向地区实体中的对应地区- 传播地区ID (TransmissionRegionID) - 外键，指向地区实体中的对应地区- 传播开始时间 (StartTime) - 传播开始的时间- 事件状态 (EndTime) – 结束（1）或者正在发生（0）- 传播路径 (TransmissionPath) - 传播路径的描述

实体联系：

医院实体(Hospital)与病例实体(Case)之间是一对多关系，即一个医院可以有多个病例，但每个病例只能属于一个医院。通过病例实体中的医院ID(HospitalID)外键，可以连接医院实体中的医院ID，找到对应的医院。

地区实体(Region)与病例实体(Case)之间是一对多关系，即一个地区可以有多个病例，但每个病例只能属于一个地区。通过病例实体中的地区ID(RegionID)外键，可以连接地区实体中的地区ID，找到对应地区。

地区实体(Region)与风险监测实体(risk_monitor)之间是一对一关系，每个地区只对应一个风险监测信息，通过风险监测实体中的区域编号（RegionID）外键，可以连接地区实体的地区ID。

地区实体(Region)与传播事件实体(TransmissionEvent)之间也是一对多关系，一个地区可以有多个传播事件，而每个传播事件只能发生在一个地区。通过传播事件实体中的发生地区ID(OccurrenceRegionID)和传播地区ID(TransmissionRegionID)外键，可以分别连接地区实体中的地区ID，找到对应的两个地区。

病毒实体(Virus)与病例实体(Case)之间也是一对多的关系，一个病毒可以感染多个病例。通过病例实体中的病毒ID(VirusID)外键，可以连接病毒实体中的病毒ID，找到对应的病毒信息。

患者实体(Patient)与病例实体(Case)之间是一对多的关系，一个患者可以对应多个病例。通过病例实体中的患者ID(PatientID)外键，可以连接患者实体中的患者ID，找到对应的患者信息。(视图：通过病例找出患者的历史病例)

病例实体(Case)与传播事件实体(TransmissionEvent)之间是一对一关系，每个病例只能对应一个传播事件，而每个传播事件只能由一个病例引起。通过传播事件实体中的病例ID(CaseID)外键，可以连接病例实体中的病例ID，找到对应的病例。

(视图：通过传播事件中的病例ID查找病例数)

综上，以上实体之间的联系主要为一对多关系和一对一关系。在SQL Server中可以通过外键约束来实现这些联系。例如，可以通过病例实体中的医院ID外键和医院实体中的医院ID主键进行关联，确保每个病例只能属于一个医院，同时还能通过查询语句实现从医院实体到病例实体的关联查询。

（参考）

医院数据流动和收集如下：

1. 医院数据的收集：获取各地的医院信息，包括医院的名称、地址和简单介绍，并将其存储到医院实体中。
2. 病例数据的收集：医院根据患者的诊断记录向数据库添加新的病例数据。每个病例都包括病例ID、状态、患者ID、医院ID、环节ID，诊断码和记录时间等信息。这些病例信息存储到病例实体中。
3. 患者数据的收集：医院通过患者信息查询或生成患者ID，并将患者ID、姓名、性别、年龄、居住地、到达时间等信息存储到患者实体中。
4. 环节数据的收集：医院向数据库添加新的环节数据，描述诊疗过程的环节包括环节ID和环节描述等信息，并将其存储到环节实体中。

在这个数据库设计中，病例实体和患者实体之间建立了一对多的关系，因为一个患者可能有多个病例记录。病例实体和医院实体之间也建立了一对多的关系，因为一个医院可能有多个病例记录。最后，在病例实体和环节实体之间建立了一对一的关系，因为每个病例只属于一个环节。

在建立表结构时，确保满足第三范式，避免数据冗余和不一致性。例如，在上述实体中，所有与医院相关的信息都包含在医院实体中，而不是在病例实体中存储重复的信息。同样，所有与患者相关的信息都包含在患者实体中。这样，可以确保数据在不同表之间的一致性，并提高数据查询效率。

统计数据：

该系统需要统计的数据主要包括以下方面：

疫情发展趋势：根据疫情实体中记录的数据信息对疫情发展的趋势进行分析，可以包括病例数、疫情地理分布、病例死亡率等数据指标。

风险区域监测效果：根据风险监测实体中记录的监测数据和风险实体中记录的基本信息，分析监测效果，计算监测得分等。

人群监测及干预措施效果：通过人群监测实体和干预措施实体记录的数据信息，分析监测效果和干预措施的效果，计算干预措施完成度等。

ER图：

以下是该系统的ER图：

ER图转初始关系模式及·规范化(因为上面的实体都是一对多或一对一的关系，所以，满足两个实体转两个表的要求，去掉中间联系表)：

地区（地区ID，地区名称）­

医院（医院ID，医院名字，地址）

病毒（病毒ID，名称，种类，基因型，基因序列，收录时间）

风险监测实体（风险监测ID，监测人员，监测时间，监测内容，风险区域ID）

患者（患者ID，姓名，年龄，性别，联系方式，注册时间，地区ID）

病例（病例ID，感染症状，状态，发病时间，录入时间，病毒ID，患者ID，地区ID，医院ID）

传播事件（传播事件ID，传播类型，传播开始时间，事件状态，传播路径，病例ID，发生地区ID，传播地区ID）

该系统各个实体的SQL建表语句如下：

-- 地区（Region）实体：

CREATE TABLE Region (

    RegionID varchar(20) PRIMARY KEY, -- 地区ID

    Name VARCHAR(50) -- 地区名称

);

-- 风险监测实体（RiskMonitor）：

CREATE TABLE RiskMonitor (

    RiskMonitorID varchar(20) PRIMARY KEY, -- 风险监测实体ID

    MonitorPerson VARCHAR(50), -- 监测人员

    MonitorTime DATETIME, -- 监测时间

    MonitorContent VARCHAR(MAX), -- 监测内容

    RiskAreaID varchar(20) FOREIGN KEY REFERENCES Region(RegionID), -- 风险区域ID（外键，引     用地区表）

);

--医院（Hospital）实体：

CREATE TABLE Hospital (

    HospitalID varchar(30) PRIMARY KEY, -- 医院ID

    Name VARCHAR(100), -- 医院名字

    Address VARCHAR(200), -- 医院地址

);

-- 病毒（Virus）实体：

CREATE TABLE Virus (

    VirusID varchar(40) PRIMARY KEY, -- 病毒ID

    Name VARCHAR(50), -- 病毒名字

    Type VARCHAR(50), -- 病毒种类

    Genotype VARCHAR(50), -- 病毒基因型

    GenomeSequence VARCHAR(MAX), -- 病毒基因序列

    RecordTime DATETIME -- 病毒记录时间

);

-- 病例（Case）实体：

CREATE TABLE [Case] (

    CaseID varchar(30) PRIMARY KEY, -- 病例ID

    VirusID varchar(40) FOREIGN KEY REFERENCES Virus(VirusID), -- 病毒ID（外键，引用病毒表）

    Symptoms VARCHAR(MAX), -- 感染症状

    Status VARCHAR(50), -- 病例状态

    OnsetTime DATETIME, -- 发病时间

    RecordTime DATETIME, -- 记录时间

    PatientID varchar(30) FOREIGN KEY REFERENCES Patient(PatientID), -- 患者ID（外键，引用患者表）

    RegionID varchar(20) FOREIGN KEY REFERENCES Region(RegionID), -- 区域ID（外键，引用地区表）

    HospitalID varchar(30) FOREIGN KEY REFERENCES Hospital(HospitalID), -- 医院ID（外键，引用医院表）

);

-- 患者（Patient）实体：

CREATE TABLE Patient (

    PatientID varchar(30) PRIMARY KEY, -- 患者ID

    Name VARCHAR(50), -- 患者姓名

    Age INT, -- 患者年龄

    Gender VARCHAR(10), -- 患者性别

    Contact VARCHAR(20), -- 患者联系方式

    Address VARCHAR(200), -- 患者地址

    RegisterTime DATETIME -- 注册时间

);

-- 传播事件（TransmissionEvent）实体：

CREATE TABLE TransmissionEvent (

    TransmissionEventID varchar(20) PRIMARY KEY, -- 传播事件ID

    CaseID varchar(30) FOREIGN KEY REFERENCES [Case](CaseID), -- 病例ID（外键，引用病例表）

    TransmissionType VARCHAR(50), -- 传播类型

    OccurrenceRegionID varchar(20) FOREIGN KEY REFERENCES Region(RegionID), -- 发生地区ID（外键，引用地区表）

    TransmissionRegionID varchar(20) FOREIGN KEY REFERENCES Region(RegionID), -- 传播地区ID（外键，引用地区表）

StartTime DATETIME, -- 传播开始时间

Status char(2),-- 事件状态 – 结束（1）或者正在发生（0

    TransmissionPath VARCHAR(MAX) -- 传播路径

);
-- 数据录入

--插入表1 Region 测试数据：

INSERT INTO Region (RegionID, Name)

VALUES

('R001', '北京市'),

('R002', '上海市'),

('R003', '广州市'),

('R004', '深圳市'),

('R005', '武汉市');

--插入表2 Patient 测试数据：

INSERT INTO Patient (PatientID, Name, Age, Gender, Contact, Address, RegisterTime)

VALUES

('P001', '张三', 28, '男', '13312345678', '北京市海淀区', '2021-05-01'),

('P002', '李四', 35, '女', '13987654321', '上海市浦东新区', '2021-04-15'),

('P003', '王五', 45, '男', '15811112222', '广州市天河区', '2021-03-20'),

('P004', '赵六', 22, '女', '13655557777', '深圳市福田区', '2021-06-01'),

('P005', '钱七', 50, '男', '17799998888', '武汉市江汉区', '2021-06-05');

--插入表3 Hospital 测试数据：

INSERT INTO Hospital (HospitalID, Name, Address)

VALUES

('H001', '北京市朝阳医院', '北京市朝阳区朝阳路100号'),

('H002', '上海市卢湾医院', '上海市卢湾区黄陂北路60号'),

('H003', '广州市天河医院', '广州市天河区天河北路452号'),

('H004', '深圳市福田医院', '深圳市福田区福田街道福中路1016号'),

('H005', '武汉市汉口医院', '武汉市汉口区汉中路152号');

--插入表4 Virus 测试数据：

INSERT INTO Virus (VirusID, Name, Type, Genotype, GenomeSequence, RecordTime)

VALUES

('V001', '新型冠状病毒', '病毒', 'B.1.617.2', 'ATAGGTTTTGATCAGACTGCGCTACCATTGCCGTCAGCCTG', '2022-01-01'),

('V002', '甲型H1N1流感病毒', '病毒', '2009 pdm H1N1-like', 'TGAGGGCAATTTTCTCTCTATGGGCAAAAGAGTGTCAGTG', '2021-12-01'),

('V003', '布鲁氏菌', '细菌', '-', 'TGCTGAGCTCATTCAGCGCGTGAGCTGCTGACATCATTCC', '2021-11-01'),

('V004', '结核分枝杆菌', '细菌', '-', 'GCGAGCTCGGTCCGTGGACAACTCGGAGGTTTTGACCGCT', '2022-03-01'),

('V005', '登革热病毒', '病毒', 'DENV-1', 'ATGGCATTGTGAGACCTCACCACCAACCTTACGGTGCTCG', '2022-04-01');

--插入表5 Case 测试数据：

INSERT INTO [Case] (CaseID, VirusID, Symptoms, Status, OnsetTime, RecordTime, PatientID, RegionID, HospitalID)

VALUES

('C001', 'V001', '发热、咳嗽、乏力', '确诊', '2022-01-03', '2022-01-05', 'P001', 'R001', 'H001'),

('C002', 'V002', '发热、咳嗽、头痛', '待检测', '2021-12-28', '2021-12-30', 'P002', 'R002', 'H002'),

('C003', 'V003', '发热、关节痛、皮疹', '待检测', '2021-11-25', '2021-11-28', 'P003', 'R003', 'H003'),

('C004', 'V004', '咳嗽、咳痰、胸闷', '确诊', '2022-03-05', '2022-03-08', 'P004', 'R004', 'H004'),

('C005', 'V005', '发热、头痛、肌肉疼痛', '待检测', '2022-04-03', '2022-04-05', 'P005', 'R005', 'H005');

--插入表6 TransmissionEvent 测试数据：

INSERT INTO TransmissionEvent (TransmissionEventID, CaseID, TransmissionType, OccurrenceRegionID, TransmissionRegionID, StartTime, Status, TransmissionPath)

VALUES

('T001', 'C001', '感染', 'R001', 'R002', '2022-01-07', '1', '火车站'),

('T002', 'C002', '接触', 'R002', 'R003', '2022-01-01', '0', '餐厅'),

('T003', 'C003', '飞沫传播', 'R003', 'R004', '2021-11-30', '1', '地铁'),

('T004', 'C004', '接触', 'R004', 'R001', '2022-03-10', '1', '超市'),

('T005', 'C005', '蚊虫叮咬传播', 'R005', 'R002', '2022-04-06', '0', '家中');

--插入表7 RiskMonitor 测试数据：

INSERT INTO RiskMonitor (RiskMonitorID, MonitorPerson, MonitorTime, MonitorContent, RiskAreaID)

VALUES

('M001', '张三', '2022-01-07 10:00:00', '对病毒进行监测', 'R001'),

('M002', '李四', '2022-01-05 11:00:00', '监测病毒传播情况', 'R002'),

('M003', '王五', '2021-11-29 09:00:00', '排查飞沫传播情况', 'R003'),

('M004', '赵六', '2022-03-10 15:00:00', '对隐蔽性感染点进行排查', 'R004'),

('M005', '钱七', '2022-04-05 12:00:00', '监测蚊虫病毒传播情况', 'R005');

操作

基于病毒表结构，可以实现以下操作：

插入病毒
INSERT INTO Virus (VirusID, Name, Type, Size, Genotype, HostType, RecordTime) VALUES (1, ‘COVID-19’, ‘RNA病毒’, 0.1, ‘B.1.1.7’, ‘人类’, GETDATE());

插入病例
INSERT INTO Case (CaseID, VirusID, Status, OnsetTime, RecordTime, PatientID, RegionID) VALUES (1, 1, ‘确诊’, ‘2020-01-20’, GETDATE(), 1, 1);

插入患者
INSERT INTO Patient (PatientID, Name, Age, Gender, Contact, Address, RegisterTime) VALUES (1, ‘张三’, 30, ‘男’, ‘13800xxxxxx’, ‘北京市朝阳区’, GETDATE());

插入地区
INSERT INTO Region (RegionID, Name, ParentRegionID) VALUES (1, ‘中国’, NULL);
INSERT INTO Region (RegionID, Name, ParentRegionID) VALUES (2, ‘北京市’, 1);
INSERT INTO Region (RegionID, Name, ParentRegionID) VALUES (3, ‘朝阳区’, 2);

插入传播事件
INSERT INTO TransmissionEvent (TransmissionEventID, CaseID, TransmissionType, OccurrenceRegionID, TransmissionRegionID, StartTime, EndTime, TransmissionPath)
VALUES (1, 1, ‘密切接触’, 2, 3, ‘2020-01-20’, ‘2020-01-25’, ‘张三 -> 李四 -> 王五’);

以上是建立传染病基因库的一个可行方案，可以实现对传染病的收集和监测，同时也支持传播率和致死率的统计等功能。需要注意的是，在具体实践中，具体的表结构和操作可能需要根据具体需求进行调整。

--视图创建

-- 整体视图，查看传染病的传播详细信息

create view get_all as

    select top(100)  r.RegionID, r.name 地区名称, m. RiskMonitorID 风险监测id,

m. MonitorPerson 监测人员, m. MonitorTime 监测时间, m. MonitorContent 监测内容, m.RiskAreaID 风险地区id,

h. HospitalID 医院id, h. Name 医院名称,  h. Address 医院地址,

v. VirusID 病毒id, v. Name 病毒名称, v. Type 病毒种类, v. Genotype 病毒基因型, v. GenomeSequence 病毒基因序列, v.RecordTime 病毒记录时间,

c. CaseID 病例ID, c. Symptoms 感染症状, c. Status 病例状态, c. OnsetTime 发病时间, c. RecordTime 记录时间,

p. PatientID 患者ID, p. Name 患者姓名, p. Age 患者年龄, p. Gender 患者性别, p.Contact 患者联系方式, p. Address 患者地址, p. RegisterTime 患者注册时间,

t. TransmissionEventID 传播事件id, t. TransmissionType 传播类型, t. StartTime 传播开始时间, t. Status 是否结束, t. TransmissionPath 传播路径

    FROM [Case] c

JOIN Virus v ON c.VirusID = v.VirusID

JOIN Patient p ON c.PatientID = p.PatientID

JOIN Region r ON c.RegionID = r.RegionID

LEFT JOIN Hospital h ON c.HospitalID = h.HospitalID

LEFT JOIN TransmissionEvent t ON c.CaseID = t.CaseID

LEFT JOIN Region tr ON t.TransmissionRegionID = tr.RegionID

LEFT JOIN RiskMonitor m ON c.RegionID = m.RiskAreaID

LEFT JOIN Region ra ON m.RiskAreaID = ra.RegionID

 order by c.VirusID;

-- 建立视图，实现查询未结束的传染病感染情况

create view get_key as

    select top(100)

         v. Name 病毒名称, v.RecordTime 病毒记录时间, t. StartTime 传播事件开始时间,

         p. Name 患者姓名, p. Age 患者年龄, p. Gender 患者性别,p. Address 患者地址, p. RegisterTime 患者注册时间,

         m. MonitorPerson 监测人员, m. MonitorTime 监测时间, m. MonitorContent 监测内容,

         h. Name 医院名称,

         c. Symptoms 感染症状, c. Status 病例状态, c. OnsetTime 发病时间, c. RecordTime 记录时间,

         t. TransmissionEventID 传播事件id, t. TransmissionType 传播类型, t. TransmissionPath 传播路径

    FROM [Case] c

JOIN Virus v ON c.VirusID = v.VirusID

JOIN Patient p ON c.PatientID = p.PatientID

LEFT JOIN Hospital h ON c.HospitalID = h.HospitalID

LEFT JOIN TransmissionEvent t ON c.CaseID = t.CaseID

LEFT JOIN Region tr ON t.TransmissionRegionID = tr.RegionID

LEFT JOIN RiskMonitor m ON c.RegionID = m.RiskAreaID

LEFT JOIN Region ra ON m.RiskAreaID = ra.RegionID

  where t.Status=1 order by c.VirusID;

/* 创建存储过程，实现输入传播事件id，结束传播事件(业务需求，对正在传播的事件，如果结束了，

 输入事件id，实现对传播事件的终止)*/

 -- 事件是否结束，采用逻辑删除，规定：0--事件已结束；1--事件正在进行中

 create procedure over_Event @EventID varchar(20) --status相当于逻辑删除

as

 update TransmissionEvent set Status = 0 where TransmissionEventID=@EventID

 exec over_event 'T007';

 -- 创建查询视图，实现对各病毒的病例情况统计

 create view show_virus_case as

 select top(100) v.Name 病毒名称,p.Name 患者姓名,p.Age 患者年龄, Symptoms 患者症状

 from Virus v

 left join [Case] c on v.VirusID = c.VirusID

 left join Patient p on p.PatientID = c.PatientID

 order by v.VirusID,c.RecordTime

-- 创建视图，实现对各地区的传染病事件统计（只显示未结束的传染病事件）

 create view show_Region_case as

 select top(100) r.Name 地区, v.Name, t.StartTime,t.TransmissionPath

 from Virus v

 left join [Case] c on v.VirusID = c.VirusID

 left join region r on c.RegionID = r.RegionID

 left join TransmissionEvent t on t.CaseID = c.CaseID

 where t.Status = '1'

 order by r.RegionID,v.VirusID,c.RecordTime