LibreOffice homework. Resolving redundancy. "translating" two foreign keys (either of which is absent) into one (supertype)

Question

I am using LibreOffice Base with embedded HSQLDB for educational purposes.

The subject area is MOOCs. I have the following entities: Student, Teacher, User(superclass), Course, Forum, Question, Progress Journal.

For Course I want to distinguish whether it is Student or Teacher, Students study many courses (n:m) and Teachers teach many (n:m); but for Question, in my case, I do not need any distinction - one User (either Student OR Teacher) can ask many questions on the Forum (1:n).

Here is the picture of my Relations Table.

Please, ignore the intended redundancy of Student/Teacher attributes, I was required to have 6 entities at least.

Obviously, the User cannot have both ID_Student and ID_Teacher as non-empty, either of them should be Nan.

How do I solve redundancy of free space (Nan-s), or should I leave it as is?

Or maybe the problem lies in my peculiar definition of the subject area?

I could have created the ID_user attribute in Question which would have been storing both the Student (1:n) and Teacher (1:n) IDs, given that their IDs don't collide. I was unsure whether it was correct, so created separate User entity.

When trying to use REFERENCES keyword I face an error: Unexpected token: REFERENCES in statement [create table students ( student_id int primary key references]

Is there another way around? Should I download any extras to make LibreOffice execute it properly?

Answer 1

The foreign keys are the wrong way around.

User is the supertype, so student and teacher should have foreign keys to that:

create table users ( user_id int primary key, ... );

create table students ( student_id int primary key references users ( user_id ), ... );

create table users ( teacher_id int primary key references users ( user_id ), ... );