Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE course (
    course_number     NUMBER(10) NOT NULL,
    hours             NUMBER(10) NOT NULL,
    title             VARCHAR2(45) NOT NULL,
    section           NUMBER(10) NOT NULL,
    department_name   VARCHAR2(45) NOT NULL,
    faculty_id        NUMBER(10) NOT NULL
);
ALTER TABLE course ADD CONSTRAINT course_pk PRIMARY KEY ( course_number );
CREATE TABLE department (
    name          VARCHAR2(45) NOT NULL,
    major         VARCHAR2(45) NOT NULL,
    minor         VARCHAR2(45),
    chair_id      NUMBER(10) NOT NULL,
    chair_email   VARCHAR2(45) NOT NULL
);
ALTER TABLE department ADD CONSTRAINT departrment_pk PRIMARY KEY ( name );
CREATE TABLE faculty (
    faculty_id        NUMBER(10) NOT NULL,
    cs_id             VARCHAR2(45) NOT NULL,
    status            VARCHAR2(45) NOT NULL,
    name              VARCHAR2(45) NOT NULL,
    faculty_email             VARCHAR2(45) NOT NULL,
    department_name   VARCHAR2(45) NOT NULL
);
ALTER TABLE faculty ADD CONSTRAINT faculty_pk PRIMARY KEY ( faculty_id );
CREATE TABLE location (
    building_number   NUMBER(10) NOT NULL,
    room_number       NUMBER(10) NOT NULL,
    department_name   VARCHAR2(45) NOT NULL
);
CREATE TABLE student (
    student_id      NUMBER(10) NOT NULL,
    student_email   VARCHAR2(45) NOT NULL,
    ethnicity       NUMBER(10) NOT NULL,
    standing        VARCHAR2(45) NOT NULL,
    name            VARCHAR2(45) NOT NULL,
    gender          VARCHAR2(45) NOT NULL,
    faculty_id      NUMBER(10) NOT NULL
);
ALTER TABLE student ADD CONSTRAINT student_pk PRIMARY KEY ( student_id );
CREATE TABLE student_took_course (
    student_id      NUMBER(10) NOT NULL,
    course_number   NUMBER(10) NOT NULL,
    grade           NUMBER(10),
    constraint pkey_stc primary key( student_id, course_number )
);
ALTER TABLE course
    ADD CONSTRAINT course_faculty_fk FOREIGN KEY ( faculty_id )
        REFERENCES faculty ( faculty_id )
            ON DELETE CASCADE;
ALTER TABLE student_took_course
    ADD CONSTRAINT course_number_fk FOREIGN KEY ( course_number )
        REFERENCES course ( course_number )
            ON DELETE CASCADE;
ALTER TABLE student_took_course
    ADD CONSTRAINT student_id_fk FOREIGN KEY ( student_id )
        REFERENCES student ( student_id )
            ON DELETE CASCADE;
ALTER TABLE faculty
    ADD CONSTRAINT faculty_department_fk FOREIGN KEY ( department_name )
        REFERENCES department ( name );
ALTER TABLE location
    ADD CONSTRAINT location_department_fk FOREIGN KEY ( department_name )
        REFERENCES department ( name );
ALTER TABLE student
    ADD CONSTRAINT student_faculty_fk FOREIGN KEY ( faculty_id )
        REFERENCES faculty ( faculty_id );
begin
-- department
INSERT INTO department (name, major, minor, chair_id, chair_email) VALUES
    ('mechanical engineering', 'mechanical major', 'mechanical minor', '1', 'bob@mechanical.edu');
INSERT INTO department (name, major, minor, chair_id, chair_email) VALUES
    ('computer science', 'computer science major', null, '4', 'jerry@cs.edu');    
INSERT INTO department (name, major, minor, chair_id, chair_email) VALUES
    ('nuclear engineering', 'nuclear major', NULL, '3', 'james@nuckear.edu');
INSERT INTO department (name, major, minor, chair_id, chair_email) VALUES
    ('biology', 'biology major', NULL, '8', 'elizabeth@biology.edu');  

