Trying to create tables with Oracle for an assignment but I can't see the issues with the syntax. Any help is much appreciated

Question

This is the code:

create table instructor (
       id varchar(10) primary key,
       name varchar(15) not null,
       dept_name varchar(20) not null
            references department(dept_name) on delete cascade,
       salary varchar(20) not null
);

create table teaches (
       id varchar(10) primary key
            references instructor(id) on delete cascade,
       course_id varchar(15) not null check
            references section(course_id),
       sec_id varchar(5) not null
            references section(sec_id),
       semester varchar(15)
            references section(semester),
       year int not null
            references section(year)
);

create table student (
       id varchar(10) not null,
       name varchar(20) not null,
       dept_name varchar(15) not null
            references department(dept_id) on delete cascade,
       tot_cred int not null
);

create table advisor (
       s_id  varchar(10) primary key
            references student(id) on delete cascade,
       i_id varchar(6)
            references instructor(id) on delete cascade
);

create table department (
       dept_name varchar(15) primary key,
       building varchar(10),
       budget int
);

create table course (
       course_id varchar(10) primary key,
       title varchar(10),
       dept_name varchar(15)
            references department(dept_name) on delete cascade,
       credits int
);

create table prereq (
        course_id varchar(10) 
            references course(course_id) on delete cascade,
        prereq_id varchar(10) 
            references course(course_id) on delete cascade,
        primary key (course_id, prereq_id)
);

create table takes (
        id varchar(10) not null
            references student(id) on delete cascade,
        course_id varchar(10) not null
            references section(course_id) on delete cascade,
        sec_id varchar(10) not null
            references section(sec_id) on delete cascade,
        semester varchar(10) not null
            references section(semester) on delete cascade,
        year int not null
            references section(year) on delete cascade,
        grade varchar(10) not null
        primary key (id, course_id, sec_id, semester, year, grade)
);

create table section (
        course_id varchar(10) not null
                references course(course_id),
        sec_id varchar(10) not null, 
        semester varchar(10) not null,
        year varchar(10) not null,
        building varchar(10) not null
            references classroom(building),
        room_no varchar(5) not null
            references classroom(room_no),
        time_slot_id varchar(5) not null
            references time_slot(time_slot_id),
        primary key (course_id, sec_id, semester, year)
);

create table classroom (
        building varchar(10) not null,
        room_no varchar(5) not null,
        capacity varchar(10) not null,
        primary key (building, room_no)
);

create table time_slot (
        time_slot_id varchar(5) not null
            references section(time_slot_id),
        day varchar(10) not null,
        start_time varchar(10) not null,
        end_time varchar(10) not null,
        primary key (time_slot_id, day, start_time)
);

This is the output:

Table created.

ORA-00906: missing left parenthesis

ORA-00904: "DEPT_ID": invalid identifier

ORA-00942: table or view does not exist

ORA-00955: name is already used by an existing object

Table created.

Table created.

ORA-00907: missing right parenthesis

ORA-02270: no matching unique or primary key for this column-list

ORA-00955: name is already used by an existing object

ORA-00942: table or view does not exist

Answer 1

• In student, the foreign key should reference department.dept_name and not department.dept_id.
• In student there is no primary key, so all the other tables that reference student will fail.
• In time_slot, you have a referential constraint that references section which, in turn, references back to the same column in time_slot so you have circular constraints; you probably want to remove the foreign key constraint from time_slot.
• In time_slot, you probably want the primary key on just time_slot_id and a composite unique constraint on time_slot_id, day, and start_time.
• NEVER store days and times as strings; always use a DATE data type (which, in Oracle, can store both date and time). If, in time_slot you are storing a date and time then just use two DATE columns for start_time and end_time. If you are storing day-of-week and time-of-day then you could use a NUMBER(1,0) for day-of-week and INTERVAL DAY(0) TO SECOND(0) for start and end times (or don't use a day column and just use two INTERVAL DAY(1) TO SECOND (0) data types for start and end time and measure from the start of the week).
• In section you reference classroom.building and classroom.room individually; however, that is a composite primary key and should be a single referential constraint.
• In teaches there is a random check keyword that should be removed.
• In teaches and takes, course_id, sec_id, semester and year are a composite key that need to be referenced together.
• Data types are optional when you have a column that is part of a referential constraint. If you remove the data type then it will use the parent table's data type and will ensure consistency between tables.
• You need to re-order the tables so that primary keys are created before the foreign keys that reference them.
• salary, capacity and year should be numeric data types.

db<>fiddle here