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)