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

Question

Hi I currently have a database set up with a table like this:

Grades: student name|class name|overall|hw|test|quiz|project|val

where student name and class name are type varchar2 with the rest being type number. The categories overall,hw,test and quiz are expected to be 1 or 0 and val is the actual grade out of 100. So a sample row with a student getting a 95 on her test would look like this:

Grades: Sally | Biology | 0 | 0 | 1 | 0 | 0 | 95

This structure is clearly suboptimal, so I decided to normalize this database with help from this question here to :

Student: id|firstname | lastname (pk = id)

Class: id|name|room num (pk = id)

Categories: id|name|weight (pk = (id, name, weight))

Grades: student_id (fk)| class_id (fk)| category_id (fk) | name | grade_val 

ClassEnrollment: student_id (fk)| class_id (fk) | overall_grade (pk = student_id, class_id)

My main question is how to get the data from three tables to fill out the class enrollment table?

I can select all the overall grade using:

SELECT val
FROM grades_old
WHERE overall = 1;

And I can select the student id in a number of steps :

INSERT INTO temp(student, class)
SELECT student_name, class_name 
FROM grades_old;

SELECT temp.student_name, students.name, students.id
INNER JOIN students
ON temp.student_name = students.name

However, I'm not sure how to put all this together to get the overall grade, student_id and class_id in one query.

Edit:

I have manually filled out the Category database since there is only 5 categories in the system right now (overall, hw, test, quiz and project). I have a name column in the student table for now for purposes of matching, I figured I could worry about splitting it later then drop that column once I have split the names.

So I guess my focused question is for now how can I select the overall grade, student_id and class_id from the original grades table to insert it into the class Enrollment table?

Answer 1

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)

Answer 2

Standard method to extract some field(s) from denormalized data to separate table with the aim of normalization looks like:

1. Alter source table (or tables - in this case You may create view formed one recordset with all data and use it as a source) and add the field which will reference store for the values of fields which will be moved to separate table with datatype dependent of theoretical limit of new table' records amount (INT or BIGINT usually).

2. Create new table which will store fields data moved from source table, with proper fields (and possibly proper datatypes, if they are not optimal for data stored in source table) and PK-autoincrement with the type of reference field created on the step 1.

3. SELECT DISTINCT moved fields from source and INSERT the result into new table. Of course, it's one query. PK is filled auto.

4. Remove PK and UNIQUE properties from PK field.

5. Check moved data for "non-equal duplicates" (misprints, synonyms, etc.). Correct "PK" values of poor duplicates to the value of proper one.

6. Update source and fill ref. field with PK from new table (join tables by values of moved fields, solving NULL values problem).

7. Restore PK on PK field. Remove records with "poor dups" (they have no referals).

8. Check JOIN result matched to original state.

9. Drop moved fields.

Answer 3

There are a variety of ways to go about a task like this. This is probably not the most efficient, but is one that can work.

You have several steps that are not just inserts, but require you to perform some logic checks. For example, you have a name field that you're trying to split into first name, last name. This may require some serious effort. Oh, you could arbitrarily split on a space, assuming the names are entered in your source table as "first last". But as this article describes, that is a bad idea, generally speaking. So there's your first nontrivial task.

Next, your description of class has a room number. That's going to have to be entered manually, as it is new data that does not exist in your current schema.

Also, you have two tables with the same name, but they are NOT the same table. That has to be solved, too.

So, once you solve THOSE issues, your plan should probably be something like:

 Create Cursor StudentCur;
 Create Cursor ClassCur;
 Create Cursor CategoryCur;
 Create Cursor GradeCur;
 Create Cursor ClassEnrollCur;

 Declare 
    StudentFirst varchar2(100);
    StudentLast  varchar2(100);
    StudentName varchar2(200);
    StudentID number(6,0);
    ClassID number(6,0);
    CategoryID number(6,0);
    GradeID number(6,0);
    ClassEnrollID number(6,0);
    ClassName varchar2(200);
    OverallVal number(1,0);
    HwVal  number(1,0);
    TestVal  number(1,0);
    QuizVal  number(1,0);
    ProjectVal  number(1,0);

    Val  number(1,0);
    Cursor cSource is
      Select studentname, classname, overall, hw, test, quiz, project, val 
      From Grades;
 begin
    Open cSource;
    Loop
       Fetch cSource into StudentName, ClassName, OverallVal, HwVal, TestVal, QuizVal, ProjectVal, val;
       Exit When cSource%NotFound;
       -- Do some magic to derive your student 1st and last name from StudentName ...
       -- see if our student already exists?
       Declare Cursor cStudent as Select id from cStudent where FirstName = StudentFirst and LastName = studentLast;
       open cStudent;
       Fetch cStudent into StudentID;
       IF cStudent%NotFound then
          -- this is a new student.
          SET StudentID = StudentCur.NextVal;
         Insert Into Student (id, firstname, lastname) Values (StudentID, StudentFirst, StudentLast);
       END IF;
       Close cStudent;
       --repeat the above sort of logic for each of your child tables see if the class already exists, or the category... 
       --    each should result in an ID that is filled in.

       Insert Into  NewGrades (student_Id, class_id, category_id, name, grade_val) Values (StudentID, ClassID, CategoryID, studentName, val);

    End Loop;
    Close cSource;
    Commit;         
 End;  

The above provides a skeleton; it doesn't fill in all the blanks. You can copy/paste this code and fix it for the missing class ID and category ID. It also doesn't resolve the student name concern I mention above. That's not a DB issue.

Finally, I'm not sure what you mean by "Name" in your new table, so I made an assumption in the final insert. Adjust accordingly.

Once you're done, you should cycle back through to do your class enrollment table.

But this should give you a framework to get you started.