《数据库原理及应用》考查报告

爱编程的小芯

于 2020-12-08 18:15:56 发布

阅读量356

点赞数 1

分类专栏：课程设计数据库文章标签： mysql

本文链接：https://blog.csdn.net/HsingYang/article/details/110878137

版权

课程设计同时被 2 个专栏收录

3 篇文章 1 订阅

订阅专栏

数据库

1 篇文章 0 订阅

订阅专栏

本文详细阐述了电脑仓库管理系统的功能需求，包括入库与出库流程，E-R图设计，数据库表结构（如Supplier、Computer、Warehouse等），以及MySQL数据库的创建、表设计和存储过程编写。涵盖了从需求分析到数据库实现的全过程。

摘要由CSDN通过智能技术生成

文章首发网址：https://www.xxdiandeng.cn，欢迎关注点灯君的原创博客网站！

需求分析

现要求开发电脑仓库管理系统。电脑仓库管理系统主要针对电脑的库存信息管理，对于仓库日常发生的业务，分为两大类，即入库和出库。经过数据库的设计后，用户通过相应的模块，对仓库进行简单的基本操作，即可轻松管理仓库。
经过可行性分析和初步的需求调查，确定了系统的功能边界，该系统应能完成下面的功能：

信息登记：在数据库中添加相关电脑、供货商、仓库的信息；
入库：增加电脑库存并生成入库记录；
出库：减少电脑库存并生成出库记录。

功能设计

顶层设计

顶层数据流图

第0层设计

第0层数据流图

入库设计

输入：待入库的电脑名称（型号）、存储该类电脑仓库名称、入库电脑数量
输出：入库记录
操作逻辑：
1. 根据输入信息，检查电脑型号、仓库名称是否存在，若只要有一个不存在，拒绝入库请求；
2. 查询库存表中是否有记录，如果没有记录，那么认定为新库存信息，向库存中添加这一信息即可；如果有记录，那么将该记录的电脑数量数据项修改为入库后的数量即可。

入库处理

出库设计

输入：待出库的电脑名称（型号）、存储该类电脑仓库名称、出库电脑数量
输出：出库记录
操作逻辑：
1. 根据输入信息，检查电脑型号、仓库名称是否存在，若只要有一个不存在，拒绝出库请求；
2. 查询库存表中是否有记录，如果没有记录，那么表示该仓库中没有存储这类电脑，拒绝出库请求；如果有记录，检测出库电脑数量是否大于库存数量，如果大于，表示库存不足，拒绝出库请求；如果有记录并且库存充足，那么将该记录的电脑数量数据项修改为出库后的数量即可。

出库处理

关系模型

E-R图

关系模型设计

关系名	属性及码	其他约束条件
供应商	供应商编号，供应商名称，供应商地址，供应商电话	1、供应商名称不允许为空；2、供应商电话不允许为空
电脑	电脑编号，电脑名称，电脑品牌，电脑单价，供货商编号	1、电脑名称不允许为空；2、电脑单价不允许为空；3、供货商编号引用供货商中的供应商编号
仓库	仓库编号，仓库名称，仓库地址，仓库电话	1、仓库名称不允许为空；2、仓库电话不允许为空
库存	库存记录编号，电脑编号，仓库编号，电脑数量	1、电脑编号引用电脑关系中的电脑编号；2、仓库编号引用仓库关系中的仓库编号
入库记录	入库记录编号，请求入库电脑名称，仓库名称，入库数量，入库总金额，操作日期时间，是否为新型号电脑，成功入库否	1、操作日期时间不允许为空；2、入库否不允许为空
出库记录	出库记录编号，请求出库电脑名称，仓库名称，出库数量，操作日期时间，是否全部出库，成功出库否	1、操作日期时间不允许为空；2、成功出库否不允许为空

数据库表设计

Supplier（供应商表）

字段名	字段含义	字段类型	是否为空	列级约束
Supplier_ID	供应商编号	varchar(7)	not null	primary key
Supplier_Name	供应商名称	varchar(30)	not null	unique
Supplier_Address	供应商地址	varchar(50)
Supplier_Tel	供应商电话	Varchar(11)	not null

