MySQL table with "calculated values" and keys that are primary and foreign at the same time

Question

I already read through other questions such as these:

Is it fine to have foreign key as primary key?

Primary and Foreign Key at the same time

However, it seems to me as the answers are wildly different and the answer of whether or not it can be okay to have keys be both primary and foreign depends on the specific database and purpose of the table containing the keys.

This was done as part of an exam project and I have to either be able to explain my database design or come up with something else for my presentation.

My question(s) are:

How would you best justify the "selected_answers" table in my DB? i.e. does it make sense?

Is is still in 3rd normal form?

Could I make it different and simple while fulfilling the same purpose and functions of the current table?

This is my current relational model:

Relational model

This is my current SQL tables script:

SQL tables

The idea is to create a database which can store "audit" data on a firm that wants to audit how well they are complying with GDPR. It was an assignment for an exam.

The reason why I have "selected answers" is so that I can compare selected answers from users with the questions being asked.

Keys being both primary and foreign keys is so the numbers are "unique" while still referencing audit and question table.

The database is also supposed to be able to store each audit and the answers thereof individually.

For some more context I populated my tables with "test data" so that I could use stored triggers, procedures and a function.

Test data 1

Test data 2

All questions are pre set as question_ID 1 - 12 and all answers are answer_ID 1-5. So these numbers will definitely repeat themselves, but for the purposes of storing audits and what was answered.

This is the final result which the database calculates: Company name and registration number (CVR - danish), Time of audit (is updated through a stored trigger), average score of answers and a "compliance level" based on the average score.

Final result

I hope that I have provided enough information for you to help me :-) if you also need to see stored procedures, function and trigger tell me and I'll upload 'em.

Answer 1

Your table makes sense. You can view it as a many-to-many-relationship between audit and question (each audit can answer many questions; each question can be answered in many audits) with an attribute indicating what the audit's answer to the question is, constrained (by the foreign key to the answer table) to be one of a number of predefined answers. I also advise you write down the relvar predicates and see if they make sense.

It is in 3NF, unless there are odd functional dependencies (FDs) you haven't told us about. There seems to be a single non-trivial FD, {Audit_ID, Answer_ID} -> {Answer_ID}, and the left-hand side is a superkey (a key, in fact), so the table is in Boyce-Codd normal form, which implies 3NF. (I really don't know why people bother with 3NF when there is BCNF.)

I can't think of any simpler solution that fulfils the same requirements. Your design may have issues due to the use of surrogate keys combined with the lack of natural keys, but that is a different question.