COLLEGE STUDENT & COURSE MANAGEMENT SYSTEM

Introduction:

In this blog, we explore the design and implementation of a College Student & Course Management System using Oracle SQL on the LiveSQL platform. The system highlights how different SQL operations can be applied to manage and organize academic data including students, courses, faculty, and enrollments.

The project mainly focuses on:
=>Creating tables to represent different entities.
=>Inserting records to populate the database with sample data. =>Applying constraints to maintain accuracy and consistency.
=>Running queries with functions, aggregates, joins, and group operations.
=>Building a view for simplified access to combined data.
=>Writing a stored procedure to handle automated updates.

By implementing this system, we get a clear understanding of how SQL can be used in a real-world college scenario to handle tasks such as student course registrations, faculty records, grade management, and department-level statistics.

SCHEMA DESIGN FOR COLLEGE MANAGEMENT SYSTEM:

The designed database has four core tables: Students, Courses, Enrollments, and Faculty.
Each table holds specific information and is connected through primary and foreign keys to maintain proper relationships.

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

1. DDL – Create a Table

=> DDL defines the structure of database objects (tables, views, indexes).
=> We created a Faculty table with FacultyID, FacultyName, Dept, and Email.

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

2. DML – Insert Data

=> DDML (Data Manipulation Language) is used to add, update, delete, or fetch records in a table.
=> We inserted 3 faculty members (Dr. Neha Sharma, Dr. Rajeev Nair, Dr. Meena Reddy) into the Faculty table using INSERT INTO.

INSERT INTO Faculty (FacultyID, FacultyName, Dept, Email)
VALUES (1, 'Dr. Neha Sharma', 'CSE', '[e-mail]');

INSERT INTO Faculty (FacultyID, FacultyName, Dept, Email)
VALUES (2, 'Dr. Rajeev Nair', 'ECE', '[e-mail]');

INSERT INTO Faculty (FacultyID, FacultyName, Dept, Email)
VALUES (3, 'Dr. Meena Reddy', 'MECH', '[e-mail]');

3. ALTER Table – Add Column

=> ALTER modifies the structure of an existing table (add/modify/drop columns).
=> We added a new column PhoneNo in the Students table.

ALTER TABLE Students
ADD PhoneNo NUMBER(10);

4. Defining Constraints

=> Constraints enforce rules on data to maintain integrity (like N NULL, UNIQUE, CHECK).
=> We applied a CHECK constraint so that Credits in Courses must be between 1 and 5.

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

5. SELECT with Functions

=> Functions in SELECT perform operations like text conversion, length calculation, etc.
=> We displayed student names in uppercase and showed the length of their email IDs.

SELECT Name, UPPER(Name) AS UpperName, LENGTH(Email) AS EmailLength
FROM Students;

6. Aggregate Functions

=> Aggregate functions are used to perform calculations on a set of values and return a single result. Common functions include AVG, COUNT, SUM, MIN, and MAX. They are often used in reporting and analysis.
=> SELECT AVG(Credits) AS Avg_Credits FROM Courses; → calculated the average credits of all courses.
=> SELECT COUNT(*) AS Total_Students FROM Students; → counted the total number of students in the Students table.

SELECT AVG(Credits) AS Avg_Credits
FROM Courses;

SELECT COUNT(*) AS Total_Students
FROM Students;

7. JOIN Operation

=> JOIN combines related data from multiple tables using common keys.
=> We listed students with the courses they are enrolled in and their grades.

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;

8. GROUP BY with HAVING

=> GROUP BY groups rows based on column values, and HAVING filters groups.
=> We grouped students by department and displayed only departments with more than 2 students.

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

9. Views

=> A View is a virtual table created from a query for easier data access.
=> We created StudentCoursesView to show StudentName, CourseName, and Grade.

CREATE OR REPLACE 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;

10. Stored Procedure

=> A Stored Procedure is a saved block of SQL statements that can be executed when needed.
=> We wrote UpdateGrade procedure to update a student’s grade in Enrollments.

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;