仓库管理系统数据库的设计与实现 SQL server.doc

● 实验题目：仓库管理系统数据库的设计与实现
描述：设计一个仓库管理系统，实现下列功能：
①零件信息登记（包括种类，名称和库存数量等信息）；
②零件进库登记（包括种类，名称和库存数量等信息）；
③零件出库登记（包括种类，名称和库存数量等信息）；

实验代码和实验结果和实验总结：
在实验代码中分别应用了相关实验操作的结果，通过上一个结果截图与其随后的结果截图和相关代码对比课已看出相关代码的作用（主要是各个触发器的作用）。

--------------------------------------
--数据库的创建
create database 仓库管理系统数据库
on
primary
( name = 仓库管理系统数据库_data,
filename ='E:\仓库管理系统数据库_Data.MDF',
size = 50,
maxsize = 500,
filegrowth = 5)

log on
( name = 仓库管理系统数据库_log,
filename ='E:\仓库管理系统数据库_Log.LDF',
size = 10,
maxsize = 200,
filegrowth = 5)
----------------------------------------
--数据表的创建

use 仓库管理系统数据库

create table 零件信息登记
( 零件代号 char(10) primary key,
名称 char(10) not null,
种类 char(10) not null ,
价格 numeric(10,2) not null,
库存数量 int default 0)

create table 零件进库登记
(
零件代号 char(10) foreign key references 零件信息登记(零件代号) ,
进货代号 int ,
进货人工作号 char(10),
名称 char(10) not null,
种类 char(10) not null ,
价格 numeric(10,2) not null,
进库数量 int not null default 0,
存放位置 char(10) not null,
进库时间 datetime ,
primary key (零件代号,进货代号))

create table 零件出库登记
(
零件代号 char(10) foreign key references 零件信息登记(零件代号) ,
出货代号 int ,
出货人工作号 char(10) not null,
名称 char(10) not null,
种类 char(10) not null ,
价格 numeric(10,2) not null,
出库数量 int not null default 0,
取货单号 char(10) not null,
出库时间 datetime ,
primary key(零件代号,出货代号))

----------------------------------------
--通过存储过程来实现表中数据的输入

create procedure pro_ins1
(@no char(20), @name char(20), @class char(20), @s numeric(10,2), @num int)
as
insert into 零件信息登记
values(@no, @name, @class, @s, @num)

create procedure pro_ins2
(@no char(20),@jno int,@wno char(10), @name char(20), @class char(20), @s numeric(10,2), @num int, @m char(20) )
as
declare @time datetime
set @time = getdate()
insert into 零件进库登记
values(@no, @jno ,@wno ,@name, @class, @s, @num, @m, @time)

create procedure pro_ins3
(@no char(20),@jno int,@wno char(10), @name char(20), @class char(20), @s numeric(10,2), @num int, @m char(20))
as
declare @time datetime
set @time = getdate()
insert into 零件出库登记
values(@no,@jno ,@wno , @name, @class, @s, @num, @m, @time)

create procedure pro_select
as
begin
select *
from 零件信息登记
select *
from 零件进库登记
select *
from 零件出库登记
end

----------------------------------------------------------
--通过第一个存储过程来实现零件的信息登录

exec pro_ins1 '20112001','螺栓','金属',5.2,50
exec pro_ins1 '20112002','龙头','金属',9.3,70
exec pro_ins1 '20112003','水杯','塑料',36.8,80
exec pro_ins1 '20112004','书包','布料',52,150
exec pro_ins1 '20112005','电视','电子',4000,50

-
--------------------------------------------------
--当零件进库时通过下面的触发器来实现数据的完整性

create trigger tri_统一零件管理
on 零件进库登记
instead of insert
as
begin
if(exists (select 零件代号 from 零件信息登记 where 零件代号 = (select 零件代号 from inserted)))
begin
print '在库里已经此零件，并且放置成功'
update 零件信息登记
set 库存数量 = 库存数量 + (select 进库数量 from inserted )
where 零件代号 = (select 零件代号 from inserted)
end
if(not exists (select 零件代号 from 零件信息登记 where 零件代号 = (select 零件代号 from inserted)))
begin
print '库里不存在此零件，已经把此零件加入零件信息登记中'
insert into 零件信息登记
select 零件代号,名称,种类,价格,进库数量
from inserted
insert into 零件进库登记
select 零件代号,进货代号,进货人工作号,名称,种类,价格,进库数量,存放位置,进库时间
from inserted
end
End

--------------------------------------------------------------
--对触发器“tri_统一零件管理”的相关验证信息
exec pro_select

exec pro_ins2 '20112002',1120,'c2011a','龙头','金属',9.3,70,'一排号'
exec pro_ins2 '20112009',1122,'c2011b','电池','金属',8.3,150,'一排号'
exec pro_ins2 '20112012',1132,'c2011c','水桶','塑料',7.9,130,'二排号'
exec pro_ins2 '20112013',1134,'a2012d','饮料','食品',54,200,'二排号'

exec pro_select

