数据库课程设计——羽晴YuQ3，物资管理系统

附录1课程设计题目 

（1） 设计题目：物资管理及出借系统

        背景资料：

1) 摆放物资的有两间办公室，每间办公室都有若干个位置摆放物资。

2) 每个摆放物资的地方都有一个唯一的编号。

3) 物资的基本资料包括物资编号、物资名称、摆放位置、可用数量、租金、押金、状态（如：正常、维修等）、出借范围（如：院内、校内等）、类型（如：消耗品、非消耗品）以及描述等。

4) 用户注册后，管理员设置其角色，规定其可借用物品的范围。

5) 用户的角色用6种，包括系统管理员、部门管理员、团学用户、院内用户、校内用户及访客。访客没有出借物资的权限。

6) 用户通过在系统中提交物品出借预约。除校内用户外，均无需租金押金。管理员收到预约后会在24小时内回复是否通过其预约申请。

7) 用户按照预约申请的出借时间和预计归还时间进行物资的出借与归还，事后，管理员应及时做好归还记录。

8）管理员可以删除其他用户，增加、编辑、删除物资，增加、编辑、删除物资位置。

9）系统可以根据预约记录与归还记录生成每年的出借记录。

附录3课程设计报告书写格式要求 

为了培养学生严谨的工作态度，锻炼学生编写文档的能力，要求实验报告包括下面主要部分（包括附录1，附录2，附录3，附录4）： 

1、前言（课程设计的目的、意义、要求） 

本次课程设计目的及意义在于实践上学期学习的数据库原理的理论知识。将学到的数据库基本概念、基本原理、关系数据库的设计理论、设计方法用到实处。要求通过所学知识建立数据库模型，熟悉数据建模工具PowerDesigner的使用。充分掌握SQL语句的编写，并利用其创建数据库、表、视图以及存储过程和触发器等，以熟悉MicrosoftSQL Server的操作环境。把理论经验转化成实践经验。

(1)课程设计目的：

1) 加深对讲授内容的理解

《数据库系统》中有关数据库技术的基本理论、基本概念、设计与实现的方法和阶段性知识，光靠课堂讲授既枯燥无味又难以记住，但它们都很重要，要想熟练掌握，必须经过大量实践环节加深对它们的理解。

2) 通过课程设计，掌握数据库系统设计与开发的方法及步骤

数据库是一门应用性很强的学科，开发一个数据库系统需要集理论、系统和应用三方面为一体，以理论为基础，以系统（DBMS）作支柱，以应用为目的，将三者紧密结合起来。同时结合实际需要开发一个真实的数据库系统，对于较大型的系统可多人一起完成，但无论如何都应完成数据库的需求分析、数据的分析与建模、数据库的建立、数据库的开发与运行等全部过程。在此过程中将所学的知识贯穿起来，达到能够纵观全局，分析、设计具有一定规模的题目要求，基本掌握数据库系统设计与开发的基本思路和方法并且做到对知识的全面掌握和运用。

3) 培养学生自学以及主动解决问题的能力

通过本次设计，使同学能够主动查阅与数据库相关资料，掌握一些课堂上老师未曾教授的知识，从而达到培养学生自学以及主动解决问题的能力的目的。

(2)课程设计基本要求：

1)  课程设计应由学生本人独立完成，严禁抄袭，如果发现最后的设计基本相同者（系统需求分析与功能设计、数据库的概念设计、逻辑设计，数据库的实现与运行等内容基本相同），一经验收教师认定其抄袭行为，则成绩均为不及格。

2) 掌握所学的基础理论知识，数据库的基本概念、基本原理、关系数据库的设计理论、设计方法等。熟悉数据建模工具PowerDesigner与数据库管理系统Oracle（或者选择SqlServer）软件的使用。

3) 按时上机调试，认真完成课程设计。

4) 认真编写课程设计报告，请务必按照要求编写：课程设计报告的封面见附录2，课程设计的格式见附录3。

2、 需求分析 

