sms.sql

毕业设计作品--超市管理系统

--------------------------------------------------------------
----超市管理系统
--------------------------------------------------------------

--------------------------------------------------------------
----建库
--------------------------------------------------------------
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