数据库原理与应用课程设计------实验室设备管理系统sql

本文链接：https://blog.csdn.net/2301_79834881/article/details/135601429

想要文档(包含E-R图，源文件、备份文件等实验截图)的同学可以私聊我

一、设计题目

1、背景资料:

为了实现实验室设备的集中和自动化管理，需要一套网络版的设备运营和管理系统,

这样可以通过网络对各个实验室、办公室的设备集中和自动化管理。

2、内容:学生根据所学的数据库系统原理与程序设计的知识，针对实验室设备管理系

统，进行系统的需求分析，系统设计，数据库设计，编码，测试等，完成题目要求的功能

从而达到掌握开发-一个小型数据库的目的。

3、操作过程:

1)通过社会调查，选择一个实际应用数据库系统的课题。

2)进行系统需求分析和系统设计，写出系统分析和系统设计报告。

3)设计数据库模型并进行优化，确定数据库结构、功能结构和系统安全性和完整性要

求。

4)完成数据库定义工作，实现系统数据的数据处理和数据录入。

5)实现应用程序的设计、编程、优化功能，实现数据安全性、数据完整性和并发控制技

术等功能，并针对具体课题问题提出解决方法。

4、基本要求:

能实现以下主要功能:

基本设备管理

用户管理

故障设备管理

维修过程管理

二、设计目的

本次课程设计的主要目的是综合运用所学的数据库相关知识解决一个比较实际问题，侧重对数据库进行设计等相关内容的综合应用，使同学们能进一步熟悉掌握数据库的基础知识，进一步提升自己的解决问题和编程调试能力，为后续专业课程的学习打下良好的基础。

三、设计要求

1.自己独立完成，最终提交课程设计报告和实验成果。

2.课程设计报告格式(附后) :封面、题目、时间地点、目的及要求、内容、详细的步骤。

和代码编写过程(绘制出E-R图)、总结。

3. 按照实验课表安排进行，不得无故旷课。

4.实验设计内容必须以老师指定的题目为准，老师指定的题目为最终检查的依据。

四、需求分析

1、实验室设备管理:系统应具备实验室设备的基本信息管理、设备借出与归还管理、设备维修与保养等功能。

2、实验器材管理:系統应实现实验器材的采购、库存管理、使用与归还等环节的信息化管理。

3、实验人员管理:系统应对实验人员的信息进行记录和管理，包括个人资料、职务、所属单位等,方便实验室管理人员对实验人员情况进行掌握。

五、外部设计

本数据库主要采用微软公司的成熟数据库系统SQLServer2020，提供了较为灵活操作简单的功能，可以为数据库管理员和系统开发人员提供很好的支持。

SQL Server数据库

SQL是英文Structured Query Language的缩写，意思为结构化查询语言。SQL语言的主要功能就是同各种数据库建立联系，进行沟通。按照ANSI (美国国家标准协会)的规定，SQL 被作为关系型数据库管理系统的标准语言。SQL 语可以用来执行各种各样的操作，例如更新数据库中的数据，从数据库中提取数据等。

目前，绝大多数流行的关系型数据库管理系统，如Oracle、Sybase、 Microsoft SQL Server. Access 等都采用了SQL 语言标准[13]。虽然很多数据库都对SQL语句进行了再开发和扩展，但是包括Select、Insert, 、Update、 Delete. Create以及Drop在内的标准的SQL命令仍然可以被用来完成几乎所有的数据库操作。

六、数据库结构设计

6.1数据库逻辑结构

6.1.1系统涉及关系实体和对应关系

用户与购买设备信息：用户负责设备的购买，因此用户与购买记录之间存在一对多的关系。
实验室与购买设备信息：实验室是设备购买的单位，因此实验室与购买记录之间存在一对多的关系。
设备信息与设备状态：每台设备都有其当前的状态，因此设备信息与设备状态之间存在一对一的关系。
设备信息与报修：当设备出现故障需要报修时，需要关联到设备的原始信息，因此设备信息与报修记录之间存在一对一的关系。
设备信息与报废：当设备报废时，需要关联到设备的原始信息，因此设备信息与报废记录之间存在一对一的关系。
报修与报废：这两者都是对设备的处理方式，且可以多次进行，因此它们之间存在多对多的关系。

七、实验代码

create database 实验室设备管理系统
on
primary
(
	name = '实验室设备管理_data',
	filename = 'D:\实验室设备管理_data.mdf',
	size = 5MB,
	maxsize = 100MB,
	filegrowth = 5%
)
log on
(
	name = '实验室设备管理_log',
	filename = 'D:\实验室设备管理_log.ldf',
	size = 5MB,
	maxsize = 100MB,
	filegrowth = 5%
)

use 实验室设备管理系统
drop table if exists 用户
create table 用户
(
	用户ID char(8) primary key,
	用户名 nvarchar(10) not null,
	用户性别 nchar(1) default '男' check(用户性别='男'or 用户性别='女'),
	电话 char(11) unique not null,
	用户权限 int not null check (用户权限 <=3 and 用户权限 >= 0)
)
insert into 用户 values('3','小红','男','17865452652',3)

