学生宿舍管理系统 javaswing 学生宿舍管理系统er图

宿舍管理系统

前言

此次大作业共分为四个阶段，四次实验分别为数据库设计，存储过程，数据库应用开发,数据库设计与应用开发综合。

需求分析

• 对所有学生信息统计建表存储。
• 对宿管成员组信息建表存储。
• 对各楼层寝室居住信息进行建表存储。
• 对居住学生寝室动态进行跟踪记录。
• 对学生居住寝室每月卫生进行评分统计。
• 生活服务管理(水费，电费信息呈现)。

概念模型

E-R图

E-R图1

E-R图2

E-R图3

E-R图4

实体与属性关系

公寓楼（公寓号，公寓名，宿管编号）；

寝室（公寓号，寝室楼层，寝室号，寝室成员人数，寝室长学号）；

学生表（学号，性别，姓名，所属学院，班级，入住年月，公寓楼，寝室号，电话号码，班主任编号）；

老师表（班主任编号，姓名，班级，电话号码）；

宿管表（公寓号，工作编号，性别，姓名，电话号码）；

电费表（公寓号，寝室号，费用，日期）；

水费表（公寓号，寝室号，费用，日期）；

快递表（快递号，学号，学生姓名，电话，日期）；

评分表（公寓号，寝室号，评分，日期）；

更换宿舍表（学生学号，目的宿舍楼，目的宿舍编号，原宿舍楼，原宿舍编号）；

毕业表（学号，性别，姓名，所属学院，班级，退宿年月，公寓楼，电话号码）；

字典

公寓楼表(flat)

属性名	类型	长度	备注
公寓号(f_id)	int		公寓楼的编号
公寓名(f_name)	varchar	32	公寓楼的名字
宿管编号(m_id)	int		本楼宿管的工作编号

寝室表(dorm)

属性名	类型	长度	备注
公寓号(f_id)	int		公寓楼的编号
寝室楼层(d_floor)	int		寝室所在的楼层
寝室号(d_id)	int		寝室的编号
寝室人数(d_num)	int		寝室居住的人数
寝室长学号(d_s_id)	varchar	24	寝室长的学号

学生表(student)

属性名	类型	长度	备注
学号(s_id)	varchar	24	学生学号
性别(s_sex)	varchar	8	学生性别
名字(s_name)	varchar	32	学生名字
所属学院(s_major)	varchar	32	学生在读的学院
班级(s_class)	varchar	32	学生所在的班级
入住年月(s_in_date)	date		学生入住寝室的年月
公寓楼(f_id)	int		公寓楼编号
寝室号(d_id)	int		寝室号
电话号码(s_numb)	varchar	24	学生电话号码
班主任编号(t_id)	int		班主任工作编号

老师表(teacher)

属性名	类型	长度	备注
班主任编号(t_id)	int		班主任工作编号
姓名(t_name)	varchar	32	班主任姓名
班级(t_class)	varchar	32	所带的班级
电话号码(t_numb)	varchar	24	班主任电话号码

宿管表(manager)

属性名	类型	长度	备注
公寓号(f_id)	int		公寓楼的编号
工作编号(m_id)	int		宿管工作编号
性别(m_sex)	varchar	8	宿管性别
姓名(m_name)	varchar	32	宿管姓名
电话号码(m_numb)	varchar	24	宿管的电话号码

电费表(electric)

属性名	类型	长度	备注
公寓号(f_id)	int		公寓楼的编号
寝室号(d_id)	int		寝室的编号
费用(e_cost)	decimal	(7,2)	寝室消耗的电费
日期(e_date)	date		消耗至日期

水费表(water)

属性名	类型	长度	备注
公寓号(f_id)	int		公寓楼的编号
寝室号(d_id)	int		寝室的编号
费用(w_cost)	decimal	(7,2)	寝室消耗的水费
日期(w_date)	date		消耗至日期

快递表(deliver)

属性名	类型	长度	备注
快递号(d_id)	varchar	32	快递单号
学号(s_id)	varchar	24	学生学号
名字(s_name)	varchar	32	学生名字
电话号码(s_numb)	varchar	24	学生电话号码
日期(d_date)	date		快递存放日期

评分表(mark)

