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

大型数据库课程设计
设计报告

题 目：库存物资管理系统后台数据库
学 号：
学生姓名:
指导教师：
提交时间： 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数据字典
表
商品表
仓库表
库存表
入库表
出库表

商品表
字段名
数据类型
是否可以为空
Is PK
Is FK
商品编号
Char(5)
not null
yes
no
商品名称
Varchar(50)
not null
no
no
生产厂家
Varchar(50)
not null
no
no
型号
Varchar(50)
not null
no
no
规格
Varchar(20)
not null
no
no

仓库表
字段名
数据类型
是否可以为空
Is PK
Is FK
仓库号
Char(10)
not null
yes
no
仓库名称
Varchar(50)
not null
no
no

库存表
字段名
数据类型
是否可以为空
Is PK
Is FK
商品编号
Char(5)
not null
yes
yes
仓库号
char(10)
not null
yes
yes
库存数量
int
not null
no
no

入库表
字段名
数据类型
是否可以为空
Is PK
Is FK
入库号
Char(10)
not null
yes
no
商品编号
char(5)
not null
no
yes
仓库号
Char(10)
not null
no
yes
入库数量
int
not null
no
no
入库日期
smalldatetime
not null
no
no
入库单位名称
Varchar(50)
null
no
no
送货人姓名
varchar(50)
not null
no
no
性别
char(2)
null
no
no

出库表
字段名
数据类型
是否可以为空
Is PK
Is FK
出库号
Char(10)
not null
yes
no
商品编号
char(5)
not null
no
yes
仓库号
Char(10)
not null
no
yes
出库数量
int
not null
no
no
出库日期
smalldatetime
not null
no
no
出库单位名称
Varchar(50)
null
no
no
提货人姓名
varchar(50)
not null
no
no
性别
char(2)
null
no
no

第2章 面向对象分析和设计

类和对象设计如下：

商品
商品编号：char
商品名称：varchar
生产厂家: varchar
规格:varchar
型号：varchar
添加()
删除()
查询()
修改()

仓库

仓库号:char
仓库名称：varchar
添加()
修改()
删除()
查询()

库存
商品编号：商品
仓库号：仓库
库存数量：int
查询()
修改()
添加()
删除()

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

第3章 逻辑结构设计
生产厂家
商品名称
商品编号

分E-R图：

商品
规格
型号

仓库

仓库名称

仓库号

库存关系：
库存
商品
仓库
n m

入库
入库关系：
商品
仓库
n m

出库
出库关系：
商品
仓库
n m

商品名称
商品编号
总E-R图：
生产厂家

规格
商品
型号

入库号

出库号
库存数量

n n n
出库数量
入库
入库
入库数量
出库

出库日期
入库日期

提货人姓名
送货人姓名

m m m
性别
出库单位名称
入库单位名称
仓库

性别

仓库名称
仓库号

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\DATA\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主键及索引

表名
主键
建立索引
商品表
(商品编号)
create clustered index 商品表
on 商品表(商品编号)
仓库表
(仓库号)
create clustered index 仓库表
on仓库表(仓库号)
入库表
(入库号)
create clustered index 入库表
on 入库表(入库号)

出库表

(出库号)
create clustered index 出库表
on 出库表(出库号)

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,@rukuriqi ,@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
go
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)
as
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。
还有数据库要求数据量充足，主表不能少于一千条数据，数据较多，做起来比较繁琐，录数据将就是一个很大的工程了，虽然这次数据库课程设计要求只做后台，但是由于也要完成一个完整的数据库系统功能，所以工作量还是很大的。做数据库不能急于求成