医院病房管理系统的数据库设计(SQL Server)

1 绪论一个好的管理系统应该具有如下功能快速准确进行出院结算实现高效的病房管理提高病房使用率方便管理者随时了解病床使用情况减轻员工的工作负担促使员工把更多的精力放在为病人提供更加优质的服务上等。本次课题将以此为目标设计一个医院病房管理系统。我们将主要实现对病人的住院登记、出院结算、病人的信息管理、医院内部的信息管理以及系统安全管理的功能从而使医院能够更好地利用信息来提高医院病房管理的效率改善医院服务质量更好的服务于患者和工作人员。2需求分析需求分析阶段主要有两个方面的任务分析用户的数据要求和分析用户的处理要求。数据流程图如下2.1 功能分析根据用户调查系统应有以下功能需求1用户医生、护士登录需求医生、护士只有输入正确的工作证号和密码才能登录系统如果还没有注册可联系系统管理人员输入相关信息进行注册。2住院登记需求病人住院前应当完成入院登记登记信息包括病人病历号、姓名、性别、民族、电话号码、家庭住址、诊断、主管医生、病房号、床位号、住院日期、出院日期。其中病房应由系统按照病房、病床余量以及病人主管医生所属科室、诊断情况合理分配。3信息管理需求信息管理需求应包括病人信息管理、医院内部信息管理。管理员可通过此系统对病人信息、医生信息、护士信息、病房和病床信息进行更新和处理。4出院结算需求能够根据病人的入院日期、出院日期和所住病房的收费标准计算出病人所需支付的费用以便病人快速办理出院。5安全管理需求能够保证系统的安全性,用户可通过对工作证号和旧密码的验证来修改登录密码。2.2行为操作分析系统功能模块图如下3数据概念模型设计经过以上分析已经基本了解了用户对处理的要求接下来要求做的工作是分析用户对数据的要求。最常用的表示概念性数据模型的方法是实体-联系方法即E—R图。4数据逻辑结构的设计4.1 将E-R图转化为关系数据模型根据以上各实体图和E-R 图。经过转换可以导出各个关系。其转换现则为首先每个实体应该化为一个关系有属性的联系也应转化为一个关系然后需要给出每个关系的关键字。根据以上规则直接给出所有关系。医生工作证号姓名性别年龄职称科室名密码病人病历号姓名性別年龄民族电话号码家庭住址病房号床位号住院日期出院日期治疗工作证号病历号诊断护士护士号姓名密码科室科室名科室地址科室电话病房病房号护士号科室名收费标准病床床位号病房号目前状态4.2 关系模式的规范化将E-R图转换为以上的关系模式后需要完成规范化即确定各关系模式是否是第三范式。1分析关系模式“医生”存在下列函数依赖姓名性别年龄职称科室名密码完全函数依赖于工作证号2分析关系模式“病人”存在下列函数依赖病历号函数决定病房号床位号病房号床位号函数决定姓名性別年龄民族电话号码家庭住址病房号床位号住院日期出院日期所以病历号传递函数决定姓名性別民族电话号码家庭住址病房号床位号住院日期出院日期。分解为病人病历号病房号床位号住院病房号床位号姓名性別民族电话号码家庭住址住院日期出院日期3分析关系模式“治疗”存在下列函数依赖工作证号病历号函数决定诊断病历号函数决定诊断分解为治疗病历号诊断4分析关系模式“护士”存在下列函数依赖护士号完全函数决定姓名密码5分析关系模式“科室”存在下列函数依赖科室名完全函数决定科室地址科室电话6分析关系模式“病房”存在下列函数依赖病房号完全函数决定护士号科室名收费标准7分析关系模式“病床”存在下列函数依赖床位号病房号完全函数决定目前状态8关系模式“医生”、“病人”、“住院”、“治疗”、“护士”、“科室”、“病房”、“病床”不存在部分和传递依赖关系因些属于第三范式。至此所有关系模式都符合第三范式要求。4.3 关系模式的优化1根据第2章“需求分析”中病人信息管理的需求需要多次查询病人信息而由病历号直接查询到其他信息是最为快捷的方式所以即使病人病历号姓名性別民族电话号码家庭住址病房号床位号住院日期出院日期这个关系模式存在传递函数依赖我们也准备采取这个关系模式2考虑到对医生—病人信息查询的需求该医院病床系统还需要生成医生—病人信息报表因此在此设计出“医生—病人表”CREATE VIEW doctor_patient AS SELECT doctor.Dcode,Dname,Dsex,Dage,Dtitle,Departname,patient.Pcode,Pname,Psex,Paage,Pnation, Pphone,Proom,Pbed,Pdiagnosis,Pinhospital,Pleavehospital FROM treatment JOIN doctor ON treatment.Dcode doctor.Dcode JOIN patient ON treatment.Pcode patient.Pcode; SELECT * INTO doc_pat FROM doctor_patient; SELECT * FROM doc_pat;3考虑到病人出院结算打印收据的需求可利用住院日期、出院日期和病房日收费计算总费用因此设计“收据表”CREATE VIEW receipt_view AS SELECT Pcode,Pname,Psex,DATEDIFF(DAY,Pinhospital,Pleavehospital) * Fees fees FROM patient JOIN patientroom ON patient.Proom patientroom.Proom WHERE Pleavehospital IS NOT NULL ; SELECT * INTO receipts FROM receipt_view; SELECT * FROM receipts;5物理实现和操作本次设计采用了SQL Server 2019为工具软件完成表的建立和操作。5.1 数据库的建立CREATE DATABASE HPMS;5.2 表的建立本次设计一共建立了7张表下面给出创建这些表的SQL语句和实现图。1department表 CREATE TABLE department( Departname varchar(20) PRIMARY KEY, Departadress varchar(40) NOT NULL, Departphone char(11) ) 2nurse表 CREATE TABLE nurse( Ncode char(8) PRIMARY KEY, Nname varchar(10) NOT NULL, Npass varchar(15) ) 3patientroom表 CREATE TABLE patientroom( Proom char(4) PRIMARY KEY, Ncode char(8) NOT NULL, Departname varchar(20) NOT NULL, Fees char(7), FOREIGN KEY (Ncode) REFERENCES nurse (Ncode), FOREIGN KEY (Departname) REFERENCES department (Departname) ) 4patientbed表 CREATE TABLE patientbed( Pbed char(4) NOT NULL, Proom char(4) NOT NULL, Pstatus char(2), PRIMARY KEY (Pbed,Proom), FOREIGN KEY (Proom) REFERENCES patientroom (Proom) ) 5doctor表 CREATE TABLE doctor( Dcode char(8) PRIMARY KEY, Dname char(10) not null, Dsex char(2), Dage tinyint, Dtitle Char(10), Departname varchar(20) not null, Dpass varchar(15), FOREIGN KEY (Departname) REFERENCES department (Departname) ) 6patient表 CREATE TABLE patient( Pcode char(8) PRIMARY KEY, Pname char(10) not null, Psex char(2), Paage tinyint, Pnation Char(8), Pphone char(11), Paddress varchar(40), Proom char(4) not null, Pbed char(4) not null, Pinhospital date not null, Pleavehospital date, FOREIGN KEY (Proom) REFERENCES patientroom (Proom) ) 7treatment表 CREATE TABLE treatment( Dcode char(8) , Pcode char(8), Pdiagnosis varchar(40), primary key(Dcode,Pcode), FOREIGN KEY (Dcode) REFERENCES doctor (Dcode), FOREIGN KEY (Pcode) REFERENCES patient (Pcode) )关系图如下所示5.3 操作1排序操作日收费为40元的为普通病房100元的为VIP病房将病房日收费按从大到小排序SELECT patientroom.Proom,Pcode,Pname,Fees FROM patientroom JOIN patient ON patientroom.Proom patient.Proom WHERE FEES IN(100,40) ORDER BY FEES;2 子查询建立医生信息视图因为医生表中有一列属性是密码所以当查询医生信息时不应显示出来GO CREATE VIEW V_DOCTOR AS SELECT Dcode,Dname,Dsex,Dage,Dtitle,Departname FROM doctor; GO SELECT * FROM V_DOCTOR;3联表查询建立医生-病人视图可查询相关医生信息和病人信息GO CREATE VIEW doctor_patient AS SELECT doctor.Dcode,Dname,Dsex,Dage,Dtitle,Departname,patient.Pcode,Pname,Psex,Paage,Pnation, Pphone,Proom,Pbed,Pdiagnosis,Pinhospital,Pleavehospital FROM treatment JOIN doctor ON treatment.Dcode doctor.Dcode JOIN patient ON treatment.Pcode patient.Pcode; GO SELECT * FROM doctor_patient;(4)出院结算依据住院日期、出院日期以及病房日收费即可结算出院费用SELECT Pcode,Pname,Psex,DATEDIFF(DAY,Pinhospital,Pleavehospital) * Fees fees FROM patient JOIN patientroom ON patient.Proom patientroom.Proom WHERE Pleavehospital IS NOT NULL ;5修改、删除操作病人出院后首先通过出院日期查询出院病人的病房号和病床号然后将此病人所住病房的病床状态改为空然后即可删除治疗表以及病人表的该病人相关信息SELECT Proom,Pbed FROM patient WHERE Pcode(SELECT Pcode FROM PATIENT WHERE Pleavehospital IS NOT NULL); UPDATE patientbed SET Pstatus NULL WHERE (Proom 5101 and Pbed 3); SELECT * FROM patientbed; ALTER TABLE treatment DROP CONSTRAINT [FK__treatment__Pcode__35BCFE0A]; ALTER TABLE treatment ADD FOREIGN KEY(Pcode) REFERENCES patient(Pcode) ON DELETE CASCADE; DELETE FROM patient WHERE Pcode(SELECT Pcode FROM PATIENT WHERE Pleavehospital IS NOT NULL); SELECT * FROM patient;6查询、修改操作假定病历号为05060211的病人想要由普通病房换到VIP病房则先查询病房日收费为100元且病床状态为空的病床然后查询该病人所住病房病床将该病人所住病房病床修改为查询到的VIP病房空床然后将该VIP病房病床状态改为满。SELECT Proom,Pbed FROM patient WHERE Pcode 05060211; SELECT patientroom.Proom,Pbed FROM patientbed JOIN patientroom ON patientbed.Proom patientroom.Proom WHERE Pstatus IS NULL AND Fees 100; UPDATE patient SET Proom 5101,Pbed 3 WHERE Pcode 05060211; SELECT * FROM patient; UPDATE patientbed SET Pstatus 满 WHERE (Proom 5101 AND Pbed 3); SELECT * FROM patientbed;7数据库备份操作BACKUP DATABASE HPMS TO disk D:\HPMSfile.bak WITH FORMAT, NAME HPMS备份扩展触发器1在病人表上创建一个insert_patient触发器在插入新的病人记录前先判断记录中的床位状态若状态为“满”则回滚到插入操作前并提示操作人员该床位已满否则成功插入该记录防止操作失误。CREATE TRIGGER insert_patient ON patient INSTEAD OF INSERT AS BEGIN DECLARE bed char(4),room char(4),code char(8) ,name char(10),sex char(2),nation Char(8),phone char(11); DECLARE age tinyint; DECLARE address varchar(40); DECLARE inhospital date,leavehospital date; SELECT bedPbed,roomProom,codePcode,namePname,sexPsex,nationPnation,phonePphone,agePaage,addressPaddress,inhospitalPinhospital,leavehospitalPleavehospital FROM inserted IF ( (SELECT Pstatus FROM patientbed WHERE (Pbedbed AND Proomroom) ) 满) BEGIN PRINT 该床位已满 ROLLBACK TRANSACTION END ELSE BEGIN INSERT INTO patient VALUES(code,name,sex,age,nation,phone,address,room,bed,inhospital,leavehospital) END END2在病人表上创建一个UPDATE_patient触发器病人可能会出现更换床位的情况在修改病人记录前判断记录中的床位状态若状态为“满”则回滚到修改操作前并提示操作人员该床位已满否则成功修改该床位记录。CREATE TRIGGER UPDATE_patientbed ON patient INSTEAD OF UPDATE AS BEGIN DECLARE bed char(4),room char(4),code char(8) SELECT bedPbed,roomProom,codePcode FROM inserted IF ( (SELECT Pstatus FROM patientbed WHERE (Pbedbed AND Proomroom) ) 满) BEGIN PRINT 该床位已满 ROLLBACK TRANSACTION END ELSE BEGIN UPDATE patient SET Proomroom,Pbedbed WHERE Pcodecode END END3在病人表上创建一个input_bed触发器当插入新的病人记录或病人更换床位后将插入病人记录对应床位或更换后的床位的状态修改为“满”。CREATE TRIGGER input_bed ON patient AFTER INSERT,UPDATE AS BEGIN DECLARE bed char(4),room char(4) SELECT bedPbed,roomProom FROM inserted UPDATE patientbed SET Pstatus满 WHERE Pbedbed AND Proomroom END4在病人表上创建一个dele_bed触发器当病人出院后删除病人记录或病人更换床位后将删除病人记录对应的床位或更换前的床位的状态修改为“NULL”释放床位。CREATE TRIGGER dele_bed ON patient AFTER DELETE,UPDATE AS BEGIN DECLARE bed char(4),room char(4) SELECT bedPbed,roomProom FROM deleted UPDATE patientbed SET PstatusNULL WHERE Pbedbed AND Proomroom END6设计总结本次数据库设计围绕医院病房管理系统展开核心目标是提升医院病房管理效率实现病人住院登记、出院结算、信息管理、医院内部信息管理及系统安全管理等核心功能。设计过程中主要面临功能分析不全面、数据流程图不完善、关系模式规范化与实际需求冲突、物理操作代码繁琐等问题。通过查阅资料不仅夯实了已有知识还掌握了触发器使用等额外技能。此次设计实践让我深刻认识到数据库设计需重视需求分析全面深入考量以避免实现中的不合理性需严格遵循实体完整性、参照完整性及用户定义完整性建表需符合设计、索引、SQL语句等规范代码编写需兼顾使用者实际降低操作难度。此外为增强系统可靠性本次设计对医院病房管理系统进行备份提升其容错能力保障数据安全。