drop table if exists 实验室
create table 实验室
(
	实验室编号 char(8) primary key,
	实验室名称 nvarchar(10) unique not null,
	实验室地址 nvarchar(10) unique not null

)
insert into 实验室 values ('1','计算机研究中心','北京路')
insert into 实验室 values ('2','生物实验室','上海路')
insert into 实验室 values ('5','物理实验室','上海路13号')

drop table if exists 设备信息
create table 设备信息
(
	设备编号 char(8) primary key,
	设备名称 nvarchar(20) unique not null,
	型号 nvarchar(10),
	类别 nvarchar(5) default '计算机' check(类别 in('计算机','生物','电器','工具')), 
	规格 nvarchar(10),
	总数量 int check(总数量>=0),
	当前剩余数量 int check(当前剩余数量 >=0 ),
	生产厂家 nvarchar(10) not null
)

drop table if exists 购买设备信息
create table 购买设备信息
(
	购买ID int primary key identity(1,1),
	设备编号 char(8) unique ,
	实验室编号 char(8),
	设备名称 nvarchar(20)not null,
	型号 nvarchar(10),
	类别 nvarchar(5) default '计算机' check(类别 in('计算机','电器','工具','生物')), 
	购买日期 datetime default(getdate()) not null,
	规格 nvarchar(10),
	数量 int check(数量>=0),
	单价 money not null,
	购买人ID char(8) not null,
	生产厂家 nvarchar(10) not null,
	foreign key(实验室编号) references 实验室(实验室编号),
	foreign key(购买人ID) references 用户(用户ID)
)
insert into 购买设备信息 values ('2','5','黑笔','1','工具','2023.6.2','5',6,3,'1','中国')
insert into 购买设备信息 values ('3','2','鼠标','2','计算机','2023.12.23','20',10,100,'5','中国')

drop table if exists 设备状态
create table 设备状态
(
	设备编号 char(8) ,
	--设备ID   char(8) ,
	实验室编号 char(8),
	设备情况 nvarchar(5) default '正常' check(设备情况 in('正常','维修','报废'))
	primary key (设备编号,实验室编号),
	foreign key (设备编号) references 设备信息(设备编号),
	foreign key (实验室编号) references 实验室(实验室编号),
)

drop table if exists 报修 
create table 报修
(
	报修ID int primary key identity(1,1),
	设备编号 char(8) ,
	实验室编号 char(8),
	报修原因 nvarchar(50),
	报修日期 datetime default(getdate()) not null,
	维修日期 date,
	维修人员 nvarchar(10),
	维修费用 money,
	是否修复 nvarchar(1) check(是否修复 in ('是','否',null)),
	
	foreign key(设备编号,实验室编号) references 设备状态(设备编号,实验室编号),
)
insert into 报修 values('2','5','没墨水了','2023.12.26',null,null,null,null)
insert into 报修 values('3','2','鼠标不会滑动','2023.12.26',null,null,null,null)

drop table if exists 报废 
create table 报废
(
	报废ID int primary key identity(1,1),
	设备编号 char(8) ,
	实验室编号 char(8),
	报废原因 nvarchar(30),
	报废日期 datetime default(getdate()) not null,
	报废人员ID char(8)	
	foreign key (报废人员ID) references 用户(用户ID)
)
alter table 报废
add constraint FK_ID1 foreign key(设备编号,实验室编号) references 设备状态(设备编号,实验室编号)

insert into 报废 values('3','2','自然抛锚','','1')
insert into 报废 values('2','5','自然抛锚','','2')
/*
*
*触发器的创建
*
*/

drop trigger if exists 购买设备 
Go
create trigger 购买设备
on 购买设备信息
instead of insert
as
begin
--设备信息表的插入变量
	declare @EquipmentID as char(8)
	declare @LaboratoryID as char(8)
	declare @EquipmentName as nvarchar(20)
	declare @Model as nvarchar(10)
	declare @type as nvarchar(10)
	declare @guige as nvarchar(10)
	declare @count as int
	declare @maker as nvarchar(20)
	declare @buyerID char(8)
	
--设备大小号插入变量
	declare @pre_count as int
	declare @now_count as int

	select @EquipmentID=inserted.设备编号,@LaboratoryID=inserted.实验室编号,@EquipmentName=inserted.设备名称,@Model=inserted.型号,@type=inserted.类别,@guige=inserted.规格,@count=inserted.数量,@buyerID=inserted.购买人ID,@maker=inserted.生产厂家 from inserted
	if not exists(select * from 用户 where @buyerID=用户ID ) 
	begin
	print('实验室查无此人')
	rollback
	end
	else 
	begin
	if not exists(select * from 设备信息 where @EquipmentID=设备编号)
	begin
		insert into 设备信息(设备编号,设备名称,型号,类别,规格,总数量,当前剩余数量,生产厂家) values(@EquipmentID,@EquipmentName,@Model,@type,@guige,@count,@count,@maker)
		insert into 设备状态 values(@EquipmentID,@LaboratoryID,'正常')
	end
	else
	begin
		update 设备信息 set 总数量=总数量+@count,当前剩余数量=当前剩余数量+@count where @EquipmentID=设备编号
	end
	end
