数据库课程设计-商品库存管理系统

1、杭州电子科技大学数据库课程设计课程设计手册姓名学号设计小组长课程设计名称商品库存管理系统指导教师设计时间 2012/9/5 至 2012/9/17杭州电子科技大学管理学院编制目录第一章 系统概述 111系统开发背景 112系统开发意义 1第二章 系统需求分析 221基本信息管理 222库存管理功能 223权限管理功能 324业务流程图 325数据流程图 426数据字典 6第三章 系统总体设计 93 1 系统总体设计目标 932 系统概念设计 10322 关系模式 10第四章逻辑结构设计 11第五章物理结构设计 13第六章数据实施和维护 13第七章数据库系统实现 227.1 系统实现工具简介 2

2、27.1.1 SQL Server 2000 简介 22第一章 系统概述63 系统开发背景处于社会经济技术快速发展的今天，各种各样的行业竞争日益激烈。行业不断寻求 自身的优势劣势，扬长避短，加强自己的竞争力，力争在新世纪经济利润的大蛋糕中分 得一杯羹。然而无论出于何种时期，超市永远是人们生活离不开的一部分，大街小巷随处可见 的大小型超市构建起了人们点点滴滴的生活。众所周知，若单靠工作人员手工或者借助 简单的管理信息系统进行管理，特别是超市的库存，不仅耗费大量的时间精力，而且在 运作中由于信息不对称或者操作的时间前后不吻合，往往错误百出，严重影响运营和工 作人员的工作情绪。例如在老式的仓库管理中

3、在盘点一遍遍地核对清点，一不留神清点 错误又要重新开始，对于出库入库也异常麻烦，经常需要闭库，停止仓库任何变动来进 行专门清点工作。由此可见，将库存管理信息化，提高仓库管理的效率，使各种货物信息明朗化，给 管理人员创造方便快捷的工作环境已经迫在眉睫。63 系统开发意义超市管理作为计算机应用的一个分支，有着手工管理无法比拟的优点：(1). 使用本系统，可以迅速提升超市的库存管理水平，为降低经营成本，大大提高 超市的运作效率。(2). 提高效益，增强超市扩张力， 提供有效的技术保障。因此，开发一套能够为超市工作人员提提供方便的库存管理系统，数据库的设计将 是非常必要的第二章 系统需求分析2 1 基

4、本信息管理2.1.1 系统用户资料管理用户资料即使用该系统的人员基本信息，包括了库长，库工，采购员，仓库清算员。 各种人员信息又包含其工号，密码，性别，年龄，姓名，职位。使用该数据库系统时提 供其工号和密码登录即可自由进行其相应权限内的操作。当然，也可以凭借自己的工号 密码查询自己的信息，而库长则因为有超级用户的权限，可以输入员工的编号查询任意 员工的个人基本信息和操作记录，也可对员工的信息进行增、删、改的操作。2.1.2 供应商资料管理供应商资料主要包括超市货物的各家供应商的基本信息，包括供应商号码，商家名 称，商家所提供商品名称及数量，商家地址与供货商信誉度。在入库时，需要提供供应商的信息

5、；用户也可通过输入供应商号码来查询相关供应 商的基本信息，和它近一年对仓库的供货记录。2.1.3 仓库信息管理仓库信息管理仓库信息仓库的编号、仓库名称、仓库地点、仓库容量现有库存及所 存储商品类别。2.1.4 商品信息管理商品信息包括了商品编号、商品类别、名称、生产日期、保质期、价格和余量等信 息。商品信息管理功能模块可以实现以下功能：添加商品类别、修改商品类别、删除商 品类别、 查看商品类别、 添加商品信息、 修改商品信息、 删除商品信息、 查询商品信息2 2 库存管理功能库存操作管理模块可以实现入库管理、出库管理和库存盘点等功能。2.2.1 入库管理入库管理：库存管理员根据库存情况将采购计