属性名	类型	长度	备注
公寓号(f_id)	int		公寓楼的编号
寝室号(d_id)	int		寝室的编号
评分(m_num)	int		所得评分
日期(m_date)	date		打分日期

更换宿舍表(change)

属性名	类型	长度	备注
学号(s_id)	varchar	24	学生学号
目的公寓楼(a_f_id)	int		目的公寓楼编号
目的寝室号(a_d_id)	int		目的寝室号
原公寓楼(s_f_id)	int		原公寓楼编号
原寝室号(s_d_id)	int		原寝室号

逻辑结构定义

flat

属性名	类型	长度	备注	完整性约束
f_id	int		公寓楼的编号	key
f_name	varchar	32	公寓楼的名字	非空
m_id	int		本楼宿管的工作编号	非空

dorm

属性名	类型	长度	备注	完整性约束
f_id	int		公寓楼的编号	非空
d_floor	int		寝室所在的楼层	非空
d_id	int		寝室的编号	非空
d_num	int		寝室居住的人数	非空
d_s_id	varchar	24	寝室长的学号	非空

student

属性名	类型	长度	备注	完整性约束
s_id	varchar	24	学生学号	key
s_sex	varchar	8	学生性别	非空
s_name	varchar	32	学生名字	非空
s_major	varchar	32	学生在读的学院	非空
s_class	varchar	32	学生所在的班级	非空
s_in_date	date		学生入住寝室的年月	非空
f_id	int		公寓楼编号	非空
d_id	int		寝室号	非空
s_numb	varchar	24	学生电话号码	非空
t_id	int		班主任工作编号	非空

teacher

属性名	类型	长度	备注	完整性约束
t_id	int		班主任工作编号	key
t_name	varchar	32	班主任姓名	非空
t_class	varchar	32	所带的班级	非空
t_numb	varchar	24	班主任电话号码	非空

manager

属性名	类型	长度	备注	完整性约束
f_id	int		公寓楼的编号	非空
m_id	int		宿管工作编号	key
m_sex	varchar	8	宿管性别	非空
m_name	varchar	32	宿管姓名	非空
m_numb	varchar	24	宿管的电话号码	非空

electric

属性名	类型	长度	备注	完整性约束
f_id	int		公寓楼的编号	非空
d_id	int		寝室的编号	非空
e_cost	decimal	(7,2)	寝室消耗的电费	非空
e_date	date		消耗至日期	非空

water

属性名	类型	长度	备注	完整性约束
f_id	int		公寓楼的编号	非空
d_id	int		寝室的编号	非空
w_cost	decimal	(7,2)	寝室消耗的水费	非空
w_date	date		消耗至日期	非空

deliver

属性名	类型	长度	备注	完整性约束
d_id	varchar	32	快递单号	key
s_id	varchar	24	学生学号	非空
s_name	varchar	32	学生名字	非空
s_numb	varchar	24	学生电话号码	非空
d_date	date		快递存放日期	非空

mark

属性名	类型	长度	备注	完整性约束
f_id	int		公寓楼的编号	非空
d_id	int		寝室的编号	非空
m_num	int		所得评分	非空
m_date	date		打分日期	非空

change

属性名	类型	长度	备注	完整性约束
s_id	varchar	24	学生学号	非空
a_f_id	int		目的公寓楼编号	非空
a_d_id	int		目的寝室号	非空
s_f_id	int		原公寓楼编号	非空
s_d_id	int		原寝室号	非空

数据实施

建立数据表

flat表

create table flat(
f_id int PRIMARY key COMMENT '公寓楼编号',
f_name varchar(32) not null COMMENT '公寓楼名字',
m_id int not null comment '宿管编号');

dorm表

create table dorm(
f_id int not null COMMENT '公寓楼编号',
d_floor int not null COMMENT '宿舍楼层',
d_id int not null comment '宿舍编号',
d_num int not null comment '宿舍人数',
d_s_id varchar(24) not null comment '寝室长学号' );

student表

create table student(
s_id varchar(24) PRIMARY key COMMENT '学号',
s_sex varchar(8) not null COMMENT '性别',
s_name varchar(32) not null COMMENT '姓名',
s_major varchar(32) not null comment '非空',
s_class varchar(32) not null comment '班级',
s_in_date date not null COMMENT '入住日期',
f_id int not null COMMENT '公寓楼编号',
d_id int not null comment '寝室号',
s_numb varchar(24) not null comment '电话号码',
t_id int not null COMMENT '老师编号');

