COLLEGE STUDENT AND COURSE MANAGEMENT SYSTEM

I have just completed the database management system for college student and course management system.
Here,is the step by step process :

1. Creating the Students Table

First, we define a Students table to store basic information about each student.

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

Key Points:

StudentID is the primary key, ensuring uniqueness.

Email has a unique constraint to avoid duplicates.

Name is marked as NOT NULL to ensure every student has a name.

1. Creating the Courses Table

Next, we define the Courses table that lists all courses offered.

CREATE TABLE Courses (
CourseID NUMBER PRIMARY KEY,
CourseName VARCHAR2(50) NOT NULL,
Credits NUMBER(2)
);

Later, we’ll add a check constraint on the Credits column to ensure values stay within a valid range.

1. Creating the Enrollments Table

This table maps students to the courses they’re enrolled in.

CREATE TABLE Enrollments (
EnrollID NUMBER PRIMARY KEY,
StudentID NUMBER REFERENCES Students(StudentID),
CourseID NUMBER REFERENCES Courses(CourseID),
Grade CHAR(2)
);

Key Points:

StudentID and CourseID are foreign keys linking to their respective tables.

Grade stores the letter grade (like A+, B, etc.).

1. Inserting Student Records

Now let’s add three students from different departments:

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'VINCENT', 'Computer Science', TO_DATE('2004-03-15', 'YYYY-MM-DD'), '[e-mail]');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'SYLVIA', 'Electrical Engineering', TO_DATE('2005-11-22', 'YYYY-MM-DD'), '[e-mail]');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'TOM HOLLAND', 'Mechanical Engineering', TO_DATE('2006-07-09', 'YYYY-MM-DD'), '[e-mail]');

1. Adding a New Column: PhoneNo

We realized students need a phone number field. Here’s how to add it:

ALTER TABLE Students
ADD PhoneNo VARCHAR2(10);

1. Adding a Check Constraint on Course Credits

We only want courses to have between 1 and 5 credits:

ALTER TABLE Courses
ADD CHECK (Credits BETWEEN 1 AND 5);

This helps maintain data integrity by preventing invalid credit values.

1. Inserting Courses

Let’s add three sample courses into our Courses table:

INSERT INTO Courses (CourseID, CourseName, Credits)
VALUES (101, 'CYBER SECURITY', 3);

INSERT INTO Courses (CourseID, CourseName, Credits)
VALUES (102, 'BEEE', 4);

INSERT INTO Courses (CourseID, CourseName, Credits)
VALUES (103, 'FLUID MECHANICS', 5);

1. Committing Changes

In Oracle, changes aren’t permanent until you commit:

COMMIT;

1. Querying the Database a. Display Student Names in Uppercase and Email Length SELECT UPPER(Name) AS Student_Name, LENGTH(Email) AS Email_Length FROM Students;

This returns names in uppercase along with the length of each email.

b. View All Courses
SELECT CourseID, CourseName, Credits
FROM Courses;

Useful for displaying a course catalog.

c. View All Students
SELECT StudentID, Name, Dept, DOB, Email, PhoneNo
FROM Students;

Shows all students, including the newly added PhoneNo field.

In this simple Oracle SQL project, we:

Created relational tables with primary and foreign keys

Added constraints for data validation

Populated the tables with sample data

Executed basic queries to retrieve and format information

This project forms a foundational structure for any educational management system. From here, you can explore adding features like attendance, scheduling, and advanced reports.

🙌 Thank you @santhoshnc sir for guiding me!!