大型数据库课程设计
设计报告
题目：库存物资管理系统后台数据库学号：
学生姓名: 指导教师：
提交时间： 2013-11-22
目录
库存物资管理系统后台数据库 (1)
第1章进行需求分析，编写数据字典 (1)
1.1系统功能要求设计： (1)
1.2模块设计 (1)
1.3数据字典 (2)
第2章面向对象分析和设计 (4)
第3章逻辑结构设计 (5)
3.1类和对象向关系模式转换 (6)
第4章数据库物理结构设计 (7)
4.1存取方法设计 (7)
4.2存储结构设计 (7)
4.3物理设计 (7)
第5章数据完整性设计 (11)
5.1主键及索引 (11)
表名 (11)
5.2完整性约束的说明 (11)
5.3 check约束 (12)
5.4 Default默认值 (12)
5.5 触发器设计 (12)
第6章数据库视图的设计 (16)
第7章存储过程设计 (19)
总结 (28)
参考文献： (28)
库存物资管理系统后台数据库
第1章进行需求分析，编写数据字典
1.1系统功能要求设计：
(1)数据表以及数据量
1）有一个存放商品的仓库，每天都有商品出库和入库。
2）商品有1000多种，每种商品都有名称、生产厂家、型号、规格等。
3）出入库时必须填写出入库单据，单据包括商品名称、生产厂家、型号、规格、数量、日期、时间、入库单位（或出库单位）名称、送货（或提货）人姓名。
(2)设计要求：
1) 进行需求分析，编写数据字典。
2) 设计E-R图。
3) 设计出入库单据的录入。
4) 实现按商品名称、出入库日期的查询。
5) 实现分别按日、月和年对出入库商品数量的统计。
(3)主要功能：
1)商品管理：增加商品、修改商品、删除商品、浏览商品
2)库存管理：
通过触发器实现商品的入库，同时完成对库存表的更新
通过触发器实现商品的出库，同时完成对库存表的更新
实现按商品名称查询库存数量、入库数量和出库数量
实现按入库日期查询入库数量、出库数量
实现分别按日、月和年对入库商品数量的统计
实现分别按日、月和年对入库商品数量的统计
1.2模块设计
库存物资管理大体可以分为3大块，如下图所示：首先是商品入库模块，该模块主要是描述把采购回来的商品，分类别的放置到指定的仓库中去，然后是商品出库模块，该模块主要描述从指定的仓库中拿出商品，最后是商品库存模块，
这个模块主要是记录商品的库存数量。
1.3数据字典
第2章面向对象分析和设计类和对象设计如下：
第3章逻辑结构设计
分E-R图：
3.1类和对象向关系模式转换
关系模式如下：
商品（商品编号，商品名称，生产厂家，型号，规格）
商品编号为主键
仓库（仓库号，仓库名称）
仓库号为主键
库存（商品编号，仓库号，库存数量）
（商品编号，仓库号）为主键，同时也分别为外键
入库（入库号，商品编号，仓库号，入库数量，入库日期，入库时间，入库单位名称，送货人姓名，性别）
（入库号）为主键，（商品编号，仓库号）为外键。
出库（出库号，商品编号，仓库号，出库数量，出库日期，出库时间，出库单位名称，提货人姓名，性别）
（出库号）为主键，（商品编号，仓库号）为外键。
第4章数据库物理结构设计
4.1存取方法设计
对于库存物资管理系统来说，为了提高某些属性(如：商品编号，仓库号、入库号，入库日期，入库数量等)的查询速度，可以选择聚簇存取的方法，即把这些属性上具有相同值的元组集中放在连续的物理块上。
这样在查询某种商品就会大大提高查询速度。
因此，该系统中选择聚簇存取方法。
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=Material,
filename='C:\PrograFiles\MicrosoftSQLServer\MSSQL.2\MSSQL\DATA \Material.mdf',
size=3MB,
maxsize=UNLIMITED,
filegrowth=1MB
)
log on(
name=Material_LOG,
filename='C:\ProgramFiles\MicrosoftSQLServer\MSSQL.2\MSSQL\DA TA\Material_LOG.ldf',
size=1MB,
filegrowth=10%)
2、创建商品表
create table商品表
(商品编号char(5)not null primary 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 key(商品编号)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,
入库单位名称varchar(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,
出库单位名称varchar(50)null,
提货人姓名varchar(10)not null,
性别char(2)default('男')check(性别in('男''女'))
第5章数据完整性设计
5.1主键及索引
5.2完整性约束的说明
1、商品表的商品编号10001~11000之间（便于查询的使用），商品名称不能取空；该仓库的商品型号统一为DA0001~DA1000格式。
1）USE Material/*定义商品表唯一性的约束*/
GO
alter table商品表add unique(商品名称,生产厂家,型号,规格)
2）USE Material/*定义型号的完整性约束条件:商品型号统一为DA0001-DA1000格式*/
GO
alter table商品表
add constraint CS1 check(型号like'[D][A][0-1][0-9][0-9][0-9]')
5.3 check约束
在商品表中将型号进行check约束：check(型号like'[D][A][0-1][0-9][0-9][0-9]')
在入库表和出库表中将性别进行check约束：check(性别in('男''女'))
5.4 Default默认值
在入库表和出库表中性别默认值设为：男。
default('男')
5.5 触发器设计
1、通过触发器实现商品的入库，同时完成对库存表的更新
（1）入库
use Material
go
if exists(select*from sysobjects where name='rukuinsert')
drop procedure rukuinsert
go
create procedure rukuinsert @rukuhao char(10),@shangpinbianhao char(5),@cangkuhao char(10),@rukushuliang int,@rukuriqi smalldatetime,@rukudanwei varchar(50),@songhuorenname varchar(50),@sex char(2)
as
if(exists(select*from入库表where入库号=@rukuhao))
print('该商品已经存在，不能再次插入')
else
begin
insert into入库表
values( @rukuhao ,@shangpinbianhao,@cangkuhao,@rukushuliang,@ru kuriqi ,@rukudanwei,@songhuorenname ,@sex )
print('插入成功')
end
（2）更新
USE Material
GO
create trigger rkb_into on入库表
after update
as
begin
declare @a char(5),@b char(10),@d int
select @a=商品编号from inserted
select @b=仓库号from inserted
select @d=入库数量from inserted
update库存表
set库存数量=库存数量+@d
where商品编号= @a and仓库号=@b
End
2、通过触发器实现商品的出库，同时完成对库存表的更新
（1）出库
use Material
go
if exists(select*from sysobjects where name='chukuinsert')
drop procedure chukuinsert
go
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
if(exists(select*from出库表where出库号=@chukuhao))
print('该商品已经存在，不能再次插入')
else
begin
insert into出库表
values(@chukuhao ,@shangpinbianhao,@cangkuhao,@chukushuliang, @chukuriqi ,@chukudanwei,@tihuorenname ,@sex )
print('插入成功')
end
（2）更新
USE Material
GO
create trigger ckb_into on出库表
after update
as
begin
declare @aa char(10),@bb char(5), @dd int select @aa=仓库号from deleted
select @bb=商品编号from deleted
select @dd=出库数量from inserted
update库存表
set库存数量=库存数量-@dd
where商品编号=@bb and仓库号=@aa End
第6章数据库视图的设计
1、建立商品表视图
USE Material
GO
create view商品表视图
as
select*
from商品表
2、创建商品库存视图来浏览库存中相同编号商品的信息，其中包括：商品编号、商品名称和库存数量。
USE Material
GO
create view商品库存视图(商品编号,商品名称,库存数量)
as
select库存表.商品编号,商品表.商品名称,sum(库存表.库存数量)
from商品表,库存表
where商品表.商品编号=库存表.商品编号
group by库存表.商品编号,商品表.商品名称
3、创建商品入库视图
USE Material
GO
create view商品入库视图
as
select商品表.商品编号,商品名称,生产厂家,型号,规格,入库号,仓库号,入库数量,入库日期
from商品表,入库表
where商品表.商品编号=入库表.商品编号
4、创建商品出库视图
USE Material
GO
create view商品出库视图
as
select商品表.商品编号,商品名称,生产厂家,型号,规格,出库号,仓库号,出库数量,出库日期
from商品表,出库表
where商品表.商品编号=出库表.商品编号
5、创建商品入库仓库视图
USE Material
GO
create view商品入库仓库视图
as
select商品表.商品编号,商品名称,入库表.仓库号,仓库名称
from商品表,入库表,仓库表
where商品表.商品编号=入库表.商品编号and入库表.仓库号=仓库表.仓库号
6、创建商品出库仓库视图
USE Material
GO
create view商品出库仓库视图
as
select商品表.商品编号,商品名称,出库表.仓库号,仓库名称
from商品表,出库表,仓库表
where商品表.商品编号=出库表.商品编号and出库表.仓库号=仓库表.仓库号
第7章存储过程设计
1、实现商品表的插入
use Material
go
if exists(select*from sysobjects where name='shangpininsert ')
drop procedure shangpininsert
go
create procedure shangpininsert @bianhao char(5),@name varchar(50),@changjia varchar(50),@xinghao varchar(50),@guige varchar(20)
as
if(exists(select*from商品表where商品编号=@bianhao))
print('该商品已经存在，不能再次插入')
else
begin
Insert into商品表
values( @bianhao,@name,@changjia,@xinghao,@guige)
print('插入成功')
End
exec shangpininsert '11111','毛巾','纺织有限公司','DA0111','30*70cm'
2、实现商品表的删除
use Material
if exists(select*from sysobjects where name='shangpindelete') drop procedure shangpindelete
go
create procedure shangpindelete @bianhao char(5)
as
if(exists(select商品编号from商品表where商品编号=@bianhao)) begin
delete
from商品表
where商品编号=@bianhao
print('删除成功，该商品已被删除')
end
else print('该商品不存在,不能删除')
exec shangpindelete '12200'
3、按商品名称查询库存数量
use Material
go
if exists(select*from sysobjects where name='shangpin_info1') drop procedure shangpin_info1
go
create procedure shangpin_info1 @shangpinname varchar(50)
select a.商品编号,商品名称,b.库存数量
from商品表a join库存表b
on a.商品编号=b.商品编号
where商品名称=@shangpinname
exec shangpin_info1 'LG冰箱'
4、按商品名称查询入库数量
use Material
go
if exists(select*from sysobjects where name='ruku_info10') drop procedure ruku_info10
go
create procedure ruku_info10 @shangpinname varchar(50) as
select商品表.商品编号,商品名称,入库数量
from商品表join入库表
on商品表.商品编号=入库表.商品编号
where商品名称=@shangpinname
exec ruku_info10 'acer宏基笔记本电脑'
5、按商品名称查询出库数量
use Material
go
if exists(select*from sysobjects where name='chuku_info11')
drop procedure chuku_info11
go
create procedure chuku_info11 @shangpinname varchar(50) as
select商品表.商品编号,商品名称,出库数量
from商品表join出库表
on商品表.商品编号=出库表.商品编号
where商品名称=@shangpinname
exec chuku_info11'msi微星电脑主板'
6、按入库日期查询入库数量
use Material
go
if exists(select*from sysobjects where name='ruku_info2') drop procedure ruku_info2
go
create procedure ruku_info2 @rukudate smalldatetime
as
select商品表.商品编号,商品名称,入库日期,入库数量
from商品表join入库表
on商品表.商品编号=入库表.商品编号
where入库日期=@rukudate
exec ruku_info2 '2012-3-5 0:00'
7、按出库日期查询出库数量
use Material
go
if exists(select*from sysobjects where name='chuku_info3')
drop procedure chuku_info3
go
create procedure chuku_info3 @chukudate smalldatetime
as
select商品表.商品编号,商品名称,出库日期,出库数量
from商品表join出库表
on商品表.商品编号=出库表.商品编号
where出库日期=@chukudate
exec chuku_info3'2010/2/16 0:00:00'
8、按年对入库商品数量的统计
use Material/*按年对入库商品数量的统计*/
go
if exists(select*from sysobjects where name='ruku_info4')
drop procedure ruku_info4
go
create procedure ruku_info4 @start_date smalldatetime,@end_date smalldatetime
as
select商品名称,sum(入库数量)as'总入库数量'
from商品表join入库表
on商品表.商品编号=入库表.商品编号
where入库日期between @start_date and @end_date
group by商品名称
exec ruku_info4 '2010-1-1','2010-12-31'
9、按年对出库商品数量的统计
use Material/*按年对出库商品数量的统计*/
go
if exists(select*from sysobjects where name='chuku_info5')
drop procedure chuku_info5
go
create procedure chuku_info5 @start_date smalldatetime,@end_date smalldatetime
as
select商品名称,sum(出库数量)as'出总库数量'
from商品表join出库表
on商品表.商品编号=出库表.商品编号
where出库日期between @start_date and @end_date
group by商品名称
exec chuku_info5'2012-1-1','2012-12-31'
10、按月对入库商品数量的统计
use Material/*按月对入库商品数量的统计*/
go
if exists(select*from sysobjects where name='ruku_info6')
drop procedure ruku_info6
go
create procedure ruku_info6 @start_date smalldatetime,@end_date smalldatetime
as
select商品名称,sum(入库数量)as'总入库数量'
from商品表join入库表
on商品表.商品编号=入库表.商品编号
where入库日期between @start_date and @end_date
group by商品名称
exec ruku_info6 '2010-1-1','2010-1-31'
11、按月对出出库商品数量的统计
use Material/*按月对出库商品数量的统计*/
go
if exists(select*from sysobjects where name='chuku_info7')
drop procedure chuku_info7
go
create procedure chuku_info7 @start_date smalldatetime,@end_date smalldatetime
as
select商品名称,sum(出库数量)as'出总库数量'
from商品表join出库表
on商品表.商品编号=出库表.商品编号
where出库日期between @start_date and @end_date
group by商品名称
exec chuku_info7 '2012-7-1','2012-7-31'
12、按日对入库商品数量的统计
use Material/*按日对入库商品数量的统计*/
go
if exists(select*from sysobjects where name='ruku_info8')
drop procedure ruku_info8
go
create procedure ruku_info8 @start_date smalldatetime,@end_date smalldatetime
as
select商品名称,sum(入库数量)as'总入库数量'
from商品表join入库表
on商品表.商品编号=入库表.商品编号
where入库日期between @start_date and @end_date
group by商品名称
13、按日对出库商品数量的统计
use Material/*按日对出库商品数量的统计*/
go
if exists(select*from sysobjects where name='chuku_info9')
drop procedure chuku_info9
go
create procedure chuku_info9 @start_date smalldatetime,@end_date smalldatetime
as
select商品名称,sum(出库数量)as'出总库数量'
from商品表join出库表
on商品表.商品编号=出库表.商品编号
where出库日期between @start_date and @end_date
group by商品名称
exec chuku_info9'2010-2-16','2010-2-16'
总结
通过这个库存物料管理的数据库，我学到了很多东西，首先是我必须学会去查找有关物料管理这个系统有关的组成部分和相关的需求，只有弄懂了这些才能得出一个比较合理的结果。
需求分析是最重要的一步，下面的逻辑分析、关系表和表之间都要在这个基础上来设计，由于时间关系，不能做到很完善，包括E-R图的构思。
设计表的属性包括商品属性要用到多少个字符，时间属性用datetime还是smalldatetime，因为想细到分钟，所以就决定用smalldatetime。
还有数据库要求数据量充足，主表不能少于一千条数据，数据较多，做起来比较繁琐，录数据将就是一个很大的工程了，虽然这次数据库课程设计要求只做后台，但是由于也要完成一个完整的数据库系统功能，所以工作量还是很大的。
做数据库不能急于求成，要理清自己的思路，以免半途而废，从新做。
通过这次数据库课程设计不仅使我对以前学过的知识的巩固，也使我学到了很多新的知识，对整个管理系统有了一个清楚的认识。
最重要的是与同学讨论了不少问题，也学到了很多的，还有通过查找资料，也学到了很多。
参考文献：
郑阿奇等. SQLserver实用教程（第3版）[M] . 电子工业出版社,2009
《大型数据库课程设计》报告评分标准。
