College Student & Course Management System

Introduction

In this post, I am sharing a simple Database Management System (DBMS) mini project using SQL (Oracle).
I designed a database schema for a college system that manages Students, Faculty, Courses, and Enrollments.
Then I implemented different queries step by step — from table creation to stored procedures.

🗂 Database Schema

Students table → stores student details

Faculty table → stores faculty details

Courses table → stores courses offered

Enrollments table → connects students and courses (many-to-many relationship)

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

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

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

-- Enrollments table (Many-to-Many relationship)
CREATE TABLE Enrollments (
EnrollID NUMBER PRIMARY KEY,
StudentID NUMBER REFERENCES Students(StudentID),
CourseID NUMBER REFERENCES Courses(CourseID),
Grade CHAR(2)
);

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'Arun Kumar', 'CSE', TO_DATE('2004-03-15','YYYY-MM-DD'), '[e-mail]');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'Priya Sharma', 'ECE', TO_DATE('2003-07-22','YYYY-MM-DD'), '[e-mail]');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'Rahul Raj', 'MECH', TO_DATE('2004-11-10','YYYY-MM-DD'), '[e-mail]');

ALTER TABLE Students
ADD PhoneNo VARCHAR2(10);

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

SELECT
UPPER(Name) AS StudentName_Uppercase,
LENGTH(Email) AS Email_Length
FROM Students;

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

Through this mini-project, I practiced DDL, DML, Constraints, Joins, Group By, Views, and Stored Procedures in SQL.
This step-by-step approach gave me a solid understanding of how a database schema works in real-world academic systems.

Thanks to @santhoshnc for guiding and supporting us.