SQL with ER diagram

file.sql

CREATE TABLE COLLEGE (
    CollegeID NUMBER(10) PRIMARY KEY,
    College_Name VARCHAR2(100) NOT NULL,
    College_Details VARCHAR2(500)
);

CREATE TABLE DEPARTMENT (
    DeptID NUMBER(10) PRIMARY KEY,
    DeptName VARCHAR2(100) NOT NULL,
    Phone VARCHAR2(15),
    CollegeID NUMBER(10),
    CONSTRAINT fk_dept_college FOREIGN KEY (CollegeID) 
        REFERENCES COLLEGE(CollegeID)
        ON DELETE SET NULL
);

CREATE TABLE STUDENT (
    StudentID NUMBER(10) PRIMARY KEY,
    Name VARCHAR2(100) NOT NULL,
    Email VARCHAR2(100) UNIQUE,
    DOB DATE,
    DeptID NUMBER(10),
    CONSTRAINT fk_student_dept FOREIGN KEY (DeptID) 
        REFERENCES DEPARTMENT(DeptID)
        ON DELETE SET NULL
);

CREATE TABLE FACULTY (
    FacultyID NUMBER(10) PRIMARY KEY,
    Name VARCHAR2(100) NOT NULL,
    Designation VARCHAR2(50),
    DeptID NUMBER(10),
    CONSTRAINT fk_faculty_dept FOREIGN KEY (DeptID) 
        REFERENCES DEPARTMENT(DeptID)
        ON DELETE SET NULL
);

CREATE TABLE COURSE (
    CourseID NUMBER(10) PRIMARY KEY,
    CourseName VARCHAR2(100) NOT NULL,
    Credits NUMBER(2),
    DeptID NUMBER(10),
    FacultyID NUMBER(10),
    CollegeID NUMBER(10),
    CONSTRAINT fk_course_dept FOREIGN KEY (DeptID) 
        REFERENCES DEPARTMENT(DeptID)
        ON DELETE SET NULL,
    CONSTRAINT fk_course_faculty FOREIGN KEY (FacultyID) 
        REFERENCES FACULTY(FacultyID)
        ON DELETE SET NULL,
    CONSTRAINT fk_course_college FOREIGN KEY (CollegeID) 
        REFERENCES COLLEGE(CollegeID)
        ON DELETE SET NULL
);


CREATE TABLE ENROLLMENT (
    StudentID NUMBER(10),
    CourseID NUMBER(10),
    Grade VARCHAR2(2),
    CONSTRAINT pk_enrollment PRIMARY KEY (StudentID, CourseID),
    CONSTRAINT fk_enrollment_student FOREIGN KEY (StudentID) 
        REFERENCES STUDENT(StudentID)
        ON DELETE CASCADE,
    CONSTRAINT fk_enrollment_course FOREIGN KEY (CourseID) 
        REFERENCES COURSE(CourseID)
        ON DELETE CASCADE
);

INSERT INTO COLLEGE (CollegeID, College_Name, College_Details) 
VALUES (1, 'College of Engineering', 'Premier engineering institution offering undergraduate and graduate programs');

INSERT INTO COLLEGE (CollegeID, College_Name, College_Details) 
VALUES (2, 'College of Science', 'Focused on pure and applied sciences with state-of-the-art laboratories');

INSERT INTO COLLEGE (CollegeID, College_Name, College_Details) 
VALUES (3, 'College of Arts', 'Liberal arts college with diverse humanities and social science programs');

INSERT INTO DEPARTMENT (DeptID, DeptName, Phone, CollegeID) 
VALUES (101, 'Computer Science', '555-0101', 1);

INSERT INTO DEPARTMENT (DeptID, DeptName, Phone, CollegeID) 
VALUES (102, 'Electrical Engineering', '555-0102', 1);

INSERT INTO DEPARTMENT (DeptID, DeptName, Phone, CollegeID) 
VALUES (201, 'Physics', '555-0201', 2);

INSERT INTO DEPARTMENT (DeptID, DeptName, Phone, CollegeID) 
VALUES (301, 'English Literature', '555-0301', 3);

INSERT INTO STUDENT (StudentID, Name, Email, DOB, DeptID) 
VALUES (1001, 'John Smith', 'john.smith@college.edu', TO_DATE('2003-05-15', 'YYYY-MM-DD'), 101);

INSERT INTO STUDENT (StudentID, Name, Email, DOB, DeptID) 
VALUES (1002, 'Emma Johnson', 'emma.johnson@college.edu', TO_DATE('2003-08-22', 'YYYY-MM-DD'), 101);

INSERT INTO STUDENT (StudentID, Name, Email, DOB, DeptID) 
VALUES (1003, 'Michael Brown', 'michael.brown@college.edu', TO_DATE('2002-12-10', 'YYYY-MM-DD'), 102);

INSERT INTO STUDENT (StudentID, Name, Email, DOB, DeptID) 
VALUES (1004, 'Olivia Martinez', 'olivia.martinez@college.edu', TO_DATE('2002-11-30', 'YYYY-MM-DD'), 201);

INSERT INTO STUDENT (StudentID, Name, Email, DOB, DeptID) 
VALUES (1005, 'Robert Thomas', 'robert.thomas@college.edu', TO_DATE('2002-06-20', 'YYYY-MM-DD'), 301);

INSERT INTO FACULTY (FacultyID, Name, Designation, DeptID) 
VALUES (2001, 'Dr. Sarah Connor', 'Professor', 101);

INSERT INTO FACULTY (FacultyID, Name, Designation, DeptID) 
VALUES (2002, 'Dr. Nikola Tesla', 'Professor', 102);

INSERT INTO FACULTY (FacultyID, Name, Designation, DeptID) 
VALUES (2003, 'Dr. Marie Curie', 'Professor', 201);

INSERT INTO FACULTY (FacultyID, Name, Designation, DeptID) 
VALUES (2004, 'Dr. George Orwell', 'Associate Professor', 301);


INSERT INTO COURSE (CourseID, CourseName, Credits, DeptID, FacultyID, CollegeID) 
VALUES (3001, 'Data Structures and Algorithms', 4, 101, 2001, 1);

INSERT INTO COURSE (CourseID, CourseName, Credits, DeptID, FacultyID, CollegeID) 
VALUES (3002, 'Database Management Systems', 3, 101, 2001, 1);

INSERT INTO COURSE (CourseID, CourseName, Credits, DeptID, FacultyID, CollegeID) 
VALUES (3003, 'Digital Electronics', 4, 102, 2002, 1);

INSERT INTO COURSE (CourseID, CourseName, Credits, DeptID, FacultyID, CollegeID) 
VALUES (3004, 'Quantum Mechanics', 4, 201, 2003, 2);

INSERT INTO COURSE (CourseID, CourseName, Credits, DeptID, FacultyID, CollegeID) 
VALUES (3005, 'British Literature', 3, 301, 2004, 3);


INSERT INTO ENROLLMENT (StudentID, CourseID, Grade) VALUES (1001, 3001, 'A');
INSERT INTO ENROLLMENT (StudentID, CourseID, Grade) VALUES (1001, 3002, 'A-');
INSERT INTO ENROLLMENT (StudentID, CourseID, Grade) VALUES (1002, 3001, 'B+');
INSERT INTO ENROLLMENT (StudentID, CourseID, Grade) VALUES (1003, 3003, 'A');
INSERT INTO ENROLLMENT (StudentID, CourseID, Grade) VALUES (1004, 3004, 'B+');