-- location
INSERT INTO location (building_number, room_number, department_name) VALUES
    ('04', '213', 'biology');
INSERT INTO location (building_number, room_number, department_name) VALUES
    ('12', '103', 'mechanical engineering');    
INSERT INTO location (building_number, room_number, department_name) VALUES
    ('43', '209', 'computer science');
INSERT INTO location (building_number, room_number, department_name) VALUES
    ('65', '132', 'nuclear engineering');

-- faculty    
INSERT INTO faculty (faculty_id, cs_id, faculty_email, status, name, department_name) VALUES
    ('1', '1', 'bob@mechanical.edu', 'active', 'bob', 'mechanical engineering');
INSERT INTO faculty (faculty_id, cs_id, faculty_email, status, name, department_name) VALUES
    ('4', '2', 'jerry@cs.edu', 'active', 'jerry', 'computer science');    
INSERT INTO faculty (faculty_id, cs_id, faculty_email, status, name, department_name) VALUES
    ('3', '7', 'james@nuclear.edu', 'active', 'james', 'nuclear engineering');    
INSERT INTO faculty (faculty_id, cs_id, faculty_email, status, name, department_name) VALUES
    ('8', '6', 'elizabeth@biology.edu', 'active', 'elizabeth', 'biology');
INSERT INTO faculty (faculty_id, cs_id, faculty_email, status, name, department_name) VALUES
    ('2', '9', 'dave@biology.edu', 'resigned', 'dave', 'biology');    
     
     
-- student    
INSERT INTO student (student_id, student_email, ethnicity, standing, name, gender, faculty_id) VALUES
    ('900123456', 'alice@student.edu', '50', 'good', 'alice', 'f', '1');
INSERT INTO student (student_id, student_email, ethnicity, standing, name, gender, faculty_id) VALUES
    ('900987654', 'noah@student.edu', '23', 'good', 'noah', 'm', '4');    
INSERT INTO student (student_id, student_email, ethnicity, standing, name, gender, faculty_id) VALUES
    ('900316487', 'bruce@student.edu', '23', 'probation', 'bruce', 'm', '4');  
INSERT INTO student (student_id, student_email, ethnicity, standing, name, gender, faculty_id) VALUES
    ('900876543', 'alex@student.edu', '37', 'probation', 'alex', 'm', '3');
INSERT INTO student (student_id, student_email, ethnicity, standing, name, gender, faculty_id) VALUES
    ('900452367', 'paul@student.edu', '05', 'good', 'paul', 'm', '8');

    
-- course      
INSERT INTO course (course_number, hours, title, section, department_name, faculty_id) VALUES
    ('543', '3', 'fluids', '01', 'mechanical enginering', '1');      
INSERT INTO course (course_number, hours, title, section, department_name, faculty_id) VALUES
    ('460', '4', 'golang', '01', 'computer science',  '4');
INSERT INTO course (course_number, hours, title, section, department_name, faculty_id) VALUES
    ('845', '2', 'reactors', '01', 'nuclear enginering', '3');    
INSERT INTO course (course_number, hours, title, section, department_name, faculty_id) VALUES
    ('324', '3', 'micro biology', '01', 'biology', '8');
INSERT INTO course (course_number, hours, title, section, department_name, faculty_id) VALUES
    ('113', '3', 'intro to biology', '01', 'biology', '2');    
      
      
-- student_took_course  
INSERT INTO student_took_course (student_id, course_number, grade) VALUES
    ('900987654', '460', '4'); 
INSERT INTO student_took_course (student_id, course_number, grade) VALUES
    ('900123456', '543', '3'); 
INSERT INTO student_took_course (student_id, course_number, grade) VALUES
    ('900876543', '845', '2');
INSERT INTO student_took_course (student_id, course_number, grade) VALUES
    ('900452367', '324', '4');
INSERT INTO student_took_course (student_id, course_number, grade) VALUES
    ('900452367', '113', '2');
end ;
/
1 rows affected