teacher表

create table teacher(
t_id int PRIMARY key comment '老师编号',
t_name varchar(32) not null comment '老师姓名',
t_class varchar(32) not null comment '所带班级',
t_numb varchar(24) not null COMMENT '电话');

manager表

create table manager(
f_id int not null comment '公寓楼编号',
m_id int not null comment '工作编号',
m_sex VARCHAR(8) not null COMMENT '性别',
m_name VARCHAR(32) not null comment '姓名',
m_numb VARCHAR(24) not null comment '电话号码');

electric表

create table electric(
f_id int not null comment '公寓楼编号',
d_id int not null comment '寝室编号',
e_cost DECIMAL(7,2) not null comment '电费',
e_date date not null comment '日期');

water表

create table water(
f_id int not null comment '公寓楼编号',
d_id int not null comment '寝室编号',
w_cost DECIMAL(7,2) not null comment '水费',
w_date date not null comment '日期');

运行结果：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-W7sdeNxY-1591152473183)(7water.png)]

mark表

create table mark(
f_id int not null comment '公寓楼编号',
d_id int not null comment '寝室编号',
m_num int not null comment '得分',
m_date date not null comment '日期');

deliver表

create table deliver(
d_id varchar(32) PRIMARY key COMMENT '快递单号',
s_id varchar(24)  not null comment '学生学号',
s_name varchar(32) not null comment '姓名',
s_numb VARCHAR(24) not null comment '电话号码',
d_date date comment '存放日期');

changee表

create table changee(
s_id varchar(24) not null comment '学号',
a_f_id int not null comment '目的楼',
a_d_id int not null comment '目的寝',
s_f_id int not null comment '原楼',
s_d_id int not null comment '原寝');

gra 毕业表

create table gra(
s_id varchar(24) PRIMARY key COMMENT '学号',
s_sex varchar(8) not null COMMENT '性别',
s_name varchar(32) not null COMMENT '姓名',
s_major varchar(32) not null comment '非空',
s_class varchar(32) not null comment '班级',
s_in_date date not null COMMENT '入住日期',
f_id int not null COMMENT '公寓楼编号',
d_id int not null comment '寝室号',
s_numb varchar(24) not null comment '电话号码',
t_id int not null COMMENT '老师编号')charset utf8;

插入数据

-- flat表
insert into flat VALUES (1,'练剑楼',1),
												(2,'归云楼',3),
												(3,'烟雨楼',4),
												(4,'潮声楼',2),
												(5,'碧海楼',5);
								
-- dorm表

-- 试剑楼
insert into dorm VALUES (1,1,101,4,'18010541'),
						(1,2,205,4,'18010542');
-- 归云楼
insert into dorm VALUES (2,1,101,4,'18010101'),
						(2,2,201,4,'18010122');
-- 烟雨楼
insert into dorm VALUES (3,1,101,4,'18010201'),
						(3,2,201,4,'18010222');
-- 潮声楼
insert into dorm VALUES (4,1,101,4,'18010111'),
						(4,2,201,4,'18010512');
-- 碧海楼
insert into dorm VALUES (5,1,101,4,'18010116'),
						(5,2,201,4,'18010516');

-- student表
INSERT into student VALUES 
('18010101','男','郭靖','数计','18级1班','2018-09-01',2,101,'18825567788',1),
('18010102','男','李靖','数计','18级1班','2018-09-01',2,101,'15825567788',1),
('18010103','男','郭破胡','数计','18级1班','2018-09-01',2,101,'18829567788',1),
('18010104','男','易靖','数计','18级1班','2018-09-01',2,101,'18829967788',1),
													 
('18010111','女','黄蓉','数计','18级1班','2018-09-01',4,101,'18824597908',1),
('18010112','女','小龙女','数计','18级1班','2018-09-01',4,101,'18824598888',1),
('18010113','女','郭襄阳','数计','18级1班','2018-09-01',4,101,'18820597908',1),
('18010114','女','黄蓉','数计','18级1班','2018-09-01',4,101,'18824597908',1),
													  
