1

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)

  • Can the Overall Grade be overridden by the teacher? If the value can always be calculated, you could hide the calculation within a VIEW – Michael Kutz May 30 '18 at 13:28
  • @MichaelKutz In this case it can, but I didn't think of that before. – ryanmattscott May 30 '18 at 13:30
3

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 );

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.