6、划反映给采购员，经批准后采购员将 从供应商处订货。当收到提货通知单后，采购员将入库商品清单交至库存管理员，确定 商品准确无误后生成入库记录。入库记录主要包括相关的产品信息、供应商信息、仓库信息、库存管理员即用户信息、涉及的金额和入库时间等。222出库管理出库管理：库长接到提货通知后，根据出库计划把已批准的提货商品清单交给库工， 库工根据商品清单完成商品出库，同时生成出库记录。出库记录主要包括相关的产品信 息、仓库信息、用户信息、涉及金额和出库时间等。库存提醒库存警示是指对库存中接近安全库存或超过一定数额的产品进行报警。在产品信息中，包括产品的合理数量范围和有效期限。当产品的库存数量小于安全库存

7、时，系统进 行缺货提醒；当产品的数量大于系统设定的上限时，系统进行超储提醒。产品出现缺货、 超储、接近或超过有效期限时系统需报警。库存盘点由于人为操作等原因，系统中的库存数据与实际数据之间可能会存在误差。为保证 库存数据的准确性，每隔一段时间就需要对库存进行盘点。通过对库存商品的盘点生成 库存台账。当计划台账与库存台账不匹配时，系统产生库存提示，仓库管理人员经根据 提示进行库存检查，并采取相应措施。2. 3权限管理功能普通用户，包括采购员，库工，供应商超级用户，库长2. 4业务流程图下面试超市库存管理系统的总体业务流程图，用一下三种符号表示：单位2. 5数据流程图数据流程图全面地描述信息系统逻

8、辑模型。它用下面几种符号综合地反映信息在系统中的流动、处理和存储情况数据流处理数据存储顶层数据流程图:第一层数据流程图:rt H 国:第二层数据流程图2. 6数据字典数据流编号：D1数据流名称：发货单简 述：供货单位开给仓储部门的发货凭证数据流来源：供应商 数据流去向：仓库存储收货部门 数据项组成：货物代号 +商品名称 +数量+金额+时间 数据流量： 1 张/ 次数据流编号： D2 数据流名称：退货单 简 述：仓储收获部门开给供应商的退货凭证 数据流来源：仓储收获部门 数据流去向：供应商数据项组成：货物代号 +商品名称 +数量+时间 数据流量： 1 张/ 次数据流编号： D3 数据流名称：入库

9、单 简 述：仓储收货部开给存储管理部的入库凭证 数据流来源：仓储收货部 数据流去向：存储部数据项组成：货物代号 +商品名称 +数量+时间 数据流量： 1 张/ 次数据流编号： D4数据流名称：出库单简 述：取货单位或个人开给仓库存储部的出库凭证数据流来源：取货单位或个人数据流去向：仓库存储部门数据项组成：货物代号 +商品名称 +数量+时间 数据流量： 1 张/ 次数据流编号： D5 数据流名称：计划单 简 述：取货单位或个人开给仓库部门预期内取货的凭证单据 数据流来源：取货单位或个人数据流去向：仓库存储部门 数据项组成：货物代号 +商品名称 +数量+时间 数据流量： 1 张/ 次处理逻辑编号：

10、 P2.1处理逻辑名称名称：验货处理简 述：仓库收货单位对收到的货物检验处理数据流来源：供货单位输入的数据流：发货单，订货单，质量标准处理：根据订货单和发货单进行比对，并对实际收到的货物进行比对，对收到的货物 质量检验处理频率：对每批供货商发来的货物检查一次处理逻辑编号： P2.2处理逻辑名称：库存检查简 述：仓库管理部门对仓库内存储的货物进行盘点统计 输入的数据流：计划单，当前库存剩余数据及盘点 处理：对库存货物盘点，核对剩余货物，比对计划单，检查是否缺货，如缺货或将要 不足，想采购发出通知输出的数据流：如果缺货，发出缺货通知，及对应商品种类信息以及数量大小，时间 期限。数据流量：每次计划单

11、核对一次处理逻辑编号： P2.3 处理逻辑名称：出入库处理 简 述：仓库管理部门在收到进货单和出货单后的对仓储货物的处理 输入的数据流：进货单，出货单，并按照相应单据进出货 处理：按照相应单据进行处理 输出的数据流：实际收到的或者发出的 货物代号 +商品名称 +数量+时间 处理频率： 1 批/ 次处理逻辑编号： P2.4 处理逻辑名称：库存提示 简 述：仓库管理部门在收到计划单后根据仓库内存储的数据和计划单上数据的比 较向采购部门发出提示输入的数据流：库存台帐，计划台帐 处理：对输入的库存台帐和计划台帐比对，如有不对，发出通知 数据项组成：不相符的条目信息，包括：货物代号 +商品名称 +数量

