超市进销存系统 数据库设计报告

超市进销存系统 数据库设计报告

注意：本设计报告省略了需求分析部分、实体说明、联系说明、索引说明等，重点是数据库的实现过程以及sql语言的编写以及其他一些我认为的重点展示
另外：本系统前期主要使用了软件PowerDesigner，从需求分析到模型设计、约束条件、视图、业务规则等，都是用的该软件。

产品简介

超市进销存系统主要为商品的进货上架、销售收银、仓库存储提供线上管理的功能。

目标客户：中小型超市

客户的业务需求：

1. 改变传统的人工管理，实现日常管理信息化；

2. 通过对库存和销售信息的快速查询和处理，提高商品的采购的速度和科学性；

3. 提升超市管理水平，降低经营成本，提高工作效率。

本系统的总体框架

---

数据模型

BPM模型

CDM 模型

PDM模型

部分存储器、触发器、函数的设计

本人负责的是关于存货模块的存储器、触发器、函数设计，这里举了几个例子：

• 存储器设计1、

/*具有“采购员”职称的员工加薪15%，“理货员”加薪10%，“收银员”加薪5%，“经理”加薪20%*/
create procedure proc_raise
as
declare cur cursor for select workerNum,job from t_user
declare @increment decimal(2,1)
declare @num int
declare @tjob varchar(50)
open cur
fetch next from cur into @num,@tjob
while(@@fetch_status=0)
begin
if @tjob='采购员' set @increment=0.15
else if @tjob='理货员'set @increment=0.1
else if @tjob='收银员' set @increment=0.05
else if @tjob='经理' set @increment=0.2
update t_user
set salary=salary*(1+@increment)
where workerNum=@num
fetch next from cur into @num,@tjob
end
close cur
deallocate cur
go

测试结果：
未执行存储器之前的员工表：

执行加薪功能的的存储器之后：

execute proc_raise

• 存储器设计2、

/*清空库存信息表：t_inventory钟库存量为0的商品信息*/
create procedure proc_amount0
as
declare cur cursor for select goodsNum,amount from t_inventory
declare @gnum int
declare @gamount int
open cur
fetch next from cur into @gnum,@gamount
while(@@fetch_status=0)
begin
if @gamount=0
delete from t_inventory
where goodsNum=@gnum
fetch next from cur into @gnum,@gamount
end
close cur
deallocate cur

测试结果：
未执行存储器之前的t_inventory:

执行存储器：

execute proc_amount0

• 用户自定义函数设计1、

/*输入商品编号，在t_goodsOn表上将该商品标注为' 促销-买一送一'*/

create function dbo.func_num_cuxiao(@gnum int)
returns varchar(50)
as
begin
declare @gname varchar(50);
declare @gprice int;
select @gname=goodsName from t_goodsOn where t_goodsOn.goodsNum=@gnum;
select @gprice=price from t_goodsOn where t_goodsOn.goodsNum=@gnum;
set @gname=@gname+'促销-买一送一';
return @gname
end

declare @t varchar(50)
execute @t= dbo.func_num_cuxiao "01";
update t_goodsOn
set goodsName=@t where goodsNum=01 ;
select *from t_goodsOn

测试结果：

• 用户自定义函数设计2、

/*对于库存量小于10的商品，备注remark里面添加“库存紧张！！！”的字段*/
create function dbo.func_less(@gnum int)
returns varchar(50)
as 
begin
declare @mark varchar(50)
set @mark='库存紧张！！！'+(select remark from t_inventory where goodsNum=@gnum)
return @mark
end

create procedure proc_tmp
as
declare cur cursor for select goodsNum from t_inventory
declare @gnum int
declare @ta int 
declare @t varchar(50)
open cur
fetch next from cur into @gnum
while(@@fetch_status=0)
begin
select @ta=amount from t_inventory where goodsNum=@gnum
if @ta<10
begin
execute @t= dbo.func_less @gnum ;
update t_inventory
set remark=@t where goodsNum=@gnum ;
end
fetch next from cur into @gnum
end
close cur
deallocate cur

结果测试：
原来的库存信息表t_inventory:

execute proc_tmp

执行之后：

• 触发器设计1、

/*触发器：t_goodsBuy商品进价变，t_incentory的同商品的进价也变，售价+（现进价-原进价）,t_goodsSale商品售价也变*/
create trigger tri_alter_cost
on dbo.t_goodsBuy
after insert
as
begin 
declare @num int;
declare cur cursor for select goodsNum from inserted;
open cur;
fetch from cur into @num;
while @@FETCH_STATUS=0
begin
update t_inventory
set cost=(select cost from inserted where goodsNum=@num)
where goodsNum=@num;
update t_inventory
set price=price+(select cost from inserted where goodsNum=@num)-(select cost from t_inventory where goodsNum=@num)
where goodsNum=@num;
update t_goodsSale
set price=price+(select cost from inserted where goodsNum=@num)-(select cost from t_inventory where goodsNum=@num)
where goodsNum=@num;
fetch next from cur into @num;
end
close cur;
end

