数据库课程设计医院管理系统 SQL

07-11 1887阅读

文章目录

一、背景资料（需求分析）
二、功能模块
三、功能设计说明书(要求详细描述各模块功能，给出模块结构图（SC图）
四、数据库设计
- 1、概念模型（ＥＲ图）
- 2、逻辑模型
- 五、建表以及sql语句
- - （1）病人表
  - （2）医生表
  - （3）科室表
  - （4）病房表
  - 六、功能模块详细设计
  - 七、测试结果
  - 总结
    一、背景资料（需求分析）
    
    某医院希望建立数据库来管理其主要业务信息，业务规则如下：
    （1）一个科室有多个病房，多个医生；
    （2）一个病房属于一个科室；
    （3）一个医生只能属于一个科室，但可负责多个病人的诊治；
    （4）一个病人的主管医生只有一人；
    
    二、功能模块
    
    1、基本信息管理模块
    （1）科室基本信息的录入，删除，修改，查询。
    其中基本信息包括：科名，科地址，科电话，主任医生姓名等信息
    （2）病房基本信息的录入，删除，修改，查询。
    其中基本信息包括：病房号，床位号，所属科室名等信息
    （3）医生基本信息的录入，删除，修改，查询。
    其中基本信息包括：姓名，职称，所属科室名，年龄，工作证号等信息
    （4）病人基本信息的录入，删除，修改，查询。
    其中基本信息包括：病历号，姓名，性别，诊断，主管医生，病房号等信息。
    2、业务功能模块
    （1）信息查询模块
    病人信息查询
    医生工作情况查询
    查询负责病人最多的前 10 个的医生信息及其负责病人的个数
    查询负责病人最多的前 10 个的科室信息及其负责病人的个数
    （2）费用计算模块
    住院所需费用计算
    
    三、功能设计说明书(要求详细描述各模块功能，给出模块结构图（SC图）
    
    业务功能模块设计说明：
    （1）信息查询模块
    病人信息查询：设计一个存储过程，以病历号作为输入参数，检索病人的情况，包括病人病历号，病人姓名、主治医生姓名，诊断结果，病房号和病床号。
    医生工作情况查询：设计一个存储过程，检索每一位医生的工作情况，包括医生工作证号，负责的病人姓名，没有治疗过任何病人的医生也应出现在结果中。
    查询医生负责的病人人数：查询负责病人最多的前 10 个的医生信息及其负责的病人个数
    查询科室负责的病人人数：查询负责病人最多的前 10 个的科室信息及其负责病人的个数
    （2）费用计算模块
    住院费用：（出院日期-入院日期）* 病房收费标准。
    设置存储过程，输入病人病历号和出院日期，计算住院费用。显示病历号，病人姓名，住院时间，病房收费标准，住院费用。
    
    四、数据库设计
    
    1、概念模型（ＥＲ图）
    
    2、逻辑模型
    
    (1) 基础逻辑模型(实体-联系转换的对应表，写出关系模式,标出主码)
    实体表（ER图中的方块）
    1-1 病人表（病历号，姓名，性别，诊断结果，病房号，病床号，入院日期，主治医师）
    1-2 医生表（工作证号，姓名，职称，所属科室名，年龄）
    1-3 科室表（科名，科电话，科地址，主任医生姓名）
    1-4 病房表（病房号，所属科室名，收费标准，床位总数，已使用床位数）
    联系表（ER图中的菱形）
    2-1 属于表（工作证号，科名）
    2-2 包含表（病房号，科名）
    2-3 入住表（病历号，病房号）
    2-4 诊治（病历号，工作证号）
    (2)数据库优化分析和说明（包括关系模式的合并和范式检查）
    2.1关系模式合并：
    1.病人表和入住表和诊治表的主码相同，可以将三个表进行合并
    合并后的复合表：
    3-1病人表（病历号，姓名，性别，诊断结果，病房号，病床号，入院日期，主治医生）
    2.医生表和属于表的主码相同，将两个表进行合并
    合并后的复合表：
    3-2 医生表（工作证号，姓名，职称，所属科室名，年龄）
    3.病房表和包含表的主码相同，将两个表进行合并
    合并后的复合表：
    3-3 病房表（病房号，所属科室名，收费标准，床位总数，已使用床位数）
    
    五、建表以及sql语句
    
    （1）病人表
    
    （病历号，姓名，性别，诊断结果，病房号，病床号，入院日期，出院日期，工作证号）
    表语句：
```
create table patient(
	Patient_no varchar(50) primary key,
	Patient_name varchar(50) NULL,
	Patient_sex varchar(50) NULL,
	Patient_result varchar(50),
	Patient_roomno varchar(50),
	Patient_bedno varchar(50),
	Patient_intime date,
	Patient_doctor varchar(50),
	foreign key (Patient_doctor) references doctor(doctor_no),
	foreign key (Patient_roomno) references room(room_no));
```
    （2）医生表
    
    （工作证号，姓名，职称，所属科室名，年龄）
    表语句：
```
create table doctor(
	doctor_no varchar(50) primary key,
	doctor_name varchar(50),
	doctor_post varchar(50),
	doctor_office varchar(50),
	doctor_age int,
	foreign key (doctor_office) references office(office_name));
```
    （3）科室表
    
    （科名，科电话，科地址，主任医生姓名）
    表语句：
```
create table office (
	office_name varchar(50) primary key,
	office_phone varchar(50),
	office_address varchar(50),
	office_leader varchar(50));
```
    （4）病房表
    
    （病房号，所属科室名，收费标准, 总床位数，已使用床位数）
    表语句：
```
create table room(
	room_no varchar(50) primary key,
	room_office varchar(50),
	room_money varchar(50),
	room_bed int,
	room_usebed int,
	foreign key (room_office) references office(office_name));
```
    六、功能模块详细设计
    
    1、存储过程1：病人信息查询
```
Create procedure patient_info(no varchar(50))
Begin
Select patient.panient_no,patient.patient_name,doctor.doctor_name,patient.patient_result,patient.patient_roomno,patient.patient_bedno 
from patient,doctor 
where patient.patient_no=no and patient.patient_doctor=doctor.doctor_no;
End
```
    2、存储过程2：医生工作情况查询
```
Create procedure doctor_work()
Begin
Select doctor.doctor_no,doctor.doctor_name,patient.patient_name from doctor left join patient on patient.patient_doctor=doctor.doctor_no;
End
```
    3、存储过程3：查询医生负责的病人人数
```
Create procedure doctor_front10()
Begin
Select doctor.*,count(doctor.doctor_no) from patient,doctor where patient.patient_doctor=doctor.doctor_no
Group by doctor.doctor_no
Order by count(doctor.doctor_no) desc
Limit 0,10;
End
```
    4、存储过程4：查询科室负责的病人人数
```
Create procedure office_front10()
Begin
Select office.*,countn(doctor.doctor_no)
From office,doctor,patient
Where patient.patient.doctor=doctor.doctor_no and office.office_name=doctor.doctor_office
Group by office.office_name
Order by count(doctor.doctor_no) desc
Limit 0,10;
End
```
    5、存储过程5：费用计算
```
Create procedure cost(no varchar(50),out_time date)
Begin
Declare cost int;
Declare in_time date;
Declare days int;
Declare name varchar(50);
Declare value int;
Select patient_name into name from patient where patient_no=no;
Select patient_intime into in_time from patient where patient_no=no;
Select room.room_money into value from patient,room 
where patient.patient_no=no and patient.patient_roomno=room.room_no;
set days=datediff(out_time,in_time);
set cost=days*value;
select no,name,days,value,cost;
end
```
    触发器1：当有新病人入住时，相应病房的已使用床位数加一
```
Create trigger in_hospital
After insert on patient
For each row
Begin
Update room set room_usebed=room_usebed+1 where room_no=new.patient_roomno;
End
```
    触发器2：当有病人出院时，相应病房的已使用床位数减一
```
Create trigger out_hospital
After delete on patient
For each row
Begin
Update room set room_usebed=room_usebed-1 where room_no=old.patient_roomno;
End
```
    七、测试结果
    
    稍微放两个
    调用存储过程4：查询科室负责的病人人数
    
    调用存储过程5：费用计算
    
    
    总结
    
    实现的功能相对简单，有待完善
    基本上只要ER图画好，后续功能的实现还是比较容易的