Please enable Javascript to view the contents

Student dormitory management

 ·  ☕ 4 分钟

Demand analysis

The system is designed to facilitate the management of student residences.

Provide registration for students and teachers, check the student dormitory number, adjust the dormitory and other functions.

At the same time, considering the need for maintenance and refurbishment of the dormitory, the system provides the function of submitting and accepting maintenance applications. After the student submits the repair application, the dormitory management will dispatch maintenance personnel to the maintenance.

User classification

  • Student (Male/Female): Managed by a boarding teacher
  • Dormitory manager: Manage all rooms and students
  • Maintenance personnel: Including cleaning, maintenance personnel, etc…

Functions

Dormitory manager:

  • Check student accommodation
  • Reasonable allocation of rooms
  • Adjust student dormitory allocation
  • Delete students who are no longer staying

Maintenance personnel:

  • Inquiry repair application form
  • Query working hours
  • Check the repair area (room id)

Database Design

This database includes a total of 5 tables: student_db, administrator_db, dormitry_db, maintenance_personnel_db, maintenance_application_db

  • student_db: {ID, name, sex, room number (room id)}

    This table is used to record and manage different kinds of students.

  • administrator_db: {ID, sex, managed room id}

    This table is used to record the student’s boarding teacher and the objects they need to manage.

  • dormitry_db: {ID, types(male or female), sister teacher's id}

    This table is used to record information about the dorms that need to be managed.

  • maintenance_personnel_db: {ID, name, sex, position (project responsible for maintenance), working time, off work time}

    This table is used to record information about maintenance personnel, including working hours, etc.

  • maintenance_application_db: {room id, application for repair}

    This tavle is used to record the repair application submitted by the student and is also the work content of the maintenance staff.

Contact

  • Student accommodation: {student_db, dormitry_db}
  • Dormitory management: {administrator_db, dormitry_db}
  • Repair application: {student_db, maintenance_application_db}
  • Dormitory maintenance: {maintenance_personnel_db, maintenance_application_db}

E - R

E-R

Table design

student_db:

列名数据类型允许 Null 值
student_idnchar(10)False
student_namenchar(10)False
student_sexnchar(10)False
room_idnchar(10)False

administrator_db:

列名数据类型允许 Null 值
administrator_idnchar(10)False
administrator_namenchar(10)False
administrator_sexnchar(10)False

dormitry_db:

列名数据类型允许 Null 值
room_idnchar(10)False
dormitry_typesnchar(10)False
administrator_idnchar(10)False

maintenance_personnel_db:

列名数据类型允许 Null 值
maintenance_personne_idnchar(10)False
maintenance_personnel_namenchar(10)False
maintenance_personnel_sexnchar(10)False
working_timedatetimeTrue
off_work_timedatetimeTrue

maintenance_application_db:

列名数据类型允许 Null 值
maintenance_application_idint IDENTITY(1, 1)False
maintenance_applicationvarchar(50)False
room_idnchar(10)False
maintenance_personnel_idnchar(10)True
work_statusbit DEFAULT(0)False

Database implementation

Case data

student_db:

student_idstudent_namestudent_sexroom_id
0001MogekoMaleM101
0002EzieFemaleF101
0003WashingtonMaleM101
0004LicolnMaleM102
0005DaisyFemaleF101
0006TrumpMaleM101
0007NoahMaleM102
0008LucyFemaleF101
0009ObamaMaleM101
0010LucyFemaleF101

administrator_db:

administrator_idadministrator_nameadministrator_sex
001HerringtonMale
002VanMale
003RossettMale
004JadeFemale
005EvaFemale
006MayFemale

dormitry_db:

room_iddormitry_typesadministrator_id
M101Male001
M102Male001
M103Male001
M104Male002
M105Male002
F101Female004
F102Female004
F103Female004
F104Female004
F105Female004

maintenance_personnel_db:

maintenance_personnel_idmaintenance_personnel_namemaintenance_personnel_sexworking_timeoff_work_time
001RooseveltMale2018-12-22T09:00:002018-12T22 22:00:00
002KennedyMale
003NannieFemale2018-12-22T09:00:002018-12-22T22:00:00
004DawnFemale

maintenance_application_db:

maintenance_application_idmaintenance_applicationroom_idmaintenance_personnel_idwork_status
1Door damageM1020010
2Faucet damageF1010

Import case data

Create the tables

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

/*
 Create tables
  - student_db
  - administrator_db
  - dormitry_db
  - maintenance_personnel_db
  - maintenance_application_db
 */
CREATE TABLE student_db
(
student_id nchar(10) NOT NULL PRIMARY KEY,
student_name nchar(10) NOT NULL,
student_sex nchar(10) NOT NULL,
room_id nchar(10) NOT NULL
)
CREATE TABLE administrator_db
(
administrator_id nchar(10) NOT NULL PRIMARY KEY,
administrator_name nchar(10) NOT NULL,
administrator_sex nchar(10) NOT NULL
)
CREATE TABLE dormitry_db
(
room_id nchar(10) NOT NULL PRIMARY KEY,
dormitry_types nchar(10) NOT NULL,
administrator_id nchar(10) NOT NULL
)
CREATE TABLE maintenance_personnel_db
(
maintenance_personnel_id nchar(10) NOT NULL PRIMARY KEY,
maintenance_personnel_name nchar(10) NOT NULL,
maintenance_personnel_sex nchar(10) NOT NULL,
working_time datetime,
off_work_time datetime
)
CREATE TABLE maintenance_application_db
(
maintenance_application_id int IDENTITY(1, 1) PRIMARY KEY,
maintenance_application varchar(50) NOT NULL,
room_id nchar(10) NOT NULL,
maintenance_personnel_id nchar(10),
work_status bit DEFAULT(0) NOT NULL,
)

Set FOREIHN KEY

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

/* Set the foreign key */

ALTER TABLE STUDENT_DB.dbo.student_db
ADD FOREIGN KEY (room_id)
REFERENCES dormitry_db(room_id)

ALTER TABLE STUDENT_DB.dbo.dormitry_db
ADD FOREIGN KEY (administrator_id)
REFERENCES administrator_db(administrator_id)

ALTER TABLE STUDENT_DB.dbo.maintenance_application_db
ADD FOREIGN KEY (room_id)
REFERENCES dormitry_db(room_id)

ALTER TABLE STUDENT_DB.dbo.maintenance_application_db
ADD FOREIGN KEY (maintenance_personnel_id)
REFERENCES maintenance_personnel_db(maintenance_personnel_id)

Insert data into the form STUDENT_DB.dbo.administrator_db

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

/* Insert data into the form STUDENT_DB.dbo.administrator_db */
INSERT INTO STUDENT_DB.dbo.administrator_db
(administrator_id, administrator_name, administrator_sex)
VALUES('001', 'Herrington', 'Male')
INSERT INTO STUDENT_DB.dbo.administrator_db
(administrator_id, administrator_name, administrator_sex)
VALUES('002', 'Van', 'Male')
INSERT INTO STUDENT_DB.dbo.administrator_db
(administrator_id, administrator_name, administrator_sex)
VALUES('003', 'Rossett', 'Male')
INSERT INTO STUDENT_DB.dbo.administrator_db
(administrator_id, administrator_name, administrator_sex)
VALUES('004', 'Jade', 'Female')
INSERT INTO STUDENT_DB.dbo.administrator_db
(administrator_id, administrator_name, administrator_sex)
VALUES('005', 'Eva', 'Female')
INSERT INTO STUDENT_DB.dbo.administrator_db
(administrator_id, administrator_name, administrator_sex)
VALUES('006', 'May', 'Female')

Insert data into the form STUDENT_DB.dbo.dormitry_db

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

/* Insert data into the form STUDENT_DB.dbo.dormitry_db */
INSERT INTO STUDENT_DB.dbo.dormitry_db
(room_id, dormitry_types, administrator_id)
VALUES('M101', 'Male', '001')
INSERT INTO STUDENT_DB.dbo.dormitry_db
(room_id, dormitry_types, administrator_id)
VALUES('M102', 'Male', '001')
INSERT INTO STUDENT_DB.dbo.dormitry_db
(room_id, dormitry_types, administrator_id)
VALUES('M103', 'Male', '001')
INSERT INTO STUDENT_DB.dbo.dormitry_db
(room_id, dormitry_types, administrator_id)
VALUES('M104', 'Male', '002')
INSERT INTO STUDENT_DB.dbo.dormitry_db
(room_id, dormitry_types, administrator_id)
VALUES('M105', 'Male', '002')
INSERT INTO STUDENT_DB.dbo.dormitry_db
(room_id, dormitry_types, administrator_id)
VALUES('F101', 'Female', '004')
INSERT INTO STUDENT_DB.dbo.dormitry_db
(room_id, dormitry_types, administrator_id)
VALUES('F102', 'Female', '004')
INSERT INTO STUDENT_DB.dbo.dormitry_db
(room_id, dormitry_types, administrator_id)
VALUES('F103', 'Female', '004')
INSERT INTO STUDENT_DB.dbo.dormitry_db
(room_id, dormitry_types, administrator_id)
VALUES('F104', 'Female', '004')
INSERT INTO STUDENT_DB.dbo.dormitry_db
(room_id, dormitry_types, administrator_id)
VALUES('F105', 'Female', '004')

Insert data into the form STUDENT_DB.dbo.student_db

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

/* Insert data into the form STUDENT_DB.dbo.student_db */
INSERT INTO STUDENT_DB.dbo.student_db
(student_id, student_name, student_sex, room_id)
VALUES('0001', 'Mogeko', 'Male', 'M101')
INSERT INTO STUDENT_DB.dbo.student_db
(student_id, student_name, student_sex, room_id)
VALUES('0002', 'Ezie', 'Female', 'F101')
INSERT INTO STUDENT_DB.dbo.student_db
(student_id, student_name, student_sex, room_id)
VALUES('0003', 'Washington', 'Male', 'M101')
INSERT INTO STUDENT_DB.dbo.student_db
(student_id, student_name, student_sex, room_id)
VALUES('0004', 'Licoln', 'Male', 'M102')
INSERT INTO STUDENT_DB.dbo.student_db
(student_id, student_name, student_sex, room_id)
VALUES('0005', 'Daisy', 'Female', 'F101')
INSERT INTO STUDENT_DB.dbo.student_db
(student_id, student_name, student_sex, room_id)
VALUES('0006', 'Trump', 'Male', 'M101')
INSERT INTO STUDENT_DB.dbo.student_db
(student_id, student_name, student_sex, room_id)
VALUES('0007', 'Noah', 'Male', 'M102')
INSERT INTO STUDENT_DB.dbo.student_db
(student_id, student_name, student_sex, room_id)
VALUES('0008', 'Lucy', 'Female', 'F101')
INSERT INTO STUDENT_DB.dbo.student_db
(student_id, student_name, student_sex, room_id)
VALUES('0009', 'Obama', 'Male', 'M101')
INSERT INTO STUDENT_DB.dbo.student_db
(student_id, student_name, student_sex, room_id)
VALUES('0010', 'Lucy', 'Female', 'F101')

Insert data into the form STUDENT_DB.dbo.maintenance_application_db

1
2
3
4
5
6
7

/* Insert data into the form STUDENT_DB.dbo.maintenance_application_db */
INSERT INTO STUDENT_DB.dbo.maintenance_application_db
(maintenance_application, room_id)
VALUES ('Door damage', 'M102')
INSERT INTO STUDENT_DB.dbo.maintenance_application_db
(maintenance_application, room_id)
VALUES ('Faucet damage', 'F102')

Insert data into the form STUDENT_DB.dbo.maintenance_personnel_db

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

/* Insert data into the form STUDENT_DB.dbo.maintenance_personnel_db */
INSERT INTO STUDENT_DB.dbo.maintenance_personnel_db
(maintenance_personnel_id, maintenance_personnel_name, maintenance_personnel_sex, working_time, off_work_time)
VALUES('001', 'Roosevelt', 'Male', '2018-12-22T09:00:00', '2018-12-22T22:00:00')
INSERT INTO STUDENT_DB.dbo.maintenance_personnel_db
(maintenance_personnel_id, maintenance_personnel_name, maintenance_personnel_sex)
VALUES('002', 'Kennedy', 'Male')
INSERT INTO STUDENT_DB.dbo.maintenance_personnel_db
(maintenance_personnel_id, maintenance_personnel_name, maintenance_personnel_sex, working_time, off_work_time)
VALUES('003', 'Nannie', 'Female', '2018-12-22T09:00:00', '2018-12-22T22:00:00')
INSERT INTO STUDENT_DB.dbo.maintenance_personnel_db
(maintenance_personnel_id, maintenance_personnel_name, maintenance_personnel_sex)
VALUES('004', 'Dawn', 'Female')

(Example) Features

Check student accommodation information.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

/*
Check student accommodation information
INPUT @stu_name -- The name of the student being queried
*/

DECLARE @stu_name nchar(10)
-- Check accommodation information in Mogeko
SET @stu_name = 'Mogeko'

SELECT student_id, student_name, student_sex, room_id
FROM STUDENT_DB.dbo.student_db
WHERE student_name=@stu_name

Newborn stay.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

/*
Newborn stay
INPUT @Stu_id -- The ID of nweborn
INPUT @Stu_name -- The name of newborn
INPUT @Stu_sex -- The gender of newborn
INPUT @room_id -- The room number of newborn
*/

DECLARE @Stu_id nchar(10)
DECLARE @Stu_name nchar(10)
DECLARE @Stu_sex nchar(10)
DECLARE @room_id nchar(10)

-- New student information
SET @Stu_id = '0011'
SET @Stu_name = 'Bob'
SET @Stu_sex = 'Male'
SET @room_id = 'M103'

INSERT INTO STUDENT_DB.dbo.student_db
(student_id, student_name, student_sex, room_id)
VALUES (@Stu_id, @Stu_name, @Stu_sex, @room_id)

Students move out of the bedroom.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

/*
Students move out of the bedroom
INPUT Stu_id -- The ID of the student
*/

DECLARE @Stu_id nchar(10)
-- Input the ID of the student
SET @Stu_id = '0011'

DELETE FROM STUDENT_DB.dbo.student_db
WHERE student_id = @Stu_id

Submit a repair request.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

/*
Submit a repair request
INPUT @request -- Repair application
INPUT @room_id -- Repair location
*/

DECLARE @requst varchar(50)
DECLARE @room_id nchar(10)

-- Repair application information
SET @requst = 'The cabinet was damaged'
SET @room_id = 'M101'

INSERT INTO STUDENT_DB.dbo.maintenance_application_db
(maintenance_application, room_id)
VALUES (@requst, @room_id)

Check the repair application list.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

/*
Check the repair application list
INPUT @staff_name -- Name of the maintenance person
*/

DECLARE @staff_name nchar(10)

-- Input the name of the maintenance person
SET @staff_name = 'Roosevelt'

SELECT maintenance_application_id, maintenance_application, room_id
FROM STUDENT_DB.dbo.maintenance_application_db
WHERE (maintenance_personnel_id is NULL) and room_id in (
 SELECT room_id
 FROM STUDENT_DB.dbo.dormitry_db
 WHERE dormitry_types = (
  SELECT maintenance_personnel_sex
  FROM STUDENT_DB.dbo.maintenance_personnel_db
  WHERE maintenance_personnel_name = @staff_name
 )
)

Accept repair request.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

/*
Accept repair request
INPUT @staff_id -- ID of the maintenance person
INPUT @application_id -- ID of the maintenance application
*/

DECLARE @staff_id nchar(10)
DECLARE @application_id int

-- Input the ID of the maintenance person
SET @staff_id = '001'
-- Input the ID of the maintenance application
SET @application_id = 3

UPDATE STUDENT_DB.dbo.maintenance_application_db
SET maintenance_personnel_id = @staff_id
WHERE maintenance_application_id = @application_id

Complete repair.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

/*
Complete repair
INPUT @application_id -- ID of the maintenance application
*/

DECLARE @application_id int

-- Input the ID of the maintenance application
SET @application_id = 3

UPDATE STUDENT_DB.dbo.maintenance_application_db
SET work_status = 1
WHERE maintenance_application_id = @application_id
分享
Mogeko
作者
Mogeko
I am a student of Debrecen University, Hungary, majoring in Computer Science. I hope to be a Full-stack in the future.