大数据库医院网上挂号系统说明书

实用文档

中北大学

数据库课程设计

说明书

学院、系：软件学院

专业：软件工程

学生班级：13140B04

学生姓名：王玲燕学号：1314011504 设计题目：医院网上挂号系统数据库设计

起迄日期: 2015年6月8日- 2015年6月19日指导教师:杨剑

2015 年 6月 19 日

一、需求分析 1、网上预约挂号

人类进入21世纪，医院作为一个极其重要的服务部门，其发展应适应计算机技术的发展。我国的医疗体制正在进行改革需要医疗市场的进一步规范化，

这就利用现代化的工具对

鉴于此目的，我们利用mysql 技术设计了此医院管理数据库系统，为患者提供个人信息登记注册、查询医师信息、患者的就诊情况和消费等功能，为医师提供患者信息和就诊情况以辅助治疗。

2、基本流程图

3、用户特点

医院网上预约挂号系统的使用者，大致包括三类用户：医院系统信息管理人员、医院的全部医师、就诊的患者。对于患者用户不需要有太多的专业知识，只要能熟练使用本系统提供的浏览界面，就可以使用本软件；前台操作人员和医师也不需要有太多的专业知识，但是要会操作电脑，熟悉该系统。医院系统管理人员要熟悉整个数据库和流程，系统进行操作和修改。系统的维护人员：医院网上预约挂号系统的维护人员为该软件的项目开发小组，要详细了解C/S 构架、java 编程语言、操作系统基础知识、数据库设计及操作等知识。

4、用例图（除管理员外）

图1.2

５、对功能的描述

通过对用户需求的分析，本系统的功能主要包括两块，前台用户操作及后台管理。

图1.3

各个功能的具体描述如下：

后台管理员可操作的有：专家管理、用户管理、预约管理、系统管理等功能设置，具体描述如下：

<1>专家管理

对专家进行添加与删除，修改专家档案，查看每个专家的预约情况。

<2>用户管理

显示用户的预约信息，也可以对某个特定用户进行查询。

<3>预约管理

可以分别按用户名、专家名、预约号、科室、预约日期、就诊日期进行查询管理，

取消已过期的预约信息。

<4>系统管理

对管理员进行添加与删除，修改管理员信息。

图1.4

6、数据字典数据项

数据结构

处理过程

7、预约挂号系统中

(1)专家:姓名、性别、职称、科室、医生编号、年龄、从医年限、主诊

(2)预约信息:科别、预约编号、预约时间、预约人、预约医生、专长

(3)病人:姓名、性别、年龄、密码、身份证号、症状

(4)管理员：管理员名、密码、管理员

二、小组成员分工

三、概要设计

局部E-R图分析

属性分析：

专家实体：

图2.1

管理员实体：

图2.3 预约信息：

图2.4

联系设计：

图2.5

E-R图：

2、 E-R图转化为关系模型：

专家（姓名、性别、职称、科别、编号、年龄、从医年限、主诊）

病人（用户名，姓名，性别，年龄，症状，密码，身份证号）

预约信息（专长，科室，预约医生，预约人，预约时间，预约编号）

管理员（管理员名，密码，管理员）

3、关系模型优化：

管理员（管理员编号，管理员名，登录密码）

病人（用户名，姓名，密码，性别，年龄，症状，病人编号）

专家（姓名，性别，职称，主诊，医生编号，年龄，从医年限，毕业院校）医院科别（科别，科别号，医生编号）

预约信息（预约编号，病人编号，医生编号，预约时间，就诊时间）

4、数据库表的设计：

管理员表(admin)：

专家表(doctor)：

病人信息表(patient)：

预约信息表(date)：

医院科别表（kebie）：

五、实验代码

创建表：

create database hospital;

use hospital;

create table admin(

adminNum varchar(50) primary key, adminName varchar(20) not null,

password varchar(20) not null

);

create table doctor(

Ename varchar(50) not null,

sex varchar(10) not null,

zhicheng varchar(20) not null,

major varchar(50) not null,

docNum varchar(20) primary key,

age int not null,

jobTime int not null,

college varchar(50) not null,

doTime varchar(50) not null

);

create table patient(

username varchar(20) ,

name varchar(20) ,

password varchar(10) ,

sex varchar(10) ,

age varchar(10) ,

symptom varchar(50) ,

userNum varchar(10) ,

primary key(username,userNum)

);

create table date(

dateNum varchar(10) primary key,

userNum varchar(10) not null,

docNum varchar(10) not null,

yyTime datetime not null,

jzTime varchar(50) not null

);