12、+时间 数据流量：每种商品每月处理一次处理逻辑编号： P2.5 处理逻辑名称：查询打印简 述：根据现有数据统计打印输出 输入的数据流：供应商信息，商品信息，合同台账，计划台账 处理：根据相关单位或个人的需求打印出相应的信息 输出的数据流：货物代号+商品名称+数量+时间+供应商信息 +批次+合同台账+计划台账 处理频率： 1 次/ 次第三章 系统总体设计3 1 系统总体设计目标商品库存管理系统是超市经营不可缺少的部分， 它对于超市的管理者和决策者来说 都至关重要，所以仓库货物管理系统应该能够为用户提供充足的信息和快捷的查询手 段。本库存管理系统主要针对传统的库存管理效率低，商品的进销存控制困难，

13、可以实 现全面管理仓库中存储的商品和货物，缩短了库存信息流转时间，使超市的物料管理层 次分明、井然有序、为采购和促销提供依据，从而提高超市的经营效率。3. 2. 2关系模式1、商品（商品编号，商品名称，保质期，价格）2、仓库（仓库编号，仓库名称，仓库地点）3、供应商（供应商编号，供应商名称，供应商地址，联系方式）4、供应（商品编号，供应商编号，生产日期，供应量）5、入库（商品编号，仓库编号，生产日期，入库量，入库时间）6、出库（商品编号，仓库编号，生产日期，出库量，出库时间）7、商品总库存（商品编号，仓库编号，库存上限，库存下限）&商品价格变动表（商品编号，生产日期，变动前价格，变动后

14、价格）第七张表，确定同一类商品在某一仓库的库存量上限以及下限，便于确定商品是否缺货 或库存过多。第八张表，在节假日，可能会进行促销，改动商品价格某大型超市的仓库，遍布全国各地。不同的商品有不同的编号，对于同一商品，因 为生产日期的不同，所处理的手段也不同。公司部门可根据此8张表，查询具体时间的进货（入库），销售（出库）情况，可查询快到期的商品，及时进行处理，可进行商品 库存的查询，及时进行进货或加快销售等处理。每个仓库的同一类商品的最大最低库存 量记录在商品总库存表中，库存表记录的是不同商品或同商品不同生产日期的库存量。 商品价格变动表是记录商品价格因促销或缺货等因素而造成的改变。假定，仓库中

15、商品 最大库存的80%为合适的库存量，低于库存下限的为缺货商品。第四章逻辑结构设计1.商品表（products）字段名称含义字段类型约束pno商品编号char(5)primary keypn ame商品名称varchar2(30)not n ullpvaliddate保质期（单位：月）nu mber(2)not n ull ,>0pprice商品价格nu mber(5)not n ull ,>02.仓库表（warehouses ）字段名称含义字段类型约束wno仓库编号char(5)Primary keywn ame仓库名称varchar2 ( 30)Not nullwplace仓库

16、地点varchar2 ( 100)Not null3.供应商表（providers）字段名称含义字段类型约束pdno供应商编号Char( 5)Primary keypdn ame供应商名称Varchar2 ( 30)Not nullpdplace供应商地址Varchar2(100)Not nullpdphonenum联系方式Varchar2(11)Not null4.供应表（supply）字段名称含义字段类型约束pdno供应商编号Char( 5)外键，引用providers 表的 pdno字段；联合主键pno商品编号Char(5)外键，引用products表的pno字段；联合主键product

