College Student & Course Management System

INTRODUCTION

Hi everyone

I’m a college student currently learning about databases, and I recently worked on an assignment where I had to build a Student & Course Management System using SQL. It was a great hands-on way to practice what i have been learning in class like creating tables, inserting data, using joins, writing queries, and even building stored procedures.

In this blog post, I will walk you through everything I did step by step. If you're also learning SQL or working on a similar project, I hope this helps you understand the concepts more clearly.

📘 Assignment Objective

Design and create tables for Students, Courses, Enrollments, and Faculty

Use DDL, DML, constraints, joins, views, and a stored procedure

Demonstrate data manipulation, querying, and basic database programming

While applying what we learned in class to build a real world style database schema.

Step-by-Step Implementation

1. Creating the Core Tables

I started by creating the base tables:

Students Table
CREATE TABLE Students (
StudentID NUMBER PRIMARY KEY,
Name VARCHAR2(50) NOT NULL,
Dept VARCHAR2(30),
DOB DATE,
Email VARCHAR2(50) UNIQUE
);

Courses Table
CREATE TABLE Courses (
CourseID NUMBER PRIMARY KEY,
CourseName VARCHAR2(50) NOT NULL,
Credits NUMBER(2)
);

Enrollments Table
CREATE TABLE Enrollments (
EnrollID NUMBER PRIMARY KEY,
StudentID NUMBER REFERENCES Students(StudentID),
CourseID NUMBER REFERENCES Courses(CourseID),
Grade CHAR(2)
);

Faculty Table
CREATE TABLE Faculty (
FacultyID NUMBER PRIMARY KEY,
FacultyName VARCHAR2(50) NOT NULL,
Dept VARCHAR2(30),
Email VARCHAR2(50) UNIQUE
);

Insight: Creating relationships between tables helped me understand the concept of foreign keys and how they maintain referential integrity.

1. Inserting Sample Data INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES (1, 'CATHERINE', 'Computer Science', TO_DATE('2001-05-15', 'YYYY-MM-DD'), '[e-mail]');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES
(2, 'FAITH', 'Mechanical Engineering', TO_DATE('2000-08-22', 'YYYY-MM-DD'), '[e-mail]');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES
(3, 'Charlie Brown', 'Physics', TO_DATE('2002-01-10', 'YYYY-MM-DD'), '[e-mail]');

Insight: Practicing DML helped me get more comfortable with date formats and inserting accurate test data.

1. Altering the Table
   ALTER TABLE Students
   ADD PhoneNo NUMBER(10);

2. Defining Constraints
   ALTER TABLE Courses
   MODIFY Credits NUMBER(2) CHECK (Credits BETWEEN 1 AND 5);

Insight: Adding constraints felt like writing rules into the database. It prevents errors before they happen.

1. Using SQL Functions SELECT UPPER(Name) AS UpperCaseName, LENGTH(Email) AS EmailLength FROM Students;

6.Aggregate Functions
SELECT AVG(Credits) AS AvgCredits FROM Courses;
SELECT COUNT(DISTINCT StudentID) AS TotalStudentsEnrolled FROM Enrollments;

1. JOIN Operation SELECT S.Name AS StudentName, C.CourseName, E.Grade FROM Students S JOIN Enrollments E ON S.StudentID = E.StudentID JOIN Courses C ON C.CourseID = E.CourseID;

Insight: This was the "aha!" moment where everything connected. I saw how normalized tables work together through joins.

1. GROUP BY with HAVING SELECT Dept, COUNT() AS StudentCount FROM Students GROUP BY Dept HAVING COUNT() > 2;

9.Creating a View
CREATE VIEW StudentCoursesView AS
SELECT
S.Name AS StudentName,
C.CourseName,
E.Grade
FROM Students S
JOIN Enrollments E ON S.StudentID = E.StudentID
JOIN Courses C ON C.CourseID = E.CourseID;

Insight: Views make it easy to create simplified representations of complex joins. Perfect for reporting.

1. Stored Procedure to Update Grades
   CREATE OR REPLACE PROCEDURE UpdateGrade (
   p_StudentID IN NUMBER,
   p_CourseID IN NUMBER,
   p_NewGrade IN CHAR
   )
   AS
   BEGIN
   UPDATE Enrollments
   SET Grade = p_NewGrade
   WHERE StudentID = p_StudentID AND CourseID = p_CourseID;

   COMMIT;
   END;

Insight: Writing procedures was new for me. I learned how SQL can include some logic — not just data manipulation.

what i learnt

How normalization and foreign keys maintain database integrity

Importance of constraints for data validation

Power of joins and how they reflect real-world relationships

Views and procedures make data handling cleaner and more reusable

Final Thoughts

As a student, doing this assignment really helped me understand SQL beyond just the syntax. I got to apply it to a mini real-world use case, and it made concepts like joins, constraints, and stored procedures click.

Image

Image

Image

Image

Image

image

Image

Image