SQL Server 医院管理系统数据库设计：从ER图到6张表与15个科室数据建模实战

最新推荐文章于 2026-07-04 16:28:27 发布

原创最新推荐文章于 2026-07-04 16:28:27 发布 · 365 阅读 ·

大模型引用 1 次

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

代码可运行

SQL Server医院管理系统数据库设计实战：从ER建模到15科室数据建模

医院信息管理系统的数据库设计是医疗信息化建设的核心环节。一个设计良好的数据库不仅能提高医院运营效率，还能为患者提供更优质的医疗服务。本文将带你从零开始，完成一个包含15个科室的医院管理系统的数据库设计与实现。

1. 医院管理系统数据库设计概述

医院管理系统数据库需要处理复杂的医疗业务流程，包括患者挂号、医生排班、药品管理、病房分配等多个环节。设计这样的系统需要考虑以下几个关键因素：

数据完整性 ：确保医疗数据的准确性和一致性
查询效率 ：优化数据库结构以支持高频查询操作
扩展性 ：为未来系统功能扩展预留空间
安全性 ：保护患者隐私和敏感医疗信息

典型医院管理系统包含以下核心实体 ：

患者信息
医生信息
科室信息
病房信息
护士信息
医疗记录

这些实体之间存在复杂的关联关系，需要通过合理的数据库设计来准确表达。

2. 概念模型设计：ER图构建

2.1 实体识别与属性定义

首先我们需要识别系统中的主要实体及其属性：

科室表(Department)

科室号(DeptID)：主键
科室名(DeptName)
科室地址(Location)
科室电话(Phone)
科室主任(Head)

医生表(Doctor)

医生编号(DoctorID)：主键
姓名(Name)
性别(Gender)
年龄(Age)
职称(Title)
科室号(DeptID)：外键

患者表(Patient)

病历号(RecordID)：主键
姓名(Name)
性别(Gender)
年龄(Age)
病房号(RoomID)：外键
主治医生(DoctorName)
血型(BloodType)
诊断结果(Diagnosis)
科室号(DeptID)：外键

病房表(Ward)

病房号(RoomID)：主键
床位数(Beds)
病房地址(Location)
科室号(DeptID)：外键

护士表(Nurse)

护士编号(NurseID)：主键
姓名(Name)
性别(Gender)
年龄(Age)
科室号(DeptID)：外键

分配表(Assignment)

病房号(RoomID)：复合主键，外键
护士编号(NurseID)：复合主键，外键

2.2 实体关系分析

医院管理系统中的实体之间存在以下主要关系：

科室与医生 ：一对多关系（一个科室有多个医生）
科室与病房 ：一对多关系（一个科室管理多个病房）
科室与护士 ：一对多关系（一个科室有多个护士）
医生与患者 ：一对多关系（一个医生治疗多个患者）
病房与患者 ：一对多关系（一个病房容纳多个患者）
护士与病房 ：多对多关系（通过分配表实现）

2.3 ER图绘制

基于上述分析，我们可以绘制出医院管理系统的ER图（此处为文字描述）：

科室(DeptID, DeptName, Location, Phone, Head)
    ↑
    | 1:N
医生(DoctorID, Name, Gender, Age, Title, DeptID)
    ↑
    | 1:N
患者(RecordID, Name, Gender, Age, RoomID, DoctorName, BloodType, Diagnosis, DeptID)
    ↑
    | N:1
病房(RoomID, Beds, Location, DeptID)
    ↑
    | N:M (通过分配表)
护士(NurseID, Name, Gender, Age, DeptID)

3. 逻辑模型设计：关系模式转换

3.1 ER图到关系模式的转换

根据ER图，我们可以将其转换为以下关系模式：

科室表 ：科室(科室号, 科室名, 科室地址, 科室电话, 科室主任)
医生表 ：医生(医生编号, 姓名, 性别, 年龄, 职称, 科室号)
患者表 ：患者(病历号, 姓名, 性别, 年龄, 病房号, 主治医生, 血型, 诊断结果, 科室号)
病房表 ：病房(病房号, 床位数, 病房地址, 科室号)
护士表 ：护士(护士编号, 姓名, 性别, 年龄, 科室号)
分配表 ：分配(病房号, 护士编号)

