数据库课设——DMS（高校宿舍管理系统）

数据库课程设计报告
高校学生宿舍管理系统
目录
（一）实验目的 3
（二） 软硬件环境 3
mysql-8.0.22-winx64、Webyog SQLyog 3
（三）实验设计简述 3
（四）系统需求分析 3
1． 管理员 3
2． 校区 3
3． 楼栋 4
4． 楼栋管理员 4
5． 宿舍 4
6． 宿舍报修 4
7． 学生 4
（五）概念模型设计 4
1． 管理员信息 4
2． 校区信息 5
3． 楼栋信息 5
4． 楼栋管理员信息 5
5． 宿舍信息 5
6． 宿舍报修信息 5
7． 学生信息 5
（六）逻辑模型设计 5
（七）物理模型设计 6
1．存储记录结构设计 6
2．建立索引 9
3． 建立视图 9
（八）表的创建 10
1． 建表sql语句 10
2． 实现列、行及参照完整性 12
（九）实验数据示例：测试 13
1． 数据操纵（增） 13
2．数据操纵（改） 19
3． 数据操纵（查） 19
（十）设计存储过程、函数、触发器 22
1． 设计存储过程 22
2． 函数 24
3． 触发器 26
（一）实验目的
作为一名大学在校生，发现学校的宿舍管理模式依旧是以前的用纸笔登记记录，对于师生、宿管使用起来，学校管理极其不方便，于是我们希望开发一个高校宿舍管理系统，本文主要是对数据库的设计、测试及可行性分析。最终实现疫情当下的“网上解决问题”的愿望,以及方便宿舍管理人员的工作和广大学生，提高其工作效率。
根据高校学生宿舍管理系统的要求，通过系统需求分析、概念模型设计、逻辑模型设计、物理模型设计等全过程完成高校宿舍管理系统的数据库设计。
（二）软硬件环境
mysql-8.0.22-winx64、Webyog SQLyog
（三）实验设计简述
宿舍管理系统主要是通过提供高校的学生信息和宿舍管理信息之间的关系，建立一个高效率的宿舍管理系统。其中包括校区信息、楼栋信息、宿舍信息、学生信息、楼栋管理员信息以及宿舍事故信息。该系统可以通过学生信息的查找快速找到学生所对应的宿舍信息，以及宿舍所有的信息。还包括学生信息的添加、修改、删除及查询等以及宿舍信息的添加、删除、修改、查询。可以通过查找楼栋查找该楼所包含的所有学生信息，反之，通过学生信息也可以查找到学生所在宿舍楼及宿舍号的信息。
后续我们将准备使用Redis，key-value数据库进行改进，因为Redis支持主从同步。数据可以从主服务器向任意数量的从服务器上同步，从服务器可以是关联其他从服务器的主服务器。这使得Redis可执行单层树复制。存盘可以有意无意的对数据进行写操作。由于完全实现了发布/订阅机制，使得从数据库在任何地方同步树时，可订阅一个频道并接收主服务器完整的消息发布记录。同步对读取操作的可扩展性和数据冗余很有帮助。
另外，创建了一张系统管理员表，默认管理员类型type默认为0（一般管理员），其中设置一个管理员类型type值为1（超级管理员）。在后续的改进中预计会加入多种功能，超级管理员会给一般管理员进行权限管理。
（四）系统需求分析
1．管理员
每位系统管理员都有自己的账号，密码。当该系统出现系统故障或者需要维修时，系统管理员可以通过自己的账号、密码登陆到系统对系统漏洞进行维修管理。
2．校区
学校会分为两个校区，每个校区都会有校区id、校区全称、地址、电话、邮编号等。
3．楼栋
每栋楼都标有宿舍楼的号码，而每栋楼都有相应的楼层数目以及寝室数目。
4．楼栋管理员
每栋宿舍楼的楼栋管理员可以通过系统对学生信息、宿舍楼信息、宿舍信息、宿舍事故信息及时添加、删除、查询及修改。而且学生如果在宿舍中有问题要找宿舍管理员反馈，为了方便快捷可以通过该系统查询到管理员的信息及时进行反馈。
5．宿舍
宿舍做为该系统中与学生联系密切的一个主体主要还包含了这些信息：宿舍号，舍长，所在楼号，所在层数，总床位数，实际人数，空余床位，是否有空调，是否单独卫浴等，可以通过该系统查找每个宿舍中每位学生的个人信息。
6．宿舍报修
如果有宿舍的硬件设施出现问题，则宿舍舍长可以通过该系统填报宿舍信息以及宿舍保修信息。而最终的结果也会分为两种：
一、问题解决，则会有系统提示已解决；
二、问题未解决，则会显示“问题正在处理”。
7．学生
可以通过该系统查询到学生自己的信息核对自己的信息是否在宿舍管理系统中添加成功，如果没有添加成功或者添加信息有误则可以通过查找联系宿舍管理人员对其信息进行修改。若是宿舍出现突发事故或是有什么紧急情况，可以通过该系统查询校区信息，在该系统中拨打校园报警电话。各宿舍舍长通过宿舍信息可以及时查询，监督校内签到查寝等多个功能。
（五）概念模型设计
数据库需要表述的信息有以下几种：
1．管理员信息
2．校区信息
3．楼栋信息
4．楼栋管理员信息
5．宿舍信息
6．宿舍报修信息
7．学生信息

