数据库课程设计--学生宿舍管理系统

摘要：

学生宿舍管理系统是应对学生宿舍管理的现代化、网络化，逐步摆脱当前学生宿舍管理的人工管理方式，提高学生宿舍管理效率而开发的，它包括宿舍学生基本信息管理、楼道工人基本信息管理、宿舍楼基本信息管理、宿舍基本信息管理、宿舍事故基本信息管理、宿舍楼物品出入基本信息管理、宿舍楼保卫处基本信息管理、宿舍配备物品及处理管理等功能模块，并提供了对各功能模块的查询和更新功能，且这两种功能基本上是通过存储过程来实现的，其中宿舍学生基本信息管理、宿舍基本信息管理是系统开发的重点。

该系统开发由系统需求分析、概念设计、逻辑设计、数据库实施、系统调试和测试阶段组成。

目录

1、概述： ................................................ 2、课程设计的需求分析 .....................................

2.1、设计任务： ....................................... 2.1、设计要求： ....................................... 3、概念结构设计 ..........................................

3.1、概念结构设计工具（E-R模型） ..................... 3.2、入住登记子系统（局部） ...........................

3.2.1、子系统描述 ................................. 3.2.2、分E-R图 ...................................

3.2.3、说明....................................... 3.3、外出登记子系统 ...................................

3.3.1、子系统描述 ................................. 3.3.2、分E-R图 ................................... 3.3.3、说明....................................... 3.4、调整宿舍子系统 ...................................

3.4.1、子系统描述 ................................. 3.4.2、分E-R图 ................................... 3.4.3、说明....................................... 3.5、宿舍智能分配子系统 ...............................

3.5.1、子系统描述 ................................. 3.5.2、分E-R图 ................................... 3.5.3、说明....................................... 3.6、信息查询子系统 ...................................

3.6.1、子系统描述 ................................. 3.6.2、分E-R图 ................................... 3.6.3、说明....................................... 3.7、总体E-R图 ....................................... 4、逻辑结构设计 ..........................................

4.1、关系数据模式 .....................................

4.1.1、学生信息（stu_info） ....................... 4.1.2、宿舍信息(dorm_info) ........................ 4.1.3、出入信息（inout_info） .....................

4.1.4、入住信息(stay_info) ........................ 4.1.5、调整信息 ................................... 4.2视图的设计 ........................................

4.2.1宿舍信息表视图 ..............................

5、数据库物理设计与实施 ...................................

5.1、数据库应用的硬件、软件环境介绍 ................... 5.2、物理结构设计 ..................................... 5.3、索引的设计 ....................................... 5.4、建立数据库 .......................................

5.4.1、创建宿舍信息表 ............................. 5.4.2、创建学生信息表 ............................. 5.4.3、创建出人登记信息表 ......................... 5.4.4、创建入住信息登记表 ......................... 5.4.5、创建宿舍调整登记表 ......................... 5.5、加载测试数据 .....................................

5.5.1、加载住宿信息 ............................... 5.5.2、记载学生信息 ............................... 5.5.3、加载宿舍信息 ...............................

6、数据操作与实现 ........................................

6.1、数据查询操作 ..................................... 6.2、数据更新操作 ..................................... 6.3、数据维护操作 .....................................

6.3.1同步更新触发器 ..............................

6.3.2、按学院分配宿舍存储过程 .....................

7、收获、体会和建议....................................... 8、主要参考文献。 ........................................ 备注 .....................................................

任务分配 .............................................

1、概述：

旧的手工纪录的宿舍管理方式已经不能适应高速发展的信息化时代，新的宿舍管理系统开发出来之后，学校的现有的宿舍信息管理将有很大的改观，由过去的人工方式转变为计算机方式，由效率低、数据冗余、易产生错误转变为检索迅速、查找方便、可靠性高、存储量大。这些优点能够极大地提高效率,也是学校科学化、正规化管理的重要条件。学生宿舍管理系统采用的是计算机化管理，系统做的尽量人性化，使用者会感到操作非常方便，管理人员需要做的就是将数据输入到系统的数据库中去。由于数据库的存储容量相当大，而且比较稳定，适合较长时间的保存，也不容易丢失。这无疑是为信息存储量比较大的学校提供了一个方便、快捷的操作方式。

2、课程设计的需求分析

2.1、设计任务：

学生宿舍是同学最为熟悉的领域，假定学校有多栋宿舍楼，每栋楼有多层，每层有多个寝室，每个寝室可住多名学生，学生宿舍管理系统对学校的学生宿舍进行规范管理，其管理的对象如下： ·宿舍信息：编号、楼层、床位数、单价等。

·学生：学号、姓名、性别、年龄、所在院系、年级、电话等。 每个宿舍最多可以住4位同学，每个同学只能在一个宿舍，不同

