数据库实践课程之宿舍管理系统

一、系统需求分析

1.1 系统描述

随着社会的发展以及教育水平的提高，当今社会在校生的数量越来越庞大，使用传统的方式对学生的信息进行管理效率非常低下。在互联网技术高度发达的今天，使用数据库技术对学生的住宿信息进行管理能够使学校对学生信息的管理更加的高效，检索迅速，查找方便，可靠性高，存储量大。此外，使用数据库技术可以使得学生信息出错的可能性大大降低，还可以使学生住宿信息长时间稳定地保存，不易丢失。可见，开发出这样的一个数据库具有很强的显示意义。

1.2 数据存储需求

宿舍管理系统数据库需要存储如下信息：

系统管理员的用户名，密码，真实姓名，性别，电话；宿管老师的的用户名，密码，真实姓名，性别，电话；学生的学号，用户名，密码，真实姓名，院系信息，性别，电话；宿舍楼的名称，类型；寝室的名称，大小，现居人数；学生缺寝的日期，原因，登记老师是谁；学生的当前状态（入住还是迁出），以及相应的登记时间。

各种数据之间的关系：

寝室与学生是一对多的关系，一个寝室下可有多名学生；宿舍楼与寝室是一对多的关系，一栋宿舍楼下可有多个寝室；宿管老师与宿舍楼是多对多的关系，一个宿管老师可以负责多个宿舍楼，一个宿舍楼也可以由多个宿管老师负责；宿管老师与学生是多对多的关系，一个宿管老师可以负责多个学生的缺寝情况，一个学生也可以由多个宿管老师负责登记缺寝信息；系统管理员可以获取所有的数据信息，拥有最高的权限。

1.3 系统常做的查询与更新

经常做的查询，或许对创建索引有影响的：

根据学生的入住状态，姓名，学号，院系，查询学生相关信息根据宿管老师的姓名，电话，查询宿管老师相关信息根据宿舍楼的名称，查询宿舍楼相关信息根据寝室所在楼宇，寝室号，寝室大小，现有人数，查询寝室相关信息

根据经常做的查询，需要创建有关视图的：

学生信息 + 寝室信息视图宿舍楼信息 + 寝室信息视图

关于更新

当有学生入住某一寝室时，需要使相应的寝室现有人数加一当有学生迁出某一寝室时，需要使相应的寝室现有人数减一当有学生调换寝室时，需要使原本的寝室现有人数减一，新的寝室现有人数加一学生只能入住还有空余位置的寝室

关于删除

当删除某一寝室时，其中不能有学生入住

1.4 应用程序功能

前台的主要功能如下：

对于学生：

登录以及退出系统。每个学生可以查看自己的缺寝记录。

对于宿管老师：

登录以及退出系统。每个宿管老师可以查看自己负责的宿舍楼中学生的基本信息以及缺寝记录。

对于系统管理员：

登录以及退出系统。查看宿管老师的基本信息。查看学生的基本信息以及缺寝记录。查看宿舍楼的基本信息。查看寝室的基本信息。

后台的主要功能如下：

对于学生：

可以修改自己的密码。

对于宿管老师：

可以修改自己的密码。可以对自己负责的宿舍楼中的学生添加缺寝记录。

对于系统管理员：

可以修改自己的密码。可以添加，修改，删除宿管老师。可以添加新入住的学生，修改学生的基本信息，删除指定的学生。可以添加新的宿舍楼，修改宿舍楼的基本信息，查看每栋宿舍楼的负责老师，也可添加或移除每栋楼的负责老师。可以添加新的寝室，修改寝室的基本信息，删除指定的寝室。可以安排指定学生的寝室调换。可以迁出已经毕业的学生。可以查看所有指定学生的缺寝记录。

二、数据库概念设计

2.1 确定实体和属性

分析宿舍管理系统的系统需求，将系统中设计的人、物进行抽象，得到了系统的实体如下：

系统管理员实体集。属性包括：管理员 ID，用户名，登录密码，真实姓名，性别，电话。宿管老师实体集。属性包括：宿管老师 ID，用户名，登录密码，真实姓名，性别，电话。学生实体集。属性包括：学生 ID 即学号，用户名，登录密码，真实姓名，院系信息，性别，电话。宿舍楼实体集。属性包括：宿舍楼 ID，宿舍楼名称，宿舍楼类型。寝室实体集。属性包括：寝室 ID，寝室名称，寝室大小，寝室现有人数。