结果测试：
测试结果：
原来的库存表t_inventory:

原来的销售表t_goodsSale：

/*在采购员进货，增加t_goodsBuy之后：*/
insert into t_goodsBuy
values('01','口香糖','03','10','150','盒','绿箭公司','2016/8/23'),
      ('02','方便面','01','10','10','箱','康师傅公司','2016/8/23'),
      ('03','可乐','02','15','12','箱','可口可乐公司','2016/8/23'),
      ('04','饼干','04','11','15','箱','乐事公司','2016/8/23');

t_inventory库存表:

t_goodsSale销售表:

执行成功的SQL源码：

1. 建表、创建索引

/*==============================================================*/
/* Table: t_buyer                                               */
/*==============================================================*/
create table t_buyer (
   workerNum            int                  not null,
   workerName           varchar(50)          not null,
)
go

alter table t_buyer
   add constraint PK_T_BUYER primary key nonclustered (workerNum)
go

/*==============================================================*/
/* Index: Index_workerNum                                       */
/*==============================================================*/
create index Index_workerNum on t_buyer (
workerNum ASC
)
go

/*==============================================================*/
/* Index: Index_workerName                                      */
/*==============================================================*/
create index Index_workerName on t_buyer (
workerName  ASC
)
go

/*==============================================================*/
/* Table: t_cashier                                             */
/*==============================================================*/
create table t_cashier (
   workerNum            int                  not null,
   workerName           varchar(50)          not null,
   workTime             varchar(5)           not null
)
go

alter table t_cashier
   add constraint PK_T_CASHIER primary key nonclustered (workerNum)
go

/*==============================================================*/
/* Index: Index_workerNum                                       */
/*==============================================================*/
create index Index_workerNum on t_cashier (
workerNum ASC
)
go

/*==============================================================*/
/* Index: Index_workerTime                                      */
/*==============================================================*/
create index Index_workerTime on t_cashier (
workTime ASC
)
go

/*==============================================================*/
/* Table: t_goodsBuy                                            */
/*==============================================================*/
drop  table t_goodsBuy 
create table t_goodsBuy (
   goodsNum             int                  not null,
   goodsName            varchar(50)          not null,
   workerNum            int                  not null,
   cost                 int                  not null,
   amount               int                  not null,
   units                varchar(50)          not null,
   suppler              varchar(50)          not null,
   date                 varchar(50)          not null
)
go

alter table t_goodsBuy
   add constraint PK_T_GOODSBUY primary key nonclustered ()
go

/*==============================================================*/
/* Index: Index_goodsName                                       */
/*==============================================================*/
create index Index_goodsName on t_goodsBuy (
goodsName ASC
)
go

/*==============================================================*/
/* Index: Index_date                                            */
/*==============================================================*/
create index Index_date on t_goodsBuy (
date ASC
)
go

/*==============================================================*/
/* Index: Index_wokerNum                                        */
/*==============================================================*/
create index Index_wokerNum on t_goodsBuy (
workerNum ASC
)
go



/*==============================================================*/
/* Table: t_goodsOn                                             */
/*==============================================================*/
create table t_goodsOn (
   goodsNum             int                  not null,
   goodsName            varchar(50)          not null,
   cost                 int                  not null,
   prcie                int                  not null,
   amount               int                  not null,
   units                varchar(50)          not null,
   remark               varchar(300)         null,
   workerNum            int                  not null
)
go

alter table t_goodsOn
   add constraint PK_T_GOODSON primary key nonclustered (goodsNum, workerNum)
go

/*==============================================================*/
/* Index: Index_amount                                          */
/*==============================================================*/
create index Index_amount on t_goodsOn (
amount ASC
)
go

/*==============================================================*/
/* Table: t_goodsOnOut                                          */
/*==============================================================*/
create table t_goodsOnOut (
   workerNum            int                  not null,
   goodsNum             int                  not null,
   goodsName            varchar(50)           not null,
   amount               int                  not null
)
go

alter table t_goodsOnOut
   add constraint PK_T_GOODSONOUT primary key nonclustered (workerNum, goodsNum, t_g_workerNum)
go

/*==============================================================*/
/* Index: Index_workerNum                                       */
/*==============================================================*/
create index Index_workerNum on t_goodsOnOut (
workerNum ASC
)
go

/*==============================================================*/
/* Index: Index_goodsNum                                        */
/*==============================================================*/
create index Index_goodsNum on t_goodsOnOut (
goodsNum ASC
)
go

/*==============================================================*/
/* Table: t_goodsSale                                           */
/*==============================================================*/
drop table t_goodsSale
create table t_goodsSale (
   workerNum            int                  not null,
   goodsNum             int                  not null,
   goodsName            varchar(50)          not null,
   prcie                int                  not null,
   amount               int                  not null,
   units                varchar(50)          not null
)
go

alter table t_goodsSale
   add constraint PK_T_GOODSSALE primary key nonclustered (goodsNum)
go

/*==============================================================*/
/* Index: Index_workerNum                                       */
/*==============================================================*/
create index Index_workerNum on t_goodsSale (
workerNum ASC
)
go

