0

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. Relations table for the problem

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?

Community
  • 1
  • 1
Andrew
  • 125
  • 1
  • 8
  • This is not clear. What does "it matters whether" mean? What does "solve this redundancy" mean? Why is "normailization" in the title? Why is "inheritance"? Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. Please use technical terms where they apply. Explain what you started with & what you are trying to accomplish & how you are 1st stuck following your textbook/reference. See [ask], other [help] links, hits googling 'stackexchange homework' & the voting arrow mouseover texts. – philipxy Apr 09 '20 at 04:53
  • @Andrew `FOREIGN KEY ... REFERENCES ...` – Damir Sudarevic Apr 09 '20 at 09:44
  • Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. PS Here that means, DDL for entities/relationships/tables, attributes/columns & participations/FKs, and only the relevant ones, in text & possibly also in an image. Not just an image & not the whole design. – philipxy Apr 09 '20 at 21:47
  • This is still unclear. Ask 1 clear question, not many. You are thinking things but not saying them. What does "redundancy of free space" mean? "I want to distinguish whether it is" is not clear. You want, so what? What is "it"? "I do not need any distinction -" Any distinction between what & what, and what does that have to do with the rest of the sentence? "Obviously"--No, it's not. Your 1st edit added a paragraph; adding to something unclear does not make it clear. Don't ask us to ignore things, just if possible don't incude them. – philipxy Apr 09 '20 at 22:02
  • There are many Q&A re ER/DB/SQL subtyping/inheritance. https://stackoverflow.com/q/3579079/3404097 https://stackoverflow.com/q/190296/3404097 Before considering posting please read the manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. – philipxy Apr 09 '20 at 22:07

1 Answers1

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 ), ... );
Chris Saxon
  • 9,105
  • 1
  • 26
  • 42
  • Perhaps it works in MySQL, but I am using LibreOffice Base builtin HSQLDB, which on attempt of executing `create table students ( student_id int primary key references users ( user_id ), name varchar(50) );` throws 1: *Unexpected token: REFERENCES in statement [create table students ( student_id int primary key references]* Any idea how I can make it work with LibreOffice? – Andrew Apr 08 '20 at 19:31
  • Should I migrate to MySQL Workbench to solve my problem? – Andrew Apr 08 '20 at 19:39
  • The examples above should work in most relational databases. I'm not familiar with LibreOffice, so can't comment on that. – Chris Saxon Apr 09 '20 at 07:31
  • 1
    Once I have understood that the purpose of REFERENCES is creating foreign keys, (what I was doing using GUI ) and reading your answer a couple of times, behold, the problem is solved. – Andrew Apr 09 '20 at 14:59