Computer（电脑表）

字段名	字段含义	字段类型	是否为空	列级约束
Computer_ID	电脑编号	varchar(6)	not null	primary key
Computer_Name	电脑名称	varchar(30)	not null	unique
Computer_Brand	电脑品牌	varchar(10)
Computer_Unit_Price	电脑单价	float	not null
Supplier_ID	供货商编号	varchar(7)	not null	foreign key references Supplier (Supplier_ID)

Warehouse（仓库表）

字段名	字段含义	字段类型	是否为空	列级约束
Warehouse_ID	仓库编号	varchar(6)	not null	primary key
Warehouse_Name	仓库名称	varchar(30)	not null	unique
Warehouse_Address	仓库地址	varchar(50)
Warehouse_Tel	仓库电话	varchar(11)	not null

Stock（库存表）

字段名	字段含义	字段类型	是否为空	列级约束
Stock_ID	库存记录编号	varchar(6)	not null
Computer_ID	电脑编号	varchar(6)	not null	foreign key references Computer(Computer_ID)
Warehouse_ID	仓库编号	varchar(6)	not null	foreign key references Warehouse(Warehouse_ID)
Number	电脑总数	int	not null

表级约束：primary key (Stock_ID, Computer_ID, Warehouse_ID)

InputRecords（入库记录表）

字段名	字段含义	字段类型	是否为空	列级约束
InputRecords_ID	入库记录编号	varchar(8)	not null	primary key
Computer_Name	请求入库电脑名称	varchar(30)	not null
Warehouse_Name	仓库名称	varchar(30)	not null
Input_Number	入库数量	int
Total_Money	入库总金额	float
Input_DateTime	操作日期时间	datetime	not null
isNewTypeComputer	是否为新型号电脑	bool
isSuccess	成功入库否	bool	not null

OutputRecords（出库记录表）

字段名	字段含义	字段类型	是否为空	列级约束
OutputRecords_ID	出库记录编号	varchar(8)	not null	primary key
Computer_Name	请求出库电脑名称	varchar(30)	not null
Warehouse_Name	仓库名称	varchar(30)	not null
Output_Number	出库数量	int
Output_DateTime	操作日期时间	datetime	not null
isAllOutput	是否全部出库完	bool
isSuccess	成功出库否	bool	not null

MySQL数据库代码实践

建库、建表

-- 数据库：WarehouseDB
create database WarehouseDB;
use WarehouseDB;

-- 表：供应商
create table Supplier
(
    Supplier_ID      varchar(7) primary key,      -- 供应商编号
    Supplier_Name    varchar(30) unique not null, -- 供应商名称
    Supplier_Address varchar(50),                 -- 供应商地址
    Supplier_Tel     varchar(11)        not null  -- 供应商电话
);

-- 表：电脑
create table Computer
(
    Computer_ID         varchar(6) primary key,      -- 电脑编号
    Computer_Name       varchar(30) unique not null, -- 电脑名称
    Computer_Brand      varchar(10),                 -- 电脑品牌
    Computer_Unit_Price float              not null, -- 电脑单价
    Supplier_ID         varchar(7)         not null, -- 供货商编号
    -- 外码：Computer.Supplier_ID 参考自 Supplier.Supplier_ID
    foreign key (Supplier_ID) references Supplier (Supplier_ID)
);

-- 表：仓库
create table Warehouse
(
    Warehouse_ID      varchar(6) primary key,      -- 仓库编号
    Warehouse_Name    varchar(30) unique not null, -- 仓库名称
    Warehouse_Address varchar(50),                 -- 仓库地址
    Warehouse_Tel     varchar(11)        not null  -- 仓库电话
);