/*==============================================================*/
/* Index: Index_profit                                          */
/*==============================================================*/
--create index Index_profit on t_goodsSale (
--profit ASC
--)
--go

/*==============================================================*/
/* Index: Index_amount                                          */
/*==============================================================*/
create index Index_amount on t_goodsSale (
amount ASC
)
go

/*==============================================================*/
/* Table: t_goosClass                                           */
/*==============================================================*/
drop table t_goodsClass
create table t_goodsClass (
   classNum             int                  not null,
   className            varchar(50)          not null,
   goodsNum             int                  not null,
    goodsName            varchar(50)          not null,
   workerNum            int                  not null

)
go

alter table t_goodsClass
   add constraint PK_T_GOOSCLASS primary key nonclustered (goodsNum)
go

/*==============================================================*/
/* Index: Index_goodsName                                       */
/*==============================================================*/
create index Index_goodsName on t_goodsClass (
goodsName ASC
)
go

/*==============================================================*/
/* Index: Index_className                                       */
/*==============================================================*/
create index Index_className on t_goodsClass (
className ASC
)
go

/*==============================================================*/
/* Table: t_inventory                                           */
/*==============================================================*/
create table t_inventory (
   goodsNum             int                  not null,
   goodsName            varchar(50)          not null,
   className            varchar(10)             not null,
   amount               int                  not null,
   cost                 int                  not null,
   prcie                int                  not null,
   units                varchar(50)          not null,
   remark               varchar(300)         null,
)
go

alter table t_inventory
   add constraint PK_T_INVENTORY primary key nonclustered (goodsNum, workerNum)
go

/*==============================================================*/
/* Index: Index_className                                     */
/*==============================================================*/
create index Index_className on t_inventory (
className ASC
)
go

/*==============================================================*/
/* Index: Index_amount                                          */
/*==============================================================*/
create index Index_amount on t_inventory (
amount ASC
)
go

/*==============================================================*/
/* Table: t_manager                                             */
/*==============================================================*/
create table t_manager (
   workerNum            int                  not null,
   workerName           varchar(50)          not null
)
go

alter table t_manager
   add constraint PK_T_MANAGER primary key nonclustered (workerNum)
go

/*==============================================================*/
/* Index: Index_workerNum                                       */
/*==============================================================*/
create index Index_workerNum on t_manager (
workerNum ASC
)
go

/*==============================================================*/
/* Index: Index_workerName                                      */
/*==============================================================*/
create index Index_workerName on t_manager (
workerName ASC
)
go

/*==============================================================*/
/* Table: t_tallyClerk                                          */
/*==============================================================*/
create table t_tallyClerk (
   workerNum            int                  not null,
   workerName           varchar(50)          not null
)
go

alter table t_tallyClerk
   add constraint PK_T_TALLYCLERK primary key nonclustered (workerNum)
go

/*==============================================================*/
/* Index: Index_workerNum                                       */
/*==============================================================*/
create index Index_workerNum on t_tallyClerk (
workerNum ASC
)
go

/*==============================================================*/
/* Index: Index_workerName                                      */
/*==============================================================*/
create index Index_workerName on t_tallyClerk (
workerName  ASC
)
go

/*==============================================================*/
/* Table: t_user                                                */
/*==============================================================*/
drop table t_user
create table t_user (
   workerNum            int                  not null,
   workerName           varchar(50)          not null,
   workerSex            varchar(5) check(workerSex in('男','女'))  not null,
   workerAge            int check(workerAge>=18 and workerAge<=60) not null,
   workTime             varchar(5)           not null,
   job                  varchar(50) check(job in('采购员','收银员','理货员','经理')) not null,
   salary               int                  not null
)
go

alter table t_user
   add constraint PK_T_USER primary key nonclustered (workerNum)
go

/*==============================================================*/
/* Index: Index_workerNum                                       */
/*==============================================================*/
create index Index_workerNum on t_user (
workerNum ASC
)
go

/*==============================================================*/
/* Index: Index_workerName                                      */
/*==============================================================*/
create index Index_salary on t_user (
salary DESC
)
go

1. 创建视图

说明：视图的作用包括能够简化数据的操作、提供数据库的安全性等等，所以因为需要适时查看上架商品数量、库存数量，以及时补足，同时，查看销售量靠前的商品，优化销售方案等原因，设计了以下三个视图。

/*==============================================================*/
/* View: View_goodsOnAmount 查询上架商品数量视图                */
/*==============================================================*/
create view lihuoyuan.View_goodsOnAmount as
select
goodsNum,
goodsName,
amount
from
t_goodsOn
with check option
go

/*==============================================================*/
/* View: View_goodsSale  查询商品销售数量视图                   */
/*==============================================================*/
create view View_goodsSale as
select
goodsNum,
goodsName,
amount
from
t_goodsSale
go

/*==============================================================*/
/* View: View_inventoryAmount  查询商品库存量视图               */
/*==============================================================*/
create view caigouyuan.View_inventoryAmount as
select
goodsNum,
goodsName,
amount
from
t_inventory
with check option
go