Teacher Class Creation Process

Step 1 - System Login

The teacher logs into the system by entering their email and password. The password is hashed and checked against the database.

SELECT id_user, username 
FROM User 
WHERE email = :input_email AND password = :hashed_input_pass;

Step 2 - Teacher Dashboard

After logging in, the teacher can see the subjects and classes they are assigned to.

List Subjects Assigned to the Teacher

SELECT DISTINCT s.id_subject, s.name AS subject_name
FROM Teacher t
JOIN Course c ON t.id_teacher = c.created_by
JOIN Subject_Course sc ON c.id_Course = sc.id_Course
JOIN Subject s ON sc.id_subject = s.id_subject
WHERE t.id_user = :curr_usr_id;

Displays all subjects the teacher is responsible for.

List Teacher’s Upcoming Classes

SELECT cl.id_class, cl.start_time, cl.end_time, cl.duration, c.id_Course, s.name AS subject_name
FROM Class cl
JOIN Teacher t ON cl.teacher_id = t.id_teacher
JOIN Course_Class cc ON cl.id_class = cc.id_class
JOIN Course c ON cc.id_Course = c.id_Course
JOIN Subject_Course sc ON c.id_Course = sc.id_Course
JOIN Subject s ON sc.id_subject = s.id_subject
WHERE t.id_user = :curr_usr_id AND cl.start_time >= CURRENT_TIME;

Lists upcoming classes assigned to the teacher.

Step 3 - Creating a New Class

The teacher clicks the "Create New Class" button and is redirected to a form to enter class details.

Step 4 - Filling Out the Class Creation Form

The teacher selects:

• Subject – From the list of subjects they teach.
• Start Time – Class start time (HH:MM:SS).
• End Time – Class end time (HH:MM:SS).
• Duration – Automatically calculated based on start and end time.
• Course – The course that this class belongs to.

Retrieve Subjects for Selection

SELECT DISTINCT s.id_subject, s.name 
FROM Subject s
JOIN Subject_Course sc ON s.id_subject = sc.id_subject
JOIN Course c ON sc.id_Course = c.id_Course
WHERE c.created_by = :curr_teacher_id;

Step 5 - Submitting the Class to the Database

After filling in the form, the system inserts the new class record.

Insert New Class

INSERT INTO Class (id_class, start_time, end_time, duration, teacher_id)
VALUES (:id_class, :start_time, :end_time, TIMESTAMPDIFF(MINUTE, :start_time, :end_time), :teacher_id);

Duration is automatically calculated using TIMESTAMPDIFF().

Link the Class to a Course

INSERT INTO Course_Class (id_Course, id_class)
VALUES (:selected_course_id, :id_class);

Links the new class to an existing course.

Step 6 - Confirmation & Viewing the Schedule

Once the new class is created, the teacher can view their updated schedule.

Retrieve Updated Class Schedule

SELECT cl.id_class, cl.start_time, cl.end_time, cl.duration, s.name AS subject_name
FROM Class cl
JOIN Course_Class cc ON cl.id_class = cc.id_class
JOIN Course c ON cc.id_Course = c.id_Course
JOIN Subject_Course sc ON c.id_Course = sc.id_Course
JOIN Subject s ON sc.id_subject = s.id_subject
WHERE cl.teacher_id = :curr_teacher_id
ORDER BY cl.start_time ASC;

Shows all upcoming classes assigned to the teacher.

Step 7 - Managing Classes

Teachers can modify or cancel classes if needed.

Cancel a Class

DELETE FROM Class WHERE id_class = :id_class;

Completely removes the class from the schedule.

Update Class Timing

UPDATE Class 
SET start_time = :new_start_time, 
    end_time = :new_end_time, 
    duration = TIMESTAMPDIFF(MINUTE, :new_start_time, :new_end_time)
WHERE id_class = :id_class;

Updates the class timing dynamically.