17、 ion date生产日期Date联合主键supplydate供应日期DateNot nullsupplyam ount供应量Number(5)Not null >05.入库表(storgein)字段名称含义字段类型约束pno商品编号Char( 5)外键，引用products 表的pno字段；联合主键wno仓库编号Char(5)外键，引用 warehouses 表的 wno字段；联合主 键product ion date生产日期date联合主键storge inamount入库量Number(5)Not nullstorgei ntime入库时间dateNot null6.出库表(sto

18、rgeout)字段名称含义字段类型约束Pno商品编号Char( 5)外键，引用products表的pno字段；联 合主键wno仓库编号Char(5)外键，引用 warehouses 表的 wno字段；联合主 键product ion date生产日期date联合主键storgeoutam ount出库量Number(5)Not null >0storgeouttime出库时间dateNot null7.总库存表(totalstorge)字段名称含义字段类型约束Pno商品编号Char( 5)外键，引用products 表的pno字段；联合主键wno仓库编号Char(5)外键，引用 ware

19、houses 表的 wno字段；联合主 键storgeupper库存上限Number(5)Not null >0storgelower库存下限Number(5)Not null >08价格变动表（priceadjust）字段名称含义字段类型约束pno商品编号Char(5)联合主键，外键，引用products表的pno字段；联合主键product ion date生产日期date联合主键oldprice变动前价格Number(5)Not null >0n ewprice变动后价格Number(5)Not null >0第五章物理结构设计由于物理结构设计和实际紧密相关，因此

20、在没有结合实际情况的前提下就贸然的剔 除物理结构设计是不正确的，物理结构的设计应该和超市的营业规模紧密相关。对于较小的超市，以一家店为例，考虑到规模很小，数据的的存储容量并不会太大， 在现代硬件设备的性能相对于价格很不错的情况下，一台性能较好的个人PC就可以蛮族需要，硬盘上普通的硬盘容量也已经超过 300G而300G的硬盘空间对于这种情况下 的系统来说绝对是足够了的。因此，如果如果规模较小，一台组装的家用PC已经满足了我们的需求。内存上 3G已经够了，操作系统上考虑到数据库我们可以选择windowserver2000，cpu上普通的双核超线程cpu超过2.5G就已经能够满足我们的要求。如果规模

