Jayvantrai Harrai Desai Polytechnic
College
➢ Subject:-Relational Database Management System.
➢ Topic:- College Management System.
➢ Member Name:-
1) Mistry Dhruv (226860307023)
2) Bhagat Arik (226860307001)
3) Parekh Dhruv (226860307028)
➢ Guidede By:- Bhavika Patel
College management system
-- Create tables for College Management System
-- Table for Departments
CREATE TABLE Departments (
department_id NUMBER PRIMARY KEY,
department_name VARCHAR2(50) NOT NULL
);
-- Table for Professors
CREATE TABLE Professors (
professor_id NUMBER PRIMARY KEY,
department_id NUMBER,
professor_name VARCHAR2(50) NOT NULL,
FOREIGN KEY (department_id) REFERENCES Departments(department_id)
);
-- Table for Courses
CREATE TABLE Courses (
course_id NUMBER PRIMARY KEY,
course_name VARCHAR2(100) NOT NULL,
department_id NUMBER,
professor_id NUMBER,
FOREIGN KEY (department_id) REFERENCES Departments(department_id),
FOREIGN KEY (professor_id) REFERENCES Professors(professor_id)
);
-- Table for Students
CREATE TABLE Students (
student_id NUMBER PRIMARY KEY,
student_name VARCHAR2(50) NOT NULL,
department_id NUMBER,
FOREIGN KEY (department_id) REFERENCES Departments(department_id)
);
-- Table for Enrollments
CREATE TABLE Enrollments (
enrollment_id NUMBER PRIMARY KEY,
student_id NUMBER,
course_id NUMBER,
enrollment_date DATE,
FOREIGN KEY (student_id) REFERENCES Students(student_id),
FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);
-- Table for Grades
CREATE TABLE Grades (
grade_id NUMBER PRIMARY KEY,
enrollment_id NUMBER,
grade VARCHAR2(2),
FOREIGN KEY (enrollment_id) REFERENCES Enrollments(enrollment_id)
);
SELECT s.student_name, g.grade
FROM Students s
INNER JOIN Enrollments e ON s.student_id = e.student_id
INNER JOIN Grades g ON e.enrollment_id = g.enrollment_id
WHERE e.course_id = course_id;
DECLARE
v_department_id NUMBER := 1;
v_department_name VARCHAR2(50) := 'Computer Science';
BEGIN
INSERT INTO Departments (department_id, department_name)
VALUES (v_department_id, v_department_name);
DBMS_OUTPUT.PUT_LINE('Department inserted successfully!');
END;
