数据库课程设计库存物资管理系统后台数据库

1、大型数据库课程设计设计报告题 目：库存物资管理系统后台数据库学 号： 学生姓名: 指导教师： 提交时间： 2013-11-22 目录库存物资管理系统后台数据库1第1章 进行需求分析，编写数据字典11.1系统功能要求设计：11.2模块设计11.3数据字典2第2章 面向对象分析和设计4第3章 逻辑结构设计53.1类和对象向关系模式转换6第4章 数据库物理结构设计74.1存取方法设计74.2存储结构设计74.3物理设计7第5章 数据完整性设计115.1主键及索引11表名115.2完整性约束的说明115.3 check约束125.4 default默认值125.5 触发器设计12第6章 数据库视图的设

2、计16第7章 存储过程设计19总结28参考文献：28 库存物资管理系统后台数据库 第1章 进行需求分析，编写数据字典1.1系统功能要求设计：(1)数据表以及数据量 1）有一个存放商品的仓库，每天都有商品出库和入库。 2）商品有1000多种，每种商品都有名称、生产厂家、型号、规格等。 3）出入库时必须填写出入库单据，单据包括商品名称、生产厂家、型号、规格、数量、日期、时间、入库单位（或出库单位）名称、送货（或提货）人姓名。(2)设计要求： 1) 进行需求分析，编写数据字典。 2) 设计e-r图。 3) 设计出入库单据的录入。 4) 实现按商品名称、出入库日期的查询。 5) 实现分别按日、月和年对

3、出入库商品数量的统计。(3)主要功能： 1)商品管理：增加商品、修改商品、删除商品、浏览商品 2)库存管理： 通过触发器实现商品的入库，同时完成对库存表的更新 通过触发器实现商品的出库，同时完成对库存表的更新 实现按商品名称查询库存数量、入库数量和出库数量 实现按入库日期查询入库数量、出库数量 实现分别按日、月和年对入库商品数量的统计 实现分别按日、月和年对入库商品数量的统计1.2模块设计库存物资管理大体可以分为3大块，如下图所示：首先是商品入库模块，该模块主要是描述把采购回来的商品，分类别的放置到指定的仓库中去，然后是商品出库模块，该模块主要描述从指定的仓库中拿出商品，最后是商品库存模块，这

4、个模块主要是记录商品的库存数量。 库存物资管理商品入库模块商品库存模块商品出库模块1.3数据字典表商品表仓库表库存表入库表出库表商品表字段名数据类型是否可以为空is pkis fk商品编号char(5)not nullyesno商品名称varchar(50)not nullnono生产厂家varchar(50)not nullnono型号varchar(50)not nullnono规格varchar(20)not nullnono仓库表字段名数据类型是否可以为空is pkis fk仓库号char(10)not nullyesno仓库名称varchar(50)not nullnono库存表字段

5、名数据类型是否可以为空is pkis fk商品编号char(5)not nullyesyes仓库号char(10)not nullyesyes库存数量intnot nullnono入库表字段名数据类型是否可以为空is pkis fk入库号char(10)not nullyesno商品编号char(5)not nullnoyes仓库号char(10)not nullnoyes入库数量intnot nullnono入库日期smalldatetimenot nullnono入库单位名称varchar(50)nullnono送货人姓名varchar(50)not nullnono性别char(2)nu

6、llnono出库表字段名数据类型是否可以为空is pkis fk出库号char(10)not nullyesno商品编号char(5)not nullnoyes仓库号char(10)not nullnoyes出库数量intnot nullnono出库日期smalldatetimenot nullnono出库单位名称varchar(50)nullnono提货人姓名varchar(50)not nullnono性别char(2) nullnono 第2章 面向对象分析和设计 类和对象设计如下：商品商品编号：char商品名称：varchar生产厂家: varchar规格:varchar型号：varc

7、har 添加() 删除() 查询() 修改() 仓库仓库号:char仓库名称：varchar 添加() 修改() 删除() 查询()库存商品编号：商品仓库号：仓库库存数量：int查询()修改()添加()删除() 出库出库号：int仓库号：仓库商品编号: 商品商品名称：商品出库数量：int出库日期：smalldatetime出库单位名称：单位送货人姓名：人性别：char 添加() 修改() 删除() 查询()入库入库号：int仓库号：仓库商品编号: 商品商品名称：商品入库数量：int入库日期：smalldatetime入库单位名称：单位送货人姓名：人性别：char 添加() 删除() 查询()