3.2 范式化设计

为确保数据库设计的规范性，我们需要对关系模式进行范式化检查：

第一范式(1NF) ：所有属性都是原子的，不可再分
第二范式(2NF) ：满足1NF，且非主属性完全依赖于主键
第三范式(3NF) ：满足2NF，且消除传递依赖

经过分析，我们的设计满足第三范式：

每个表都有明确的主键
非主属性完全依赖于主键
不存在传递依赖
外键关系合理

3.3 索引设计

为提高查询效率，我们需要在关键字段上创建索引：

科室表 ：在科室号上创建主键索引
医生表 ：
- 医生编号：主键索引
- 科室号：外键索引
- 姓名：唯一索引（假设姓名唯一）
患者表 ：
- 病历号：主键索引
- 病房号：外键索引
- 科室号：外键索引
病房表 ：
- 病房号：主键索引
- 科室号：外键索引
护士表 ：
- 护士编号：主键索引
- 科室号：外键索引
分配表 ：
- (病房号, 护士编号)：复合主键索引
- 病房号：外键索引
- 护士编号：外键索引

4. 物理模型实现：SQL Server数据库创建

4.1 数据库创建

CREATE DATABASE HospitalManagementSystem
ON PRIMARY 
(
    NAME = 'HospitalManagementSystem_Data',
    FILENAME = 'C:\Data\HospitalManagementSystem.mdf',
    SIZE = 100MB,
    FILEGROWTH = 20MB
)
LOG ON 
(
    NAME = 'HospitalManagementSystem_Log',
    FILENAME = 'C:\Data\HospitalManagementSystem.ldf',
    SIZE = 50MB,
    FILEGROWTH = 10MB
);
GO

USE HospitalManagementSystem;
GO

4.2 表结构创建

-- 创建科室表
CREATE TABLE Department (
    DeptID CHAR(3) NOT NULL PRIMARY KEY,
    DeptName VARCHAR(20) NOT NULL,
    Location VARCHAR(20),
    Phone VARCHAR(10),
    Head VARCHAR(10)
);
GO

-- 创建医生表
CREATE TABLE Doctor (
    DoctorID CHAR(6) NOT NULL PRIMARY KEY,
    Name VARCHAR(20) NOT NULL UNIQUE,
    Gender CHAR(2) NOT NULL,
    Age INT NOT NULL,
    Title VARCHAR(20) DEFAULT '医师',
    DeptID CHAR(3),
    CONSTRAINT FK_Doctor_Department FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);
GO

-- 创建病房表
CREATE TABLE Ward (
    RoomID CHAR(3) NOT NULL PRIMARY KEY,
    Beds INT,
    Location VARCHAR(20),
    DeptID CHAR(3),
    CONSTRAINT FK_Ward_Department FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);
GO

-- 创建护士表
CREATE TABLE Nurse (
    NurseID CHAR(6) NOT NULL PRIMARY KEY,
    Name VARCHAR(20) NOT NULL,
    Gender CHAR(2) NOT NULL,
    Age INT NOT NULL,
    DeptID CHAR(3),
    CONSTRAINT FK_Nurse_Department FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);
GO

-- 创建患者表
CREATE TABLE Patient (
    RecordID CHAR(5) NOT NULL PRIMARY KEY,
    Name VARCHAR(20) NOT NULL,
    Gender CHAR(2) NOT NULL,
    Age INT NOT NULL,
    RoomID CHAR(3),
    DoctorName VARCHAR(20),
    BloodType CHAR(2),
    Diagnosis VARCHAR(20),
    DeptID CHAR(3),
    CONSTRAINT FK_Patient_Ward FOREIGN KEY (RoomID) REFERENCES Ward(RoomID),
    CONSTRAINT FK_Patient_Department FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);
GO

-- 创建分配表
CREATE TABLE Assignment (
    RoomID CHAR(3) NOT NULL,
    NurseID CHAR(6) NOT NULL,
    PRIMARY KEY (RoomID, NurseID),
    CONSTRAINT FK_Assignment_Ward FOREIGN KEY (RoomID) REFERENCES Ward(RoomID),
    CONSTRAINT FK_Assignment_Nurse FOREIGN KEY (NurseID) REFERENCES Nurse(NurseID)
);
GO