('18010116','女','郭芙蓉','数计','18级1班','2018-09-01',5,101,'18828867766',1),
('18010117','女','瑛姑','数计','18级1班','2018-09-01',5,101,'18828809766',1),
('18010118','女','梦姑','数计','18级1班','2018-09-01',5,101,'18898809766',1),
('18010119','女','韩小青','数计','18级1班','2018-09-01',5,101,'17878809766',1),
													  
('18010122','男','洪七公','数计','18级1班','2018-09-01',2,201,'18828866666',1),
('18010123','男','黄药师','数计','18级1班','2018-09-01',2,201,'18826666666',1),
('18010124','男','欧阳锋','数计','18级1班','2018-09-01',2,201,'18828867777',1),
('18010125','男','中神通','数计','18级1班','2018-09-01',2,201,'18829999966',1),
													 													 
('18010201','男','杨过','数计','18级2班','2018-09-01',3,101,'17825567788',2),
('18010202','男','忽必烈','数计','18级2班','2018-09-01',3,101,'17800567788',2),
('18010203','男','鲁有脚','数计','18级2班','2018-09-01',3,101,'17825007788',2),
('18010204','男','杨康','数计','18级2班','2018-09-01',3,101,'17899567788',2),

('18010222','男','乔峰','数计','18级2班','2018-09-01',3,201,'18855566688',2),
('18010223','男','肖峰','数计','18级2班','2018-09-01',3,201,'18809876688',2),
('18010224','男','张无忌','数计','18级2班','2018-09-01',3,201,'17879566688',2),
('18010225','男','谢逊','数计','18级2班','2018-09-01',3,201,'18859966608',2),
													 
('18010541','男','逍遥子','数计','18级5班','2018-09-01',1,101,'15770816917',3),
('18010540','男','黄尚','数计','18级5班','2018-09-01',1,101,'15770666917',3),
('18010539','男','庄子','数计','18级5班','2018-09-01',1,101,'15770899917',3),
('18010538','男','老子','数计','18级5班','2018-09-01',1,101,'15770810017',3),
													 
('18010542','男','段誉','数计','18级5班','2018-09-01',1,205,'17870816917',3),
('18010543','男','路飞','数计','18级5班','2018-09-01',1,205,'17870810017',3),
('18010544','男','索隆','数计','18级5班','2018-09-01',1,205,'17890800917',3),
('18010545','男','乌索普','数计','18级5班','2018-09-01',1,205,'17870811917',3),
													 
('18010512','女','汶川','数计','18级5班','2018-09-01',4,201,'15770817917',3),
('18010513','女','济川','数计','18级5班','2018-09-01',4,201,'15770822917',3),
('18010514','女','发儿','数计','18级5班','2018-09-01',4,201,'15770017917',3),
('18010515','女','哎呦','数计','18级5班','2018-09-01',4,201,'15770997917',3),
													  
('18010516','女','穆念慈','数计','18级5班','2018-09-01',5,201,'15770816917',3),
('18010517','女','穆容','数计','18级5班','2018-09-01',5,201,'15770666917',3),
('18010518','女','倩倩','数计','18级5班','2018-09-01',5,201,'15770816666',3),
('18010519','女','呈呈','数计','18级5班','2018-09-01',5,201,'15770776917',3);

-- teacher表
insert into teacher VALUES (1,'张老师',1,'17879382727'),
						   (2,'李老师',2,'17879386767'),
						   (3,'吴老师',5,'17879383636');
						 
-- electric表
insert into electric VALUES (1,101,200,'2020-04-18'),
							(1,205,300,'2020-04-20'),
							(2,101,500,'2020-03-18'),
							(2,201,400,'2020-04-04'),
							(3,101,100,'2020-04-01'),
							(3,201,300,'2020-04-15'),
							(4,101,350,'2020-03-31'),
							(4,201,400,'2020-04-01'),
							(5,101,150,'2020-04-02'),
							(5,201,260,'2020-04-18');
							
-- water表
insert into water VALUES    (1,101,60,'2020-03-18'),
							(1,205,50,'2020-03-20'),
							(2,101,50,'2020-04-18'),
							(2,201,40,'2020-03-04'),
							(3,101,100,'2020-03-01'),
							(3,201,70,'2020-03-15'),
							(4,101,35,'2020-04-30'),
							(4,201,40,'2020-03-01'),
							(5,101,75,'2020-04-02'),
							(5,201,86,'2020-03-18');