2.2 E-R 图

系统 E-R 图如图 2-1 所示：

图 2-1 E-R 图

三、数据库逻辑结构设计

3.1 关系模式设计

根据概念结构设计得到的 E-R 图和转换规则，得到如下关系模式（主键用下划线标出，外键用蓝色标志并予以说明）：

系统管理员表(管理员 ID，用户名，登录密码，真实姓名，性别，电话)宿管老师表(宿管老师 ID，用户名，登录密码，真实姓名，性别，电话)学生表(学生 ID 即学号，用户名，登录密码，真实姓名，院系信息，性别，电话)宿舍楼表(宿舍楼 ID，宿舍楼名称，宿舍楼类型)寝室表(寝室 ID，寝室名称，寝室大小，寝室现有人数)缺寝记录表(缺寝记录 ID，被登记学生的 ID，登记老师的 ID，登记日期，缺寝原因)宿舍楼-寝室表(宿舍楼-寝室对 ID，宿舍楼 ID，寝室 ID)宿舍楼-教师表(宿舍楼-教师对 ID，宿舍楼 ID，教师 ID)寝室-学生表(寝室-学生对 ID，寝室 ID，学生 ID，学生入住状态，登记日期)

3.2 基本表设计

基本表设计如表 3-1~3-9 所示。

表 3-1：系统管理员表的设计

表 3-2：宿管老师表的设计

表 3-3：学生表的设计

表 3-4：宿舍楼表的设计

表 3-5：寝室表的设计

表 3-6：缺寝记录表的设计

表 3-7：宿舍楼-寝室表的设计

表 3-8：宿舍楼-教师表的设计

表 3-9：寝室-学生表的设计

四、数据库物理设计和实施

4.1 数据库的创建

使用 Navicat for MySQL 建立寝室管理系统的数据库，数据库基本信息如下：

表 4-1：寝室管理系统的数据库参数表

4.2 创建基本表