-- 表：库存
create table Stock
(
    Stock_ID     varchar(6),   -- 库存记录编号
    Computer_ID  varchar(6),   -- 电脑编号
    Warehouse_ID varchar(6),   -- 仓库编号
    Number       int not null, -- 电脑总数
    -- 主码构成：Stock_ID, Computer_ID, Warehouse_ID
    primary key (Stock_ID, Computer_ID, Warehouse_ID),
    -- 外码：Stock.Computer_ID 参考自 Computer.Computer_ID
    foreign key (Computer_ID) references Computer (Computer_ID),
    -- 外码：Stock.Warehouse_ID 参考自 Warehouse.Warehouse_ID
    foreign key (Warehouse_ID) references Warehouse (Warehouse_ID)
);

-- 表：入库记录
create table InputRecords
(
    InputRecords_ID   varchar(8) primary key, -- 入库记录编号
    Computer_Name     varchar(30) not null,   -- 请求入库电脑名称
    Warehouse_Name    varchar(30) not null,   -- 仓库名称
    Input_Number      int,                    -- 入库数量
    Total_Money       float,                  -- 入库总金额
    Input_DateTime    datetime    not null,   -- 操作日期时间
    isNewTypeComputer bool,                   -- 是否为新型号电脑
    isSuccess         bool        not null    -- 成功入库否
);

-- 表：出库记录
create Table OutputRecords
(
    OutputRecords_ID varchar(8) primary key, -- 出库记录编号
    Computer_Name    varchar(30) not null,   -- 请求出库电脑名称
    Warehouse_Name   varchar(30) not null,   -- 仓库名称
    Output_Number    int,                    -- 出库数量
    Output_DateTime  datetime    not null,   -- 出库日期时间
    isAllOutput      bool,                   -- 是否全部出库完
    isSuccess        bool        not null    -- 成功出库否
);

编写存储过程

添加供货商

-- 添加供货商
create procedure AddSupplier(in Name varchar(30), in Address varchar(50), in Tel varchar(11))
begin
    declare ID int(5) zerofill;
    -- 生成ID号
    select max(cast(substr(Supplier_ID, 3) as signed integer))
    into ID
    from Supplier;
    if ID is null then
        set ID = 1;
    else
        set ID = ID + 1;
    end if;
    -- 插入到Supplier表中
    insert into Supplier
        value (concat('Su', ID), Name, Address, Tel);
end;

添加仓库

-- 添加仓库
create procedure AddWarehouse(in Name varchar(30), in Address varchar(50), in Tel varchar(11))
begin
    declare ID int(5) zerofill;
    -- 生成ID号
    select max(cast(substr(Warehouse_ID, 2) as signed integer))
    into ID
    from Warehouse;
    if ID is null then
        set ID = 1;
    else
        set ID = ID + 1;
    end if;
    -- 插入到Supplier表中
    insert into Warehouse
        value (concat('W', ID), Name, Address, Tel);
end;

添加电脑

-- 添加电脑
create procedure AddComputer(in Name varchar(30), in Brand varchar(10), in Price float, in SupplierName varchar(30))
AddProcedure:
begin
    -- 供货商ID
    declare SupplierID varchar(7);
    -- 要生成的电脑ID
    declare ID int(5) zerofill;

    -- 查询对应供货商编号
    select Supplier_ID
    into SupplierID
    from Supplier
    where Supplier_Name = SupplierName;

    -- 检查供货商是否存在
    if SupplierID is null then
        rollback; -- 回滚事务
        leave AddProcedure;
    end if;

    -- 生成电脑ID
    select max(cast(substr(Computer_ID, 2) as signed integer))
    into ID
    from Computer;
    if ID is null then
        set ID = 1;
    else
        set ID = ID + 1;
    end if;

    -- 插入到Computer表中
    insert into Computer
        value (concat('C', ID), Name, Brand, Price, SupplierID);
end AddProcedure;

入库

