综合设计数据库08.02.sql

小型超市进销存系统

use master

IF EXISTS (SELECT * 
	   FROM   sysdatabases 
	   WHERE  name ='supermarket' )
	DROP DATABASE supermarket
GO

CREATE DATABASE supermarket
on primary
(name=market_dat,
filename='d:\market_dat.mdf',
size=5,
maxsize=50,
filegrowth=5%
)
log on
(name=market_log,
filename='d:\market_log.ldf',
size=1,
maxsize=5,
filegrowth=5%
)
go

use supermarket
go
create table t_provider--供应商表
(providerid varchar(5) primary key,
providername varchar(20),
linkman varchar(20),
job varchar(20),
mobile varchar(15),
offictel varchar(15),
provideradress varchar(200),
employeeid varchar(5)
)
go
create table t_brand--品牌表
(
brand varchar(20) unique,
providerid varchar(5)
)
go
create table t_area--超市区域表
(
sarea varchar(4) primary key
)
go
create table t_type--商品类型表
(
typename varchar(20) unique,
sarea varchar(4)
)
go
create table t_goods--库存表
(goodsid varchar(8) primary key,
goodsname varchar(20),
upc varchar(15),
brand varchar(20),
typename varchar(20),
spec varchar(10),
unit varchar(4),
store int ,
area varchar(20)
)
go
create table t_remove--调货表
(goodsid varchar(8),
goodsname varchar(20),
spec varchar(10),
num int
)
go

create table t_price--价格表
(goodsid varchar(8),
goodsname varchar(20),
spec varchar(10),
brand varchar(20),
typename varchar(20),
rate float default 1.2,
inprice money,
outprice money check (outprice>0)
)
go

create table t_market--卖场表
(goodsid varchar(8),
goodsname varchar(20),
brand varchar(20),
unit varchar(4),
num int
primary key (goodsid)
)

go
create table t_order--订单表
(orderid varchar(8) primary key ,
providerid varchar(5),
allmoney money,
stime smalldatetime,
etime smalldatetime,
orderstate varchar(4),
demployeeid varchar(5),
lemployeeid varchar(5)
)
go
create table t_orderdetail--订单详情表
(orderid varchar(8),
goodsid varchar(8),
goodsname varchar(20),
upc varchar(15),
spec varchar(10),
unit varchar(4),
num int,
inprice money
)
go
create table t_department--部门表
(
department varchar(20)
)
go
create table t_employee--雇员表
(employeeid varchar(5) primary key,
empname varchar(20),
sex varchar(2) not null,
birthday smalldatetime,
knowledge varchar(4) not null,
cardid varchar(18),
department varchar(20)
)
go
create table t_purview--权限表
(employeeid varchar(5),
purview tinyint,--权限的级别，使用小整型（0-255）
pwd varchar(20)
)
go
create table t_sale--销售表
(saleid int identity(1,1) primary key,
stime smalldatetime,
employeeid varchar(5),
amoney money,
saletype varchar(4)--销售类型（网上，店面）
)
go

create table t_sdetail--销售详情表
(saleid int,
goodsid varchar(8),
goodsname varchar(20),
unit varchar(4),
num int,
outprice money
)

go

create table t_waitgoods--待退货商品
(backid int identity(1,1) primary key,
goodsid varchar(8),
goodsname varchar(20),
goodsnum int,
typename varchar(20),
brand varchar(20),
spec varchar(10)
)
create table t_bgoodtable--退货流水表
(backid varchar(8) primary key,
providerid varchar(5),
stime smalldatetime,
protime smalldatetime,
allmoney money,
state varchar(4),
employeeid varchar(5)
)
go
create table t_bgooddetail--退货详情表
(backid varchar(8),
goodsid varchar(8),
goodsname varchar(20),
upc varchar(15),
num int,
inprice money
)
go
create table t_qback--前台退货
(qbackid varchar(8) primary key,
saleid int,
allmoney money,
employeeid varchar(5),
tdate smalldatetime
)
go