物资管理及出借系统包括如下功能，见图1。

(1)  添加物资位置：管理员添加物资摆放地点的位置。 

(2)  编辑物资位置：管理员编辑物资摆放点的信息。 

(3)  删除物资位置：管理员删除某一物资摆放点位置信息。

(4)  添加物资：管理员新增一件物资，并填写其基本信息。 

(5)  修改物资信息：管理员编辑某一件物资的信息。 

(6)  删除物资：管理员删除某一件不存在的物资信息。 

(7)  查看预约记录：管理员查看未处理的预约申请。 

(8)  查看出借记录：管理员查看过去的出借记录。

(9)  添加出借记录：用户归还物资之后，管理员做好物资归还的记录。

(10) 编辑出借记录：用户取消预约成功的出借，管理员修改出借的记录。

(11) 删除出借记录：管理员将超过保存期限的出借记录删除。

(12) 修改用户权限：用户注册后，管理员根据其身份设置其角色已确定其可出借物资的范围。

(13) 更改其他用户密码：用户忘记密码后，管理员将其密码进行修改。

(14) 删除用户：管理员删除过期出借记录后，同时删除已毕业的用户。

3、 数据库概念结构设计 

物资管理及出借系统： 

(1) 在物资管理及出借系统应用中主要涉及的实体的属性

物资位置（编号，办公室号，位置名称）

物资（编号，名称，位置编号，数量，单位，图片名称，租金，押金，状态，出借范围，类型，剩下数量，描述）

角色（编号，角色名）

用户角色（角色编号，用户编号）  

用户（编号，用户名，密码，姓名，学院，组织，长号，短号）  

预约（编号，用户编号，物资编号，出借时间，预期归还时间，租金，押金，数量，状态，留言）

出借记录（编号，归还时间，银码，备注）

(2) 实体间的联系

物资位置与物资之间是1:n（n≥0）的联系；     

角色与用户角色之间是1:n（n≥0）的联系；

用户角色与用户之间是1：1的联系；

用户与预约是1：n（n≥0）的联系；

物资与预约是1：n（n≥0）的联系；

预约与出借记录是1：m(m=1或0)的联系；

(3) 物资管理及出借系统的E-R图，见图2。

4、 数据库逻辑结构设计 

(1) 逻辑结构设计图型描述，见图3。

(2) 逻辑结构设计列表描述，见表1。

序号	表名	中文名	作用
1	Locations	物资位置表	记录物资摆放点信息
2	Goods	物资表	记录物资信息
3	Role	角色表	记录角色信息
4	UserRole	用户角色表	记录每位用户对应的角色
5	User	用户表	记录用户信息
6	Reservation	预约表	记录用户预约情况
7	Records	出借记录表	记录用户出借物资信息

5、 数据库实现 

5.1建立数据库、数据表、视图、索引

5.1.1建立数据库

