Building a Student Registration System with SQLite (Part 2)

Completing the student registration project with data operations.

In Part 1, we set up the database schema for a student registration system, including tables for students, courses, and enrollments. Now, in Part 2, we will focus on the data operations that power the system: enrolling students in courses, managing course capacities, updating grades, and handling student withdrawals.

These data operations are the core functions that make the student registration system work. We will walk through each step and show how to use SQL queries to implement them effectively.

Step 1: Enrolling a Student in a Course

The first operation we need to handle is enrolling a student in a course. This is a common action in any student registration system. To do this, we need to insert a record into the enrollments table that links the student to the course they are enrolling in.

Enrolling a Student

Let’s assume we have a student with student_id = 1 and a course with course_id = 2. The following SQL query will enroll the student in the course:

-- Enroll a student in a course
INSERT INTO enrollments (student_id, course_id, enrollment_date)
VALUES (1, 2, '2025-02-01');

This operation is relatively straightforward: it inserts a row into the enrollments table, linking the student to the course they’ve chosen.

Important Consideration

Before performing this operation, we should check if there’s space available in the course (i.e., the max_enrollment field has not been reached). If there are no spots available, we should prevent the enrollment from happening.

Step 2: Checking Course Capacity Before Enrolling

To check the capacity of a course before enrolling a student, we can query the number of students already enrolled in that course and compare it with the course’s max_enrollment.

Here’s the query to check the available spots in a course:

-- Check if there’s room in the course
SELECT max_enrollment - (SELECT COUNT(*) FROM enrollments WHERE course_id = 2) AS available_spots
FROM courses WHERE course_id = 2;

This query calculates the number of available spots by subtracting the number of enrolled students from the maximum enrollment limit. If the result is greater than zero, the course has space available, and the student can be enrolled.

Step 3: Updating Course Capacities

Once a student is successfully enrolled in a course, we need to update the course’s available spots. This is an important step in managing course enrollments and ensuring that no course exceeds its capacity.

We can update the course capacity like this:

-- Update the course capacity after enrolling a student
UPDATE courses
SET max_enrollment = max_enrollment - 1
WHERE course_id = 2;

This query reduces the available spots for the course by one. This ensures that we accurately track how many students are enrolled in each course.

We can also adjust the capacity dynamically based on certain conditions (e.g., drop/add classes during the registration period).

Step 4: Managing Grades

Once a student completes a course, we need to assign a grade. Let’s assume that the grading process happens at the end of the course. The enrollments table should be updated to reflect the student’s grade.

Here’s the query to update the grade for a student:

-- Update the grade for a student in a course
UPDATE enrollments
SET grade = 'A'
WHERE student_id = 1 AND course_id = 2;

This SQL query updates the grade field for a particular student and course, indicating that the student has completed the course and received a grade.

Step 5: Handling Course Cancellations or Student Withdrawals

In the case where a student withdraws from a course or the course is canceled, we need to remove the enrollment and adjust the available spots.

Removing an Enrollment

Sometimes, students may need to withdraw from a course, or the course may be canceled. In this case, we need to remove the enrollment and update the course’s available spots.

-- Remove a student from a course
DELETE FROM enrollments WHERE student_id = 1 AND course_id = 2;

This query deletes the enrollment record for the student in the specified course. Afterward, we can update the course’s available spots:

Updating Available Spots After Withdrawal

Once the enrollment is removed, we need to update the course’s available spots:

-- Increase the available spots after a student withdraws
UPDATE courses
SET max_enrollment = max_enrollment + 1
WHERE course_id = 2;

This query increases the available spots in the course by one, reflecting that a student has withdrawn and there’s now space for another student.

Step 6: Automating Common Operations with Triggers

We can use triggers to automate certain operations, reducing the need for manual intervention. For example, when a student enrolls in a course, we can create a trigger that automatically reduces the available spots for that course.

Here’s a trigger that updates the course capacity after a new enrollment is inserted:

CREATE TRIGGER update_capacity_after_enroll
AFTER INSERT ON enrollments
FOR EACH ROW
BEGIN
    UPDATE courses
    SET max_enrollment = max_enrollment - 1
    WHERE course_id = NEW.course_id;
END;

This trigger automatically reduces the available spots for a course after a new enrollment is added. It helps keep the course data up to date without requiring additional queries.

Step 7: Handling Complex Scenarios

As your system grows, you may encounter more complex scenarios, such as managing prerequisites (where a student must complete one course before enrolling in another) or waitlists (where students can be added to a waitlist if a course is full). These types of features can be implemented with additional database queries or triggers, depending on your requirements.

Conclusion

In Part 2, we’ve implemented the core data operations for our student registration system. We covered how to enroll students, check course capacity, update course availability, manage grades, and handle student withdrawals. We also explored how triggers can automate common tasks, like updating course capacities after an enrollment.

Now that these operations are in place, we can continue refining the system by adding more advanced features, such as student notifications, course prerequisites, or reporting features for admins.

In Part 3, we’ll explore how to add some of these advanced features to enhance the functionality of our system.

Subscribe Now

Stay updated with the latest tips and tricks for building efficient SQLite systems! Subscribe now to receive expert advice and updates directly in your inbox. Don't miss out on future posts, and join our community at the SQLite Forum to ask questions, share experiences, and connect with fellow developers.