This is a made-up scenario, but I imagine that it can be of general interest, so I'll post it here. Imagine the following business rule:

  1. A course has a grade scale that determines what grades a student can get on their diplomas

How would this BR be implemented (no procedure logic) while preserving 3NF? The DBMS does not support general expressions in CHECK constraints, so we are restricted to row expressions

A naive approach would be something like:

create table gradesscales
( gradescale_id int not null primary key );

create table grades
( grade char(1) not null primary key );

create table grades_in_gradescales
( gradescale_id int not null
    references gradesscales (gradescale_id)
, grade char(1) not null
    references grades (grade)
, primary key (gradescale_id, grade)

create table courses
( course_code char(5) not null primary key
, gradescale_id int not null
    references gradesscales (gradescale_id)

create table diplomas
( student_no int not null
, course_code char(5) not null
    references courses (course_code)
, grade char(1)
    references grades (grade)
, primary key (student_no, course_code)

insert into gradesscales (gradescale_id) values (1),(2);

insert into grades (grade) values ('1'),('2'),('3'),('A'),('B');

insert into grades_in_gradescales (gradescale_id, grade)
values (1,'1'),(2,'2'),(1,'3'),(2,'A'),(2,'B');

insert into courses (course_code, gradescale_id)
values ('MA101', 1),('FY201', 2);

So far so good, but nothing prevents us from adding a grade from a grade scale not related to the course:

insert into diplomas (student_no, course_code, grade)
values (1,'MA101','B');

One pragmatic approach is to add gradescale_id to diplomas, and reference grades_in_gradescales instead of grades. In addition add a super key course_no, gradescale_id in course:

alter table courses add constraint ...
    unique (course_code, gradescale_id);

alter table diplomas add constraint C
    add column gradescale_id int not null
    -- drop foreign key against grades
    add foreign key (gradescale_id, grade)
          references grades_in_grade_scales (gradescale_id, grade);
    -- replace foreign key against course
    add foreign key (gradescale_id, course_code)
          references courses (...);

but I'm not too happy about that. Other thoughts?

Even though I used SQL in my example, I would like to keep the discussion at a conceptual/logical level. I.e. model this with attributes from the universe of discourse (no auto-generated surrogate attributes).

  • My "naive aproach" is that there must exist joinig table which joins (M:N) courses and grades_in_gradescales (not directly cources to gradescales). And diplomas must refer to it, not to separate cources and grades.
    – Akina
    Commented Sep 20, 2019 at 9:12
  • Thanks, so we add a new relation (say) course_grades_in_gradescales and remove grade-scale from course? Ensuring that a course belongs to a grade-scale is then the responsibility of the transaction that adds courses, correct? Commented Sep 20, 2019 at 10:06
  • But we would still need grade-scale in diploma, correct? Commented Sep 20, 2019 at 10:08
  • 2
    Also check some of my answers, regarding what I call "diamond shape" Commented Sep 22, 2019 at 13:51
  • 2
    And a similar problem with a great answer form MDDCL Commented Sep 22, 2019 at 13:54


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.