就医信息管理系统-数据库设计（数据流图、ER图、数据库建立）

本文主要介绍就医信息管理系统数据库的逻辑结构设计、概念结构设计以及数据库建立，主要包括数据流图、ER图和数据库源码。

数据流图

ER图

1.总ER图

2.医生科室关系

3.挂号关系

4.诊断结果关系

5.开处方关系

6.病人病历关系

7.病人费用关系

8.处方医嘱关系

9.医嘱药品关系

10.药品药房关系

数据库建立

CREATE  DATABASE  HOSPITAL;
USE HOSPITAL;
#科室
CREATE TABLE ROOM(
    RNUM CHAR(15) PRIMARY KEY ,
    RNAME CHAR(15) NOT NULL ,
    PEUPLE INT CHECK ( PEUPLE>=0 ),
    RTEL CHAR(15) NOT NULL
);

#医生
CREATE TABLE DOCTOR(
    DNUM CHAR(15) PRIMARY KEY ,
    DAGE INT CHECK ( 25<DAGE AND DAGE<80 ),          #年龄
    DNAME CHAR(15) NOT NULL ,    #姓名
    DSEX CHAR(2) CHECK ( DSEX IN('男','女')),       #性别
    RNUM CHAR(15),    #所属科室编号
    DSPC CHAR(50) ,      #擅长方向
    DPROF CHAR(15),     #职称
    DINFO TEXT(1000),   #简介
    DFEES FLOAT CHECK ( DFEES>=0 AND DFEES <=200),        #诊费
    FOREIGN KEY(RNUM) REFERENCES ROOM(RNUM)
    ON DELETE CASCADE  ON UPDATE CASCADE
);

#病人
CREATE TABLE PAT(
    PNUM CHAR(15) PRIMARY KEY ,
    PNAME CHAR(15) NOT NULL , #姓名
    PSEX CHAR(15) CHECK ( PSEX IN('男','女')),  #性别
    PAGE INT CHECK ( PAGE>0 ),       #年龄
    PBIRTH DATE,    #出生日期
    PID CHAR(20),   #身份证号
    PSID CHAR(20),  #社保卡号
    PTEL CHAR(20),  #电话
    PETEL CHAR(20)  #紧急联系人
);

#病历
CREATE TABLE MEDRECORD(
    MNUM CHAR(20) PRIMARY KEY ,
    DNUM CHAR(15),
    PNUM CHAR(15),  #患者编号
    EMED TEXT,      #病情说明
    ADAVICE TEXT,    #医嘱
    MTIME DATE,     #时间
    FOREIGN KEY(PNUM) REFERENCES PAT(PNUM)
    ON DELETE CASCADE  ON UPDATE CASCADE,
    FOREIGN KEY (DNUM) REFERENCES DOCTOR(DNUM)
    ON DELETE CASCADE  ON UPDATE CASCADE
);

#挂号
CREATE TABLE REGIS(
    RENUM CHAR(15) PRIMARY KEY ,
    RNUM CHAR(15),   #科室编号
    DNUM CHAR(15),   #医生编号
    PNUM CHAR(15),  #患者编号
    PDISC TEXT,     #患者描述
    PTIME DATETIME, #挂号时间
    STAUS CHAR(4) CHECK ( STAUS IN ('已挂号','已就诊','已开处方')), #状态
    FOREIGN KEY (PNUM) REFERENCES PAT(PNUM)
    ON DELETE CASCADE  ON UPDATE CASCADE,
    FOREIGN KEY (RNUM) REFERENCES ROOM(RNUM)
    ON DELETE CASCADE  ON UPDATE CASCADE,
    FOREIGN KEY (DNUM) REFERENCES DOCTOR(DNUM)
    ON DELETE CASCADE  ON UPDATE CASCADE
);

#消费记录
CREATE TABLE CONSRECORD(
    RECNUM CHAR(15) PRIMARY KEY ,
    PNUM CHAR(15), #患者编号
    PRNUM CHAR(15),#处方编号
    DFEES FLOAT CHECK ( DFEES>=0 ),  #诊费
    DRAFEES FLOAT CHECK ( DRAFEES>=0 ), #药品总费用
    AFEES FLOAT CHECK ( AFEES>=0),  #总费用
    STATUS CHAR(5) CHECK ( STATUS IN ('已支付','未支付') ), #状态
    FOREIGN KEY (PNUM) REFERENCES PAT(PNUM)
    ON DELETE CASCADE  ON UPDATE CASCADE,
    FOREIGN KEY (PRNUM) REFERENCES PRESCRI(PRNUM)
    ON DELETE CASCADE  ON UPDATE CASCADE
);