4.3 数据完整性约束

除了上述表定义中的外键约束，我们还添加了以下约束：

医生表 ：职称默认值为"医师"
患者表 ：
- 年龄必须大于0
- 血型只能是A、B、AB、O四种
病房表 ：床位数必须大于等于0

-- 添加患者表约束
ALTER TABLE Patient
ADD CONSTRAINT CK_Patient_Age CHECK (Age > 0),
    CONSTRAINT CK_Patient_BloodType CHECK (BloodType IN ('A', 'B', 'AB', 'O'));
GO

-- 添加病房表约束
ALTER TABLE Ward
ADD CONSTRAINT CK_Ward_Beds CHECK (Beds >= 0);
GO

5. 数据初始化：15个科室及样本数据

5.1 科室数据插入

-- 插入15个科室数据
INSERT INTO Department (DeptID, DeptName, Location, Phone, Head)
VALUES 
('101', '内科', 'A1101', '10001', '高飞'),
('102', '外科', 'A1102', '10002', '李想'),
('103', '儿科', 'A1103', '10003', '邓杨'),
('104', '妇科', 'A1104', '10004', '刘宇'),
('105', '神经科', 'A1105', '10005', '白皓'),
('201', '脑外科', 'A1201', '20001', '宋思琪'),
('202', '泌尿科', 'A1202', '20002', '孙怡'),
('203', '骨科', 'A1203', '20003', '杨子龙'),
('204', '内分泌科', 'A1204', '20004', '张子怡'),
('205', '口腔科', 'A1205', '20005', '郭易曼'),
('301', '眼科', 'A1301', '30001', '王陆明'),
('302', '耳喉鼻科', 'A1302', '30002', '韩子琦'),
('303', '皮肤科', 'A1303', '30003', '徐若鸿'),
('304', '心脏外科', 'A1304', '30004', '李丽'),
('305', '胸外科', 'A1305', '30005', '陈若玉');
GO

5.2 医生数据插入

-- 插入医生数据
INSERT INTO Doctor (DoctorID, Name, Gender, Age, Title, DeptID)
VALUES 
('101001', '高飞', '男', 30, '主任医师', '101'),
('101002', '杨蕾', '女', 24, '住院医师', '101'),
('101003', '齐风', '男', 27, '主治医师', '101'),
('101004', '李想', '女', 45, '主任医师', '102'),
('101005', '朱珠', '女', 30, '主治医师', '102'),
('101006', '陆风', '男', 35, '主治医师', '102'),
('101007', '邓杨', '男', 30, '主治医师', '103'),
('101008', '程悦', '女', 28, '副主治医师', '103'),
('101009', '刘宇', '女', 40, '副主任医师', '104'),
('101010', '刘子怡', '女', 34, '主治医师', '104'),
('101011', '白皓', '男', 58, '主任医师', '105'),
('101012', '苏楠', '女', 32, '主治医师', '105'),
('101013', '宋思琪', '女', 50, '主任医师', '201'),
('101014', '陆大明', '男', 24, '住院医师', '201'),
('101015', '孙怡', '女', 33, '主治医师', '202'),
('101016', '刘楠', '男', 33, '主治医师', '202'),
('101017', '杨子龙', '男', 35, '主治医师', '203'),
('101018', '焦丹', '女', 23, '住院医师', '203'),
('101019', '张子怡', '女', 38, '副主任医师', '204'),
('101020', '李娟', '女', 32, '主治医师', '204'),
('101021', '郭易曼', '女', 35, '主治医师', '205'),
('101022', '刘晓', '男', 22, '住院医师', '205'),
('101023', '王陆明', '男', 36, '主治医师', '301'),
('101024', '张珍', '女', 29, '副主治医师', '301'),
('101025', '韩子琦', '男', 31, '主治医师', '302'),
('101026', '乔芳', '女', 31, '主治医师', '302'),
('101027', '徐若鸿', '男', 49, '主任医师', '303'),
('101028', '陆磊', '男', 31, '主治医师', '303'),
('101029', '李丽', '女', 51, '主任医师', '304'),
('101030', '高涛', '男', 31, '主治医师', '304'),
('101031', '陈若玉', '女', 39, '副主任医师', '305'),
('101032', '刘思雨', '女', 30, '主治医师', '305');
GO

