Student Class Selection Process

Step 1 - System Login

The student logs into the system using their email and password. The password is securely hashed before verification.

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

Note: :hashed_input_pass is securely hashed before being checked.

Step 2 - Student Dashboard

After logging in, the student can see their enrolled courses and available classes.

List Enrolled Courses

SELECT c.id_Course, s.name AS subject_name, c.num_of_classes
FROM Enrollment e
JOIN Course c ON e.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 e.id_student = :curr_student_id;

Displays the courses the student is currently enrolled in.

List Upcoming Available Classes

SELECT cl.id_class, cl.start_time, cl.end_time, cl.duration, t.id_teacher, u.username AS teacher_name, s.name AS subject_name
FROM Class cl
JOIN Teacher t ON cl.teacher_id = t.id_teacher
JOIN User u ON t.id_user = u.id_user
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.start_time >= CURRENT_TIME
AND c.id_Course IN (SELECT id_Course FROM Enrollment WHERE id_student = :curr_student_id);

Lists upcoming classes for courses the student is enrolled in.

Step 3 - Selecting a Class to Attend

The student clicks on an "Attend Class" button next to an available class.

Step 4 - Registering Attendance

The system registers the student's attendance for the selected class.

Insert Attendance Record

INSERT INTO Attendance (id_attendance, id_student, id_class, attended)
VALUES (:new_attendance_id, :curr_student_id, :selected_class_id, TRUE);

Marks the student as attending the selected class.

Step 5 - Viewing Attendance History

The student can review their past attendance records.

Retrieve Attendance History

SELECT a.id_class, cl.start_time, cl.end_time, cl.duration, s.name AS subject_name, a.attended
FROM Attendance a
JOIN Class cl ON a.id_class = cl.id_class
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 a.id_student = :curr_student_id
ORDER BY cl.start_time DESC;

Displays all past classes the student attended.

Step 6 - Checking Completed Classes

The student can see which classes they have completed.

Retrieve Completed Classes

SELECT DISTINCT cl.id_class, cl.start_time, cl.end_time, cl.duration, s.name AS subject_name
FROM Attendance a
JOIN Class cl ON a.id_class = cl.id_class
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 a.id_student = :curr_student_id AND cl.end_time <= CURRENT_TIME;

Lists all classes the student has already completed.

Step 7 - Unregistering from a Class (Optional)

If a student mistakenly registers for a class, they can remove themselves.

Remove Attendance Record

DELETE FROM Attendance 
WHERE id_student = :curr_student_id AND id_class = :selected_class_id;

Removes the student's attendance entry.