Normalization repeating groups

[fred90]

My data consists of questions and answers. It also shows what the version number is of the answer and shows which persons changed which Answer. Its possible that some questions have the same answer.

Table looks like this:

    questionID    Question      answerID     Answer       VersionNr    User            date
         1        Who is....?    14          W.H. Smith     1.0        ...@test.com   1/1/14          
                                                            1.1        ...@test.com   3/8/14

         2        What is...?    28          3%             1.0        ...@test.com   1/2/14

bold is primary/composite key

0NF:
(questionID, question, AnswerID, answer,RG{versionNr, user, date}

1NF:
(questionID, question, AnswerID, answer)
(questionID, AnswerID, VersionNr, user, date)

2NF/3NF:
Q(questionID, question, AnswerID)
Ans(AnswerID, answer)
Version(questionID, AnswerID, VersionNr, user, date)

My question is whether I should remove questionID from Version, because the versionNr, date and user gives information about the Answer and Not the question.

 

[MarkK]

How many tables do you have? What are they?

 

[ButtonMoon]

Welcome Fred,

This looks like homework to me. The best way to tackle such an exercise is to write down the dependencies as you would understand them to be and then validate your potential database designs against those dependencies. For example, I could guess that AnswerID->QuestionID (meaning AnswerID functionally determines QuestionID) in which case QuestionID would belong in your Ans table not in the Version table. AnswerID->QuestionID is only my assumption however and given such a tiny sample of data any number of other things are possible. What is important is that you can justify your own analysis and any assumptions you make.

Another important tip. You need to identify any and all candidate keys in the tables. Identifying just one key (primary key) per table is not necessarily sufficient. To satisfy 3NF you have to ensure that every determinant of a non-prime attribute includes a candidate key.

EDIT: I missed the point that some questions may have the same answer. I expect that means that AnswerID->QuestionID would not be correct. The rest of my answer still holds.

 

[The_Doc_Man]

I don't know how far back the archives go but this question has been asked before, many times. And yes, it was acknowledged as homework or a class project.

Search this forum for "Questionnaires" and you will see many discussions on the subject. Since I don't get graded on this, I won't do the work, but I don't mind telling you where to look. If you do the appropriate research, then you might find that this problem has been solved many times on this forum.

 

[fred90]

Thanks for the help.