-- 入库
create procedure Push(in ComputerName varchar(30), in WarehouseName varchar(30), in PushNumber int)
begin
    -- 库存中电脑编号
    declare ComputerID varchar(6);
    -- 库存中仓库编号
    declare WarehouseID varchar(6);
    -- 要生成的记录ID
    declare RecordsID int(7) zerofill;
    -- 当前库存数量
    declare CurrentNumber int;
    -- 电脑单价
    declare UnitPrice float;
    -- 入库总金额
    declare TotalMoney float;

    PUSH:
    begin
        -- 检查参数
        CheckOut:
        begin
            -- 查询对应电脑编号
            select Computer_ID
            into ComputerID
            from Computer
            where Computer_Name = ComputerName
            limit 1;

            -- 查询对应仓库编号
            select Warehouse_ID
            into WarehouseID
            from Warehouse
            where Warehouse_Name = WarehouseName
            limit 1;

            -- 检查电脑ID是否已存在
            if ComputerID is null then
                set @isSuccess = false; -- 标识请求未成功
                leave PUSH;
            end if;

            -- 检查仓库id是否已存在
            if WarehouseID is null then
                set @isSuccess = false; -- 标识请求未成功
                leave PUSH;
            end if;

            -- 查询当前库存数量
            select Number
            INTO CurrentNumber
            from Stock
            where Stock.Computer_ID = ComputerID
              and Stock.Warehouse_ID = WarehouseID;

            -- 查询电脑单价
            select Computer_Unit_Price
            into UnitPrice
            from Computer
            where Computer_Name = ComputerName;

            -- 计算入库总金额
            set TotalMoney = UnitPrice * PushNumber;
        end CheckOut;

        -- 入库
        PushProcedure:
        begin
            set @isSuccess = true; -- 标记请求成功
            if CurrentNumber is null then
                -- 新电脑入库
                begin
                    -- 要生成的库存号
                    declare StockID int(5) zerofill;
                    -- 新电脑入库标记
                    set @isNew = true;
                    -- 生成库存号
                    select max(cast(substr(Stock_ID, 2) as signed integer))
                    into StockID
                    from Stock;
                    if StockID is null then
                        set StockID = 1;
                    else
                        set StockID = StockID + 1;
                    end if;
                    -- 库存表更新
                    insert into Stock
                        value (concat('S', StockID), ComputerID, WarehouseID, PushNumber);
                end;
            else
                begin
                    -- 查询该型号电脑的库存ID
                    select Stock_ID
                    into @CurrentID
                    from Stock
                    where Stock.Computer_ID = ComputerID
                      and Stock.Warehouse_ID = WarehouseID;
                    -- 库存表更新
                    update Stock
                    set Number = Number + PushNumber
                    where Stock_ID = @CurrentID;
                    -- 标记非新电脑入库
                    set @isNew = false;
                end;
            end if;
        end PushProcedure;
    end PUSH;

    ReturnInfo:
    begin
        -- 生成入库记录ID
        select max(cast(substr(InputRecords_ID, 2) as signed integer))
        into RecordsID
        from InputRecords;
        if RecordsID is null then
            set RecordsID = 1;
        else
            set RecordsID = RecordsID + 1;
        end if;

        -- 根据@isSuccess生成相应入库记录
        if @isSuccess = true then
            -- 插入到记录表
            insert into InputRecords
            values (concat('I', RecordsID), ComputerName,
                    WarehouseName, PushNumber,
                    TotalMoney, sysdate(), @isNew, true);

            -- 展示本次请求信息
            select concat('I', RecordsID)      记录编号,
                   ComputerName                请求入库电脑,
                   WarehouseName               仓库,
                   PushNumber                  入库数量,
                   TotalMoney                  总金额,
                   sysdate()                   操作日期时间,
                   if(@isNew = true, '是', '否') 是否为新型号电脑,
                   '是'                         是否成功入库;
        else
            -- 插入到记录表
            insert into InputRecords
            values (concat('I', RecordsID), ComputerName,
                    WarehouseName, PushNumber, null, sysdate(), null, false);

            -- 展示本次请求信息
            select concat('I', RecordsID) 记录编号,
                   ComputerName           请求入库电脑,
                   WarehouseName          仓库,
                   PushNumber             入库数量,
                   sysdate()              操作日期时间,
                   '否'                    是否成功入库;
        end if;
    end ReturnInfo;

    -- 提交事务
    commit;
end;

出库