5.3 病房数据插入

-- 插入病房数据
INSERT INTO Ward (RoomID, Beds, Location, DeptID)
VALUES 
('001', 4, 'B1001', '101'),
('002', 5, 'B1002', '101'),
('003', 1, 'B1003', '102'),
('004', 2, 'B1004', '102'),
('005', 0, 'B1005', '103'),
('006', 3, 'B1006', '103'),
('007', 2, 'B1007', '104'),
('008', 3, 'B1008', '105'),
('009', 1, 'B1009', '105'),
('010', 2, 'B1010', '201'),
('011', 1, 'B1011', '201'),
('012', 3, 'B1012', '202'),
('013', 2, 'B1013', '203'),
('014', 2, 'B1014', '204'),
('015', 1, 'B1015', '204'),
('016', 5, 'B1016', '205'),
('017', 2, 'B1017', '301'),
('018', 1, 'B1018', '302'),
('019', 3, 'B1019', '303'),
('020', 1, 'B1020', '303'),
('021', 2, 'B1021', '304'),
('022', 1, 'B1022', '304'),
('023', 3, 'B1023', '305'),
('024', 2, 'B1024', '305');
GO

5.4 护士数据插入

-- 插入护士数据
INSERT INTO Nurse (NurseID, Name, Gender, Age, DeptID)
VALUES 
('110101', '夏一柳', '女', 25, '101'),
('110102', '席梦荣', '女', 28, '101'),
('110103', '李文倩', '女', 22, '102'),
('110104', '绕詹林', '男', 24, '102'),
('110105', '吴申萌', '女', 29, '103'),
('110106', '田甜', '女', 23, '103'),
('110107', '高悦然', '女', 25, '104'),
('110108', '刘子诺', '女', 21, '104'),
('110109', '张以诺', '女', 22, '105'),
('110110', '赵佳宇', '女', 22, '105'),
('110111', '张怡铭', '女', 35, '201'),
('110112', '蒋欣芹', '女', 26, '201'),
('110113', '马航钰', '女', 30, '202'),
('110114', '苏芷璇', '女', 25, '202'),
('110115', '王晓甜', '女', 24, '203'),
('110116', '陈子媛', '女', 26, '203'),
('110117', '董璇', '女', 21, '204'),
('110118', '孙玉', '女', 23, '204'),
('110119', '李婉婷', '女', 28, '205'),
('110120', '宋雨涵', '女', 31, '205'),
('110121', '邓梦琪', '女', 24, '301'),
('110122', '崔佳玉', '女', 26, '301'),
('110123', '郭星瞳', '女', 24, '302'),
('110124', '郭思羽', '女', 23, '302'),
('110125', '王雨橙', '女', 28, '303'),
('110126', '刘子琳', '女', 27, '303'),
('110127', '李雨霏', '女', 29, '304'),
('110128', '陆嘉晨', '女', 24, '304'),
('110129', '张馨予', '女', 23, '305'),
('110130', '刘欣悦', '女', 27, '305');
GO

5.5 患者数据插入

