How to transfer data from denormalized table to normalized tables in Oracle?
General

In general, you need to fill out the table in hierarchical order (parent, child, grandchild, etc).  The best way to do that is using SELECT DISTINCT.

This works very well if the text value (eg class_name) is UNIQUE.  This way, you can JOIN your source data to get the parent Id ( class_id ) for your child tables.

In some cases, as the process gets more complicated, you may want to consider creating some staging tables. These are tables whose purpose in life is to hold partially processed data temporarily so that you can review it before going onto the next step.

Your Specific

First, I am assuming that one and only one of the Category columns will have a value of 1.

First thing you need to do is UNPIVOT all of your data into a staging table so that you can use @Akina's method.

with data as (
SELECT
  'Sally' AS first_name
 ,'Biology' AS class_name
 ,0 AS overall
 ,0 AS hw
 ,1 AS test
 ,0 AS quiz
 ,0 AS project
 ,95 AS val
FROM dual
)
select *
from data
UNPIVOT (
  is_used
  for Category in ( OVERALL,HW,TEST,QUIZ,PROJECT)
)
WHERE IS_USED = 1;


This will return a row like this

Name  | Class | Val | Category | IS_USED
Sally | Biology | 95 | TEST | 1


Now you can


SELECT DISTINCT student_name to get individual students for
Student.
SELECT DISTINCT class_name to get individual Classes for each
Class.
SELECT DISTINCT student_name, class_name to get everyone's
ClassEnrollment


You'll need to join to Class on class_name to get class_id
You'll need to join to Student on student_name to get student_id

Finally, after you JOIN all the tables together, you can record
everyone's Grades


This assumes that you have clean data.  @Akina's method seems to assume your data is not clean (which is usually the case)