-- 出库
create procedure Pop(in ComputerName varchar(30), in WarehouseName varchar(30), in PopNumber int)
begin
    -- 未出库前库存数量
    declare NumberInStock int;
    -- 库存中电脑编号
    declare ComputerID varchar(6);
    -- 库存中仓库编号
    declare WarehouseID varchar(6);
    -- 要生成的记录ID
    declare RecordsID int(7) zerofill;

    POP:
    begin
        -- 查询对应电脑编号
        select Computer_ID
        into ComputerID
        from Computer
        where Computer_Name = ComputerName
        limit 1;

        -- 查询对应仓库编号
        select Warehouse_ID
        into WarehouseID
        from Warehouse
        where Warehouse_Name = WarehouseName
        limit 1;

        -- 查询库存中的电脑数量
        select Number
        into NumberInStock
        from Stock
        where Computer_ID = ComputerID
          and Warehouse_ID = WarehouseID
        limit 1;

        -- 库存中没有这种型号的电脑
        if (NumberInStock is null) then
            set @isSuccess = false; -- 标记请求失败
            leave POP;
        end if;
        -- 库存中电脑数量小于待出库电脑数量
        if (NumberInStock < PopNumber) then
            set @isSuccess = false; -- 标记请求失败
            leave POP;
        end if;
        -- 库存中电脑数量等于待出库电脑数量
        if (NumberInStock = PopNumber) then
            set @isAllOutput = true; -- 全部出库完
        end if;
        -- 库存中电脑数量大于待出库电脑数量
        if (NumberInStock > PopNumber) then
            set @isAllOutput = false;
        end if;

        -- 修改Stock表
        update Stock
        set Number = Number - PopNumber
        where Computer_ID = ComputerID
          and Warehouse_ID = WarehouseID;

        set @isSuccess = true; -- 标记请求成功
    end POP;

    -- 生成出库记录ID
    select max(cast(substr(OutputRecords_ID, 2) as signed integer))
    into RecordsID
    from OutputRecords;
    if RecordsID is null then
        set RecordsID = 1;
    else
        set RecordsID = RecordsID + 1;
    end if;

    -- 根据@isSuccess生成相应入库记录
    if @isSuccess = true then
        -- 生成出库记录
        insert into OutputRecords
        values (concat('O', RecordsID), ComputerName,
                WarehouseName, PopNumber, sysdate(), @isAllOutput, true);

        -- 展示本次请求信息
        select concat('O', RecordsID)            记录编号,
               ComputerName                      请求出库电脑,
               WarehouseName                     仓库,
               PopNumber                         出库数量,
               sysdate()                         操作日期时间,
               if(@isAllOutput = true, '是', '否') 是否全部出库完,
               '是'                               是否成功出库;
    else
        -- 生成出库记录
        insert into OutputRecords
        values (concat('O', RecordsID), ComputerName,
                WarehouseName, PopNumber, sysdate(), null, false);

        -- 展示本次请求信息
        select concat('O', RecordsID) 记录编号,
               ComputerName           请求出库电脑,
               WarehouseName          仓库,
               PopNumber              出库数量,
               sysdate()              操作日期时间,
               '否'                    是否成功出库;
    end if;

    -- 提交事务
    commit;
end;

查询仓库

-- 查询仓库
create procedure ShowStock()
begin
    select Stock_ID            编号,
           Computer_Brand      品牌,
           Computer_Name       型号,
           Computer_Unit_Price 单价,
           Number              数量,
           Warehouse_Name      仓库地址,
           Supplier_Name       供货商
    from Stock,
         Computer,
         Warehouse,
         Supplier
    where Stock.Computer_ID = Computer.Computer_ID
      and Stock.Warehouse_ID = Warehouse.Warehouse_ID
      and Computer.Supplier_ID = Supplier.Supplier_ID;
end;

SQL语句执行

登记相关信息