create table kebie(

kebie varchar(50) not null,

kebieNum varchar(20) not null,

docNum varchar(20) primary key,

foreign key(docNum) references doctor(docNum);

插入数据

Insert into doctor(Ename ,sex ,zhicheng ,major ,docNum ,age ,jobTime ,college ,doTime ) values("李晨","男","医师","骨科","032561",35,4,"协和医学院","周一全天");

insert into doctor(Ename ,sex ,zhicheng ,major ,docNum ,age ,jobTime ,college ,doTime ) values("黄宏","男","医师","骨科","032492",40,8,"湘雅医学院","周三全天");

insert into doctor(Ename ,sex ,zhicheng ,major ,docNum ,age ,jobTime ,college ,doTime ) values("张虹","女","医师","内科","033615",32,4,"长治医学院","周一全天");

insert into doctor(Ename ,sex ,zhicheng ,major ,docNum ,age ,jobTime ,college ,doTime ) values("易中天","男","医师","内科","033716",36,6,"协和医学院","周五全天");

insert into

doctor(Ename ,sex ,zhicheng ,major ,docNum ,age ,jobTime ,college ,doTime ) values("张晓","女","医师","外科","034628",36,8,"福州中医学院","周二全天"); insert into doctor(Ename ,sex ,zhicheng ,major ,docNum ,age ,jobTime ,college ,doTime ) values("贾丽","女","医师","外科","034384",35,4,"协和医学院","周三全天"); insert into doctor(Ename ,sex ,zhicheng ,major ,docNum ,age ,jobTime ,college ,doTime ) values("李众众","男","医师","眼科","035725",31,4,"协和医学院","周一全天"); insert into doctor(Ename ,sex ,zhicheng ,major ,docNum ,age ,jobTime ,college ,doTime ) values("王玲燕","女","医师","眼科","035613",35,7,"长治医学院","周二全天"); insert into doctor(Ename ,sex ,zhicheng ,major ,docNum ,age ,jobTime ,college ,doTime ) values("冯楚燕","女","医师","儿科","036932",35,8,"协和医学院","周一全天"); insert into doctor(Ename ,sex ,zhicheng ,major ,docNum ,age ,jobTime ,college ,doTime ) values("高小君","男","医师","儿科","036128",39,11,"协和医学院","周四全天");

insert into patient(Username,Name,password,Sex,age,Symptom,UserNum)values("用户一","张越","qwerty","女",16,"骨折","000001");

insert into patient(Username,Name,password,Sex,age,Symptom,UserNum)values("用户二","胡兵","uiopas","男",30,"肩膀脱臼","000002");

insert into patient(Username,Name,password,Sex,age,Symptom,UserNum)values("用户三","孙伟","dfghjk","男",34,"胃胀","000003");

insert into patient(Username,Name,password,Sex,age,Symptom,UserNum)values("用户四","马宇飞","lzxcvb","男",18,"胸闷","000004");

insert into patient(Username,Name,password,Sex,age,Symptom,UserNum)values("用户五","刘继承","nmqwer","男",20,"皮肤烧伤","000005");

insert into patient(Username,Name,password,Sex,age,Symptom,UserNum)values("用户六","董丁","tyuiop","男",40,"阑尾炎","000006");

insert into patient(Username,Name,password,Sex,age,Symptom,UserNum)values("用户七","李婉","asdfgh","女",4,"感冒","000007");

insert into patient(Username,Name,password,Sex,age,Symptom,UserNum)values("用户八","马羽坤","jklzxc","男",34,"青光眼","000008");

insert into patient(Username,Name,password,Sex,age,Symptom,UserNum)values("用户九","郑子鹏","vbnmqw","男",2,"感冒","000009");

insert into patient(Username,Name,password,Sex,age,Symptom,UserNum)values("用户十","李翔","ertyui","男",2,"咳嗽","000010");

insert into admin(AdminNum,AdminName,password)values("A00001","李小光","admin");

insert into date(DateNum,UserNum,DocNum,yyTime,jzTime)values("d001","000001","032561"," 2015-06-01 12:12:12","本周周一上午");

insert into date(DateNum,UserNum,DocNum,yyTime,jzTime)values("d002","000002","032492"," 2015-06-02 10:15:31","本周周五下午");

insert into date(DateNum,UserNum,DocNum,yyTime,jzTime)values("d003","000003","033615"," 2015-06-03 09:50:23","本周周二下午");

insert into date(DateNum,UserNum,DocNum,yyTime,jzTime)values("d004","000004","033716"," 2015-06-04 11:19:21","本周周一上午");

insert into date(DateNum,UserNum,DocNum,yyTime,jzTime)values("d005","000005","034628"," 2015-06-05 14:23:34","本周周一下午");

insert into date(DateNum,UserNum,DocNum,yyTime,jzTime)values("d006","000006","034384"," 2015-06-06 08:30:10","本周周四下午");

insert into date(DateNum,UserNum,DocNum,yyTime,jzTime)values("d007","000007","035725"," 2015-06-07 10:30:55","本周周二下午");

insert into date(DateNum,UserNum,DocNum,yyTime,jzTime)values("d008","000008","035613"," 2015-06-08 14:35:42","本周周四下午");

insert into date(DateNum,UserNum,DocNum,yyTime,jzTime)values("d009","000009","036932"," 2015-06-09 16:32:52","本周周四上午");

insert into date(DateNum,UserNum,DocNum,yyTime,jzTime)values("d010","000010","036128"," 2015-06-10 17:30:21","本周周一下午");

insert into kebie(kebie,kebieNum,DocNum)values("骨科","001","032561"); insert into kebie(kebie,kebieNum,DocNum)values("骨科","001","032492"); insert into kebie(kebie,kebieNum,DocNum)values("内科","002","033615"); insert into kebie(kebie,kebieNum,DocNum)values("内科","002","033716"); insert into kebie(kebie,kebieNum,DocNum)values("外科","003","034628"); insert into kebie(kebie,kebieNum,DocNum)values("外科","003","034384"); insert into kebie(kebie,kebieNum,DocNum)values("眼科","004","035725"); insert into kebie(kebie,kebieNum,DocNum)values("眼科","004","035613"); insert into kebie(kebie,kebieNum,DocNum)values("儿科","005","036932"); insert into kebie(kebie,kebieNum,DocNum)values("儿科","005","036128");

创建储蓄过程：

delimiter //

获得所有科别：

create procedure getAllKebie()

begin

select kebie as 科室,Ename as 医生 from kebie,doctor

where kebie.docNum=doctor.docNum;

end//

call getAllKebie()//

获得医生科别：

create procedure getDocKebie(in name varchar(50))

begin

set @name=name;

select kebie as 科室 from kebie where docNum=any(select docNum from doctor where Ename=name);

end//

call getDocKebie("王玲燕")//

获得医生所有出诊时间：

create procedure getAllTime()

begin

select doTime as 出诊时间,Ename as 医生 from doctor ;

end//

call getAllTime()//

获得医生出诊时间：

create procedure getDocTime(in name varchar(50))

begin

set @name=name;

select doTime as 出诊时间 from doctor where Ename=name;

end//

call getDocTime("王玲燕");

获得所有预约信息：

create procedure getAllDate()

begin

select DateNum as 预约编号,Ename as 医生,Name as 姓名,jzTime as 就诊时间,yyTime as 登记时间

from doctor,patient,date

where date.DocNum=doctor.DocNum and https://www.360docs.net/doc/ba1219458.html,erNum=https://www.360docs.net/doc/ba1219458.html,erNum;

end//

call getAllDate()//

);

