drugmanager.sql

一个简单的药店进

/*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','操作员');

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);


/*****************建立存储区表**************************/
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 ('RKD0013','2003-05-7','黄静波',250);


insert into OrderForm
values ('RKD0014','2007-07-8','黄静波',300);


/***************** 建立出库单 ************************/
if exists(select name from sysobjects
	where name = 'SaleForm' and type = 'u')
   drop table SaleForm
go

--出库单(ID,出库时间,操作员,总的价钱)
create table SaleForm(
    SFNo char(10) primary key,  
    SFTime DateTime, 
    SFUserName char(20),
    SFPrice  real  
);

insert into SaleForm
values ('CKD0012','2004-02-15','小黄',200);


insert into SaleForm
values ('CKD0013','2003-05-7','小黄',250);


insert into SaleForm
values ('CKD0014','2007-07-8','小黄',300);


/******************出库单 细目表********************/
if exists(select name from sysobjects
	where name = 'SaleItem' and type = 'u')
   drop table SaleItem
go


--出库单 细目表(出库单号,药品号,药品数量,出售价格)
create table SaleItem(
    SFNo char(10) references SaleForm(SFNO),  
    DNo char(5) references Drug(dno), 
    DNum int,
    SIPrice  real  ,
    primary key(SFNO,dNo)
);

insert into SaleItem
values('CKD0012','Y0857',10,5.5)

insert into SaleItem
values('CKD0013','Y0865',20,3.5)

insert into SaleItem
values('CKD0014','Y0855',20,8.5)


/************** 建立药品与药房之间的存储关系 **************/
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,
   DSMin int,
   DSMax 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('RKD0013','GYS10072','Y0865',50,2.5)

insert into EnStore
values('RKD0014','GYS10035','Y0855',40,7.5)