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_id nchar(10) False
student_name nchar(10) False
student_sex nchar(10) False
room_id nchar(10) False

administrator_db:

列名 数据类型 允许 Null 值
administrator_id nchar(10) False
administrator_name nchar(10) False
administrator_sex nchar(10) False

dormitry_db:

列名 数据类型 允许 Null 值
room_id nchar(10) False
dormitry_types nchar(10) False
administrator_id nchar(10) False

maintenance_personnel_db:

列名 数据类型 允许 Null 值
maintenance_personne_id nchar(10) False
maintenance_personnel_name nchar(10) False
maintenance_personnel_sex nchar(10) False
working_time datetime True
off_work_time datetime True

maintenance_application_db:

列名 数据类型 允许 Null 值
maintenance_application_id int IDENTITY(1, 1) False
maintenance_application varchar(50) False
room_id nchar(10) False
maintenance_personnel_id nchar(10) True
work_status bit DEFAULT(0) False

Database implementation

Case data

student_db:

student_id student_name student_sex room_id
0001 Mogeko Male M101
0002 Ezie Female F101
0003 Washington Male M101
0004 Licoln Male M102
0005 Daisy Female F101
0006 Trump Male M101
0007 Noah Male M102
0008 Lucy Female F101
0009 Obama Male M101
0010 Lucy Female F101

administrator_db:

administrator_id administrator_name administrator_sex
001 Herrington Male
002 Van Male
003 Rossett Male
004 Jade Female
005 Eva Female
006 May Female

dormitry_db:

room_id dormitry_types administrator_id
M101 Male 001
M102 Male 001
M103 Male 001
M104 Male 002
M105 Male 002
F101 Female 004
F102 Female 004
F103 Female 004
F104 Female 004
F105 Female 004

maintenance_personnel_db:

maintenance_personnel_id maintenance_personnel_name maintenance_personnel_sex working_time off_work_time
001 Roosevelt Male 2018-12-22T09:00:00 2018-12T22 22:00:00
002 Kennedy Male
003 Nannie Female 2018-12-22T09:00:00 2018-12-22T22:00:00
004 Dawn Female

maintenance_application_db:

maintenance_application_id maintenance_application room_id maintenance_personnel_id work_status
1 Door damage M102 001 0
2 Faucet damage F101 0

Import case data

Create the tables

/*
 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

/* 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

/* 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

/* 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

/* 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

/* 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

/* 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.

/*
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 [email protected]_name

Newborn stay.

/*
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.

/*
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.

/*
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.

/*
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.

/*
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.

/*
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