SQL service 数据库 某工厂的物料管理系统数据库设计与实现

1. 实现物料的分类管理；
2. 实现部门和员工信息管理；
3. 实现物料的入库和领用管理；
4. 实现物料的转仓管理；
5. 创建触发器，实现物料入库和领用时相应物料库存的自动更新；
6. 创建触发器，实现转仓时转入仓库物料增加、转出仓库物料减少；
7. 创建存储过程统计各仓库各种物料的现存数量；
8. 创建存储过程统计指定时间段内各种物料的入库数量和领用数量；
9. 建立数据库相关表之间的参照完整性约束；

系统功能结构:

数据流程图:

逻辑结构设计

 局部E-R图

实体： 部门：部门号,部门名

员工：员工号,姓名,性别,电话,职称

仓库：仓库号,仓库名 

物料：物料编号,物料名称,类型 

实体联系：

员工和部门：一个员工属于一个部门,一个部门可以有多个员工,属于多对一的关系；

员工和物料：物料由员工领用，一个员工可以领到多种物料,不同的物料也可以有不同的员工来领，它们属于多对多的关系；

仓库和物料;一个仓库可以有多种物料,一种物料可在多个仓库有,属于多对多的关系。

全局E-R图

 

数据字典

员工表（S）

字段名称	类型	约束控制	字段含义
SNo	Char(10)	primary key	员工号
SN	Char(20)	not null	员工姓名
Sex	char(1)	not null	性别
TNo	Char(17)	not null	员工电话
Dept	Char(20)	not null	职称

 

部门表（B）

字段名称	类型	约束控制	字段含义
BNo	Char(10)	primary key	部门号
BN	Char(20)	not null	部门名

 

仓库表（D）

字段名称	类型	约束控制	字段含义
DNo	Char(10)	primary key	仓库号
DN	Char(20)	not null	仓库名

 

物料表（A）

字段名称	类型	约束控制	字段含义
ANo	Char(10)	primary key	物料号
AN	Char(20)	not null	物料名
Class	Char(20)	not null	物料类型

 

部门员工表（BS）

字段名称	类型	约束控制	字段含义
BNo	Char(10)	primary key	部门号
SNo	Char(10)	primary key	员工号

 

物料仓库表（AD）

字段名称	类型	约束控制	字段含义
DNo	Char(10)	primary key	仓库号
ANo	Char(10)	primary key	物料号
Num	int	not null	物料数量

 

员工物料领取表（SA）

字段名称	类型	约束控制	字段含义
Id	int	primary key	自增编号
SNo	Char(10)	foreign key	员工号
ANo	Char(10)	foreign key	物料号
getNum	int	not null	领取数量
getTime	datetime	default	领取时间

 

物料出入库表（ioD）

字段名称	类型	约束控制	字段含义
Id	int	primary key	自增编号
ANo	Char(10)	foreign key	物料号
DNo	Char(10)	foreign key	仓库号
InOut	int	1'或'0'	出入标志1入0出
inoutTime	datetime	default	出入库时间

 

物料转仓表（RD）

字段名称	类型	约束控制	字段含义
ANo	Char(10)	foreign key	物料号
DNo1	Char(10)	foreign key	转出仓库号
DNo2	Char(10)	foreign key	转入仓库号
remNum	int	not null	转仓数量

 

物理结构设计

--创建数据库AMS
create database AMS
on
( name=AMS_Data,
  filename='C:\AMSData.mdf',
  size=10,
  maxsize=500,
  filegrowth=10)
log on
( name=AMS_Log,
  filename='C:\AMSLog.ldf',
  size=5,
  maxsize=500,
  filegrowth=5)

--创建数据表及约束
use AMS
go
--创建员工表
create table S
(  SNo char(10) not null constraint SNo_Prim primary key,
   SN  char(20) not null constraint SN_Uniq unique,
   Sex char(2)  not null constraint Sex_Che check(Sex='男' or Sex='女'),
   TNo char(20) null,
   Dept char(20) null)
go
--创建部门表
create table B
( BNo char(10) not null constraint BNo_Prim primary key,
  BN  char(20) not null )
go
--创建仓库表
create table D
( DNo char(10) not null constraint DNo_Prim primary key,
  DN char(20) not null )