宿舍的费用标准可以不同。不同院系、年级的同学可以住同一间宿舍。

2.1、设计要求：

系统要能够对宿舍、学生、住宿信息进行登记、调整，并能随时进行各种查询、统计等处理。包括： ·寝室分配：根据院系、年级分配寝室。

·学生管理：实现入住学生信息的登记、维护和查询功能。 ·信息查询：按公寓楼号、学生姓名等查询住宿信息。 ·出入登记（可选）：对学生进出公寓的情况进行登记、实现基本的出入监控功能

3、概念结构设计

3.1、概念结构设计工具（E-R模型） 3.2、入住登记子系统（局部）

主要是学生入住的管理，包括学生入住的申请，查询是否存在该学生，查询是否有空余的宿舍以及宿舍的分配

可以通过这个系统来解决学生入住的申请和分配等各种问题

3.3、外出登记子系统

子系统描述

主要是学生外出的管理问题，包括学生外出的申请，外出的审

核，外出的等级等为题。

说明

可以通过这个系统解决学生外出的登记的各种问题。

3.4、调整宿舍子系统

子系统描述

主要是学生宿舍的调整，宿舍表的更新问题。

分E-R图

可以通过这个系统解决学生宿舍的调整问题，包括宿舍的申请调整，调整原因的判断以及调整后宿舍表的更新问题。

3.5、宿舍智能分配子系统

主要是学生宿舍的分配问题。

可以通过这个子系统来分配学生的宿舍，包括按学院分配以及按年级分配。

3.6、信息查询子系统

主要是查询学生宿舍的问题。

可以通过子系统来查询学生的宿舍包括按公寓查询以及按学生名字查询。

3.7、总体E-R图

4、逻辑结构设计

4.1、关系数据模式

字段 描述 数据类数据长NULL 型 Stu_num 学号 name sex age fac 姓名 性别 年龄 Int 度 N N N N N Primarykey Y N N N N 男/女 0-99 约束 varchar 12 varchar 4 int 所在学varchar 50 院 class 所在班varchar 50 级 N N celphone Entry_date 字段 电话 varchar 16 N N 入学日date 期 描述 N N 数据类数据长NULL 型 度 N Primarykey Y 约束 dorm_nu宿舍编int m flo 号 所在楼int 层 N N 0-20 Bad_amount 床位数 Int N N <=4 Avi_bad 可用床Int 位数 unit_price 字段 描述 单价 float Y N <=4 N N 数据类数据长NULL 型 度 N Primarykey foreignkey 约束 stu_num 学号 int dorm_nu所住宿int m 舍 N foreignkey left_ti离开时Datetim me 间 e N N back_ti回来时Datetim me 间 e N N reason 离开原varchar 50 因 over_ti是否晚varchar 2 N N N N 是/否 me 字段 归 描述 数据类数据长NULL 型 度 N Primarykey foreignkey int N foreignkey 约束 dorm_nu宿舍编int m 号 stu_num 学号 Have_in 已住人int 数 in_date 入住日date 期 调整信息 字段 描述 N N <=4 N N 数据类数据长NULL 型 度 N N N Primarykey Y N N 约束 stu_num 学号 src dst int 原宿舍 int 调后宿int 舍 ch_date 调整日date 期 N N ch_reas调整原varchar 50 N N on 因 4.2视图的设计

创建查看宿舍信息表的视图，其中应该包含宿舍的所有信息。并且应该按可用床位数递减 create view view_dorm AS

SELECT top 100

[dorm_num],[flo] ,[bad_amount] ,[unit_price],[avi_bad]

FROM dorm_info order by avi_bad desc

创建按宿舍号查看住宿信息的视图，其中应包含住宿登记时的所有信息，并且按照宿舍号递减排列 create view view_stay_bydorm AS

SELECT top 100 dorm_num, stu_num, have_in, in_date FROM stay_info order by dorm_num desc

创建按学号查看住宿信息的视图，其中应包含住宿登记时的所有信息，并且按照宿舍号递减排列 create view view_stay_bystu AS

SELECT top 100 dorm_num, stu_num, have_in, in_date

FROM stay_info order by stu_num desc

创建查看出入信息的视图，其中应包含出入登记时的所有信息，并且按照离开时间递减排列

create view view_io_info as

SELECT top 100 stu_num, dorm_num, left_time, back_time, reason, over_time

FROM dbo.inout_info order by left_time

5、数据库物理设计与实施

5.1、数据库应用的硬件、软件环境介绍

电脑配置为AMD Athlon(速龙) II X2 260 双核 处理器，2G内存，Windows XP 专业版 32位 SP3 ( DirectX 9.0c )操作系统，安装mysql 数据库服务做测试。

5.2、物理结构设计

考虑到索引能加快查询的速度，所以在需要经常进行查询的列创建索引。