E-R模型
（六）逻辑模型设计
通过ER模型到关系模型的转化，可以得到如下关系模式：
(1)系统管理员转换 关系：admin(id，account，password，new_file_name，old_file_name，type)
(2)校区信息转换为 关系：campus_info(id，name，address，phone，email)
(3)楼栋实体集转换为 关系：build_info(id，number，name，floor_number，dorm_number，campusid)
(4)楼栋管理员信息转换为 关系：build_admin_info(id，name，sex，phone，dormid，buildid)
(5)宿舍信息转换为 关系：dorm_info(id，number，chief，floor，bed_number，actual_number，freebed，air_conditioner，remark，buildid，build_adminid)
(6)宿舍报修信息转换为 关系：repair_info(id，type，time，reason，solution，remark，dormid)
(7)学生信息转换为 关系：student_info（id，name，number，sex，grade，faculty，major，class，phone，enter_time，state，feedback，dormid）
每个关系模式的主键码都用加粗并用下划线标出。

关系模型
一个校区信息有n个楼栋信息，一个楼栋信息包含n个宿舍信息，同时也有一个楼栋管理员信息，一个楼栋管理员可以管理n个宿舍信息，n个宿舍信息能反馈n个保修信息，同时一个宿舍信息包含n个学生信息。
（七）物理模型设计
1.存储记录结构设计

表名：admin
属 性 含义 数据类型/范围 备注
id 管理员（主键） int(10)
account 账号 varchar(100)
password 密码 varchar(100)
new_file_name 新头像名 varchar(100)
old_file_name 旧头像名 varchar(100)
type 类型 int 0默认为一般管理员

表名：campus_info
属 性 含义 数据类型/范围 备注
id 校区id（主键） int(10)
name 校区全称 varchar(100)
address 校区地址 varchar(100)
phone 电话 varchar(11)
email 邮编号 varchar(100)

表名：build_info
属 性 含义 数据类型/范围 备注
id 楼栋id（主键） int(10)
number 楼号 varchar(100)
name 楼名 varchar(100)
floor_number 总层数 varchar(100)
dorm_number 总宿舍数 varchar(100)
campusid 所属校区（外键） int

表名：build_admin_info
属 性 含义 数据类型/范围 备注
id 楼栋管理员id（主键） int(10)
name 姓名 varchar(10)
sex 性别 char(1)
phone 电话 varchar(11)
dormid 所属宿舍（外键） int
buildid 所管理的楼栋（外键） int

表名：dorm_info
属 性 含义 数据类型/范围 备注
id 宿舍id（主键） int(10)
number 宿舍号 varchar(10)
chief 舍长 varchar(10)
floor 所在层数 varchar(100)
bed_number 总床位数 varchar(100)
actual_number 实际人数 varchar(100)
freebed 空余床位 varchar(100)
air_conditioner 是否有空调 char(1) 是/否
remark 备注 text
buildid 所属楼栋（外键） int(10)
build_adminid 所属楼管管理 int(10)

表名：repair_info
属 性 含义 数据类型/范围 备注
id 报修记录id（主键） int(10)
type 报修类型 varchar(100)
time 保修时间 datetime
reason 报修原因 varchar(100)
solution 解决情况 varchar(100)
remark 备注 text
dormid 所属宿舍（外键） int(10)