-- mark表
insert into mark VALUES (1,101,9,'2020-04-18'),
						(1,205,7,'2020-04-18'),
						(2,101,9,'2020-04-18'),
						(2,201,10,'2020-04-18'),
						(3,101,6,'2020-04-18'),
						(3,201,7,'2020-04-18'),
						(4,101,8,'2020-04-18'),
						(4,201,9,'2020-04-18'),
						(5,101,7,'2020-04-18'),
						(5,201,10,'2020-04-18'),
												
						(1,101,8,'2020-04-28'),
						(1,205,9,'2020-04-28'),
						(2,101,7,'2020-04-28'),
						(2,201,8,'2020-04-28'),
						(3,101,9,'2020-04-28'),
						(3,201,5,'2020-04-28'),
						(4,101,6,'2020-04-28'),
						(4,201,8,'2020-04-28'),
						(5,101,10,'2020-04-28'),
						(5,201,9,'2020-04-28');


-- deliver表
insert into deliver values ('20204187312','18010541','逍遥子','15770816917','2020-04-18'),
						   ('20204156662','18010101','郭靖','18825567788','2020-04-22'),
						   ('20204199889','18010222','乔峰','18855566688','2020-04-20'),
						   ('20204120098','18010122','洪七公','18828866666','2020-04-22'),
				           ('20204210876','18010114','黄蓉','18824597908','2020-04-23');

存储过程创建

存储过程1

in_stu()

说明：0,通过学生id删除学生信息 1，查询所有学生信息 2，添加学生信息 3，按学号查询信息；

delimiter //
create procedure in_stu(in i int,in in_s_id varchar(24),
in in_s_sex varchar(8),
in in_s_name varchar(32),
in in_s_major varchar(32),
in in_s_class VARCHAR(32),
in in_s_in_date date,
in in_f_id int,
in in_d_id int,
in in_s_numb varchar(24),
in in_t_id int)
BEGIN
  IF i = 1 THEN 
		SELECT 
		s_id as '学号',s_sex as '性别',s_name as '姓名',s_major as '学院',
		s_class as '班级',s_in_date as '入住日期',f_id as '楼号',d_id as '寝室号',
		s_numb as '电话号码',t_id as '班主任编号'
		from student;
  ELSEIF i = 0 THEN
    DELETE FROM student where s_id = in_s_id;
  ELSEIF i = 3 THEN
    SELECT 
		s_id as '学号',s_sex as '性别',s_name as '姓名',s_major as '学院',
		s_class as '班级',s_in_date as '入住日期',f_id as '楼号',d_id as '寝室号',
		s_numb as '电话号码',t_id as '班主任编号'
		from student WHERE s_id = in_s_id;
  ELSE		
		INSERT into student VALUES (in_s_id,in_s_sex,in_s_name,in_s_major,in_s_class,in_s_in_date,in_f_id,in_d_id,in_s_numb,in_t_id);

END IF;
END //

-- i为0时
call in_stu(0,'18010545',null,null,null,null,null,null,null,null,null);
-- i为1时
call in_stu(1,null,null,null,null,null,null,null,null,null,null);
-- i为2时
call in_stu(2,'1801055','男','乌索普','数计','18级5班','2018-09-01',1,205,'17890811917',3);
-- i为3时
call in_stu(3,'1801045',null,null,null,null,null,null,null,null,null);

运行结果：

2.

注：此处易出现存储过程字符集不符的情况

可以通过：

-- 用于修改字符集
set character_set_client = utf8;
set character_set_server = utf8;
set character_set_connection = utf8;
set character_set_database = utf8;
set character_set_results = utf8;
set collation_connection = utf8_general_ci;
set collation_database = utf8_general_ci;
set collation_server = utf8_general_ci;

也可以在my.ini文件中修改。

存储过程2

in_栋楼信息
注：1查询栋楼具体信息，2查询输入公寓号的公寓全部学生信息,in_flat变量表示公寓号。