/*	select @pre_count=count(*) from 设备状态 where @EquipmentID=设备状态.设备编号
	set @now_count=@pre_count+@count
	set @pre_count=@pre_count+1
	while @pre_count<=@now_count
	begin
		insert into 设备状态(设备编号,实验室编号) values(@EquipmentID,@LaboratoryID)
		set @pre_count = @pre_count+1
	end
*/
	insert into 购买设备信息(设备编号,实验室编号,设备名称,型号,类别,购买日期,规格,数量,单价,购买人ID,生产厂家) select 设备编号,实验室编号,设备名称,型号,类别,购买日期,规格,数量,单价,购买人ID,生产厂家 from inserted
	
	select distinct 购买ID,购买设备信息.设备编号,购买设备信息.实验室编号,购买设备信息.设备名称,购买设备信息.型号,购买设备信息.类别,购买日期,购买设备信息.规格,数量,单价,购买人ID,用户名, 购买设备信息.生产厂家,总数量,当前剩余数量,设备情况 from 购买设备信息,设备信息,设备状态,用户 where 用户ID=@buyerID
end
Go

drop trigger if exists 报修操作
Go
create trigger 报修操作
on 报修
after insert
as
begin
	declare @EquipmentID as char(8)
	declare @EquipmentID2 as char(8)

	select @EquipmentID=inserted.设备编号,@EquipmentID2=inserted.实验室编号 from inserted

	update 设备状态 set 设备情况='维修' where @EquipmentID=设备编号 and @EquipmentID2=实验室编号
	update 设备信息 set 当前剩余数量=当前剩余数量-1 where @EquipmentID=设备编号
	
	select * from 报修
end
Go
--select * from 报修
--select * from 报废
--select * from 设备信息
--select * from 设备详细编号
drop trigger if exists 报废操作
Go
create trigger 报废操作
on 报废
after insert
as
begin
	
	declare @EquipmentID as char(8)
	declare @EquipmentID2 as char(8)
	declare @DesoryeerID as char(8)

	select @EquipmentID=inserted.设备编号,@EquipmentID2=inserted.实验室编号,@DesoryeerID=inserted.报废ID from inserted
	if not exists(select * from 用户 where @DesoryeerID=用户ID ) 
	begin
	print('实验室查无此人')
	rollback
	end

	if '正常'=(select 设备情况 from 设备状态 where @EquipmentID=设备编号 and @EquipmentID2=实验室编号)
	begin
		update 设备信息 set 当前剩余数量=当前剩余数量-1 where @EquipmentID=设备编号
	end


	update 设备状态 set 设备情况='报废' where @EquipmentID=设备编号 and @EquipmentID2=实验室编号
	select * from 设备信息 where @EquipmentID=设备编号
end
Go
/*
create trigger 设备借用
on 设备状态
after update
as
begin
	declare @EquipmentID as char(8)
	declare @EquipmentState as nvarchar(5)
	
	select @EquipmentID=inserted.设备编号,@EquipmentState=inserted.设备情况 from inserted
	if @EquipmentState='使用中'
	begin
	update 设备信息 set 当前剩余数量=当前剩余数量-1 where @EquipmentID=设备编号
	end
	if @EquipmentState='正常'
	begin
	update 设备信息 set 当前剩余数量=当前剩余数量+1 where @EquipmentID=设备编号
	end
end
Go
*/

drop trigger if exists 修理完成
Go
create trigger 修理完成
on 报修
after update
as
begin
	declare @EquipmentID as char(8)--大号
	declare @EquipemntNo as char(8)--小号
	
	select @EquipmentID=inserted.设备编号,@EquipemntNo=inserted.实验室编号 from inserted

	update 设备信息 set 当前剩余数量=当前剩余数量+1 where @EquipmentID=设备编号
	update 设备状态 set 设备情况='正常' where @EquipmentID=设备编号 and @EquipemntNo=实验室编号
	select * from 报修 where @EquipmentID=设备编号 and @EquipemntNo=实验室编号
	select * from 设备状态 where @EquipmentID=设备编号 and @EquipemntNo=实验室编号
end
Go

drop trigger if exists insert_用户
Go
create trigger insert_用户 on 用户 after insert as
select * from 用户
Go

drop trigger if exists insert_实验室
Go
create trigger insert_实验室 on 实验室 after insert as
select * from 实验室
Go

drop trigger if exists insert_设备信息
Go
create trigger insert_设备信息 on 设备信息 after insert as
select * from 设备信息
Go

drop trigger if exists insert_设备信息1
Go
create trigger insert_设备信息1 on 购买设备信息 after insert as
select * from 设备信息
Go

select * from 设备状态  where 设备情况='正常'

update 报修 set 维修日期=GETDATE(),维修人员='Lucy',维修费用='30',是否修复='是' where 设备编号='2' and 实验室编号='5'
update 报修 set 维修日期=GETDATE(),维修人员='Tom',维修费用='100',是否修复='是' where 设备编号='3' and 实验室编号='2'

select * from 用户 where 用户权限='1'

drop table 报修
drop table 报废
drop table 购买设备信息
drop table 设备状态
drop table 设备信息