Oracle Database – How to Prevent Null Foreign Key During Normalization

data integritydatabase-designnormalizationoracle

I'm rather new to databases and am having a hard time trying to figure out how to structure my database. This database contains students, classes and all their grades both overall and in categories (such as tests, homework, etc.)

So far I have created the following tables:

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

My question is if I wanted to save the students overall grade with this schema how would I do it without having a null foreign key and preserve normalization?

Currently to store their grade I would have to have an entry like Grade: 1, 1, null, 'Overall Grade', 95 in the database.

What is the correct way to do this? Add another table, an extra row in category like: Category: 1, 'Overall', 100 or something else?

Edit:

Currently the primary key on the grades table is just the combination of the other columns.
pk = (student_id, class_id, category_id, name)

Best Answer

NOTE

Many-to-Many relationships are represented by a linking table.

You are missing the link between Student(s) and Class(es)

create class_enrollment (
  student_id int not null references Student( id ),
  class_id   int not null references Class( id ),
  constraint class_enrollment_pk primary key ( student_id, class_id )
);

Adjustment

Your Grades table can then be modified to reference the PK of class_enrollment.

create table GRADES (
  student_id  int,
  class_id    int,
  category_id int,
  grade_val    number,
  constraint grade_pk primary key
     (student_id, class_id, category_id), -- a surrogate key is recommended
  constraint grade_fk1 foreign key (student_id, class_id)
     references class_enrollment(student_id, class_id)
);

Overall Grade

If you can always calculate a Summary value, You can hide the calculation in a VIEW (or a MATERIALIZED VIEW).

But, since teachers can override the calculated value, you'll want to store the Summary in the "parent table".

In your case, the "parent table" to Grades would be Class_Enrolement.

alter table class_enrollment add ( overall_grade  number );
Related Question