-- 插入患者数据
INSERT INTO Patient (RecordID, Name, Gender, Age, RoomID, DoctorName, BloodType, Diagnosis, DeptID)
VALUES 
('21101', '王胜安', '男', 31, '001', '齐风', 'A', '肺炎', '101'),
('21102', '蔡壮保', '男', 54, '002', '高飞', 'B', '慢性气管炎', '101'),
('21103', '易江伟', '男', 28, '003', '陆风', 'O', '自发性气胸', '102'),
('21104', '张顺谷', '男', 32, '004', '朱珠', 'AB', '胆道结石', '102'),
('21105', '李鑫灏', '男', 8, '006', '邓杨', 'A', '细菌性痢疾', '103'),
('21106', '梁澄静', '女', 8, '006', '程悦', 'A', '诺如腹泻', '103'),
('21107', '时党舒', '女', 26, '007', '刘宇', 'B', '宫颈炎', '104'),
('21108', '何刚名', '男', 66, '008', '白皓', 'O', '脑梗塞', '105'),
('21109', '严席华', '男', 58, '008', '苏楠', 'B', '脑出血', '105'),
('21110', '刘鲜发', '男', 32, '010', '宋思琪', 'A', '脑积水', '201'),
('21111', '何毅', '男', 35, '010', '陆大明', 'O', '脑脓肿', '201'),
('21112', '唐宸禹', '男', 38, '011', '宋思琪', 'B', '颅脑外伤', '201'),
('21113', '张顺联', '男', 50, '012', '刘楠', 'A', '泌尿结石', '202'),
('21114', '汤青', '女', 29, '012', '孙怡', 'B', '急性肾炎', '202'),
('21115', '柯纤栩', '女', 34, '013', '焦丹', 'AB', '腰间盘突出', '203'),
('21116', '夏莱', '女', 30, '014', '张子怡', 'B', '电解质紊乱', '204'),
('21117', '杜鹃', '女', 28, '015', '李娟', 'A', '内分泌紊乱', '204'),
('21118', '张洪赫', '男', 25, '016', '郭易曼', 'B', '慢性牙周炎', '205'),
('21119', '池慕颖', '女', 26, '016', '刘晓', 'AB', '口腔白斑', '205'),
('21120', '齐芬霞', '女', 69, '017', '王陆明', 'O', '白内障', '301'),
('21121', '卢全旭', '男', 66, '017', '张珍', 'A', '青光眼', '301'),
('21122', '卓互知', '男', 45, '018', '韩子琦', 'AB', '中耳炎', '302'),
('21123', '元感奇', '男', 16, '019', '徐若鸿', 'O', '扁平疣', '303'),
('21124', '秦长乐', '男', 32, '020', '陆磊', 'B', '皮肤癣', '303'),
('21125', '丘寸心', '男', 48, '021', '李丽', 'O', '血胸', '304'),
('21126', '路从风', '男', 18, '022', '高涛', 'B', '漏斗胸', '304'),
('21127', '王浩', '男', 29, '023', '陈若玉', 'A', '胸部异物', '305'),
('21128', '潘奕', '女', 30, '024', '刘思雨', 'AB', '胸腔积液', '305');
GO

5.6 护士分配数据插入

-- 插入护士分配数据
INSERT INTO Assignment (RoomID, NurseID)
VALUES 
('001', '110101'),
('002', '110102'),
('003', '110103'),
('004', '110104'),
('005', '110105'),
('006', '110106'),
('007', '110107'),
('007', '110108'),
('008', '110109'),
('009', '110110'),
('010', '110111'),
('011', '110112'),
('012', '110113'),
('012', '110114'),
('013', '110115'),
('013', '110116'),
('014', '110117'),
('015', '110118'),
('016', '110119'),
('016', '110120'),
('017', '110121'),
('017', '110122'),
('018', '110123'),
('018', '110124'),
('019', '110125'),
('020', '110126'),
('021', '110127'),
('022', '110128'),
('023', '110129'),
('024', '110130');
GO

6. 数据库操作与查询示例

6.1 基本CRUD操作

插入新患者记录

INSERT INTO Patient (RecordID, Name, Gender, Age, RoomID, DoctorName, BloodType, Diagnosis, DeptID)
VALUES ('21129', '张淦', '男', 25, '024', '刘思雨', 'O', '胸腔积液', '305');
GO

更新护士年龄

UPDATE Nurse 
SET Age = 26 
WHERE Name = '夏一柳';
GO

删除患者记录

DELETE FROM Patient 
WHERE RecordID = '21129';
GO

6.2 复杂查询示例

查询患有脑积水的患者信息

SELECT p.RecordID, p.Name, p.Gender, p.Age, p.RoomID, p.BloodType, 
       d.Name AS DoctorName, d.Title, d.DeptID
FROM Patient p
JOIN Doctor d ON p.DoctorName = d.Name
WHERE p.Diagnosis = '脑积水';
GO

统计各科室护士人数

SELECT d.DeptName, COUNT(n.NurseID) AS NurseCount
FROM Department d
LEFT JOIN Nurse n ON d.DeptID = n.DeptID
GROUP BY d.DeptName;
GO

查询拥有多个病房的科室

SELECT d.DeptName, COUNT(w.RoomID) AS WardCount
FROM Department d
JOIN Ward w ON d.DeptID = w.DeptID
GROUP BY d.DeptName
HAVING COUNT(w.RoomID) > 1;
GO