更新病人信息：

create procedure updatePatient(in username varchar(20),in name varchar(20),in password varchar(10),in sex varchar(10),in age varchar(10),in symptom varchar(50)) begin

select max(userNum) into @userNum from patient;

set @userNum=@userNum+1;

set @username=username;

set @name=name;

set @password=password;

set @sex=sex;

set @age=age;

set @symptom=symptom;

set @insertSql=concat('insert into patient values(?,?,?,?,?,?,?)');

prepare stmtinsert from @insertSql;

Execute stmtinsert using

@username,@name,@password,@sex,@age,@symptom,@userNum;

deallocate prepare stmtinsert;

end//

call updatePatient("张三1","张三2","123","男","19","内科")//

create procedure updateDate(in userNum varchar(10),in docNum varchar(10)) begin

set @userNum=userNum;

set @docNum=docNum;

set @dateNum=(select max(dateNum) from date);

set @dateNum=@dateNum+1;

set @yyTime=sysdate();

set @jzTime=(select doTime from doctor where docNum=doctor.docNum);

set @insertSql=concat('insert into date values(?,?,?,?,?)');

prepare stmtinsert from @insertSql;

execute stmtinsert using @dateNum,@userNum,@docNum,@yyTime,@jzTime;

deallocate prepare stmtinsert;

end//

call updateDate(https://www.360docs.net/doc/ba1219458.html,erNum,docNum);

六、实验结果