go
--创建物料表
create table A
( ANo char(10) not null constraint ANo_Prim primary key,
  AN char(20) not null,
  Class char(20) not null )
go
--部门员工表
create table BS
( BNo char(10) not null constraint BS_ForeB foreign key references B(BNo),
  SNo char(10) not null constraint BS_ForeS foreign key references S(SNo),
  constraint BS_Prim primary key(BNo,SNo) )
go
--物料仓库表
create table AD
( DNo char(10) not null constraint AD_ForeD foreign key references D(DNo),
  ANo char(10) not null constraint AD_ForeA foreign key references A(ANo),
  Num int not null,
  constraint AD_Prim primary key(DNo,ANo))
go
--员工物料领取表
create table SA
( Id int not null identity,
  SNo char(10) not null constraint SA_ForeS foreign key references S(SNo),
  ANo char(10) not null constraint SA_ForeA foreign key references A(ANo),
  DNo char(10) not null constraint SA_ForeD foreign key references D(DNo),
  getNum int not null,
  getTime datetime default (getdate()) not null,
  constraint SA_Prim primary key(Id))
go
--物料出入库表
create table ioD
( Id int not null identity,
  ANo char(10) not null ,
  DNo char(10) not null ,
  inoutNum int not null,
  InOut int not null constraint InOut_Che check(InOut=1 or InOut=0),
  inoutTime datetime default (getdate()) not null,
  constraint ioD_Prim primary key(Id))
--物料转仓表
create table RD
( ANo char(10) not null constraint RD_ForeA foreign key references A(ANo),
  DNo1 char(10) not null constraint RD_ForeD1 foreign key references D(DNo),
  DNo2 char(10) not null constraint RD_ForeD2 foreign key references D(DNo),
  remNum int not null)

--创建触发器

--ioD表只能插入删除操作
use AMS
go
create trigger delete_ioD on ioD
instead of delete 
as 
print '禁止对此表进行删除操作！'
rollback ---将对表全部锁定禁止操作
go
create trigger update_ioD on ioD
instead of update 
as 
print '禁止对此表进行修改操作！'
rollback ---将对表全部锁定禁止操作

--创建触发器
--ioD表只能插入操作
use AMS
go
create trigger delete_ioD on ioD
instead of delete 
as 
print '禁止对此表进行删除操作！'
rollback ---将对表全部锁定禁止操作
go
create trigger update_ioD on ioD
instead of update 
as 
print '禁止对此表进行修改操作！'
rollback ---将对表全部锁定禁止操作

/*创建触发器，实现物料入库物料库存的自动更新；*/
--插入触发器
use AMS
go
create trigger insert_AD on AD 
after insert
as
declare @ano char(10),@dno char(10),@num int
select @ano=ANo,@dno=DNo,@num=Num from inserted
if not exists(select ANo from A where ANo=@ano)
     begin
	 print '不存在'+@ano+'物料不能入库！'rollback 
	 end
     else if not exists(select DNo from D where DNo=@dno)
	      begin
	      print '不存在'+@dno+'仓库物料不能入库！'rollback 
	      end
		  else   begin
		         insert into ioD(ANo,DNo,inoutNum,InOut) values(@ano,@dno,@num,1)
				 print @ano+'物料入库! 仓库:'+@dno+'数量:'+convert(varchar(10),@num)
		         end
--更新触发器
go
create trigger update_AD on AD 
after update
as
declare @ano1 char(10),@dno1 char(10),@num1 int,
@ano2 char(10),@dno2 char(10),@num2 int
select @ano1=ANo,@dno1=DNo,@num1=Num from deleted
select @ano2=ANo,@dno2=DNo,@num2=Num from inserted
if(@ano1=@ano2 and @dno1=@dno2)
    begin
	if(@num1>@num2) 
		begin 
		insert into ioD(ANo,DNo,inoutNum,InOut) values(@ano1,@dno1,@num1-@num2,0)
		print @ano1+'物料出库!仓库:'+@dno1+'数量:'+convert(varchar(10),@num1-@num2)
		end
	else  
		begin 
		insert into ioD(ANo,DNo,inoutNum,InOut) values(@ano2,@dno2,@num2-@num1,1)
		print @ano1+'物料入库!仓库:'+@dno1+'数量:'+convert(varchar(10),@num2-@num1)
		end
	end
	else if not exists(select ANo from A where ANo=@ano2)
		 begin
		 print '不存在'+@ano2+'物料,修改错误！' rollback
		 end
		 else if not exists(select DNo from D where DNo=@dno2)
			  begin
			  print '不存在'+@dno2+'仓库物料,修改错误！' rollback
			  end
			  else  begin
					insert into ioD(ANo,DNo,inoutNum,InOut) values(@ano1,@dno1,@num1,0)
					print @ano1+'物料出库!仓库:'+@dno1+'数量:'+convert(varchar(10),@num1)
					insert into ioD(ANo,DNo,inoutNum,InOut) values(@ano2,@dno2,@num2,1)
					print @ano2+'物料入库!仓库:'+@dno1+'数量:'+convert(varchar(10),@num2)
					end