create table t_qbackdetail--前台退货详情表
(qbackid varchar(8),
goodsid varchar(8),
goodsname varchar(20),
num int,
price money,
reason varchar(200),
procstyle varchar(4)
)
go
create table t_corder--客户订单表
(corderid int identity(1,1) primary key,
cname varchar(20),
ctel varchar(15),
cadress varchar(200),
cprice money,
cstime datetime,--订货日期
cetime datetime,--提货日期
cstate varchar(4),
cstyle varchar(4),--顾客取货方式
employeeid varchar(5)
)
go
create table t_corderdetail--客户订单详情表
(corderid int,
goodsid varchar(8),
goodsname varchar(20),
num int,
price money
)


alter table t_type with check add foreign key (sarea) references t_area (sarea)
alter table t_goods  with check add foreign key (brand)  references t_brand (brand)
alter table t_goods  with check add foreign key (typename)  references t_type (typename)
alter table t_price with check add foreign key (goodsid)  references t_goods (goodsid)
alter table t_price  with check add foreign key (brand)  references t_brand (brand)
alter table t_price  with check add foreign key (typename)  references t_type (typename)
alter table t_market with check add foreign key (goodsid)  references t_goods (goodsid)
alter table t_market with check add foreign key (brand) references t_brand (brand)
alter table t_remove with check add foreign key (goodsid)  references t_goods (goodsid)
alter table t_order with check add foreign key (providerid) references t_provider (providerid)
alter table t_orderdetail with check add foreign key (orderid) references t_order (orderid)
alter table t_orderdetail with check add foreign key (goodsid) references t_goods (goodsid)
alter table t_sdetail with check add foreign key (saleid) references t_sale (saleid)
alter table t_sdetail with check add foreign key (goodsid) references t_market (goodsid)
alter table t_waitgoods with check add foreign key (goodsid) references t_market (goodsid)
alter table t_bgooddetail with check add foreign key (backid) references t_bgoodtable (backid)
alter table t_bgoodtable with check add foreign key (providerid) references t_provider (providerid)
alter table t_bgooddetail with check add foreign key (goodsid) references t_market (goodsid)
alter table t_qback with check add foreign key (saleid) references t_sale (saleid)
alter table t_qbackdetail with check add foreign key (qbackid) references t_qback (qbackid)
alter table t_qbackdetail with check add foreign key (goodsid) references t_market (goodsid)
alter table t_corderdetail with check add foreign key (goodsid) references t_market (goodsid)
alter table t_corderdetail with check add foreign key (corderid) references t_corder (corderid)
alter table t_purview with check add foreign key (employeeid) references t_employee (employeeid) on delete cascade

insert into t_area values('食品')
insert into t_area values('中心')
insert into t_area values('非食')
insert into t_area values('生鲜')
insert into t_department values('采购部')
insert into t_department values('收货部')
insert into t_department values('销售部')
insert into t_department values('商品部')
insert into t_employee values('C0001','小杨','男','','','','')
insert into t_employee values('S0001','小王','男','','','','')
insert into t_employee values('X0001','小张','男','','','','')
insert into t_employee values('G0001','小郭','男','','','','')
insert into t_brand values('test','P0001')
insert into t_goods values('G0000001','test','0000000000001','test','','','',0,'食品')
insert into t_market values('G0000001','test','','','100')
insert into t_order values('D0000001','',0,'','','确认','C0001','S0001')
insert into t_price values('G0000001','test','','','',1.2,10,10.2)
insert into t_provider values('P0001','test','','','','','','')
insert into t_qback(qbackid) values('T0000001')
insert into t_type values('香烟','食品')
insert into t_type values('酒类','食品')
insert into t_type values('饮料','食品')
insert into t_type values('调味品','食品')
insert into t_type values('粮油','食品')
insert into t_type values('保健品','食品')
insert into t_type values('固体饮料','食品')
insert into t_type values('代餐食品','食品')
insert into t_type values('罐头','食品')
insert into t_type values('小食','食品')
insert into t_type values('鞋袜','中心')
insert into t_type values('纺织品','中心')
insert into t_type values('床上用品','中心')
insert into t_type values('内衣','中心')
insert into t_type values('箱包','中心')
insert into t_type values('玻璃制品','非食')
insert into t_type values('文具','非食')
insert into t_type values('电池','非食')
insert into t_type values('体育用品','非食')
insert into t_type values('运动器械','非食')
insert into t_type values('五金','非食')
insert into t_type values('厨卫用具','非食')
insert into t_type values('塑料制品','非食')
insert into t_type values('小家电','非食')
insert into t_type values('大家电','非食')
insert into t_type values('贵重商品','非食')