8、修改() 第3章 逻辑结构设计生产厂家商品名称商品编号分e-r图： 商品 规格 型号 仓库仓库名称仓库号库存关系：库存 商品 仓库 n m入库入库关系： 商品 仓库 n m出库出库关系： 商品 仓库 n m 商品名称商品编号总e-r图：生产厂家 规格 商品 型号入库号出库号库存数量 n n n出库数量入库入库入库数量出库出库日期入库日期提货人姓名送货人姓名 m m m 性别出库单位名称入库单位名称 仓库 性别仓库名称仓库号3.1类和对象向关系模式转换关系模式如下：商品（商品编号，商品名称，生产厂家，型号，规格） 商品编号为主键仓库（仓库号，仓库名称） 仓库号为主键库存（商品编号，仓库号，库存数

9、量） （商品编号，仓库号）为主键，同时也分别为外键入库（入库号，商品编号，仓库号，入库数量，入库日期，入库时间，入库单位名称，送货人姓名，性别） （入库号）为主键，（商品编号，仓库号）为外键。出库（出库号，商品编号，仓库号，出库数量，出库日期，出库时间，出库单位名称，提货人姓名，性别） （出库号）为主键，（商品编号，仓库号）为外键。 第4章 数据库物理结构设计4.1存取方法设计对于库存物资管理系统来说，为了提高某些属性(如：商品编号，仓库号、入库号，入库日期，入库数量等)的查询速度，可以选择聚簇存取的方法，即把这些属性上具有相同值的元组集中放在连续的物理块上。这样在查询某种商品就会大大提高查询