CREATE DATABASE [myDatabase]
CONTAINMENT = NONE
ON  PRIMARY 
(NAME=N'myDatabase_Data',FILENAME=N'D:\Wingtip\Wingtip\App_Data\myDatabase_Data.mdf' , SIZE = 4160KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON 
(NAME=N'myDatebase_log',FILENAME=N'D:\Wingtip\Wingtip\App_Data\myDatebase_log.ldf' , SIZE = 1040KB , MAXSIZE = 2048GB , FILEGROWTH = 10%);

5.1.2建立数据表

(1)物资位置表的建立

create table Locations
(
LocationID 		int			not null,
	OfficeID 		int 			not null,
	GoodsLocation 	nvarchar(30) not null,
	constraint 		location_PK 	primary key(LocationID),
		constraint 	location_officeID_check 	check(OfficeID=222 or OfficeID=214)
);

(2)物资表的建立

create table Goods
(
GoodsID		int         		not null,
Name      	nvarchar(100) 	not null,
LocationID  	int        		not null,
Amount     int         		not null,
Unit        	nvarchar(10)  	not null,
Image      	nvarchar(100) 	not null,
Rent       	float 			default 0.00 	not null,
Pledge     	float 			default 0.00,
Status      	int         		default 1 ,
Type       	int         		default 1 ,
RestAmount	int ,
Range      int         		default 1 ,
Synopsis    nvarchar(200),
CONSTRAINT check_goods_amount 	CHECK(amount>0),
CONSTRAINT check_goods_rent 		CHECK(rent>0),
CONSTRAINT check_goods_pledge 	CHECK(pledge>0),
CONSTRAINT check_goods_status		CHECK(status>0 and status<5),
CONSTRAINT check_goods_type 		CHECK(type>0 and type<=2),
CONSTRAINT check_goods_rest_amount CHECK(rest_amount>0),
CONSTRAINT check_goods_range 	CHECK(range>0 and range<5),
CONSTRAINT goods_pk 				PRIMARY KEY(GoodsID),
CONSTRAINT goods_fk 				
FOREIGN KEY(LocationID) 
			           REFERENCES Locations(LocationID) 
);

create table Role

(

ID                         int           notnull,

Name              nvarchar(30)not null,

CONSTRAINT     role_pk         PRIMARYKEY(ID)

);

(3)角色表的建立

create table Role
(
ID				int         	not null,
Name      		nvarchar(30) not null,
CONSTRAINT 	role_pk 		PRIMARY KEY(ID)
);

(4)用户表的建立

create table Users
(
ID         	int         	not null,
Name      	nvarchar(20) not null,
NickName	nvarchar(20) not null,
Institute		nvarchar(50) not null,
Organization	nvarchar(50) not null,
Password   	nvarchar(16) not null,
LongPhone  nchar(11) 	not null,
ShortPhone	nvarchar(11)	not null,
CONSTRAINT users_pk PRIMARY KEY(ID),
CONSTRAINT users_fk FOREIGN KEY(ID) 
			           REFERENCES UserRole(UserID) ON DELETE CASCADE
);

(5)用户角色表的建立

create table UserRole
(
UserID				int      not null,
RoleID      		int		not null,
CONSTRAINT 	userrole_pk 	PRIMARY KEY(UserID),
CONSTRAINT 	userrole1_fk FOREIGN KEY(UserID) 
			           REFERENCES Users(ID) ON DELETE CASCADE,
CONSTRAINT userrole2_fk
FOREIGN KEY(RoleID) REFERENCES Role(ID) 
);

(6)预约表的建立

create table Reservation
(
ID          int		not null,
UserID      int		not null,
GoodsID    int		not null,
LendTime   datetime	not null,
ExceptReturnTime datetime not null,
Rent       	float		not null,
Pledge     	float		not null,
Amount		int		not null,
Status      	int   	default 1,
Synopsis    nvarchar(100),
CONSTRAINT reservation_pk 				PRIMARY KEY(ID),
CONSTRAINT reservation_users_fk 		FOREIGN KEY(UserID) 
			           REFERENCES Users(ID) ON DELETE CASCADE,
CONSTRAINT reservatiRon_goods_fk 		FOREIGN KEY(GoodsID) 
			           REFERENCES Goods(GoodsID) ON DELETE CASCADE,
CONSTRAINT check_reservation_rent 		CHECK(Rent>=0),
CONSTRAINT check_reservation_pledge 	CHECK(Pledge>=0),
CONSTRAINT check_reservation_amount 	CHECK(Amount>0),
CONSTRAINT check_reservation_status 	CHECK(Status>=0 and Status<=5)
);

(7)出借记录表的建立

create table Records
(
ID			int		not null,
ReserveID   int		not null,
Returntime  datetime	not null,
CashNumber	nvarchar(100),
Synopsis   	nvarchar(150),
CONSTRAINT records_pk PRIMARY KEY(ID),
CONSTRAINT records_fk FOREIGN KEY(ReserveID) 
			           REFERENCES Reservation(ID) ON DELETE CASCADE
);

5.1.3 建立视图

  (1)用于查询和更新物资信息的视图定义

create view GoodsLocationView
(
GoodsID,Name,OfficeID,GoodsLocation,Amount,Unit,
Image,Rent,Pledge,Status,Type,RestAmount,Range,Synopsis)
as
select a.GoodsID,a.Name,b.OfficeID,b.GoodsLocation,a.Amount,a.Unit,
	a.Image,a.Rent,a.Pledge,a.Status,a.Type,a.Restamount,a.Range,a.Synopsis
from Goods a
	left join Locations b
	on a.LocationID=b.LocationID
with check option;

  (2)为方便查看出借记录，视图定义

create view RentLogs(ID,UserName,GoodsName,Amount,
					LendTime,ReturnTime,Rent,Pledge,Synopsis)
as
select a.ID,d.NickName,c.Name,a.Amount,a.LendTime,b.Returntime,a.Rent,a.Pledge,b.Synopsis
from Reservation a
	right join Records b
	on a.ID=b.ReserveID
		left join Goods c
		on a.GoodsID=c.GoodsID
			left join Users d
			on d.ID=a.UserID
with check option; 

(3) 为方便用户列表，视图定义

create view UserListView(Institue,Organization,Name, LongPhone,ShortPhone,Role)
as
select a.Institue,a.Organization,a.NickName,a.LongPhone,a.ShortPhone,c.Name
from Users a
	left join UserRole b
	on a.ID=b.UserID
		left join Role c
		on b.RoleID=c.ID
with check option; 

5.1.4 建立索引

create unique index GoodsIDIndex on Goods(GoodsID);
create unique index LocationIDIndex on Locations(LocationID);
create unique index UserIDIndex on Users(ID);
create index UserRoleIndex on UserRole(UserID,RoleID);
create unique index RoleIndex on Role(ID);
create unique index ReservationIndex on Reservation(ID);
create index RecordsIndex on Records(ReserveID,ID);

5.2数据入库

       系统包括物资位置、物资、角色、用户角色、用户、预约、出借记录，共有8张基本表,牵涉到大量数据的录入,又由于时间限制,采用事先在Excel中录入数据,然后使用SQLServer 2012数据导入/导出向导功能,直接将数据导入到相应的基本表中。各个基本表插入元组的存储功能，详见附录2。

5.3创建各个功能的存储过程

系统共创建了16个存储过程，具体列表（见表2）

编号	存储过程名称	定义	作用
P-1	LocaionsInsert	详见附录2-1	在Locations表中插入一元组
P-2	GoodsInsert	详见附录2-2	在Goods表中插入一元组
P-3	RoleInsert	详见附录2-3	在RoleInsert表中插入一元组
P-4	UserRoleInsert	详见附录2-4	在UserRole表中插入一元组
P-5	UsersInsert	详见附录2-5	在Users表中插入一元组
P-6	ReservationsInsert	详见附录2-6	在Reservations表中插入一元组
P-7	RecordsInsertInsert	详见附录2-7	在Records表中插入一元组

6运行结果（包括系统测试）

       本次的课程设计，我采用了网页的形式来实现其功能，以实现在网络上完成物品的预约和管理工作。

      登录管理员账号，点击管理功能，见图4。在这里列举了管理员的所有功能。一共分为四个部分，包括物资位置、物资、出借、用户四大方面的增删查改。

图4

图5左

图5右

        首先，点击“添加物资位置”，进入添加物资界面，见图5。如果出现错误，会出现图5左边的提示语句。添加成功或失败后，会出现提示语句，点击“立即查看”，即可查看详细信息，见图6。

图6左

图6右

        在管理页面点击“编辑物资位置”，在物资位置列表中，点击“编辑”，即可进入物资位置编辑界面，见图7和图8。如果不按要求填写信息的话，会出现错误提示，图8是修改成功后的显示效果。

图7

图8

图9左

图9右

        在物资位置列表，见图7中，点击删除，会显示图9左边的显示界面，可以选择确定删除或取消删除。点击确认，会出现图9右边显示效果。

图10左

图10右

        在管理页面，物资管理下点击“添加物资”，进入添加物资页面，如果输入错误，会出现图10左边的错误信息。正确填写信息后，点击添加物资，可见到图10右边的效果。点击立即查看，即可查看新增物资的详细信息。

图11左

图11右

        在管理员页面，点击“编辑物资”，转跳到物资列表，选择相应物资下的“编辑按钮”，进入编辑页面，见图11。编辑相应的信息，其中，在数量和租金、押金等输入框内加入了正则表达式，防止了非法输入。

图12左

图12右

        在物资列表中，选择“删除”，或点击图片进入物资的信息页面，点击“删除”，均可达到删除某件物资的效果，见图12。

接着，我们结合普通用户对系统的使用，表现我们实现出借管理和用户管理的功能。我们模拟有一位来自华南农业大学艺术学院学生会外联部的同学注册并出借物资的过程。首先，进入首页后，点击导航栏右上角的注册后，进入注册界面，见图13。

图13

成功注册后，系统会自动将其加入“访客”的角色中， 此时她还不能借用物资，管理员还需要根据实名验证后，才会加入相应的权限中。见图14，用户注册后， 显示的角色是访客，需要管理员用户管理，提升用户的权限。如图，管理员将用户的权限提升为“校内用户”，那么该用户就能出借对全校开放的物资。另外， 用户管理还能实现编辑用户信息，重置密码和删除账户的功能，见图15。

图14上

图14下

图15上

图15中

图15下

用户被提升权限后，可以搜索可出借物品，进行出借，见图16。

图16上

图16下

图17

        预约成功后，会自动转跳到用户当前的“我的预约”页面中，里面会显示当前等待通过的预约，见图17。在预约通过或被拒绝之前，用户可以点击“取消预约”以取消预约。同时，在该页面，也会显示用户已通过的预约，和未被批准的预约以及已经归还的出借历史。

附录1数据库逻辑结构定义

1. 物资位置（Locations）基本信息表，用于记录物资摆放信息，见表3。

属性名	数据类型	取值范围	主属性或外键	完整性
LocationID	int		PK	Not Null
OfficeID	Int	214或222		Not Null
GoodsLocation	Nvarchar(30)			Not Null

2. 物资（Goods）基本信息表，用于记录物资的基本信息，见表4。

属性名	数据类型	取值范围	主属性或外键	完整性
GoodsID	int		PK	Not Null
Name	Nvarchar(100)		主属性	Not Null
LocationID	int		FK	Not Null
Amount	Int	自然数		Not Null
Unit	nvarchar(10)			Not Null
Image	nvarchar(100)			Not Null
Rent	float	大于等于0
Pledge	float	大于等于0
Status	int	大于0小于5		Not Null
Type	int	1或2		Not Null
RestAmount	int	小于Amount
Range	int	大于0小于5		Not Null
Synopsis	nvarchar(200)

3. 角色（Role）基本信息表，用于记录角色信息，见表5。

属性名	数据类型	取值范围	主属性或外键	完整性
ID	int		PK	Not Null
Name	nvarchar(30)		主属性	Not Nul

4. 用户角色（UserRole）表，用于记录用户与角色之间的关系，见表6。

属性名	数据类型	取值范围	主属性或外键	完整性
UserID	int		PK，FK	Not Null
RoleID	int		FK，主属性	Not Null

5. 用户（Users）基本信息表，用于记录用户的基本信息，见表7。

属性名	数据类型	取值范围	主属性或外键	完整性
ID	int		PK	Not Null
Name	nvarchar(50)		主属性	Not Null
NickName	nvarchar(20)		主属性	Not Null
Password	nvarchar(16)			Not Null
LongPhone	nchar(11)	11位纯数字	主属性	Not Null
ShortPhone	nvarchar(11)	11位内纯数字
Institute	nvarchar(50)
Organization	nvarchar(50)

6. 出借记录（Records）表，存放用户归还物资的记录，见表8。

属性名	数据类型	取值范围	主属性或外键	完整性
ID	int		PK	Not Null
ReserveID	int		主属性，FK	Not Null
ReturnTime	datetime			Not Null
CashNumber	varchar(100)
Synopsis	nvarchar(150)

7. 预约（Reservations）表，用于存放用户预约物资的记录，见表9。

属性名	数据类型	取值范围	主属性或外键	完整性
ID	int		PK	Not Null
UserID	int		FK	Not Null
GoodsID	int		FK	Not Null
LendTime	datetime			Not Null
ExceptReturnTime	datetime	大于LendTime		Not Null
Rent	float	大于等于0
Pledge	float	大于等于0
Amount	int	小于物资总数		Not Null
Status	int	0~5之间
Synopsis	nvarchar(100)

附录2存储过程定义

1. LocationsInsert的定义（表Locations的存储过程）：

create procedure LocationsInsert
@LocationID		 int,
@OfficeID		 int,
@GoodsLocation nvarchar(30),
@Image		 	nvarchar(100)
as
insert into Locations
values (@LocationID,@OfficeID,@GoodsLocation,@Image);

2. GoodsInsert的定义（表Goods的存储过程）：

create procedure GoodsInsert
@GoodsID int,
@Name nvarchar(100),
@LocationID int,
@Amount int,
@Unit nvarchar(10),
@Image nvarchar(100),
@Rent float,
@Pledge float,
@Status int,
@Type int,
@RestAmount int,
@Synopsis nvarchar(200)
as
insert into Goods
values(@GoodsID,@Name,@LocationID,@Amount,@Unit,
@Image,@Rent,@Pledge,@Status,@Type,@RestAmount,@Synopsis);

3. RoleInsert的定义（表Role的存储过程）：

create procedure RoleInsert
@ID int,
@Name nvarchar(30)
as
insert into Role values(@ID,@Name);

4. UserRoleInsert的定义（表UserRole的存储过程）：

create procedure UserRoleInsert
@UserID int,
@RoleID int
as
insert into UserRole values(@UserID,@RoleID);

5. UsersInsert的定义（表Userse的存储过程）：

create procedure UserInsert
@ID int,
@Name nvarchar(50),
@NickName nvarchar(20),
@Password nvarchar(16),
@LongPhone nchar(11),
@ShortPhone nvarchar(11),
@Institue nvarchar(50),
@Organization nvarchar(50)
as
insert into Users 
values(@ID,@Name,@NickName,@Password,@LongPhone
,@ShortPhone,@Organization,@Institue);

6. ReservationInsert的定义（表Reservation的存储过程）：

create procedure ReservationInsert
@ID int,
@UserID int,
@GoodsID int,
@LendTime datetime,
@ExceptReturnTime
@Rent float,
@Pledge float,
@Amount int,
@Status int,
@Synposis varchar(100)
as
insert into Reservation
values(@ID,@UserID,@GoodsID,@LendTime,@ExceptReturnTime,@Rent
,@Pledge,@Amount,@Status,@Synposis);

7. RecordsInsert的定义（表Records的存储过程）：

create procedure RecordsInsert
@ID int,
@ReserveID int,
@ReturnTime datetime,
@CashNumber varchar(100),
@Synopsis varchar(150)
as
insert into Records
values(@ID,@ReserveID,@ReturnTime,@CashNumber,@Synopsis);

附录3数据查看和存储过程功能的验证

1. 基本表的数据查看(基于视图查询，以SQLServer为例)

1) 查看物资表中的数据，见图4。

图4

2) 查看用户表中的数据，见图5。

图5

2. 存储过程功能的验证（LocationsInsert和GoodsInsert为例）

1) 存储过程LocationsInsert功能的验证，见图6。

图6

2) 存储过程GoodsInsert功能的验证，见图7。

图7