Student Management System Using SQL – A Practical Project

Overview

A Student Management System (SMS) is a database application used to store, manage, and retrieve information about students, courses, enrollments, grades, and other academic records. Building this system with SQL is an excellent way to learn how to design databases, create relationships, and perform queries using real-world data.

Core Features of a Student Management System

Store student information
Manage courses and enrollments
Track student grades
Generate reports (e.g., student progress, course rosters)
Implement CRUD operations (Create, Read, Update, Delete)

Step-by-Step Database Design

1. Students Table

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    date_of_birth DATE,
    email VARCHAR(100),
    gender CHAR(1)
);

2. Courses Table

CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100),
    course_code VARCHAR(10),
    credits INT
);

3. Enrollments Table

CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

4. Grades Table

CREATE TABLE grades (
    grade_id INT PRIMARY KEY AUTO_INCREMENT,
    enrollment_id INT,
    grade VARCHAR(2),
    FOREIGN KEY (enrollment_id) REFERENCES enrollments(enrollment_id)
);

Sample Queries

Add a New Student

INSERT INTO students (first_name, last_name, date_of_birth, email, gender)
VALUES ('Alice', 'Smith', '2004-08-15', 'alice.smith@example.com', 'F');

Enroll a Student in a Course

INSERT INTO enrollments (student_id, course_id, enrollment_date)
VALUES (1, 101, CURDATE());

Update a Student's Grade

UPDATE grades
SET grade = 'A'
WHERE enrollment_id = 1;

List All Students in a Course

SELECT s.first_name, s.last_name
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
WHERE e.course_id = 101;

Calculate Average Grade for a Course

SELECT course_id, AVG(CASE
    WHEN grade = 'A' THEN 4
    WHEN grade = 'B' THEN 3
    WHEN grade = 'C' THEN 2
    WHEN grade = 'D' THEN 1
    ELSE 0
END) AS gpa
FROM enrollments
JOIN grades ON enrollments.enrollment_id = grades.enrollment_id
GROUP BY course_id;

Best Practices

Normalize tables to avoid data redundancy
Use foreign keys to maintain referential integrity
Index frequently queried columns (e.g., student_id)
Create views for common reports
Use transactions when updating multiple related tables