<code>CREATE TABLE `admin` (

`Admin_ID` int(11) NOT NULL AUTO_INCREMENT,

`Admin_Username` varchar(30) NOT NULL,

`Admin_Password` varchar(30) NOT NULL,

`Admin_Name` varchar(30) NOT NULL,

`Admin_Sex` varchar(10) DEFAULT NULL,

`Admin_Tel` varchar(30) DEFAULT '',

PRIMARY KEY (`Admin_ID`),

UNIQUE KEY `Admin_Username` (`Admin_Username`)

ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `building` (

`Building_ID` int(11) NOT NULL AUTO_INCREMENT,

`Building_Name` varchar(30) NOT NULL,

`Building_Type` varchar(30) NOT NULL,

PRIMARY KEY (`Building_ID`)

ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `dormitory` (

`Dormitory_ID` int(11) NOT NULL AUTO_INCREMENT,

`Dormitory_Name` varchar(30) NOT NULL,

`Dormitory_Size` int(11) NOT NULL,

`Dormitory_Now` int(10) unsigned zerofill NOT NULL DEFAULT '0000000000',

PRIMARY KEY (`Dormitory_ID`)

ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `dor_bui` (

`DB_ID` int(11) NOT NULL AUTO_INCREMENT,

`DB_DormitoryID` int(11) NOT NULL,

`DB_BuildingID` int(11) NOT NULL,

PRIMARY KEY (`DB_ID`),

KEY `DB_DormitoryID` (`DB_DormitoryID`),

KEY `DB_BuildingID` (`DB_BuildingID`),

CONSTRAINT `dor_bui_ibfk_1` FOREIGN KEY (`DB_DormitoryID`) REFERENCES `dormitory` (`Dormitory_ID`) ON DELETE CASCADE ON UPDATE CASCADE,

CONSTRAINT `dor_bui_ibfk_2` FOREIGN KEY (`DB_BuildingID`) REFERENCES `building` (`Building_ID`) ON DELETE CASCADE ON UPDATE CASCADE

ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `log` (

`Log_ID` int(11) NOT NULL AUTO_INCREMENT,

`Log_StudentID` int(11) NOT NULL,

`Log_Date` varchar(30) NOT NULL,

`Log_Reason` varchar(255) NOT NULL,

`Log_TeacherID` int(11) DEFAULT NULL,

PRIMARY KEY (`Log_ID`),

KEY `absence_ibfk_1` (`Log_StudentID`),

KEY `Log_TeacherID` (`Log_TeacherID`),

CONSTRAINT `log_ibfk_1` FOREIGN KEY (`Log_StudentID`) REFERENCES `student` (`Student_ID`) ON DELETE CASCADE ON UPDATE CASCADE,

CONSTRAINT `log_ibfk_2` FOREIGN KEY (`Log_TeacherID`) REFERENCES `teacher` (`Teacher_ID`)

ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `student` (

`Student_ID` int(11) NOT NULL AUTO_INCREMENT,

`Student_Username` varchar(30) NOT NULL,

`Student_Password` varchar(30) NOT NULL,

`Student_Name` varchar(30) NOT NULL,

`Student_Dept` varchar(30) DEFAULT NULL,

`Student_Tel` varchar(30) DEFAULT NULL,

`Student_Sex` varchar(10) DEFAULT NULL,

PRIMARY KEY (`Student_ID`),

UNIQUE KEY `Student_Username` (`Student_Username`)

ENGINE=InnoDB AUTO_INCREMENT=71 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `stu_dor` (

`SD_ID` int(11) NOT NULL AUTO_INCREMENT,

`SD_StudentID` int(11) NOT NULL,

`SD_DormitoryID` int(11) NOT NULL,

`SD_State` varchar(30) NOT NULL,

`SD_Date` varchar(30) NOT NULL,

PRIMARY KEY (`SD_ID`),

KEY `SD_StudentID` (`SD_StudentID`),

KEY `SD_DormitoryID` (`SD_DormitoryID`),

CONSTRAINT `stu_dor_ibfk_1` FOREIGN KEY (`SD_StudentID`) REFERENCES `student` (`Student_ID`) ON DELETE CASCADE ON UPDATE CASCADE,

CONSTRAINT `stu_dor_ibfk_2` FOREIGN KEY (`SD_DormitoryID`) REFERENCES `dormitory` (`Dormitory_ID`) ON DELETE CASCADE ON UPDATE CASCADE

ENGINE=InnoDB AUTO_INCREMENT=72 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `teacher` (

`Teacher_ID` int(11) NOT NULL AUTO_INCREMENT,

`Teacher_Username` varchar(30) NOT NULL,

`Teacher_Password` varchar(30) NOT NULL,

`Teacher_Name` varchar(30) NOT NULL,

`Teacher_Sex` varchar(10) DEFAULT NULL,

`Teacher_Tel` varchar(30) DEFAULT NULL,

PRIMARY KEY (`Teacher_ID`),

UNIQUE KEY `Teacher_Username` (`Teacher_Username`)

ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `tea_bui` (

`TB_ID` int(11) NOT NULL AUTO_INCREMENT,

`TB_TeacherID` int(11) NOT NULL,

`TB_BuildingID` int(11) NOT NULL,

PRIMARY KEY (`TB_ID`),

KEY `tea_bui_ibfk_1` (`TB_TeacherID`),

KEY `tea_bui_ibfk_2` (`TB_BuildingID`),

CONSTRAINT `tea_bui_ibfk_1` FOREIGN KEY (`TB_TeacherID`) REFERENCES `teacher` (`Teacher_ID`) ON DELETE CASCADE ON UPDATE CASCADE,

CONSTRAINT `tea_bui_ibfk_2` FOREIGN KEY (`TB_BuildingID`) REFERENCES `building` (`Building_ID`) ON DELETE CASCADE ON UPDATE CASCADE

ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4;

4.3 触发器设计

当有新的学生入住时，对应的寝室现有人数要加一：

CREATE TRIGGER stu_dor_insert_after_trigger after insert

ON stu_dor FOR EACH ROW

BEGIN

update dormitory set Dormitory_Now=Dormitory_Now+1 where Dormitory_ID=new.SD_DormitoryID;

end;

4.4 视图设计

学生信息 + 寝室信息视图：

create view stu_dor_view as

select * from Student, stu_dor, Dormitory

where SD_StudentID=Student_ID and SD_DormitoryID=Dormitory_ID;

宿舍楼信息 + 寝室信息视图：

create view dor_bui_view as

select * from Dormitory,Building,dor_bui where Dormitory_ID=DB_DormitoryID and Building_ID=DB_BuildingID;

五、应用程序设计

5.1 开发及运行环境介绍

系统使用的开发工具是 MyEclipse 2017，Navicat for MySQL运行环境是 Tomcat v8.5开发采用 Struts2 + JavaBean + JSP + MySQL 的实现方式测试浏览器为 Chrome 和 MyEclipse 内置浏览器

5.2 主要功能设计

登录身份共有三种：系统管理员，宿管老师，学生。输入用户名、密码和身份之后，判断是否和数据库中记录相符。如果用户名和密码正确，那么根据不同身份进入不同子界面。其中用到的权限控制代码段如下：

public String execute() throws Exception { -- -->

if(Type.equals("系统管理员"))

{

if (null == new AdminDao().CheckLogin(Username, Password)) {

Msg = "用户名或者密码错误";

return INPUT;

}

else

{

//获取ID

String Admin_ID = new AdminDao().CheckLogin(Username, Password);

//创建session

HttpSession session = ServletActionContext.getRequest().getSession();

session.setAttribute("id", Admin_ID);

session.setAttribute("type", "1");

return SUCCESS;

}

}

else if(Type.equals("宿管老师"))

{

if (null == new TeacherDao().CheckLogin(Username, Password)) {

Msg = "用户名或者密码错误";

return INPUT;

}

else

{

//获取ID

String Teacher_ID=new TeacherDao().CheckLogin(Username, Password);

//创建session

HttpSession session = ServletActionContext.getRequest().getSession();

session.setAttribute("id", Teacher_ID);

session.setAttribute("type", "2");

return SUCCESS;

}

}

else if(Type.equals("学生"))

{

if (null == new StudentDao().CheckLogin(Username, Password)) {

Msg = "用户名或者密码错误";

return INPUT;

}

else

{

//获取ID

String Student_ID=new StudentDao().CheckLogin(Username, Password);

//创建session

HttpSession session = ServletActionContext.getRequest().getSession();

session.setAttribute("id", Student_ID);

session.setAttribute("type", "3");

return SUCCESS;

}

}

else

{

Msg = "身份类型错误";

return INPUT;

}

}

管理员界面提供各种增删改查功能，针对的对象包括学生，宿管老师，宿舍楼，寝室。点击各个功能后会进入对应的子界面。输入需要的参数之后，点击按钮即可完成操作。

<%if(session.getAttribute("type").toString().equals("1")) {

%>

<li><a href="TeacherManager.action">宿管老师管理</a></li>code>

<li><a href="StudentManager.action">学生管理</a></li>code>

<li><a href="BuildingManager.action">楼宇管理</a></li>code>

<li><a href="DormitoryManager.action">宿舍管理</a></li>code>

<li><a href="StudentRZ.action">学生入住登记</a></li>code>

<li><a href="StudentDH.jsp">学生寝室调换</a></li>code>

<li><a href="StudentQC.jsp">学生迁出登记</a></li>code>

<li><a href="AdminLog.action">学生缺寝记录</a></li>code>

<%

}%>

教师界面和管理员类似，不过只提供了对学生缺寝记录的添加和查询操作。

<%if(session.getAttribute("type").toString().equals("2")) { -- -->

%>

<li><a href="MyStudent.action">学生管理</a></li>code>

<li><a href="MyLog.action">学生缺寝记录</a></li>code>

<%

}%>

学生界面只提供了查询自己缺寝记录的功能。

<%if(session.getAttribute("type").toString().equals("3")) { -- -->

%>

<li><a href="StudentLog.action">我的缺寝记录</a></li>code>

<%

}%>

下面展示关于教师增删改查的代码，其余类似：

//获取列表

public List<TeacherBean> GetList(String strwhere,String strorder) { -- -->

String sql="select * from Teacher";code>

if(!(isInvalid(strwhere)))

{ -- -->

sql+=" where "+strwhere;

}

if(!(isInvalid(strorder)))

{

sql+=" order by "+strorder;

}

Statement stat = null;

ResultSet rs = null;

Connection conn = new DBHelper().getConn();

List<TeacherBean> list=new ArrayList<TeacherBean>();

try {

stat = conn.createStatement();

rs = stat.executeQuery(sql);

while(rs.next()) {

TeacherBean cnbean=new TeacherBean();

cnbean.setTeacher_ID(rs.getInt("Teacher_ID"));

cnbean.setTeacher_Username(rs.getString("Teacher_Username"));

cnbean.setTeacher_Password(rs.getString("Teacher_Password"));

cnbean.setTeacher_Name(rs.getString("Teacher_Name"));

cnbean.setTeacher_Sex(rs.getString("Teacher_Sex"));

cnbean.setTeacher_Tel(rs.getString("Teacher_Tel"));

list.add(cnbean);

}

} catch (SQLException e) {

printStackTrace();

}

finally {

try {

if (conn != null)

conn.close();

if (stat != null)

stat.close();

if (rs != null)

rs.close();

} catch (SQLException e) {

printStackTrace();

}

}

return list;

}

//获取指定ID的实体Bean

public TeacherBean GetBean(int id) {

String sql="select * from Teacher where Teacher_ID="+id;code>

Statement stat = null;

ResultSet rs = null;

Connection conn = new DBHelper().getConn();

TeacherBean cnbean=new TeacherBean();

try { -- -->

stat = conn.createStatement();

rs = stat.executeQuery(sql);

while(rs.next()) {

cnbean.setTeacher_ID(rs.getInt("Teacher_ID"));

cnbean.setTeacher_Username(rs.getString("Teacher_Username"));

cnbean.setTeacher_Password(rs.getString("Teacher_Password"));

cnbean.setTeacher_Name(rs.getString("Teacher_Name"));

cnbean.setTeacher_Sex(rs.getString("Teacher_Sex"));

cnbean.setTeacher_Tel(rs.getString("Teacher_Tel"));

}

} catch (SQLException e) {

printStackTrace();

}

finally {

try {

if (conn != null)

conn.close();

if (stat != null)

stat.close();

if (rs != null)

rs.close();

} catch (SQLException e) {

printStackTrace();

}

}

return cnbean;

}

//添加

public void Add(TeacherBean cnbean) {

String sql="insert into Teacher (";code>

sql+="Teacher_Username,Teacher_Password,Teacher_Name,Teacher_Sex,Teacher_Tel";

sql+=") values(";

sql+="'"+cnbean.getTeacher_Username()+"','"+cnbean.getTeacher_Password()+"','"+cnbean.getTeacher_Name()+"','"+cnbean.getTeacher_Sex()+"','"+cnbean.getTeacher_Tel()+"'";

sql+=")";

Statement stat = null;

ResultSet rs = null;

Connection conn = new DBHelper().getConn();

try { -- -->

stat = conn.createStatement();

stat.executeUpdate(sql);

} catch (SQLException e) {

printStackTrace();

}

finally {

try {

if (conn != null)

conn.close();

if (stat != null)

stat.close();

if (rs != null)

rs.close();

} catch (SQLException e) {

printStackTrace();

}

}

}

//修改

public void Update(TeacherBean cnbean) {

String sql="update Teacher set ";code>

sql+="Teacher_Username='"+cnbean.getTeacher_Username()+"',";code>

sql+="Teacher_Password='"+cnbean.getTeacher_Password()+"',";code>

sql+="Teacher_Name='"+cnbean.getTeacher_Name()+"',";code>

sql+="Teacher_Sex='"+cnbean.getTeacher_Sex()+"',";code>

sql+="Teacher_Tel='"+cnbean.getTeacher_Tel()+"'";code>

sql+=" where Teacher_ID='"+cnbean.getTeacher_ID()+"'";code>

Statement stat = null;

ResultSet rs = null;

Connection conn = new DBHelper().getConn();

try { -- -->

stat = conn.createStatement();

stat.executeUpdate(sql);

} catch (SQLException e) {

printStackTrace();

}

finally {

try {

if (conn != null)

conn.close();

if (stat != null)

stat.close();

if (rs != null)

rs.close();

} catch (SQLException e) {

printStackTrace();

}

}

}

//删除

public void Delete(String strwhere) {

String sql="delete from Teacher where ";code>

sql+=strwhere;

Statement stat = null;

ResultSet rs = null;

Connection conn = new DBHelper().getConn();

try { -- -->

stat = conn.createStatement();

stat.executeUpdate(sql);

} catch (SQLException e) {

printStackTrace();

}

finally {

try {

if (conn != null)

conn.close();

if (stat != null)

stat.close();

if (rs != null)

rs.close();

} catch (SQLException e) {

printStackTrace();

}

}

}

5.3 主要界面