College Student & Course Management System

Introduction

In this post, I am sharing a Database Management System (DBMS) small project using SQL (Oracle).
Managing students, courses, and enrollments is a common use case in universities and colleges.
To implement this, we can design a simple database system using SQL.
This post walks through DDL, DML, Joins, Views, Procedures, and Functions step by step with examples.

• Database Schema

Students table → stores student details

Faculty table → stores faculty details

Courses table → stores courses offered

Enrollments table → connects students and courses

StudentID

StudentID → NUMBER, Primary Key

Name → VARCHAR2(50), NOT NULL

Dept → VARCHAR2(30)

DOB → DATE

Email → VARCHAR2(50), UNIQUE

PhoneNo → CHAR(10), must be 10 digits

Courses Table

CourseID → NUMBER, Primary Key

CourseName → VARCHAR2(50), NOT NULL

Credits → NUMBER(2), must be between 1 and 5

Enrollments Table

EnrollID → NUMBER, Primary Key

StudentID → NUMBER, Foreign Key → Students(StudentID)

CourseID → NUMBER, Foreign Key → Courses(CourseID)

Grade → CHAR(2)

Faculty Table

FacultyID → NUMBER, Primary Key

FacultyName → VARCHAR2(50), NOT NULL

Dept → VARCHAR2(30)

Email → VARCHAR2(50), UNIQUE

Relationships

One Student can have many Enrollments

One Course can have many Enrollments

Enrollments table = Junction table (Many-to-Many between Students and Courses)

Faculty can later be linked to Courses

• Relationships

One Student → Many Enrollments

One Course → Many Enrollments

Enrollments acts as a junction table (Many-to-Many) between Students and Courses.

Faculty can be linked later to Courses (One-to-Many).

• Create Tables (DDL)

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

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

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

• Insert Sample Data (DML)

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'Arun Kumar', 'CSE', DATE '2003-05-14', '[e-mail]');

INSERT INTO Courses (CourseID, CourseName, Credits)
VALUES (101, 'Database Systems', 4);

• Alter & Constraints

ALTER TABLE Students ADD PhoneNo CHAR(10);

ALTER TABLE Courses
ADD CONSTRAINT chk_credits CHECK (Credits BETWEEN 1 AND 5);

• Functions & Aggregates

SELECT UPPER(Name), LENGTH(Email) FROM Students;
SELECT AVG(Credits) FROM Courses;
SELECT COUNT(DISTINCT StudentID) FROM Enrollments;

• Joins & Views

SELECT s.Name, c.CourseName, e.Grade
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON e.CourseID = c.CourseID;

CREATE VIEW StudentCoursesView AS
SELECT s.Name, c.CourseName, e.Grade
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON e.CourseID = c.CourseID;

• Stored Procedure

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;
/

• Conclusion

This simple College Student & Course Management System demonstrates how SQL can be used to handle real-world scenarios like student registrations, course management, enrollments, grading, and reporting.
By using DDL, DML, Constraints, Aggregates, Joins, Views, and Stored Procedures, we built a robust and structured system.
This step-by-step approach gave me a solid understanding of how a database schema works in real-world academic systems

Thanks to @santhoshnc sir for guiding and motivating us.

Comments

[Jaiwant Karrun]

Nice work! It was so impressive and can understand easily about SQL commands from your blog