21、较大，可以采用集群的方式，本身Oracle数据库就可以同时处理很大批量的读写请求，数据库并不需要改变什么，硬件上只要集群建起来就可以了。如果规模 更大，可以考虑数据库集群，硬件上把 PC集群规模扩大，建立多个工作中心来并行处 理请求。具体问题应结合实际具体分析。第六章数据实施和维护数据库表1）.商品（商品编号，商品名称，保质期，价格）2）.仓库（仓库编号，仓库名称，仓库地点）3）.供应商（供应商编号，供应商名称，供应商地址，联系方式）4）.供应（商品编号，供应商编号，牛产日期，供应量）5）.入库（商品编号，仓库编号，生产日期，入库量，入库时间）6) .出库(商品编号，仓库编号，生产日期，出库量

22、，出库时间)7) .商品总库存(商品编号，仓库编号，库存上限，库存下限)8) .商品价格变动表(商品编号，生产日期，变动前价格，变动后价格)6. 3创建表-表一：商品表create table products(pno char(5) primary key,pn ame varchar2(30) not nu II,pvaliddate nu mber(2) not n ull check(pvaliddate>0),pprice nu mber(5) not n ull check(pprice>0);-表二：仓库表create table warehouses(wno cha

23、r(5) primary key,wn ame varchar2(30) not n ull,wplace varchar2(100) not null);-表三：供应商表create table providers(pdno char(5) primary key,pdn ame varchar2(30) not n ull,pdplace varchar2(100) not null,pdph onenum varchar2(11) not n ull);-表四：供应表create table supply(pdno char(5),pno char(5),product ion date

24、 date,supplydate date not n ull,supplyam ount nu mber(5) not n ull check(supplyam oun t>0);-主键约束primaryalter table supply add constraintsupply_pdno_pno_pdate_|key(pd no,pno ,producti on date);- 外键约束altertable supply add constraint supply_pdno_fk foreign key(pdno)references providers(pdno);alter t

25、able supply add constraint supply_pno_fk foreign key(pno) references products(pno);- 表五：入库表create table storgein(pno char(5),wno char(5),productiondate date not null,storgeinamount number(5) not null check(storgeinamount>0),storgeintime date not null);- 主键约束alter table storgein add constraint sto

26、rgein_pno_wno_pdate_pk primary key(pno,wno,productiondate);- 外键约束alter table storgein add constraint storgein_pno_fk foreign key(pno) references products(pno);alter table storgein add constraint storgein_wno_fk foreign key(wno) references warehouses(wno);- 表六：出库表create table storgeout(pno char(5),wn

27、o char(5),productiondate date not null,storgeoutamount number(5) check(storgeoutamount>0),storgeouttime date not null);- 主键约束alter table storgeout add constraint storgeout_pno_wno_pdate_pk primary key(pno,wno,productiondate);- 外键约束alter table storgeout add constraint storgeout_pno_fk foreign key(

28、pno) references products(pno);alter table storgeout add constraint storgeout_wno_fk foreign key(wno) references warehouses(wno);- 表七：总库存表create table totalstorge(pno char(5),wno char(5),total number(5),storgeupper number(5) not null check(storgeupper>0), storgelower number(5) not null check(storg

29、elower>0) );- 主键约束alter table totalstorge add constraint totalstorge_pno_wno_pk primary key(pno,wno);- 外键约束altertable totalstorgeadd constraint totalstorge_pno_fkkey(pno) references products(pno);alter table totalstorgeadd constraint totalstorge_wno_fkkey(wno) references warehouses(wno);- 表八：crea

30、te table priceadjust(pno char(5), productiondate date not null, oldprice number(5) not null check(oldprice>0), newprice number(5) not null check(newprice>0);- 主键约束alter table priceadjust add constraint priceadjust_pdate_pk primary key(pno,productiondate);- 外键约束alter table priceadjust add const

31、raint priceadjust_pno_fk key(pno) references products(pno);foreignforeignforeign6 3 插入测试数据- 插入测试数据-1. 商品insert into products values('p001','薯片',8,3.5);钙奶',6,2);清风抽纸 ',24,10);益达木糖醇 ',10,12);insert into products values('p002','AD insert into products values('

32、;p003',' insert into products values('p004','select * from products;-2. 仓库表insert into warehouses values('w001',' insert into warehouses values('w002',' insert into warehouses values('w003',' select * from warehouses;杭州仓库 ',' 上海仓库 '

33、,' 北京仓库 ','杭州');上海');北京');insert into providers values('s001',' insert into providers values('s002',' insert into providers values('s001','供应商 1',' 浙江杭州 ','0571'); 供应商 2',' 浙江金华 ','8');供应商 3','

34、; 江苏南京 ','0571');-4 供应表(供应商编号，商品编号 , 生产日期，供应量 ,供应日期) insert into supply values('s001','p001',to_date('2012-08-26','yyyy-mm-dd'),80,to_date('2012 -09-15','yyyy-mm-dd');-5. 入库(商品编号，仓库编号，生产日期，入库量，入库时间) insert into storgein values('p001'

35、;,'w001',to_date('2012-08-26','yyyy-mm-dd'),80,to_date('2012 -09-15','yyyy-mm-dd');-6. 出库(商品编号，仓库编号，生产日期，出库量，出库时间) insert into storgeout values('p001','w001',to_date('2012-09-01','yyyy-mm-dd'),20,to_date('2012 -09-15',&#

36、39;yyyy-mm-dd');-7. 总库存表(商品编号，仓库编号，库存上限，库存下限) insert into totalstorge values('p001','w001',1000,50);-8. 价格调整(商品编号，生产日期，变动前价格，变动后价格) insert into priceadjust values('p001',to_date('2012-09-01','yyyy-mm-dd'),3.5,3);6 3 查询操作- 简单查询：查询所有的供应商信息、产品信息、仓库信息 select *

37、 from supply;- 供应表select * from totalstorge;- 总库存表-2. 增加操作- 想商品表中添加一条产品记录insert into products values('p0008',' 麦兜小面包 ',12,3.5);-3. 供应商表-3. 修改操作- 修改商品表的商品编号为 update products set pname=' - 修改商品表的商品编号为p0007的名称旺仔牛奶升级版 ' where pno='p0007'p0007的价格update products set pprice=

38、pprice+1 where pno='p0007'-4.删除操作-删除商品表中商品编号为p0005的记录delete from products where pno='p0005'-删除商品表中商品编号为p0006的记录delete from products where pno='p0006'1、实现各种查询(简单查询、模糊查询、连接查询、嵌套查询等) - 模糊查询：- 查询供应商名称中带有薯片'的商品记录 select * from products where pname like '%薯片 %'- 查询供应商名称

39、中没有薯片'的商品记录select * from products where pname not like '%薯片 %'- 连接查询 :- 查询入库商品的详细信息select p.* from products p,storgein sinwhere p.pno = sin.pno;- 查询出库商品的详细信息select p.* from products p,storgeout soutwhere p.pno = sout.pno;-查询商品编号为p0001的仓库详细信息select ts.pno,w.* from warehouses w,totalstorge

40、 ts where w.wno=ts.wno and ts.pno='p0001'-查询商品编号为p0002的仓库详细信息select ts.pno,w.* from warehouses w,totalstorge ts where w.wno=ts.wno and ts.pno='p0002'-查询商品编号为p0003的仓库详细信息select ts.pno,w.* from warehouses w,totalstorge ts where w.wno=ts.wno and ts.pno='p0003'- 嵌套查询-查询供应商S0001,供

41、应商品的详细信息select * from productswhere pno in(Select pno from Supply where pdno='S0001');- 查询入库商品的详细信息Select p.* from productS p,Storgein Sinwhere p.pno = Sin.pno;- 查询商品编号为p0001的仓库详细信息 Select * from warehouSeSwhere wno =(select wno from totalstorge where pno='p0001');- 查询商品编号为 p0002 的仓库

42、详细信息select * from warehouseswhere wno =(select wno from totalstorge where pno='p0002');- 查询商品编号为 p0003 的仓库详细信息select * from warehouseswhere wno =(select wno from totalstorge where pno='p0003');- 查询商品编号为 p0004 的仓库详细信息select * from warehouseswhere wno =(select wno from totalstorge wher

43、e pno='p0004');- 查询商品编号为 p0005 的仓库详细信息select * from warehouseswhere wno =(select wno from totalstorge where pno='p0005');2、设计一个存储过程， 要求在存储过程中使用游标； 设计一个触发器， 实现表间的 级联删除或修改。-5. 存储过程-A -5.1创建一个存储过程，将所有的商品价格都增加一元create or replace procedure pro_adjustpriceasbeginupdate products set pprice=

44、pprice+1;commit;end;-5.2执行该存储过程begin pro_adjustprice;end;-5.3删除该存储过程drop procedure pro_adjustprice;-B -5.1创建一个存储过程，删除所以的商品信息create or replace procedure pro_delproductsasbegindelete from products;commit;end;-5.2执行该存储过程beginpro_delproducts;end;-5.3删除该存储过程drop procedure pro_delproducts;-C -5.1创建一个存储过程，

45、修改商品的保质期create or replace procedure pro_molvaliddate as beginupdate products set validdate=validdate+3;commit;end;-5.2执行该存储过程beginpro_molvaliddate;end;-5.3删除该存储过程drop procedure pro_molvaliddate;-D -5.1创建一个存储过程，插入多条商品信息create or replace procedure pro_insertproducts as beginfor pno in 1.10 loopinsert

46、into products values(pno,'test_procedure',0,0); commit;end loop;end;-5.2执行该存储过程beginpro_insertproducts;end;-5.3删除该存储过程drop procedure pro_insertproducts;-6. 触发器-A-6.1创建触发器 , 限制只能在工作日对该表进行插入操作create or replace trigger tri_products_insertbefore insert on productsbeginif to_char(sysdate,'DY&

47、#39;) in(' 星期六 ',' 星期日 ') thenraise_application_error（-20001,' 你只能在工作时间（周一至周 五）对商品表进行插入操作 '）;end if;end;-B-6.1创建触发器 , 限制产品表信息的删除create or replace trigger tri_products_del before delete on products for each row beginraise_application_error（-20001,' 产品表中的信息不能删除 '）; end;-D-6.1创建触发器 , 限制只能在工作时间对表进行插入操作create or replace trigger tri_providers_modify before insert on providers beginif to_char（sysdate,'DY'） in（'星期六 ',' 星期日 '） or（to_char（sysdate,'HH24:MI'） not between '08:00' and '18:00'） thenraise_applicat