delimiter //
create procedure in_flat(in in_jud int,in in_flat int)
begin
	IF in_jud = 1 THEN
	SELECT f_id as '公寓号',f_name as '公寓名',count(d_id) as '寝室数',a as '公寓楼人数',m_id as '宿管工号' 
  FROM (SELECT f_id,count(s_id) a FROM student GROUP BY f_id) j 
  join dorm d using(f_id) 
  join flat f USING(f_id)
  GROUP BY f_id;
	ELSEIF in_jud = 2 THEN
	SELECT
  s_id as '学号',s_sex as '性别',s_name as '姓名',s_major as '学院',
	s_class as '班级',s_in_date as '入住日期',f_id as '楼号',d_id as '寝室号',
	s_numb as '电话号码',t_id as '班主任编号'
	FROM student WHERE f_id = in_flat;
  END IF;
end //

运行结果：

存储过程3

宿管信息查询

delimiter //
-- 1查询全部信息，2查询指定公寓宿管信息。
create PROCEDURE in_man(in i int , in in_f_id int)
BEGIN
  IF i = 1 THEN
	SELECT f_id as '公寓号',m_id as '工号',m_sex as '性别',m_name as '姓名',m_numb as '电话号码' FROM manager;
	ELSEIF i = 2 THEN
	SELECT f_id as '公寓号',m_id as '工号',m_sex as '性别',m_name as '姓名',m_numb as '电话号码'
	FROM manager WHERE f_id = in_f_id;
	end if;
END //
call in_man(1,1)
call in_man(2,1)

运行结果：

存储过程4

cost()

返回指定寝室的水费消耗电费消耗及总和。

注：cost(in_f_id,in_d_id) in_f_id为公寓号,in_d_id为寝室号。

delimiter //
create procedure cost(in in_f_id int,in in_d_id int)
BEGIN
  SELECT w_cost as '水费',e_cost as '电费',(w_cost+e_cost) as '总和',w_date as '日期' FROM
  (SELECT w_cost,w_date FROM water where f_id = in_f_id and d_id = in_d_id ) i join 
  (SELECT e_cost,e_date FROM electric where f_id = in_f_id and d_id = in_d_id ) j on w_date = e_date;
END //
call cost(1,101)

运行结果：

存储过程5

score()评分系统

注：1返回全部评分排名，2根据公寓楼和寝室号查询评分，3添加评分信息。

delimiter //
create procedure score(in i int,in in_f_id int,in in_d_id int,in in_m_num int,in in_m_date date)
begin
	ig i = 1 THEN
	select a as '公寓号',b as '寝室号',c as '得分',d as '日期',(@a:=@a+1) 排名 
	from (select f_id a ,d_id b ,m_num c ,m_date d FROM mark ORDER BY m_date,m_num desc) j ,(SELECT @a:=0) k ORDER BY 排名;
  ELSEif i = 2 then
	select f_id as '公寓号', d_id as '寝室号', m_num as '得分', m_date as '日期' FROM mark WHERE f_id = 1 and d_id = 101;
	else
	insert into mark VALUES (in_f_id,in_d_id,in_m_num,in_m_date);
  END IF;
end //

运行结果：

存储过程6

毕业表插入，之后用触发器删除学生表内容。

delimiter //
create procedure in_gra(in in_s_id varchar(24),
in in_s_sex varchar(8),
in in_s_name varchar(32),
in in_s_major varchar(32),
in in_s_class VARCHAR(32),
in in_s_in_date date,
in in_f_id int,
in in_d_id int,
in in_s_numb varchar(24),
in in_t_id int)
begin
  insert into gra VALUES 
	(in_s_id,in_s_sex,in_s_name,in_s_major,in_s_class,in_s_in_date,in_f_id,in_d_id,in_s_numb,in_t_id);
end //
call in_gra('18010545','男','乌索普','数计','18级5班','2018-09-01',1,205,'17890811917',3);

运行结果：

触发器1

当毕业表有学生数据插入时，学生表删除相应的信息，对应寝室总人数减少。

delimiter //
CREATE TRIGGER stu_change AFTER INSERT ON gra FOR EACH ROW
BEGIN
	DELETE FROM student WHERE s_id = new.s_id;
	UPDATE dorm d SET d.d_num = d.d_num - 1 WHERE d.d_id = new.d_id and d.f_id = new.f_id;
END //

运行call in_gra(‘18010545’,‘男’,‘乌索普’,‘数计’,‘18级5班’,‘2018-09-01’,1,205,‘17890811917’,3);后，各表的情况：

student：成功减少了一条数据：

dorm：相应宿舍人数成功减少：