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:
- 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).
coursesandgrades_in_gradescales(not directlycourcestogradescales). Anddiplomasmust refer to it, not to separatecourcesandgrades. – Akina Sep 20 '19 at 09:12diplomasreferences tocourses_to_grades_in_gradescales.courses_to_grades_in_gradescalesreferences tocources(this defines course) andgrades_in_gradescales.grades_in_gradescalesreferences togrades(this defines grade) and togradescales(this defines gradescale). – Akina Sep 20 '19 at 10:22grades_in_gradescales(converting old PK to UNIQUE) 2) createcourses_to_grades_in_gradescalestable which has its own surrogate and references togrades_in_gradescalessurrogate and tocources(and UNIQUE by this two references combination) 3) remove both references fromdiplomasbut add a reference tocourses_to_grades_in_gradescalessurrogate. – Akina Sep 20 '19 at 11:52gradescale_idshould be included in both FKs, towardsgrades_in_grade_scalesand towardscourses, for the constraints to do what you want.. – ypercubeᵀᴹ Sep 22 '19 at 13:48