计算医生平均年龄

SELECT AVG(Age) AS AvgDoctorAge
FROM Doctor;
GO

按年龄降序排列患者

SELECT Name, Age
FROM Patient
ORDER BY Age DESC;
GO

6.3 视图与存储过程

创建患者信息视图

CREATE VIEW v_PatientInfo AS
SELECT p.RecordID, p.Name AS PatientName, p.Gender, p.BloodType, p.RoomID,
       d.Name AS DoctorName, d.Title, dept.DeptName, dept.Location AS DeptLocation
FROM Patient p
JOIN Doctor d ON p.DoctorName = d.Name
JOIN Department dept ON p.DeptID = dept.DeptID;
GO

-- 使用视图查询
SELECT * FROM v_PatientInfo;
GO

创建按姓名查询患者的存储过程

CREATE PROCEDURE sp_SearchPatientByName
    @NamePattern VARCHAR(20)
AS
BEGIN
    SELECT p.RecordID, p.Name, p.Gender, p.Age, p.BloodType, p.Diagnosis,
           d.Name AS DoctorName, d.Title, dept.DeptName
    FROM Patient p
    JOIN Doctor d ON p.DoctorName = d.Name
    JOIN Department dept ON p.DeptID = dept.DeptID
    WHERE p.Name LIKE @NamePattern;
END;
GO

-- 执行存储过程
EXEC sp_SearchPatientByName '张%';
GO

创建查询护士负责病房的存储过程

CREATE PROCEDURE sp_GetNurseWardInfo
    @NurseID CHAR(6),
    @RoomID CHAR(3) OUTPUT,
    @BedCount VARCHAR(10) OUTPUT
AS
BEGIN
    SELECT @RoomID = a.RoomID, @BedCount = CONVERT(VARCHAR, w.Beds)
    FROM Assignment a
    JOIN Ward w ON a.RoomID = w.RoomID
    WHERE a.NurseID = @NurseID;
END;
GO

-- 执行存储过程
DECLARE @Room CHAR(3), @Beds VARCHAR(10);
EXEC sp_GetNurseWardInfo '110101', @Room OUTPUT, @Beds OUTPUT;
PRINT '病房号: ' + @Room;
PRINT '床位数: ' + @Beds;
GO

6.4 触发器实现业务逻辑

患者出院时自动更新病床数

CREATE TRIGGER tr_PatientDischarge
ON Patient
AFTER DELETE
AS
BEGIN
    UPDATE Ward
    SET Beds = Beds + 1
    WHERE RoomID IN (SELECT RoomID FROM deleted);
END;
GO

-- 测试触发器
DELETE FROM Patient WHERE RecordID = '21102';
GO

7. 数据库设计优化建议

7.1 性能优化

索引优化 ：
- 在频繁查询的字段上创建适当的索引
- 避免过度索引，因为索引会降低插入和更新性能
查询优化 ：
- 使用JOIN代替子查询
- 只查询需要的字段，避免SELECT *
- 对大表使用分页查询
数据库配置优化 ：
- 调整SQL Server内存配置
- 优化tempdb配置
- 定期更新统计信息

7.2 安全性考虑

权限控制 ：
- 为不同角色创建不同的数据库用户
- 实施最小权限原则
数据加密 ：
- 对敏感数据如患者个人信息进行加密
- 考虑使用SQL Server的透明数据加密(TDE)功能
审计日志 ：
- 记录关键数据的修改操作
- 定期审查数据库访问日志

7.3 扩展性设计

预留扩展字段 ：
- 在关键表中添加备用字段
- 使用扩展表存储未来可能新增的属性
分区策略 ：
- 对大型表考虑按时间或科室分区
- 使用文件组分散I/O负载
归档策略 ：
- 设计历史数据归档方案
- 考虑使用SQL Server的分区切换功能实现快速归档

8. 常见问题与解决方案

8.1 数据一致性问题

问题：当删除科室记录时，如何保证相关医生、患者等数据的完整性？

解决方案 ：

使用级联删除（谨慎使用，可能导致意外数据丢失）
在应用程序中实现事务处理，先删除相关记录再删除科室
使用触发器确保数据一致性

