2

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

Lennart - Slava Ukraini
  • 23,240
  • 3
  • 32
  • 69
  • 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 Sep 20 '19 at 09: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? – Lennart - Slava Ukraini Sep 20 '19 at 10:06
  • But we would still need grade-scale in diploma, correct? – Lennart - Slava Ukraini Sep 20 '19 at 10:08
  • No. diplomas references to courses_to_grades_in_gradescales. courses_to_grades_in_gradescales references to cources (this defines course) and grades_in_gradescales. grades_in_gradescales references to grades (this defines grade) and to gradescales (this defines gradescale). – Akina Sep 20 '19 at 10:22
  • Did I get it right in edit? – Lennart - Slava Ukraini Sep 20 '19 at 11:46
  • No. I mean: 1) add surrogate PK to grades_in_gradescales (converting old PK to UNIQUE) 2) create courses_to_grades_in_gradescales table which has its own surrogate and references to grades_in_gradescales surrogate and to cources (and UNIQUE by this two references combination) 3) remove both references from diplomas but add a reference to courses_to_grades_in_gradescales surrogate. – Akina Sep 20 '19 at 11:52
  • I see, from my point of view, adding surrogate identifiers masks the underlying problems, but it does not solve them. At the conceptual/logical level of design, I tend to avoid them. – Lennart - Slava Ukraini Sep 20 '19 at 13:59
  • Why not solve? No way to insert wrong grade to course - or wrong grade to gradescale was inserted previously. – Akina Sep 20 '19 at 18:10
  • @Lennart in the solution "you are not happy with", the gradescale_id should be included in both FKs, towards grades_in_grade_scales and towards courses, for the constraints to do what you want.. – ypercubeᵀᴹ Sep 22 '19 at 13:48
  • 2
    Also check some of my answers, regarding what I call "[diamond shape"](https://dba.stackexchange.com/search?q=user%3A993+diamond) – ypercubeᵀᴹ Sep 22 '19 at 13:51
  • 2
    And a similar problem with a great answer form MDDCL – ypercubeᵀᴹ Sep 22 '19 at 13:54
  • @ybercube, for your first comment, yes definitely so. I don't have time now, but I will look at the other answers later on. – Lennart - Slava Ukraini Sep 22 '19 at 14:46

0 Answers0