-- 添加供货商
call AddSupplier('供货商一', '供货商一地址', '02710000001');
call AddSupplier('供货商二', '供货商二地址', '02710000002');
call AddSupplier('供货商三', '供货商三地址', '02710000003');
-- 添加仓库
call AddWarehouse('仓库一', '仓库一地址', '02720000001');
call AddWarehouse('仓库二', '仓库二地址', '02720000002');
-- 添加电脑
call AddComputer('Lenovo-Air14', 'Lenovo', 5999, '供货商一');
call AddComputer('Lenovo-R7000', 'Lenovo', 6057, '供货商一');
call AddComputer('Lenovo-Yoga14', 'Lenovo', 6299, '供货商一');
call AddComputer('Lenovo-Pro14', 'Lenovo', 6299, '供货商二');
call AddComputer('MiBook-Pro-15.6', 'XiaoMi', 6999, '供货商二');
call AddComputer('Dell-G3', 'Dell', 6999, '供货商二');
call AddComputer('Dell-XPS13', 'Dell', 8888, '供货商二');
call AddComputer('MacBook-Pro-13', 'Apple', 9199, '供货商三');
call AddComputer('MacBook-Pro-16', 'Apple', 17399, '供货商三');
call AddComputer('Surface-Pro-7', 'Microsoft', 5788, '供货商三');

入库

call Push('MacBook-Pro-16', '仓库一', 10);
call Push('MacBook-Pro-16', '仓库一', 1);
call Push('Dell-G3', '仓库一', 100);
call Push('Lenovo-Pro14', '仓库二', 45);
call Push('Lenovo', '仓库二', 20); -- 这个是拒绝入库的示例

出库

call Pop('Lenovo-Pro14', '仓库二', 30);
call Pop('MacBook-Pro-16', '仓库一', 1);
call Pop('Lenovo-Pro14', '仓库二', 15);
call Pop('Lenovo-Pro14', '仓库二', 1000); -- 这个是拒绝出库的示例

查询结果展示

查询供货商表

select Supplier_ID      编号,
       Supplier_Name    名称,
       Supplier_Address 地址,
       Supplier_Tel     电话
from Supplier;

结果展示：
查询供货商表

查询仓库表

select Warehouse_ID      编号,
       Warehouse_Name    名称,
       Warehouse_Address 地址,
       Warehouse_Tel     电话
from Warehouse;

结果展示：
查询仓库表

查询电脑表

select Computer_ID         编号,
       Computer_Name       型号,
       Computer_Brand      品牌,
       Computer_Unit_Price 价格,
       Supplier_Name       供货商
from Computer,
     Supplier
where Supplier.Supplier_ID = Computer.Supplier_ID
order by Computer_ID ASC;

结果展示：
查询电脑表

查询库存

call ShowStock;

结果展示：
查询库存

查询入库记录

select InputRecords_ID             序号,
       Computer_Name               请求入库电脑,
       Warehouse_Name              仓库,
       Input_Number                出库数量,
       Input_DateTime              操作时间,
       case isNewTypeComputer
           when 1 then '是'
           when 0 then '否'
           when null then null
           end                     是否为新型号电脑,
       if(isSuccess = 1, '是', '否') 成功入库否
from InputRecords;

结果展示：
查询入库记录

查询出库记录

select OutputRecords_ID              序号,
       Computer_Name                 请求出库电脑,
       Warehouse_Name                仓库,
       Output_Number                 出库数量,
       Output_DateTime               操作时间,
       case isAllOutput
           when 1 then '是'
           when 0 then '否'
           when null then null
           end                       是否全部出库完,
       if(isSuccess = 1, '是', '否') 成功出库否
from OutputRecords;

结果展示：
查询出库记录

总结

通过本次考查报告，从需求分析、概念结构设计、逻辑结构设计到数据库表实施，掌握了设计数据库的有关步骤。通过前期准备，掌握了数据库的理论、E-R图的构建。
本次使用了MySQL数据库将理论转换为实践，初步掌握了MySQL建库、建表的语句。并编写了存储过程语句，使得数据库操作简便化。在编写SQL语句时，查阅了许多资料，增强了动手能力。
本次数据的设计仍有不足，如没有考虑到数据库的多用户操作，应当对每个仓库设立若干管理员，只进行本仓库的管理，细分数据库操作，也符合实际情况，这是数据设计的改进方向。