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('貴重商品','非食')