--------------------------------------------------------------
----超市管理系統
--------------------------------------------------------------

--------------------------------------------------------------
----建庫
--------------------------------------------------------------
use master
go
if exists (select 1 from sysdatabases where name = 'DB_SMS')
	drop database DB_SMS
go
create database DB_SMS
on primary
(
	/*主數據文件描述*/
	name = 'DB_SMS_data',
	filename =  'D:\DB_SMS_data.mdf',
	size = 5MB,
	maxsize = 100MB,
	filegrowth = 10%
)
log on
(
	/*日志文件描述*/
	name = 'DB_SMS_log',
	filename = 'D:\DB_SMS_log.ldf',
	size = 3MB,
	filegrowth = 1MB
)
go

--------------------------------------------------------------
----建表
--------------------------------------------------------------
use DB_SMS
go

--------------------------------------------------------------
----商品類型表
--------------------------------------------------------------
if exists (select 1 from sysobjects where name = 'TB_GOODS_TYPE')
	drop table TB_GOODS_TYPE
go
create table TB_GOODS_TYPE
(
	gtId int identity(1,1) not null,	--商品類型編號
	gtName varchar(20) not null,		--商品類型名稱
	gtParentId int not null,			--商品父類型編號
)
go
--------------------------------------------------------------
----商品類型表的約束
--------------------------------------------------------------
--商品類型編號設為主鍵
if exists (select 1 from sysobjects where name = 'PK_GTID')
begin
	alter table TB_GOODS_TYPE
		drop constraint PK_GTID
end
go
alter table TB_GOODS_TYPE
	add constraint PK_GTID primary key(gtId)
go


--------------------------------------------------------------
----商品表
--------------------------------------------------------------
if exists (select 1 from sysobjects where name = 'TB_GOODS') 
	drop table TB_GOODS
go
create table TB_GOODS
(
	gsId varchar(14) not null,		--商品編號
	gsName varchar(30) not null,	--商品名稱
	gtId int not null,		--商品類型編號
	gsPrice numeric(10,2) not null	--商品價格
)
go
--------------------------------------------------------------
----商品表的約束
--------------------------------------------------------------
--商品編號設為主鍵約束
if exists (select 1 from sysobjects where name = 'PK_GSID')
begin
	alter table TB_GOODS
		drop constraint PK_GSID
end
go
alter table TB_GOODS
	add constraint PK_GSID primary key(gsId)
--商品類型編號引用商品類型表的主鍵
if exists (select 1 from sysobjects where name = 'FK_GS_GTID')
begin
	alter table TB_GOODS
		drop constraint FK_GS_GTID
end
go
alter table TB_GOODS
	add constraint FK_GS_GTID foreign key(gtId) references TB_GOODS_TYPE(gtId)


--------------------------------------------------------------
----員工類型表
--------------------------------------------------------------
if exists (select 1 from sysobjects where name = 'TB_EMPLOYEE_TYPE')
	drop table TB_EMPLOYEE_TYPE
go
create table TB_EMPLOYEE_TYPE
(
	etId int identity(1,1) not null,	--員工類型編號
	etName varchar(20) not null,		--員工類型名稱
)
go
--------------------------------------------------------------
----員工類型表的約束
--------------------------------------------------------------
--員工類型編號設為主鍵
if exists (select 1 from sysobjects where name = 'PK_ETID')
begin
	alter table TB_EMPLOYEE_TYPE
		drop constraint PK_ETID
end
go
alter table TB_EMPLOYEE_TYPE
	add constraint PK_ETID primary key(etId)
go


--------------------------------------------------------------
----員工表
--------------------------------------------------------------
if exists (select 1 from sysobjects where name = 'TB_EMPLOYEE')
	drop table TB_EMPLOYEE
go
create table TB_EMPLOYEE
(
	emId varchar(14) not null,		--員工編號
	emName varchar(20) not null,	--員工姓名
	emPass varchar(8) not null,		--員工密碼
	etId int not null,				--員工類型
	emState bit not null			--員工狀態 0-可用 1-禁用
)
go
--------------------------------------------------------------
----員工表的約束
--------------------------------------------------------------
--員工編號設為主鍵
if exists (select 1 from sysobjects where name = 'PK_EMID')
begin
	alter table TB_EMPLOYEE
		drop constraint PK_EMID
end
go
alter table TB_EMPLOYEE
	add constraint PK_EMID primary key(emId)
go
--員工類型引用員工類型表中的主鍵
if exists (select 1 from sysobjects where name = 'FK_EM_ETID')
begin
	alter table TB_EMPLOYEE
		drop constraint FK_EM_ETID
end
go
alter table TB_EMPLOYEE
	add constraint FK_EM_ETID foreign key(etId) references TB_EMPLOYEE_TYPE(etId)
go


--------------------------------------------------------------
----會員表
--------------------------------------------------------------
if exists (select 1 from sysobjects where name = 'TB_MEMBER')
	drop table TB_MEMBER
go
create table TB_MEMBER
(
	meId varchar(14) not null,			--會員編號
	meName varchar(20) not null,		--會員姓名
	meIdentity numeric(18,0) not null,  --證件號碼
	mePoint int not null,				--會員積分
)
go
--------------------------------------------------------------
----會員表的約束
--------------------------------------------------------------
--會員編號設為主鍵
if exists (select 1 from sysobjects where name = 'PK_MEID')
begin
	alter table TB_MEMBER
		drop constraint PK_MEID
