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🔗

Table design🔗

student_db:

administrator_db:

dormitry_db:

maintenance_personnel_db:

maintenance_application_db:

Database implementation🔗

Case data🔗

student_db:

administrator_db:

dormitry_db:

maintenance_personnel_db:

maintenance_application_db:

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 student_name=@stu_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