其中学生信息的学号、宿舍信息的宿舍编号、入住信息中学生的学号和宿舍的编号，这几个列都需要创建索引。由于前两项已经是主键，所以无需额外创建索引。现在为后两项创建索引。

5.3、索引的设计

为入住信息中学生的学号和宿舍的编号创建索引： create unique index

入住信息中学生的学号

ix_stay_info1 on stay_info(stu_num) create index

宿舍的编号 ix_stay_info2 on

stay_info(dorm_num)

5.4、建立数据库

首先创建名为sdms的学生宿舍管理数据库 ，create database sdms. use sdms

create table dorm_info(

dorm_num int not null primary key, flo int not null,

bad_amount int not null , avi_bad int null ,

unit_price float not null )

并且创建约束，使床位数和可用床位数不能大于4个，因为一个宿舍最多能住四个人,并且楼层数在0-20之间。

alter table dorm_info add constraint ck_bad check(bad_amount<=4)

alter table dorm_info add constraint ck_avi check(avi_bad<=4)

alter table dorm_info add constraint ck_flo check(flo>=0 and flo<=20) create table stu_info(

stu_num int not null primary key, name varchar(12) not null, sex varchar(4) not null , age int not null,

fac varchar(50) not null, class varchar(50) not null, celphone varchar(16) not null, entry_date date not null )

创建约束，性别只能是男或女 ，并且年龄只能在0-99之间 alter table stu_info add constraint ch_sex check(sex in('男','女'))

ALTER TABLE stu_info ADD constraint ck_age check(age>=0 and age<=99) create table inout_info(

stu_num int not null references stu_info(stu_num) ,

dorm_num int not null references dorm_info(dorm_num) ,

left_time datetime not null, back_time datetime not null, reason varchar(50) not null, over_time varchar(2) not null

)

创建约束，是否晚归字段这能选择是或者否。

alter table inout_info add constraint ck_ot check(over_time in ('是','否')) create table stay_info(

dorm_num int not null references dorm_info(dorm_num),

stu_num int not null references stu_info(stu_num), have_in int not null, in_date date )

创建约束，已住人数不能超过4个.

alter table stay_info add constraint ck_in check(have_in<=4)

create table change_info (

stu_num int not null primary key,

src int not null, dst int not null, ch_date date not null,

ch_reason varchar(50) not null )

5.5、加载测试数据

创建一个存储过程用于录入住宿信息。 USE [sdms] GO

SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE PROCEDURE [dbo].[insert_stay_info] @dorm_num int ,@stu_num int ,@have_in int AS BEGIN

SET NOCOUNT ON; insert into stay_info

values(@dorm_num,@stu_num,@have_in,GETDATE()) END

其中包括要住宿的宿舍号，要住宿的学生学号，该宿舍应经住了多少人。如图所示：

单击确定后数据成功的录入到数据库的住宿信息表中，如图： 创建一个存储过程用于录入学生信息。 USE [sdms] GO

SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE PROCEDURE [dbo].[insert_stu_info]

@stu_num int,@name varchar(12),@sex varchar(4),@fac varchar(50),@class varchar(50),@celphone varchar(16) ,@age int AS BEGIN

SET NOCOUNT ON; insert into stu_info

values(@stu_num,@name,@sex,@fac,@class,@celphone,GETDATE(),@age) END

其中包括要住宿的学号、姓名、性别、所在学院、班级、电话、年龄。

如图所示：

单击确定后数据成功的录入到数据库的学生信息表中，如图： 创建一个存储过程用于录入宿舍信息。 SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE PROCEDURE insert_dorm_info

@dorm_num int,@flo int ,@bad_amount int ,@unit_price float,@avi_bad int AS BEGIN

SET NOCOUNT ON;

insert into dorm_info

values(@dorm_num,@flo,@bad_amount,@unit_price,@avi_bad) END GO

其中包括要住宿的宿舍号、所在楼层、床位位数、单价、空余床位。如图所示：

击确定后数据成功的录入到数据库的宿舍信息表中，如图：

6、数据操作与实现

根据需求中给出的数据处理要求，设计访问数据库的具体要求，并用SQL语言加以实现。运行SQL语句进行测试。

6.1、数据查询操作

创建存储过程view_dorm_bydrom，实现按公寓查找宿舍的住宿信息。

CREATE PROCEDURE view_dorm_bydrom

@dorm_num int //定义变量用于等待用户输入宿舍号 AS BEGIN

SET NOCOUNT ON;

SELECT * from dorm_info where dorm_num=@dorm_num //查询出与输入宿舍号相符的结果 END 结果如图： 单击确定后：

创建存储过程view_dorm_bystu，实现按学号查询学生的住宿信息。

CREATE PROCEDURE view_dorm_bystu @stu_num int AS

