Dear Stackoverflow members,
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 teacher that helped me make it is away on vacation and I cannot reach him, saaaadface.
The Er Diagram:
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? No joke..
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?
Any help, tips or such will be greatly appreciated! I have a very hard time figuring out how to build this database so it will work as wanted and I would be very happy if someone could help.
Thanks a bunch in advance!
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
Question respectively. That means
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.
oindicates a disjoint or overlapping subtype relation.
Based on your requirements, I came up with the following functional and multivalued dependencies:
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.