中北大学数据库课程设计说明书学院、系：软件学院专业：软件工程学生班级：13140B04学生姓名：王玲燕学号：1314011504设计题目：医院网上挂号系统数据库设计起迄日期:2015年6月8日- 2015年6月19日指导教师:杨剑2015 年 6月 19 日一、需求分析 1、网上预约挂号人类进入21世纪，医院作为一个极其重要的服务部门，其发展应适应计算机技术的发展。

我国的医疗体制正在进行改革需要医疗市场的进一步规范化，这就利用现代化的工具对医院进行有效的管理，有利于提高医疗水平和服务质量更好的服务于社会。

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

2、基本流程图图1.1 3、用户特点医院网上预约挂号系统的使用者，大致包括三类用户：医院系统信息管理人员、医院的全部医师、就诊的患者。

对于患者用户不需要有太多的专业知识，只要能熟练使用本系统提供的浏览界面，就可以使用本软件；前台操作人员和医师也不需要有太多的专业知识，但是要会操作电脑，熟悉该系统。

医院系统管理人员要熟悉整个数据库和流程，系统进行操作和修改。

系统的维护人员：医院网上预约挂号系统的维护人员为该软件的项目开发小组，要详细了解C/S 构架、java 编程语言、操作系统基础知识、数据库设计及操作等知识。

1.注册2.登录4.选择医院选择科室选择专家3.网上挂号3.快速查询3.专家门诊3.选择科室5.选择可预约的医生7.挂号成功6.选择时段确定预约8.就诊4、用例图（除管理员外）图1.2５、对功能的描述通过对用户需求的分析，本系统的功能主要包括两块，前台用户操作及后台管理。

图1.3各个功能的具体描述如下：后台管理员可操作的有：专家管理、用户管理、预约管理、系统管理等功能设置，具体描述如下：<1>专家管理对专家进行添加与删除，修改专家档案，查看每个专家的预约情况。

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

<3>预约管理可以分别按用户名、专家名、预约号、科室、预约日期、就诊日期进行查询管理，取消已过期的预约信息。

<4>系统管理对管理员进行添加与删除，修改管理员信息。

图1.46、数据字典数据项数据结构处理过程处理过程编号处理过程名说明输入输出处理 P-5 医生登陆 已经登陆的医生用户输入密码和用户名登陆成功或失败成功：跳转到个人页面失败：重新登陆P-6医生查询病人信息已经登陆的医生查询信息病人的信息查询7、预约挂号系统中需要管理的信息如下(1)专家:姓名、性别、职称、科室、医生编号、年龄、从医年限、主诊 (2)预约信息:科别、预约编号、预约时间、预约人、预约医生、专长 (3)病人:姓名、性别、年龄、密码、身份证号、症状 (4)管理员：管理员名、密码、管理员 二、小组成员分工 操作人员 学号姓名 组员分工安排11314011504王玲燕 E-R 图转换为关系模式；创建登录界面创建数据库及应用表单，视图21314011907冯楚雁创建触发器，实现其应用，整理总结实验报告三、概要设计 局部E-R 图分析属性分析：专家实体：专家姓性职从医主图2.1管理员实体：图2.3预约信息：图2.4联系设计：图2.5 E-R图：概念结构设计2、病人（用户名，姓名，性别，年龄，症状，密码，身份证号）预约信息（专长，科室，预约医生，预约人，预约时间，预约编号）管理员（管理员名，密码，管理员）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 intopatient(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()beginselect kebie as 科室,Ename as 医生 from kebie,doctorwhere kebie.docNum=doctor.docNum;end//call getAllKebie()//获得医生科别：create procedure getDocKebie(in name varchar(50))beginset @name=name;select kebie as 科室 from kebie where docNum=any(select docNum from doctor where Ename=name);end//call getDocKebie("王玲燕")//获得医生所有出诊时间：create procedure getAllTime()beginselect doTime as 出诊时间,Ename as 医生 from doctor ;end//call getAllTime()//获得医生出诊时间：create procedure getDocTime(in name varchar(50))beginset @name=name;select doTime as 出诊时间 from doctor where Ename=name;end//call getDocTime("王玲燕");获得所有预约信息：create procedure getAllDate()beginselect DateNum as 预约编号,Ename as 医生,Name as 姓名,jzTime as 就诊时间,yyTime as 登记时间from doctor,patient,datewhere date.DocNum=doctor.DocNum and erNum=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)) begins elect max(userNum) into @userNum from patient;s et @userNum=@userNum+1;s et @username=username;s et @name=name;s et @password=password;s et @sex=sex;s et @age=age;s et @symptom=symptom;s et @insertSql=concat('insert into patient values(?,?,?,?,?,?,?)');p repare stmtinsert from @insertSql;E xecute stmtinsert using@username,@name,@password,@sex,@age,@symptom,@userNum;d eallocate prepare stmtinsert;end//call updatePatient("张三1","张三2","123","男","19","内科")//create procedure updateDate(in userNum varchar(10),in docNum varchar(10)) begins et @userNum=userNum;s et @docNum=docNum;s et @dateNum=(select max(dateNum) from date);s et @dateNum=@dateNum+1;s et @yyTime=sysdate();s et @jzTime=(select doTime from doctor where docNum=doctor.docNum);s et @insertSql=concat('insert into date values(?,?,?,?,?)');p repare stmtinsert from @insertSql;e xecute stmtinsert using @dateNum,@userNum,@docNum,@yyTime,@jzTime;d eallocate prepare stmtinsert;end//call updateDate(erNum,docNum);六、实验结果七、遇到的问题1、在创建存储过程后执行不通过。