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

╰半夏微凉° 2022-07-10 04:59 137阅读 0赞

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

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

### 产品简介 ###

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

目标客户：中小型超市

客户的业务需求：

1.  改变传统的人工管理，实现日常管理信息化；
2.  通过对库存和销售信息的快速查询和处理，提高商品的采购的速度和科学性；
3.  提升超市管理水平，降低经营成本，提高工作效率。

本系统的总体框架  
![这里写图片描述][SouthEast]

--------------------

### 数据模型 ###

BPM模型  
![这里写图片描述][SouthEast 1]  
CDM 模型  
![这里写图片描述][SouthEast 2]  
PDM模型  
![这里写图片描述][SouthEast 3]

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

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

*  存储器设计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

测试结果：  
未执行存储器之前的员工表：  
![这里写图片描述][SouthEast 4]

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

execute proc_raise

![这里写图片描述][SouthEast 5]

*  存储器设计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:  
![这里写图片描述][SouthEast 6]

执行存储器：

execute proc_amount0

![这里写图片描述][SouthEast 7]

*  用户自定义函数设计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

测试结果：  
![这里写图片描述][SouthEast 8]

*  用户自定义函数设计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:  
![这里写图片描述][SouthEast 9]

execute proc_tmp

执行之后：  
![这里写图片描述][SouthEast 10]

*  触发器设计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:  
![这里写图片描述][SouthEast 11]  
原来的销售表t\_goodsSale：  
![这里写图片描述][SouthEast 12]

/*在采购员进货，增加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库存表:  
![这里写图片描述][SouthEast 13]  
t\_goodsSale销售表:  
![这里写图片描述][SouthEast 14]

### 执行成功的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

[SouthEast]: /images/20220709/377b3aa08fe54e56accdb6e018cd8d6f.png
[SouthEast 1]: /images/20220709/54ea5f0fa5404d32a5346ffd701a2f60.png
[SouthEast 2]: /images/20220709/e256343c9dc54bf18bb9ba124e273a83.png
[SouthEast 3]: /images/20220709/6b312697f4f24221a3cffb43cee5c159.png
[SouthEast 4]: /images/20220709/9835bd4d74a84e54941430587994b4a6.png
[SouthEast 5]: /images/20220709/d93df664e2d84a1bb4e56a1082eddaec.png
[SouthEast 6]: /images/20220709/b92b7600b56e44c68b8926ea3e64aed5.png
[SouthEast 7]: /images/20220709/563bc82050554d81b8dec0dc0c9f452d.png
[SouthEast 8]: /images/20220709/3ad524523be24ae6af58aeb02eb06932.png
[SouthEast 9]: /images/20220709/ca2eec2473014b35b2af0a9cf8e79e18.png
[SouthEast 10]: /images/20220709/70949727b3ac4fed9e5d8eaf615807a7.png
[SouthEast 11]: /images/20220709/9cddd37283ac48cd815785a2e68001ff.png
[SouthEast 12]: /images/20220709/4824388e5f674f56b4b4b20e1a37db99.png
[SouthEast 13]: /images/20220709/2ae2881c273345be8d345dd8df406dff.png
[SouthEast 14]: /images/20220709/240d9db9a76140db81bddda8af7389d8.png