Introduction
Let me be honest SQL has been giving me a hard time for a while. So, I was getting stuck even with the basics. So I decided to stop running away and build a real project from scratch: a student enrollment system.
I wanted something simple but useful, where I could understand how real systems are built with SQL,,including relationships, foreign keys, and triggers. This is how it went down.
What the System Does
This project is all about managing a small school system. It handles:
- Storing student info (name, email, date of birth)
- Keeping track of instructors
- Linking courses to instructors
- Enrolling students to courses
- Logging every enrollment using a trigger
Tables I Created
| Table | Description |
|---|---|
| Students | Stores student details like name and date of birth |
| Instructors | Stores instructor info |
| Courses | Each course is taught by one instructor |
| Enrollments | Shows which student is taking which course |
| Enrollment_Log | Automatically logs new enrollments using a trigger |
SQL Code I Used
1. Students Table
CREATE TABLE Students (
student_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100) UNIQUE,
date_of_birth DATE
);
2. Instructors Table
CREATE TABLE Instructors (
instructor_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100) UNIQUE
);
3. Courses Table
CREATE TABLE Courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(100),
instructor_id INT,
FOREIGN KEY (instructor_id) REFERENCES Instructors(instructor_id)
);
4. Enrollments Table
CREATE TABLE Enrollments (
enrollment_id INT PRIMARY KEY,
student_id INT,
course_id INT,
grade CHAR(2),
FOREIGN KEY (student_id) REFERENCES Students(student_id),
FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);
5. Enrollment Log Table
CREATE TABLE Enrollment_Log (
log_id INT PRIMARY KEY AUTO_INCREMENT,
enrollment_id INT,
log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
6. Trigger to Log Enrollments
CREATE TRIGGER LogNewEnrollment
AFTER INSERT ON Enrollments
FOR EACH ROW
INSERT INTO Enrollment_Log (enrollment_id)
VALUES (NEW.enrollment_id);
Sample Data I Added
1.Instructors
INSERT INTO Instructors (instructor_id, first_name, last_name, email) VALUES
(1, 'Thomas', 'Ndegwa', 'thomas.ndegwa@example.com'),
(2, 'Lilian', 'Achieng', 'lilian.a@example.com'),
(3, 'George', 'Kariuki', 'george.kariuki@example.com');
2. Students
INSERT INTO Students (student_id, first_name, last_name, email, date_of_birth) VALUES
(101, 'Alice', 'Mwende', 'alice.mwende@example.com', '2002-04-12'),
(102, 'Brian', 'Otieno', 'brian.otieno@example.com', '2001-08-05');
3. Courses
INSERT INTO Courses (course_id, course_name, instructor_id) VALUES
(201, 'Database Systems', 1),
(202, 'Python Programming', 2),
(203, 'Data Analytics', 3);
4.Enrollments
INSERT INTO Enrollments (enrollment_id, student_id, course_id, grade) VALUES
(301, 101, 201, 'A'),
(302, 102, 202, 'B');
What I Learned
Through this project, I’ve learned:
How to design related tables using foreign keys
How to keep data clean and connected
How to use triggers to automate tasks (like logging)
The importance of thinking through the whole structure before writing code
This is way better than just memorizing;
SELECT * FROM table;
What I Might Add Later
Add user logins for students and instructors
Store attendance and class schedules
Build a simple dashboard with Power BI
Final Thoughts
This was a small project, but it really helped me understand SQL better. I now see why people say the database is the heart of any system.
If you're learning SQL, I encourage you to try something small like this,it makes everything more clear.
Top comments (1)
Nice read.