College Database Management System with Oracle LiveSQL #sql #oracle #database #learning

Overview

In this article, I’ll share how I built a College Database Management System using Oracle LiveSQL.
The goal was to design a system that manages students, faculty, and courses while applying SQL concepts like constraints, joins, and views.

Key Highlights

🔹 Creating tables with proper constraints
🔹 Inserting records into tables
🔹 Altering table structures
🔹 Running queries with string & aggregate functions
🔹 Performing joins
🔹 Defining views for simplified access

1. Defining Tables

I began with three main entities: Students, Faculty, and Courses.

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

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

CREATE TABLE Courses (
CourseID NUMBER PRIMARY KEY,
CourseName VARCHAR2(50) NOT NULL,
Credits NUMBER(2) CHECK (Credits BETWEEN 1 AND 5)
);

2. Populating the Tables

Sample student records:

INSERT INTO Students VALUES (1, 'Aarav Kumar', 'CSE', TO_DATE('2004-05-12','YYYY-MM-DD'), 'aarav.cs@example.com');
INSERT INTO Students VALUES (2, 'Meera Reddy', 'EEE', TO_DATE('2003-09-23','YYYY-MM-DD'), 'meera.ee@example.com');
INSERT INTO Students VALUES (3, 'Vikram Sharma', 'MECH', TO_DATE('2004-01-30','YYYY-MM-DD'), 'vikram.me@example.com');

3. Schema Modification

Later, I added a column for phone numbers to the Students table:

ALTER TABLE Students
ADD PhoneNo VARCHAR2(10);

4. Working with Functions

Query to display names in uppercase and calculate the length of email IDs:

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

5. Connecting Students and Courses

An Enrollments table was introduced to keep track of which student took which course, along with their grade.

CREATE TABLE Enrollments (
StudentID NUMBER REFERENCES Students(StudentID),
CourseID NUMBER REFERENCES Courses(CourseID),
Grade VARCHAR2(2)
);

To simplify, I created a view:

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;