-- 示例：使用触发器防止有医生的科室被删除
CREATE TRIGGER tr_PreventDepartmentDelete
ON Department
INSTEAD OF DELETE
AS
BEGIN
    IF EXISTS (SELECT 1 FROM Doctor WHERE DeptID IN (SELECT DeptID FROM deleted))
    BEGIN
        RAISERROR('无法删除有医生的科室', 16, 1);
        RETURN;
    END
    DELETE FROM Department WHERE DeptID IN (SELECT DeptID FROM deleted);
END;
GO

8.2 性能瓶颈问题

问题：患者表数据量很大时，查询性能下降明显。

解决方案 ：

对患者表按科室或时间进行分区
创建适当的覆盖索引
使用查询提示优化特定查询

-- 创建覆盖索引示例
CREATE INDEX IX_Patient_DeptID_Diagnosis 
ON Patient(DeptID, Diagnosis)
INCLUDE (Name, Gender, Age);
GO

8.3 并发访问问题

问题：多个用户同时更新同一病房的床位数时出现冲突。

解决方案 ：

使用乐观并发控制
实现行版本控制
在应用程序中使用适当的事务隔离级别

-- 使用行版本控制的更新示例
UPDATE Ward 
SET Beds = Beds - 1
OUTPUT INSERTED.RoomID, INSERTED.Beds
WHERE RoomID = '001' AND Beds > 0;
GO

9. 进阶功能实现

9.1 全文检索功能

为支持病历内容的全文检索，可以启用SQL Server的全文检索功能：

-- 创建全文目录
CREATE FULLTEXT CATALOG PatientDiagnosisCatalog AS DEFAULT;
GO

-- 在诊断字段上创建全文索引
CREATE FULLTEXT INDEX ON Patient(Diagnosis)
KEY INDEX PK_Patient
ON PatientDiagnosisCatalog;
GO

-- 使用全文检索查询
SELECT RecordID, Name, Diagnosis
FROM Patient
WHERE CONTAINS(Diagnosis, '肺炎 OR 脑出血');
GO

9.2 数据仓库与报表

为支持医院管理决策，可以构建数据仓库和报表：

-- 创建科室患者统计表
CREATE TABLE DeptPatientStats (
    StatDate DATE NOT NULL,
    DeptID CHAR(3) NOT NULL,
    DeptName VARCHAR(20) NOT NULL,
    PatientCount INT NOT NULL,
    AvgAge DECIMAL(5,2) NOT NULL,
    PRIMARY KEY (StatDate, DeptID)
);
GO

-- 创建每日统计的存储过程
CREATE PROCEDURE sp_GenerateDailyPatientStats
AS
BEGIN
    DECLARE @Today DATE = CAST(GETDATE() AS DATE);
    
    -- 删除当天的旧数据（如果存在）
    DELETE FROM DeptPatientStats WHERE StatDate = @Today;
    
    -- 插入新统计数据
    INSERT INTO DeptPatientStats (StatDate, DeptID, DeptName, PatientCount, AvgAge)
    SELECT @Today, d.DeptID, d.DeptName, 
           COUNT(p.RecordID), AVG(CAST(p.Age AS DECIMAL(5,2)))
    FROM Department d
    LEFT JOIN Patient p ON d.DeptID = p.DeptID
    GROUP BY d.DeptID, d.DeptName;
END;
GO

-- 执行统计
EXEC sp_GenerateDailyPatientStats;
GO

-- 查询统计结果
SELECT * FROM DeptPatientStats ORDER BY StatDate DESC, PatientCount DESC;
GO

9.3 数据备份与恢复策略

为确保数据安全，需要制定完善的备份策略：

-- 完整备份
BACKUP DATABASE HospitalManagementSystem
TO DISK = 'C:\Backups\HospitalManagementSystem_Full.bak'
WITH COMPRESSION, STATS = 10;
GO

-- 差异备份
BACKUP DATABASE HospitalManagementSystem
TO DISK = 'C:\Backups\HospitalManagementSystem_Diff.bak'
WITH DIFFERENTIAL, COMPRESSION, STATS = 10;
GO

-- 事务日志备份
BACKUP LOG HospitalManagementSystem
TO DISK = 'C:\Backups\HospitalManagementSystem_Log.trn'
WITH COMPRESSION, STATS = 10;
GO

标签