/*if not exists(
select * from sysobjects
where name = 'DrugStoreManage'
) create database DrugStoreManage
go*/
use DrugStoreManage
/*************************** 建立用户信息表 ***************************/
if exists(select name from sysobjects
where name = 'userInfo' and type = 'u')
drop table userInfo
go
create table userInfo(
UName char(50) primary key,
UPassword char(50) not null,
UKind char(6)
check (UKind in('管理员', '操作员'))
);
--输入数据
insert into userInfo
values('茹寅飞','123','操作员');
insert into userInfo
values('朱清和','123','操作员');
insert into userInfo
values('孙承碧','123','操作员');
insert into userInfo
values('黄静波','123','操作员');
/**********************建立供应商表***********************/
if exists(
select * from sysobjects
where name = 'Provider' and type = 'u'
) drop table Provider
--供应商(ID,名称，电话，地址)
create table Provider(
PNO varchar(10) primary key,
PName varchar(40) not null,
PTel varchar(13),
PAddress varchar(40)
)
--供应商ID均以GYS开头
insert into Provider
values('GYS10011','贵州省医药（集团）有限责任公司','0425-88301179','贵州省');
insert into Provider
values('GYS10072','沈阳光大制药有限公司','0728-79283243','辽宁省沈阳市');
insert into Provider
values('GYS10035','襄樊百合药业有限责任公司','0732-8827592',null)
/*************************** 建立药品表 ************************/
if exists(select name from sysobjects
where name = 'Drug' and type = 'u')
drop table Drug
go
--药品(ID,名称，类别，剂型，规格，生产厂家，质量层次,当前价格)
create table Drug(
DNo char(5) primary key,
DName varchar(20) not null,
DKind varchar(8) not null check(Dkind in ('中药','西药','中成药','卫生材料','其它')),
DJiXing varchar(6) check(DJiXing in('片剂','针剂','膏剂','冲剂','粉剂','胶囊','其它')) ,
DGuiGe varchar(16),
DMade varchar(20),
DQua varchar(8) check(DQua in('GMP','第一层次')),
DPri real
);
/* (药序号 药名 药种类 剂型 规格 生产厂家 质量层次 批号 价格 上限 下限) */
insert into Drug
values('Y0855','盐酸土霉素片','西药','片剂','0.25×1000','西安杨森','GMP',44.8);
insert into Drug
values('Y0857','意可贴','西药','片剂','10g×9袋','沈阳光大制药有限公司','GMP',22.6);
insert into Drug
values('Y0865','板兰根片','中成药','片剂','100片','广东百奥药业','第一层次',45.3);
--处方(ID,日期)
/*****************建立存储区表**************************/
if exists(
select * from sysobjects
where name = 'StorageArea' and type = 'u'
) drop table StorageArea
--药房(ID,名称)
create table StorageArea(
SNo int primary key check(Sno >= 0),
SName varchar(40) not null,
)
insert into StorageArea
values(0,'A区');
insert into StorageArea
values(1,'B区');
insert into StorageArea
values(2,'C区');
/************************** 建立医生表 *************************/
if exists(select name from sysobjects
where name = 'Doctor' and type = 'u')
drop table Doctor
go
--医生(ID,姓名,性别,出生日期,科室)
create table Doctor(
DNo char(7) primary key,
Dname varchar(8),
DSex char(2) check(DSex in('男','女')),
DBirthday DateTime,
class char(6),check(class in('内科','外科','儿科'))
);
/*********************** 建立病人表 **************************/
if exists(select name from sysobjects
where name = 'Patient' and type = 'u')
drop table Patient
go
--病人(ID,姓名,性别,出生日期)
create table Patient(
PNo char(10) primary key,
Pname varchar(8),
Psex char(2) check(PSex in('男','女')),
PBirthday DateTime,
);
insert into Patient
values('BR05732','夏翔','女','1988-04-23');
insert into Patient
values('BR05734','王强','男','1980-05-23');
/***************** 建立入库单 ************************/
if exists(select name from sysobjects
where name = 'OrderForm' and type = 'u')
drop table OrderForm
go
--入库单(ID,入库时间,经手人,总的价钱)
create table OrderForm(
OFNo char(10) primary key, --入库单号
OFTime DateTime, --入库时间
OFUserName char(20), --用户名
OFPrice real --总的钱数
);
insert into OrderForm
values ('RKD0012','2004-02-15','黄静波',200);
insert into OrderForm
values ('RKD0014','2003-05-7','黄静波',250);
insert into OrderForm
values ('RKD0212','2007-07-8','黄静波',300);
/************** 建立药品与药房之间的存储关系 **************/
if exists(select name from sysobjects
where name = 'DrugStore' and type = 'u')
drop table DrugStore
go
--药房<->药品(药房ID,药品ID,存储数量,上限,下限)
create table DrugStore(
SNo int references StorageArea(SNO),
DNo char(5) references Drug(dno),
DSNum int,
DSMax int,
DSMin int,
primary key(DNo,SNo)
);
insert into DrugStore
values(1,'Y0855',20,20,50);
insert into DrugStore
values(2,'Y0857',30,20,50);
insert into DrugStore
values(0,'Y0865',40,20,50);
/***************** 入库单<->经销商<->药品 **/
if exists(select name from sysobjects
where name = 'EnStore' and type = 'u')
drop table EnStore
go
--入库单<->经销商<->药品(入库单ID,经销商ID,药品ID,药品数量,药品单价)
create table EnStore(
OFNo char(10) references OrderForm(OFNo),
PNO varchar(10) references Provider(PNO),
DNo char(5) references Drug(dno),
DNum int,
DPrice real,
primary key(OFNO,PNo,DNo)
);
insert into EnStore
values('RKD0012','GYS10011','Y0857',30,3.5)
insert into EnStore
values('RKD0014','GYS10072','Y0865',50,2.5)
insert into EnStore
values('RKD0212','GYS10035','Y0855',40,7.5)
