Deciphering Database ER Diagram for questionnaire

Question

I am trying to decipher a database ER diagram that a teacher of mine helped me make. I am mainly trying to figure out how to build the tables needed and combine them correctly.

The ER diagram in question is for an online questionnaire where every question is based on the answer given for the preceding one, eventually leading to a solution/ending.

Every question can have multiple answers and the same question can have multiple different answers, depending on the preceding answers given.

The combination table shows which answers are associated with which question and what the next question should be, if a specific answer is given, eventually leading to a solution.

The ER diagram looks like this:

What I do not understand:

The solution / question table will hold both questions and solutions, which can have different entities, but I do not understand how this is done? And what is the D?

The leads to is a junction table, because it's many to many connection between Answer and Question / Solution, but why? Are they not connected in the Combination table? - Does that table not hold all the information needed to know which answers are connected to what question and which question will be up next if a specific answer is chosen?

I have a very hard time figuring out how to build this database so it will work as wanted.

Answer 1

Your diagram is not a proper ER diagram. In particular, the ovals with "Specific solution entities here" and "Specific question entities here" don't appear to indicate attributes of Solution and Question respectively. That means Question, Solution and Question/Solution don't have any attributes. Also, the Combination entity has Answer ID and Question/Solution ID as attributes instead of relating to the relevant entities.

Let's quickly review the elements of an ER diagram.

• Rectangles indicate entities, ovals indicate attributes.
• An underlined oval label indicates an attribute is part of the primary key that identifies the entity.
• Diamonds indicate relationships.
• Relationships are identified by the key attributes of the entities they relate.
• Relationships can have attributes too.
• A circle containing d or o indicates a disjoint or overlapping subtype relation.

Based on your requirements, I came up with the following functional and multivalued dependencies:

• QuestionID -> QuestionText
• AnswerID -> AnswerText
• AnswerID -> QuestionID (Possible answers)
• DecisionID -> QuestionID
• DecisionID ->> AnswerID (Available answers)
• DecisionID, AnswerID -> NextDecisionID

Based on this I suggest the following ER diagram:

Translated to a tabular model (I denormalized relations with the same determinant, which is why there's no table for PossibleAnswers and DecisionQuestion):

Using this, you can describe any number of paths of questions, with a set of available answers for each decision referring to the next. Solutions aren't explicitly modeled, rather an AvailableAnswer without a NextDecisionID indicates a solution.