10、速度。因此，该系统中选择聚簇存取方法。4.2存储结构设计库存物资管理系统是一个大型复杂的计算机网络信息系统，采用基于浏览器/服务器（b/s），客户端/服务器（c/s）混合的应用体系结构来建设库存物资管理系统。数据库管理系统采用microsoft 公司推出的sql server 2005 或以上版本，并用sql进行数据库的建立和数据库中数据的维护和查询。4.3物理设计实现该设计的环境为windows xp professional + ms sql server 2005 或以上版本。1、创建material数据库create database material on primary (name

11、=material,filename=c:prografilesmicrosoftsqlservermssql.2mssqldatamaterial.mdf,size=3mb, maxsize=unlimited,filegrowth=1mb)log on (name=material_log,filename=c:programfilesmicrosoftsqlservermssql.2mssqldatamaterial_log.ldf,size=1mb, filegrowth=10%)2、创建商品表create table 商品表(商品编号char(5) not null primary

12、key,商品名称varchar(50) not null,生产厂家varchar(50) not null,型号varchar(20) not null,规格varchar(20) not null)3、创建仓库表create table 仓库表(仓库号char(10) not null primary key,仓库名称varchar(50) not null,)4、创建库存表create table 库存表(商品编号char(5) not null ,仓库号 char(10) not null,库存数量int not null,primary key(商品编号,仓库号) ,foreign k

13、ey (商品编号) references 商品表(商品编号),foreign key (仓库号) references 仓库表(仓库号)5、创建入库表create table 入库表(入库号char(10) not null primary key,商品编号char(5) not null,仓库号char(10) not null,foreign key (商品编号) references 商品表(商品编号),foreign key (仓库号) references 仓库表(仓库号), 入库数量int not null,入库日期smalldatetime not null,入库单位名称varc

14、har(50) null,送货人姓名varchar(10) not null,性别char(2) default(男)check (性别in (男女)6、创建出库表create table 出库表(出库号char(10) not null primary key,商品编号char(5) not null,仓库号char(10) not null,foreign key (商品编号) references 商品表(商品编号),foreign key (仓库号) references 仓库表(仓库号),出库数量int not null,出库日期smalldatetime not null,出库单位

15、名称varchar(50) null,提货人姓名varchar(10) not null,性别char(2) default(男)check (性别in (男女) 第5章 数据完整性设计 5.1主键及索引 表名主键建立索引 商品表(商品编号)create clustered index 商品表on 商品表(商品编号) 仓库表(仓库号)create clustered index 仓库表on仓库表(仓库号) 入库表(入库号)create clustered index 入库表on 入库表(入库号) 出库表(出库号)create clustered index 出库表on 出库表(出库号)5.2完

16、整性约束的说明1、商品表的商品编号1000111000之间（便于查询的使用），商品名称不能取空；该仓库的商品型号统一为da0001da1000格式。1）use material/*定义商品表唯一性的约束*/goalter table 商品表add unique (商品名称,生产厂家,型号,规格)2）use material/*定义型号的完整性约束条件:商品型号统一为da0001-da1000格式*/goalter table 商品表add constraint cs1 check(型号likeda0-10-90-90-9)5.3 check约束在商品表中将型号进行check约束：check(型

17、号likeda0-10-90-90-9)在入库表和出库表中将性别进行check约束：check (性别in (男女)5.4 default默认值在入库表和出库表中性别默认值设为：男。default(男)5.5 触发器设计1、 通过触发器实现商品的入库，同时完成对库存表的更新（1） 入库use materialgoif exists (select* from sysobjects where name=rukuinsert)drop procedure rukuinsertgo create procedure rukuinsert rukuhao char(10),shangpinbianh

18、ao char(5),cangkuhao char(10),rukushuliang int,rukuriqi smalldatetime,rukudanwei varchar(50),songhuorenname varchar(50) ,sex char(2)asif(exists(select * from 入库表where 入库号=rukuhao)print(该商品已经存在，不能再次插入)elsebegininsert into 入库表values( rukuhao ,shangpinbianhao,cangkuhao,rukushuliang,rukuriqi ,rukudanwei

19、,songhuorenname ,sex )print(插入成功)end（2） 更新use materialgocreate trigger rkb_into on 入库表after updateas begin declare a char(5),b char(10) ,d intselect a=商品编号from insertedselect b=仓库号from insertedselect d=入库数量from inserted update 库存表set 库存数量=库存数量+dwhere 商品编号= a and 仓库号=bend2、 通过触发器实现商品的出库，同时完成对库存表的更新（1

20、） 出库use materialgoif exists (select* from sysobjects where name=chukuinsert)drop procedure chukuinsertgo create procedure chukuinsert chukuhao char(10),shangpinbianhao char(5),cangkuhao char(10),chukushuliang int,chukuriqi smalldatetime,chukudanwei varchar(50),tihuorenname varchar(50) ,sex char(2)as

21、if(exists(select * from 出库表where 出库号=chukuhao)print(该商品已经存在，不能再次插入)elsebegininsert into 出库表values( chukuhao ,shangpinbianhao,cangkuhao,chukushuliang,chukuriqi ,chukudanwei,tihuorenname ,sex )print(插入成功)end（2）更新use materialgocreate trigger ckb_into on 出库表after updateas begin declare aa char(10),bb ch

22、ar(5), dd intselect aa=仓库号from deletedselect bb=商品编号from deletedselect dd=出库数量from inserted update 库存表set 库存数量=库存数量-ddwhere 商品编号=bb and 仓库号=aaend 第6章 数据库视图的设计1、建立商品表视图use materialgocreate view 商品表视图as select *from 商品表2、创建商品库存视图来浏览库存中相同编号商品的信息，其中包括：商品编号、商品名称和库存数量。use materialgocreate view 商品库存视图(商品编号

23、,商品名称,库存数量)as select 库存表.商品编号,商品表.商品名称,sum(库存表.库存数量)from 商品表,库存表where 商品表.商品编号=库存表.商品编号group by 库存表.商品编号,商品表.商品名称3、创建商品入库视图use materialgocreate view 商品入库视图asselect 商品表.商品编号,商品名称,生产厂家,型号,规格,入库号,仓库号,入库数量,入库日期from 商品表, 入库表where 商品表.商品编号=入库表.商品编号4、创建商品出库视图use materialgocreate view 商品出库视图asselect 商品表.商品编

24、号,商品名称,生产厂家,型号,规格,出库号,仓库号,出库数量,出库日期from 商品表, 出库表where 商品表.商品编号=出库表.商品编号5、创建商品入库仓库视图use materialgocreate view 商品入库仓库视图asselect 商品表.商品编号,商品名称,入库表.仓库号,仓库名称from 商品表, 入库表,仓库表where 商品表.商品编号=入库表.商品编号and 入库表.仓库号=仓库表.仓库号6、创建商品出库仓库视图use materialgocreate view 商品出库仓库视图asselect 商品表.商品编号,商品名称,出库表.仓库号,仓库名称from 商品表

25、, 出库表,仓库表where 商品表.商品编号=出库表.商品编号and 出库表.仓库号=仓库表.仓库号 第7章 存储过程设计1、 实现商品表的插入use materialgoif exists (select *from sysobjects where name=shangpininsert )drop procedure shangpininsert gocreate procedure shangpininsert bianhao char(5),name varchar(50),changjia varchar(50),xinghao varchar(50),guige varchar

26、(20)asif(exists(select * from 商品表where 商品编号=bianhao)print(该商品已经存在，不能再次插入)elsebegininsert into 商品表values( bianhao,name,changjia,xinghao,guige)print(插入成功)endexec shangpininsert 11111,毛巾,纺织有限公司,da0111,30*70cm2、 实现商品表的删除use materialgoif exists (select *from sysobjects where name=shangpindelete)drop proc

27、edure shangpindeletegocreate procedure shangpindelete bianhao char(5)asif(exists(select 商品编号from 商品表where 商品编号=bianhao)begindelete from 商品表where 商品编号=bianhaoprint(删除成功，该商品已被删除)endelse print(该商品不存在,不能删除)exec shangpindelete 122003、按商品名称查询库存数量use materialgoif exists (select *from sysobjects where name=

28、shangpin_info1)drop procedure shangpin_info1gocreate procedure shangpin_info1 shangpinname varchar(50)asselect a.商品编号,商品名称,b.库存数量from 商品表a join 库存表bon a.商品编号=b.商品编号where 商品名称=shangpinname exec shangpin_info1 lg冰箱4、按商品名称查询入库数量use materialgoif exists (select *from sysobjects where name=ruku_info10)dro

29、p procedure ruku_info10gocreate procedure ruku_info10 shangpinname varchar(50)asselect 商品表.商品编号,商品名称,入库数量from 商品表join 入库表on 商品表.商品编号=入库表.商品编号where 商品名称=shangpinnameexec ruku_info10 acer宏基笔记本电脑5、按商品名称查询出库数量use materialgoif exists (select *from sysobjects where name=chuku_info11)drop procedure chuku_i

30、nfo11gocreate procedure chuku_info11 shangpinname varchar(50)asselect 商品表.商品编号,商品名称,出库数量from 商品表join 出库表on 商品表.商品编号=出库表.商品编号where 商品名称=shangpinnameexec chuku_info11 msi微星电脑主板6、按入库日期查询入库数量use materialgoif exists (select *from sysobjects where name=ruku_info2)drop procedure ruku_info2gocreate procedur

31、e ruku_info2 rukudate smalldatetimeasselect 商品表.商品编号,商品名称,入库日期,入库数量from 商品表join 入库表on 商品表.商品编号=入库表.商品编号where 入库日期=rukudateexec ruku_info2 2012-3-5 0:007、按出库日期查询出库数量use materialgoif exists (select *from sysobjects where name=chuku_info3)drop procedure chuku_info3gocreate procedure chuku_info3 chukuda

32、te smalldatetimeasselect 商品表.商品编号,商品名称,出库日期,出库数量from 商品表join 出库表on 商品表.商品编号=出库表.商品编号where 出库日期=chukudateexec chuku_info3 2010/2/16 0:00:008、按年对入库商品数量的统计use material/*按年对入库商品数量的统计*/goif exists (select *from sysobjects where name=ruku_info4)drop procedure ruku_info4gocreate procedure ruku_info4 start_

33、date smalldatetime,end_date smalldatetimeasselect 商品名称,sum(入库数量) as 总入库数量from 商品表join 入库表on 商品表.商品编号=入库表.商品编号where 入库日期between start_date and end_dategroup by 商品名称exec ruku_info4 2010-1-1,2010-12-319、按年对出库商品数量的统计use material/*按年对出库商品数量的统计*/goif exists (select *from sysobjects where name=chuku_info5)

34、drop procedure chuku_info5gocreate procedure chuku_info5 start_date smalldatetime,end_date smalldatetimeasselect 商品名称,sum(出库数量) as 出总库数量from 商品表join 出库表on 商品表.商品编号=出库表.商品编号where 出库日期between start_date and end_dategroup by 商品名称exec chuku_info5 2012-1-1,2012-12-3110、按月对入库商品数量的统计use material/*按月对入库商品数量

35、的统计*/goif exists (select *from sysobjects where name=ruku_info6)drop procedure ruku_info6gocreate procedure ruku_info6 start_date smalldatetime,end_date smalldatetimeasselect 商品名称,sum(入库数量) as 总入库数量from 商品表join 入库表on 商品表.商品编号=入库表.商品编号where 入库日期between start_date and end_dategroup by 商品名称exec ruku_in

36、fo6 2010-1-1,2010-1-3111、按月对出出库商品数量的统计use material/*按月对出库商品数量的统计*/goif exists (select *from sysobjects where name=chuku_info7)drop procedure chuku_info7gocreate procedure chuku_info7 start_date smalldatetime,end_date smalldatetimeasselect 商品名称,sum(出库数量) as 出总库数量from 商品表join 出库表on 商品表.商品编号=出库表.商品编号wh

37、ere 出库日期between start_date and end_dategroup by 商品名称exec chuku_info7 2012-7-1,2012-7-3112、按日对入库商品数量的统计use material/*按日对入库商品数量的统计*/goif exists (select *from sysobjects where name=ruku_info8)drop procedure ruku_info8gocreate procedure ruku_info8 start_date smalldatetime,end_date smalldatetimeasselect 商品名称,sum(入库数量) as 总入库数量from 商品表join 入库表on 商品表.商品编号=入库表.商品编号where 入库日期between start_date and end_dategroup by 商品名称13、 按日对出库商品数量的统计use material/*按日对出库商品数量的统计*/goif exists (select *from sysobjects where name=chuku_info9)drop procedure chuku_info9gocreate procedure chuku_info9 st