--删除触发器
go
create trigger delete_AD on AD 
after delete
as
declare @ano char(10),@dno char(10),@num int
select @ano=ANo,@dno=DNo,@num=Num from deleted
insert into ioD(ANo,DNo,inoutNum,InOut) values(@ano,@dno,@num,0)
print @ano+'物料销毁!\n仓库:'+@dno+'数量:'+convert(varchar(10),@num)

/*创建触发器，实现物料领用时相应物料库存的自动更新*/
--插入触发器
go
create trigger insert_SA on SA 
after insert
as
declare @sno char(10),@ano char(10),@dno char(10),@getnum int
select @sno=SNo,@ano=ANo,@dno=DNo,@getnum=getNum from inserted
if not exists(select SNo from S where SNo=@sno)
     begin
	 print '该工厂不存在'+@sno+'员工！'rollback 
	 end
     else if not exists(select ANo from AD where ANo=@ano and DNo=@dno)
			begin
			print @dno+'仓库没有'+@ano+'物料！'rollback 
			end
			else if(select Num from AD where ANo=@ano and DNo=@dno)<@getnum
			       begin
				   print @dno+'仓库的'+@ano+'物料库存不足！'rollback 
				   end
				   else begin
						update AD set Num=Num-@getnum where ANo=@ano and DNo=@dno
						print @sno+'员工领取'+@ano+'物料成功！仓库：'+@dno+'数量:'+convert(varchar(10),@getnum)
				        end


--更新触发器
go
create trigger update_SA on SA 
after update
as
declare @sno1 char(10),@ano1 char(10),@dno1 char(10),@getnum1 int,
@sno2 char(10),@ano2 char(10),@dno2 char(10),@getnum2 int
select @sno1=SNo,@ano1=ANo,@dno1=DNo,@getnum1=getNum from deleted
select @sno2=SNo,@ano2=ANo,@dno2=DNo,@getnum2=getNum from inserted
if(@sno1=@sno2 and @ano1=@ano2 and @dno1=@dno2)
    begin
	if(@getnum1>@getnum2) 
	     begin 
		 update AD set Num=Num+@getnum1-@getnum2 where ANo=@ano1 and DNo=@dno1
		 print @sno1+'员工领取'+@ano1+'物料修改成功！仓库：'+@dno1+'数量:'+convert(varchar(10),@getnum1-@getnum2)
		 end
		 else begin
		       if(select Num from AD where ANo=@ano1 and DNo=@dno1)<(@getnum2-@getnum1)
			       begin
				   print @dno1+'仓库的'+@ano1+'物料库存不足！'rollback
				   end
						   begin
						   update AD set Num=Num+@getnum1-@getnum2 where ANo=@ano1 and DNo=@dno1
						   print @sno1+'员工领取'+@ano1+'物料修改成功！仓库：'+@dno1+'数量:'+convert(varchar(10),@getnum2-@getnum1)
						   end
			   end
    end
	else  
	      begin
	      if not exists(select SNo from S where SNo=@sno2)
			 begin
			 print '该工厂不存在'+@sno2+'员工！'rollback 
			 end
			 else if not exists(select ANo from AD where ANo=@ano2 and DNo=@dno2)
					begin
					print @dno2+'仓库没有'+@ano2+'物料！'rollback 
					end
					else if(select Num from AD where ANo=@ano2 and DNo=@dno2)<@getnum2
						   begin
						   print @dno2+'仓库的'+@ano2+'物料库存不足！'rollback 
						   end
						   else begin
								update AD set Num=Num+@getnum1 where ANo=@ano1 and DNo=@dno1
								update AD set Num=Num-@getnum2 where ANo=@ano2 and DNo=@dno2
								print @sno2+'员工领取'+@ano2+'物料修改成功！仓库：'+@dno2+'数量:'+convert(varchar(10),@getnum1)
								end
	      end