BEGIN

SET NOCOUNT ON;

SELECT * from stay_info where stu_num=@stu_num END

执行存储过程结果如图所示：

单击“确定”，显示出学生在哪个宿舍住，该宿舍住着几个人，什么时候入住

6.2、数据更新操作

宿舍调整登记。创建一个存储过程用于登记个别学生调整宿舍的记录。同时跟新原来的住宿信息表，使得调整后学生的宿舍信息表能同步进行。这里避免创建触发器的繁杂，只使用了更新的语句。 CREATE PROCEDURE change_dorm

@stu_num int ,@dst_dorm int,@reason varchar(50) AS BEGIN

SET NOCOUNT ON;

declare cur1 cursor for

SELECT dorm_num from stay_info where stu_num=@stu_num

--查询需要调整的人员的原来住的宿舍 open cur1

declare @src int --声明游标以获取查询结果 fetch cur1 into @src insert into

change_info(stu_num,src,dst,ch_date,ch_reason) values(@stu_num,@src,@dst_dorm,GETDATE(),@reason)

update stay_info set dorm_num=@dst_dorm where stu_num=@stu_num

--将调整的信息插入到调整登记信息表中 close cur1 END

执行存储过程如下：

需要登记的信息成功录入到调整信息表中 原住宿信息表数据。 调整宿舍后：

6.3、数据维护操作

是系统使用过程中，需要使用一个触发器来同步信息。例如当有学生要住宿时，该宿舍已住人数要增加1，而宿舍信息中的相应空余床位数要减1。所以在住宿登记表中使用以下触发器。 SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE TRIGGER tri1 ON stay_info AFTER INSERT AS BEGIN

SET NOCOUNT ON;

declare @dorm_num int,@have_in int declare cur2 cursor for

select dorm_num ,have_in from stay_info open cur2 fetch cur2

while(@@FETCH_STATUS<>-1) begin

fetch next from cur2 into @dorm_num,@have_in update stay_info set have_in=(@have_in+1) where dorm_num=@dorm_num --更新相应宿舍的已住人数，增加1.

update dorm_info set avi_bad=((select avi_bad from dorm_info where dorm_num=@dorm_num)-1) where dorm_num=@dorm_num --更新宿舍信息表中的可用床位数（空

余床位数） end

close cur2 END GO

执行登记住宿信息表的存储过程 插入前： 插入后：

创建一个存储过程，用于按学院分配宿舍。使得能按同学院的学生能尽量的在同一宿舍住。 USE [sdms] GO

SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE PROCEDURE [dbo].[allowdorm_byfac] @fac varchar(20) AS BEGIN

declare @offset int,@bad_amount int,@sum int set @offset=1

set @bad_amount=1

set @sum=(select distinct(stu_num) from stu_info where fac=@fac) while(@sum>0) begin

declare @stu_num int,@dorm_num int

set @stu_num=(SELECT top (@bad_amount) stu_num from stu_info where fac=@fac and stu_num not in(select top (@offset) stu_num from stu_info where fac=@fac))

set @dorm_num=(select dorm_num from dorm_info where avi_bad=4)

insert into stay_info

values(@dorm_num,@stu_num,4,GETDATE()) set @sum=@sum-1 end END

执行存储过程，如图：

7、收获、体会和建议

做课程设计的这段时间来学到了很多东西，以前对SQL，只会照着书上的SQL语句写，没有对以前的知识进行系统的了解，没有把各

个部分知识结合在一起。通过做这次课程设计，我对数据库设计的各个方面都加深了理解，只有数据流程图和数字字典做好了，才能进行下面的E-R图设计，仔仔细细做才能不出问题。体会到把知识应用于实践是不容易的，学到了课本上没有的知识，只有实践才能找出自己的问题。

通过本次课程设计我对数据库的理解更加深刻了，清楚的认识了视图、索引、存储过程、触发器、一对多、多对多的设计模型。并且在这个宿舍管理设计中也都用上了。以前都只用mysql进行开发，虽然都支持标准的sql语句，但是在一些细节方面还是值得注意的，比如sql server 中独特的TOP语句就值得考量。在其他数据库中的LIMIT语句虽然更加强大，sql server也有他方便的地方。通过本次我合作自主的制作本作品对数据库有了一个很好的整体理解，虽然有不足之处，但是自己完成确实有趣味。

由于时间比较紧，我们所做的课程设计肯定还存在许多的不足之处，程序中也存在许多小错误，在以后的学习中会慢慢改过来努力做得更好。

8、主要参考文献。

《SQL Server从入门到精通》 清华大学出版社 明日科技SQL Server开发团队编著

数据库原理与应用技术:SQL Server作者：赵彦 出版社：清华大学出版社

MySQL技术内幕SQL编程作者：姜承尧 出版社：机械工业出版社