DEV Community

Cover image for College Database Management System – Oracle LiveSQL
Ilakkiya
Ilakkiya

Posted on

College Database Management System – Oracle LiveSQL

This project demonstrates how to design and query a mini College Database Management System (DBMS) using Oracle LiveSQL. The aim is to understand how real-world college data like students, faculty, courses, and enrollments can be efficiently stored, managed, and retrieved using SQL.

🔹 Schema Design

We create four core tables with proper constraints to maintain data integrity:

Faculty – stores teacher details such as FacultyID, FacultyName, Department, and Email.

Students – contains student details including StudentID, Name, Department, Date of Birth, Email, and Phone number.

Courses – holds subject details like CourseID, CourseName, and Credits (restricted between 1–5).

Enrollments – acts as a bridge (many-to-many relationship) between Students and Courses, mapping students to their registered courses along with their grades.

This schema ensures relational integrity by applying primary keys, unique keys, foreign keys, and constraints.

🔹 Sample Queries

Once the schema is created, we run different types of queries to explore SQL operations:

String Functions & Aggregates

SELECT UPPER(Name), LENGTH(Email) FROM Students;
SELECT AVG(Credits) AS AvgCredits, COUNT(*) AS TotalStudents
FROM Courses, Students;

These queries display student names in uppercase, measure email lengths, and calculate the average course credits.

Joins
To display the mapping between students, courses, and their grades:

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;

Group By + Having
To analyze the student distribution across departments:

SELECT Dept, COUNT() AS StudentCount

FROM Students

GROUP BY Dept

HAVING COUNT(
) > 2;

This ensures only departments with more than two students are displayed.

🔹** Views & Procedures**

Views
A view named StudentCoursesView is created to simplify repeated queries:

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;

This allows users to easily fetch student-course-grade data without rewriting complex joins.

Stored Procedures
To update student grades efficiently, we write a 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 project demonstrates a complete database workflow:

Designing relational schemas with constraints

Inserting and modifying data

Executing SQL queries with functions, joins, and grouping

Creating views for simplicity

Automating updates with stored procedures

By working on this mini-project in Oracle LiveSQL, we gain practical insights into how a College DBMS operates. It not only strengthens SQL knowledge but also builds a strong foundation for handling larger real-world databases.






Top comments (0)