#药品
CREATE TABLE DRUG(
    DRNUM CHAR(15) PRIMARY KEY ,
    DENAME CHAR(15), #名称
    DECATE CHAR(30) , #类别
    DEFEES FLOAT CHECK ( DEFEES>=0 ),   #价格
    PHNUM CHAR(15), #药房编号
    INVENT INT CHECK ( INVENT>=0 )     #总量
);

#药房
CREATE TABLE PHARMACY(
    PHNUM CHAR(15) PRIMARY KEY ,
    PHNAME CHAR(15) #药房名
);

#开处方关系
CREATE TABLE PRESCRI(
    PRNUM CHAR(15) PRIMARY KEY ,
    DNUM CHAR(15), #医生编号
    PNUM CHAR(15),#患者编号
    PRTIME DATETIME,
    FOREIGN KEY (PNUM) REFERENCES PAT(PNUM)
    ON DELETE CASCADE  ON UPDATE CASCADE,
    FOREIGN KEY (DNUM) REFERENCES DOCTOR(DNUM)
    ON DELETE CASCADE  ON UPDATE CASCADE
);

#处方
CREATE TABLE PRESCTIPTION(
    PRENUM CHAR(15) PRIMARY KEY ,
    PRNUM CHAR(15),
    DRNUM CHAR(15), #药品编号
    WAY CHAR(15), #用药方法
    COUNT INT CHECK ( COUNT>0 ),
    FOREIGN KEY (DRNUM) REFERENCES DRUG(DRNUM)
    ON DELETE CASCADE  ON UPDATE CASCADE,
    FOREIGN KEY (PRNUM) REFERENCES PRESCRI(PRNUM)
    ON DELETE CASCADE  ON UPDATE CASCADE
);

#患者账户表
CREATE TABLE P_ACCOUNT(
    U_TYPE CHAR(5),
    U_NAME CHAR(20) PRIMARY KEY ,
    U_PASS CHAR(50),
    PNUM CHAR(15),
    FOREIGN KEY (PNUM) REFERENCES PAT(PNUM)
    ON DELETE CASCADE  ON UPDATE CASCADE
);

#医生账户表
CREATE TABLE D_ACCOUNT(
    U_TYPE CHAR(5),
    U_NAME CHAR(20) PRIMARY KEY ,
    U_PASS CHAR(50),
    DNUM CHAR(15),
    FOREIGN KEY (DNUM) REFERENCES DOCTOR(DNUM)
    ON DELETE CASCADE  ON UPDATE CASCADE
);


/*                            创建视图                     */
#医生科室账号
create view r_doc_acc
as
select DOCTOR.DNUM,ROOM.RNUM,DNAME,DSEX,DAGE,RNAME,DPROF,DSPC,DFEES,U_NAME,DINFO
from doctor,room,d_account
where DOCTOR.RNUM=ROOM.RNUM
and DOCTOR.DNUM=D_ACCOUNT.DNUM;

#挂号视图
create view res_p_d
as
select RENUM,REGIS.DNUM,REGIS.PNUM,PDISC,PTIME,STAUS,DNAME,PNAME,PID,PAGE,PSEX,PETEL,PTEL
from pat,doctor,regis
where REGIS.DNUM=DOCTOR.DNUM
and REGIS.PNUM=PAT.PNUM;

#查看病历视图
create view rec_m_r
as
select MNUM,RENUM,MEDRECORD.DNUM,MEDRECORD.PNUM,PNAME,DNAME,PDISC,EMED,ADAVICE,PTIME
from MEDRECORD,REGIS,PAT,DOCTOR
where MEDRECORD.DNUM=DOCTOR.DNUM
and MEDRECORD.PNUM=PAT.PNUM
and REGIS.PNUM=PAT.PNUM
and REGIS.DNUM=DOCTOR.DNUM;


#处方视图
create view pres_drug
as
select PRESCRI.PRNUM,PRENUM,PRESCRI.DNUM,PRESCRI.PNUM,PRESCTIPTION.DRNUM,DENAME,DNAME,PNAME,WAY,COUNT,DFEES ,DEFEES,INVENT,PRTIME
FROM prescri,presctiption,drug,doctor,pat
where PRESCRI.PRNUM=PRESCTIPTION.PRNUM
and PRESCTIPTION.DRNUM=DRUG.DRNUM
and PRESCRI.DNUM=DOCTOR.DNUM
and PRESCRI.PNUM=PAT.PNUM;


#账单视图
create view bill
as
select RECNUM,CONSRECORD.PRNUM,CONSRECORD.PNUM,DRNUM,CONSRECORD.DFEES,DRAFEES,AFEES,STATUS,DNAME,COUNT,DENAME,DEFEES,INVENT,PNAME,PRTIME
from consrecord,pres_drug
where CONSRECORD.PRNUM=pres_drug.PRNUM;