end
go
alter table TB_MEMBER
	add constraint PK_MEID primary key(meId)
go


--------------------------------------------------------------
----銷售單表
--------------------------------------------------------------
if exists (select 1 from sysobjects where name = 'TB_SALE_SHEET')
	drop table TB_SALE_SHEET
go
create table TB_SALE_SHEET
(
	ssId varchar(14) not null,			--銷售單編號
	emId varchar(14) not null,			--銷售員編號
	ssAmount numeric(10,2) not null,	--銷售總額
	ssTime datetime not null,			--銷售日期
)
go
--------------------------------------------------------------
----銷售單表的約束
--------------------------------------------------------------
--銷售單編號設為主鍵
if exists (select 1 from sysobjects where name = 'PK_SSID')
begin
	alter table TB_SALE_SHEET
		drop constraint PK_SSID
end
go
alter table TB_SALE_SHEET
	add constraint PK_SSID primary key(ssId)
go
--銷售員編號引用員工表的主鍵
if exists (select 1 from sysobjects where name = 'FK_EMID')
begin
	alter table TB_SALE_SHEET
		drop constraint FK_EMID
end
go
alter table TB_SALE_SHEET
	add constraint FK_EMID foreign key(emId) references TB_EMPLOYEE(emId)
go


--------------------------------------------------------------
----銷售商品表
--------------------------------------------------------------
if exists (select 1 from sysobjects where name = 'TB_SALE_GOODS')
	drop table TB_SALE_GOODS
go
create table TB_SALE_GOODS
(
	sgId varchar(14) not null,		--銷售商品編號
	gsId varchar(14) not null,		--商品編號
	sgCount int not null,			--銷售數量		
	ssId varchar(14) not null,		--銷售單編號
)
go
--------------------------------------------------------------
----銷售商品表的約束
--------------------------------------------------------------
--銷售商品編號設為主鍵
if exists (select 1 from sysobjects where name = 'PK_SGID')
begin
	alter table TB_SALE_GOODS
		drop constraint PK_SGID
end
go
alter table TB_SALE_GOODS
	add constraint PK_SGID primary key(sgId)
go
--商品編號引用商品表的主鍵
if exists (select 1 from sysobjects where name = 'FK_SG_GSID')
begin
	alter table TB_SALE_GOODS
		drop constraint FK_SG_GSID
end
go
alter table TB_SALE_GOODS
	add constraint FK_SG_GSID foreign key(gsId) references TB_GOODS(gsId)
go
--銷售單編號引用銷售單表的主鍵
if exists (select 1 from sysobjects where name = 'FK_SG_SSID')
begin
	alter table TB_SALE_GOODS
		drop constraint FK_SG_SSID
end
go
alter table TB_SALE_GOODS
	add constraint FK_SG_SSID foreign key(ssId) references TB_SALE_SHEET(ssId)
go


--------------------------------------------------------------
----供貨商表
--------------------------------------------------------------
if exists (select 1 from sysobjects where name = 'TB_PROVIDER')
	drop table TB_PROVIDER
go
create table TB_PROVIDER
(
	prId varchar(14) not null,			--供貨商編號
	prName varchar(30) not null,		--供貨商名稱
	prLinkMan varchar(10) not null,		--聯系人		
	prTelephone varchar(30) not null,	--聯系電話
)
go
--------------------------------------------------------------
----供貨商表的約束
--------------------------------------------------------------
--供貨商編號設為主鍵
if exists (select 1 from sysobjects where name = 'PK_PRID')
begin
	alter table TB_PROVIDER
		drop constraint PK_PRID
end
go
alter table TB_PROVIDER
	add constraint PK_PRID primary key(prID)
go


--------------------------------------------------------------
----庫存表
--------------------------------------------------------------
if exists (select 1 from sysobjects where name = 'TB_STORAGE')
	drop table TB_STORAGE
go
create table TB_STORAGE
(
	stId varchar(14) not null,		--庫存編號
	gsId varchar(14) not null,		--商品編號
	prId varchar(14) not null,		--供貨商編號
	stCount int not null,			--入庫數量		
	emId varchar(14) not null,		--采購員編號
	stTime datetime not null		--入庫時間
)
go
--------------------------------------------------------------
----庫存表的約束
--------------------------------------------------------------
--庫存編號設為主鍵
if exists (select 1 from sysobjects where name = 'PK_STID')
begin
	alter table TB_STORAGE
		drop constraint PK_STID
end
go
alter table TB_STORAGE
	add constraint PK_STID primary key(stId)
go
--商品編號引用商品表的主鍵
if exists (select 1 from sysobjects where name = 'FK_ST_GSID')
begin
	alter table TB_STORAGE
		drop constraint FK_ST_GSID
end
go
alter table TB_STORAGE
	add constraint FK_ST_GSID foreign key(gsId) references TB_GOODS(gsId)
go
--供貨商編號引用供貨商表的主鍵
if exists (select 1 from sysobjects where name = 'FK_ST_PRID')
begin
	alter table TB_STORAGE
		drop constraint FK_ST_PRID
end
go
alter table TB_STORAGE
	add constraint FK_ST_PRID foreign key(prId) references TB_PROVIDER(prId)
go
--采購員編號引用員工表的主鍵
if exists (select 1 from sysobjects where name = 'FK_ST_EMID')
begin
	alter table TB_STORAGE
		drop constraint FK_ST_EMID
end
go
alter table TB_STORAGE
	add constraint FK_ST_EMID foreign key(emId) references TB_EMPLOYEE(emId)
go