--drop trigger tri_统一零件出库
---------------------------------------------------------------------
--当零件出库时通过下面的触发器来实现数据的完整性

create trigger tri_统一零件出库
on 零件出库登记
instead of insert
as
if exists(select 零件代号 from 零件信息登记 where 零件代号 = (select 零件代号 from inserted))
begin
print '次零件存在'
if exists(select 零件代号 from 零件信息登记 where 零件代号 = (select 零件代号 from inserted)
and 库存数量>=(select 出库数量 from inserted))
begin
begin
print '零件库存够出售'
update 零件信息登记
set 库存数量 = 库存数量 -(select 出库数量 from inserted)
where 零件代号 = (select 零件代号 from inserted)
end
insert into 零件出库登记
select 零件代号,出货代号,出货人工作号,名称,种类,价格,出库数量,取货单号,出库时间
from inserted
end
else
begin
print '零件库存数量不够，不能出售！'
end
end
else
begin
print '仓库里没有此零件,请通知公司'
end

--对触发器“tri_统一零件管理”的相关验证信息

exec pro_ins3 '20112002',1120,'f112a','龙头','金属',9.3,10,'东华理工'
exec pro_ins3 '20112004',1143,'f113a','书包','布料',52,1000,'南昌'
exec pro_ins3 '20112045',1220,'f114b','铅笔','用具',9.3,10,'江西'
exec pro_ins3 '20112046',1128,'g112a','锁','金属',9.3,10,'云南'

exec pro_select

-----------------------------------------------------------------
--对“零件进库登记”数据更改是触发对“零件信息登记”的更改
--同时防止对“零件进库登记”信息的非法更改

create trigger tri_up零件进库
on 零件进库登记
after update
as
if exists(select 零件代号 from deleted where 零件代号=(select 零件代号 from inserted)
and 进货代号=(select 进货代号 from inserted)
and 进库数量>(select 进库时间 from inserted))
update 零件信息登记
set 库存数量 = 库存数量+(select 进库数量 from inserted)-(select 进库数量 from deleted)
else if exists(select 零件代号 from deleted where 零件代号=(select 零件代号 from inserted)
and 进货代号=(select 进货代号 from inserted)
and 进库数量<=(select 进库时间 from inserted))
update 零件信息登记
set 库存数量 = 库存数量+(select 进库数量 from inserted)-(select 进库数量 from deleted)
else
begin
print '修改不正确'
end
---------------------------------------------------------------------
--对触发器“tri_up零件进库”的相关验证信息

update 零件进库登记
set 进库数量= 120
where 零件代号='20112002'and 进货代号=1120

update 零件进库登记
set 进库数量 = 5000
where 零件代号='20112002' and 进货代号=2187

exec pro_select

--------------------------------------------------------
--对“零件出库登记”数据更改是触发对“零件信息登记”的更改
--同时防止对“零件进库登记”信息的非法更改

create trigger tri_up零件出库
on 零件出库登记
after update
as
if exists(select 零件代号 from deleted where 零件代号=(select 零件代号 from inserted)
and 出货代号 = (select 出货代号 from inserted)
and 出库数量<=(select 出库时间 from inserted))
update 零件信息登记
set 库存数量 = 库存数量+(select 出库数量 from inserted)-(select 出库数量 from deleted)

else if exists(select 零件代号 from deleted where 零件代号=(select 零件代号 from inserted)
and 出货代号 = (select 出货代号 from inserted)
and 出库数量>(select 出货代号 from inserted))
update 零件信息登记
set 库存数量 = 库存数量+(select 出库数量 from inserted)-(select 出库数量 from deleted)
else
begin
print '修改不正确'
end
--------------------------------------------------------------------
--对触发器“tri_up零件出库”的相关验证信息

update 零件出库登记
set 出库数量 = 20
where 零件代号='20112002' and 出货代号 = 1120

update 零件出库登记
set 出库数量 = 120
where 零件代号='20112004'

update 零件出库登记
set 出货代号 = 620
where 零件代号='20112004'

exec pro_select

--------------------------------------------------------------------
--一下代码为创建相关规则、试图、用户并授权，来完成数据库的完整性和安全性
---相关的验证信息省略

create rule ru_零件数量
as
@num>=0

exec sp_bindrule 'ru_零件数量','零件信息登记.库存数量'
exec sp_bindrule 'ru_零件数量','零件进库登记.进库数量'
exec sp_bindrule 'ru_零件数量','零件出库登记.出库数量'

exec pro_ins1 '20112011','龙头','金属',9.3,-10
exec pro_ins2 '20112012','龙头','金属',9.3,-100,'东华理工'
-exec pro_ins3 '20112013','龙头','金属',9.3,-100,'东华理工'

create view view_零件进出库(零件代号,进库数量,出库数量)
as
select 零件进库登记.零件代号,进库数量,出库数量
from 零件进库登记 , 零件出库登记
where 零件进库登记.零件代号=零件出库登记.零件代号

create view view_零件库存情况
as
select 零件代号,库存数量
from 零件信息登记

select *
from view_零件进出库

select *
from view_零件库存情况