--删除触发器
go
create trigger delete_SA on SA 
after delete
as
declare @sno char(10),@ano char(10),@dno char(10),@getnum int
select @sno=SNo,@ano=ANo,@dno=DNo,@getnum=getNum from deleted
update AD set Num=Num+@getnum where ANo=@ano and DNo=@dno
print @sno+'员工领取物料归还成功！仓库：'+@dno+'数量:'+convert(varchar(10),@getnum)

/*创建触发器，实现转仓时转入仓库物料增加、转出仓库物料减少*/
--插入触发器
go
create trigger insert_RD on RD  
after insert
as
declare @ano char(10),@dno1 char(10),@dno2 char(10),@remnum int
select @ano=ANo,@dno1=DNo1,@dno2=DNo2,@remnum=remNum from inserted
if(@dno1=@dno2)
    begin
    print '相同仓库不能转库！'rollback 
    end
	else if (select Num from AD where DNo=@dno1 and ANo=@ano)<@remnum
		begin
		print @ano+'物料转库数量不足！'rollback 
		end
	    else  begin
			  update AD set Num=Num-@remnum where ANo=@ano and DNo=@dno1
			  if exists(select ANo from AD where ANo=@ano and DNo=@dno2)
					update AD set Num=Num+@remnum where ANo=@ano and DNo=@dno2
			  else  insert into AD(DNo,ANo,Num) values(@dno2,@ano,@remnum)
			  print @ano+'物料转库成功！转出仓库：'+@dno1+'转入仓库：'+@dno2+'数量:'+convert(varchar(10),@remnum)
			  end

--更新触发器	   
go
create trigger update_RD on RD  
instead of update
as
print '禁止对此表进行修改操作！'
rollback ---将对表全部锁定禁止操作

--删除触发器   
go
create trigger delete_RD on RD  
after delete
as
print '禁止对此表进行修改操作！'
rollback ---将对表全部锁定禁止操作

--创建存储过程
/*创建存储过程统计各仓库各种物料的现存数量；*/
use AMS
--统计各仓库各种物料的现存数量
go
create procedure findAD 
as
select D.DN as 仓库名,A.AN as 物料名,Num as 现存数量
from AD,A,D
where AD.ANo=A.ANo and AD.DNo=D.DNo


--查询某仓库各种物料存储情况
go
create procedure findAD_D 
( @dno char(10))
as
select D.DN as 仓库名,A.AN as 物料名,Num as 现存数量
from AD,A,D
where AD.ANo=A.ANo and AD.DNo=D.DNo and AD.DNo=@dno


----查询各仓库某种类型物料存储情况
go
create procedure findAclass
(@class char(20))
as
select D.DN as 仓库名,A.AN as 物料名,Class as 类型,Num as 现存数量
from AD,A,D
where AD.ANo=A.ANo and AD.DNo=D.DNo and Class=@class

/*创建存储过程统计指定时间段内各种物料的入库数量和领用数量；*/
go
--指定时间段内各种物料的入库数量
create procedure findTime
(@time1 datetime,
 @time2 datetime)
as
select DN as 仓库名,AN as 物料名称,inoutNum as 入库数量,inoutTime as 入库时间
from ioD,A,D
where inoutTime between @time1 and @time2 and 
      InOut=1 and ioD.ANo=A.ANo and ioD.DNo=D.DNo

go
--指定时间段内各种物料的领用数量
create procedure findGettime
(@time1 datetime,
 @time2 datetime)
as
select SN as 领取人,AN as 物料名,DN as 仓库名,getNum as 领取数量,getTime as 领取时间
from SA,A,D,S
where getTime between @time1 and @time2 and
      SA.SNo=S.SNo and SA.ANo=A.ANo and SA.DNo=D.DNo

数据库学习记录,仅供参考!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!