医院门诊管理系统数据库设计

ER图

sql语句

--新建数据库CREATE  DATABASE  HOSPITAL_OUTPATIENT_MS
ON PRIMARY
(
NAME = [201811040728_data],
FILENAME = 'E:\HOMS_data.mdf',
SIZE=10MB
)
LOG ON(
NAME = [201811040728_log],
FILENAME = 'E:\HOMS_log.ldf',
SIZE=5MB
)--新建表CREATE TABLE PAT --病人
(
PA# CHAR(10),   --病人卡号
NAME CHAR(10) NOT NULL,  --姓名
SEX CHAR(2)  NOT NULL,  --性别
AGE SMALLINT  NOT NULL,--年龄>0
NUMBER CHAR(18)  NOT NULL, --身份证号
PHONE CHAR(11)  NOT NULL,  --手机号
BALANCE DOUBLE PRECISION  NOT NULL, --卡上余额>0
PRIMARY KEY(PA#)
);ALTER TABLE PAT
ADD CONSTRAINT CEK_PAT_BAL
CHECK(BALANCE>0);ALTER TABLE PAT
ADD CONSTRAINT CEK_PAT_AGE
CHECK(AGE>0);ALTER TABLE PAT
ADD CONSTRAINT CEK_PAT_SEX
CHECK(SEX='男' OR SEX='女')CREATE TABLE DPM --科室
(
DE# CHAR(10), --科室编号
NAME CHAR(20) NOT NULL, --科室名
PHONE CHAR(11)  NOT NULL, --科室电话
PRIMARY KEY(DE#)
);CREATE TABLE RGF--挂号费
(
TITLE CHAR(10),--职称
PRICE DOUBLE PRECISION NOT NULL,--挂号费>0
PRIMARY KEY(TITLE)
);ALTER TABLE RGF
ADD CONSTRAINT CEK_RGF_PRI
CHECK(PRICE>0);CREATE TABLE DOC --医生
(
DO# CHAR(10), --医生编号
NAME CHAR(20) NOT NULL, --姓名
SEX CHAR(2)  NOT NULL, --性别
AGE SMALLINT  NOT NULL,--年龄>0
TITLE CHAR(10)  NOT NULL, --职称
DE# CHAR(10)  NOT NULL, --所属科室编号
PRIMARY KEY(DO#),
FOREIGN KEY(TITLE)REFERENCES RGF(TITLE),
FOREIGN KEY(DE#)REFERENCES DPM(DE#)
);ALTER TABLE DOC
ADD CONSTRAINT CEK_DOC_AGE
CHECK(AGE>0);ALTER TABLE DOC
ADD CONSTRAINT CEK_DOC_SEX
CHECK(SEX='男' OR SEX='女')CREATE TABLE PMC --药房
(
PH# CHAR(10), --药房编号
NAME CHAR(10)NOT NULL, --药房名
PRIMARY KEY(PH#)
);CREATE TABLE MDC --药品
(
ME# CHAR(10), --药品编号
CATEGORY CHAR(10)  NOT NULL, --类别
NAME CHAR(20) NOT NULL,  --品名
LETTER CHAR(10) NOT NULL,  --拼音首字母
SPECIFI CHAR(20)  NOT NULL, --规格
UNIT CHAR(10) NOT NULL,--单位
MPRICE DOUBLE PRECISION  NOT NULL, --单价>0
INVENTORY SMALLINT  NOT NULL, --库存量>0
PH# CHAR(10) NOT NULL,  --药房编号
PRIMARY KEY(ME#),
FOREIGN KEY(PH#)REFERENCES PMC(PH#)
);ALTER TABLE MDC
ADD CONSTRAINT CEK_MDC_PRI
CHECK(MPRICE>0);ALTER TABLE MDC
ADD CONSTRAINT CEK_MDC_INV
CHECK(INVENTORY>0);CREATE TABLE PRE --处方
(
PR# CHAR(10), --处方编号
TIME CHAR(12) NOT NULL, --时间
DID TINYINT NOT NULL,--已执行
DO# CHAR(10) NOT NULL, --医生编号
PA# CHAR(10) NOT NULL,  --病人卡号
PRIMARY KEY(PR#),
FOREIGN KEY(DO#)REFERENCES DOC(DO#),
FOREIGN KEY(PA#)  REFERENCES PAT(PA#)
);CREATE TABLE DWD--医嘱
(
WO# CHAR(10),  --医嘱编号
ME# CHAR(10) NOT NULL,--药品编号
QUANTITY TINYINT NOT NULL,--数量>0
ONCE CHAR(10) NOT NULL,--单次用量
FREQUENCY CHAR(20) NOT NULL, --频次
METHOD CHAR(10) NOT NULL, --给药方法
PR# CHAR(10) NOT NULL, --处方编号
PRIMARY KEY(WO#),
FOREIGN KEY(ME#)REFERENCES MDC(ME#),
FOREIGN KEY(PR#)REFERENCES PRE(PR#)
);ALTER TABLE DWD
ADD CONSTRAINT CEK_DWD_QUA
CHECK(QUANTITY>0);CREATE TABLE RCD --消费记录
(
RE# CHAR(10), --记录编号
PA# CHAR(10) NOT NULL, --病人卡号
PRICE DOUBLE PRECISION  NOT NULL,--交易金额
TIME CHAR(12) NOT NULL, --时间
PRIMARY KEY(RE#),
FOREIGN KEY(PA#)  REFERENCES PAT(PA#)
);CREATE TABLE RGS --挂号
(
RE# CHAR(10),--编号
DID TINYINT NOT NULL,--已执行
PA# CHAR(10) NOT NULL,--病人卡号
DO# CHAR(10) NOT NULL,--医生编号
PRIMARY KEY(RE#),
FOREIGN KEY(DO#)REFERENCES DOC(DO#),
FOREIGN KEY(PA#)  REFERENCES PAT(PA#)
);--数据插入
INSERT INTO DPM VALUES
('11041801','消化科','12330316'),
('11041807','骨科','12330313'),
('11041810','呼吸科','12330307'),
('11041811','神经科','12330321'),
('11041803','口腔科','12330317');INSERT INTO RGF VALUES
('住院医师',20),
('主治医师',50),
('主任医师',100);INSERT INTO DOC VALUES
('11803','王晓东','男',28,'住院医师','11041801'),
('11806','李亚伟','男',34,'主治医师','11041807'),
('11807','陈立新','男',36,'主治医师','11041807'),
('11813','张锋','男',47,'主任医师','11041810'),
('11801','李莉','男',53,'主任医师','11041810');INSERT INTO PMC VALUES
('20601','注射剂药房'),
('20602','外用药药房'),
('20603','口服药房'),
('20604','中药房'),
('20605','精神类药房');INSERT INTO MDC VALUES
('0925713','甲类','多潘立酮片','dpltp','12片×4板/盒','盒',18.00,273,'20603'),
('0925728','甲类','盐酸曲唑酮片','ysqztp','6片×3板/盒','盒',65.00,137,'20603'),
('0925741','甲类','六味地黄丸','lwdhw','300粒/瓶','瓶',9.80,253,'20603'),
('0925706','甲类','复合维生素B片','fhwssbp','20片×4片/盒','盒',6.80,274,'20603');INSERT INTO PAT VALUES
('573931','张明','男',40,'124171197908100813','18825410231',750),
('573714','李鑫','男',43,'124171197604070816','18854210271',320),
('573723','王丽','女',38,'124171198103120813','18845212014',560),
('573768','陈楠','女',37,'124171198207130811','18842589701',103);INSERT INTO PRE VALUES
('191020103','191020152003',0,'11803','573931'),
('191020104','191020163010',0,'11806','573714'),
('191020105','191020163824',0,'11806','573723');INSERT INTO DWD VALUES
('191020174','0925713',1,'1片','一日3次','口服','191020103'),
('191020175','0925728',2,'2片','一日3次','口服','191020104'),
('191020176','0925741',4,'10粒','一日1次','口服','191020104'),
('191020177','0925706',1,'4片','一日3次','口服','191020105');INSERT INTO RCD VALUES
('191020713','573931',-30,'191020164001'),
('191020714','573714',-103,'191020165823'),
('191020715','573723',500,'191020170103'),
('191020716','573723',-157,'191020170825');INSERT INTO RGS VALUES
('191020073',0,'573931','11803'),
('191020074',0,'573714','11803'),
('191020075',0,'573723','11806'),
('191020076',0,'573768','11803');

查询问题

1.医生给病人开药时，输入药品名的拼音，显示与输入的拼音一致的药品名，并将选定的药品的基本信息显示出来。
首先获取所有药品的拼音，与输入的拼音比对，再用所有符合条件的拼音获取所有符合条件药品的药品名，选定某一药品时，根据药品编号获取该药品的相关信息。
2.病人拿药时，将所花费金额从病人余额中扣除。
查找该病人的处方，根据处方编号查找该处方的所有医嘱。将药品单价与数量的乘积从病人的余额中扣除。