College Student & Course Management System using Oracle SQL

Introduction

This article presents the implementation of a College Student & Course Management System using Oracle SQL. The system demonstrates key relational database concepts including table creation, constraints, data insertion, queries, views, and stored procedures.

The schema supports student information, course details, faculty records, and student-course enrollments, making it suitable for academic institution management.

Database Schema

The database consists of four primary entities:

• Students – Student details.
• Courses – Course metadata.
• Enrollments – Student–Course relationships with grades.
• Faculty – Faculty information.

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

Data Insertion
INSERT INTO Students VALUES (1, 'Siva', 'CSE', DATE '2003-03-12', '[e-mail]');
INSERT INTO Students VALUES (2, 'Surya', 'ECE', DATE '2002-04-11', '[e-mail]');
INSERT INTO Students VALUES (3, 'Dinesh', 'MECH', DATE '2001-10-25', '[e-mail]');

INSERT INTO Courses VALUES (101, 'DBMS', 4);
INSERT INTO Courses VALUES (102, 'OS', 3);
INSERT INTO Courses VALUES (103, 'Networks', 5);

INSERT INTO Enrollments VALUES (1, 1, 101, 'A');
INSERT INTO Enrollments VALUES (2, 1, 102, 'B');
INSERT INTO Enrollments VALUES (3, 2, 101, 'A');
INSERT INTO Enrollments VALUES (4, 3, 103, 'C');

Table Alterations and Constraints
ALTER TABLE Students ADD PhoneNo VARCHAR2(10);
ALTER TABLE Courses
ADD CONSTRAINT chk_credits CHECK (Credits BETWEEN 1 AND 5);

Query Operations

• String Functions and Length Calculation
  SELECT UPPER(Name) AS StudentName, LENGTH(Email) AS EmailLength
  FROM Students;

• Aggregate Functions Across Tables
  SELECT AVG(Credits) AS AvgCredits, COUNT(*) AS TotalStudents
  FROM Students, Courses;

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

Grouping and Filtering
SELECT Dept, COUNT() AS StudentCount
FROM Students
GROUP BY Dept
HAVING COUNT() > 2;

Database 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 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;
END;

Conclusion
This system forms the foundation for an extensible academic management solution, which can be expanded to support attendance tracking, fee management, and academic analytics.