表名：student_info
属 性 含义 数据类型/范围 备注
id 学生id（主键） int(10)
name 姓名 varchar(10)
number 学号 varchar(100)
sex 性别 char(1)
grade 年级 varchar(100)
faculty 院系 varchar(100)
major 专业 varchar(100)
class 班级 varchar(100)
phone 联系电话 varchar(11)
enter_time 入住时间 datetime
state 在校状态 varchar(10)
feedback 意见反馈 varchar(100)
dormid 所属宿舍（外键） int(10)

2.建立索引
为了提高在表中搜索元组的速度，在实际实现的时候应该基于键码建立索引是各表中建立索引的表项：
(1)admin(id)
(2)campus_info(id)
(3)build_info(id,campusid)
(4)build_admin_info(id,dormid,buildid)
(5)dorm_info(id,buildid,build_adminid)
(6)repair_info(id,dormid)
(7)student_info(id,dormid)
3.建立视图
– 定义视图（查看陕西理工大学南校区的30岁以下楼栋管理员和他们的所属楼栋和宿舍号）

CREATE VIEW sel_build_admin_info AS
SELECT
  ba.name '楼栋管理员姓名',
  ba.sex,
  ba.age,
  ba.phone,
  d.number '宿舍号',
  b.name '楼栋名'
FROM
  build_admin_info ba,
  dorm_info d,
  build_info b
WHERE ba.dormid = d.id
  AND ba.buildid = b.id
  AND age <= 30

– 使用视图sel_build_admin_info

SELECT * FROM sel_build_admin_info

（八）表的创建
1．建表sql语句
– 创建宿舍管理系统数据库(DMS Database)

CREATE DATABASE IF NOT EXISTS dms_db CHARSET utf8;

– 创建系统管理员表

CREATE TABLE admin (
  id INT PRIMARY KEY AUTO_INCREMENT,
  account VARCHAR (100),
  PASSWORD VARCHAR (100),
  new_file_name VARCHAR (20),
  old_file_name VARCHAR (20),
  TYPE INT DEFAULT  0
)

– 创建校区信息表

CREATE TABLE campus_info (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR (100),
  address VARCHAR (100),
  phone VARCHAR (11),
  email VARCHAR (100)
)

– 创建楼栋信息表

CREATE TABLE build_info (
  id INT PRIMARY KEY AUTO_INCREMENT,
  number VARCHAR (100),
  NAME VARCHAR (100),
  floor_number VARCHAR (100),
  dorm_number VARCHAR (100),
  campusid INT
)

– 创建楼栋管理员表

CREATE TABLE build_admin_info (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR (10),
  sex CHAR (1),
  phone VARCHAR (11),
  dormid INT,
  buildid INT
)

– 创建宿舍信息表

CREATE TABLE dorm_info (
  id INT PRIMARY KEY AUTO_INCREMENT,
  number VARCHAR (10),
  chief VARCHAR (10),
  FLOOR VARCHAR (100),
  bed_number VARCHAR (100),
  actual_number VARCHAR (100),
  freebed VARCHAR (100),
  air_conditioner CHAR (1),
  remark TEXT,
  buildid INT,
  build_adminid INT
)

– 创建报修表

CREATE TABLE repair_info (
  id INT PRIMARY KEY AUTO_INCREMENT,
  TYPE VARCHAR (100),
  TIME DATETIME,
  reason VARCHAR (100),
  solution VARCHAR (100),
  remark TEXT,
  dormid INT
)

– 创建学生信息表

CREATE TABLE student_info (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR (10),
  number VARCHAR (100),
  sex CHAR (1),
  grade VARCHAR (100),
  faculty VARCHAR (100),
  major VARCHAR (100),
  class VARCHAR (100),
  phone VARCHAR (11),
  enter_time DATETIME,
  state VARCHAR (10),
  feedback VARCHAR (100),
  dormid INT
)

2．实现列、行及参照完整性
– 添加外键约束：
– ALTER TABLE 表名 ADD [CONSTRAINT 约束名(FK_主表_从表)] FOREIGN KEY(外键列) REFERENCES关联表(主键);
– 把楼栋信息表中campusid与校区信息表中的id关联，campusid作为外键