BCNF decomposition does not prevent insert anomaly?

by taffer   Last Updated November 09, 2018 00:06 AM

I am in the process of designing a schema for a simple polling app and have tried to normalize it with the decomposition algorithm from this lecture.

Requirements

A poll has a unique id (poll_id), a question (question) and multiple options (option) that can be voted on. A participant (participant_id) can vote for each option by giving it one to ten stars (stars).

What I have so far:

I came up with the following relation that contains all attributes:

(poll_id, question, option, participant_id, stars)

...and the following functional dependencies:

  • poll_id -> question
  • poll_id, option, participant_id -> stars

After applying the algorithm I have the following relations (keys are bold):

  1. (poll_id, question)
  2. (poll_id, option, participant_id, stars)

My Question:

At this point the schema should be in BCNF, but it's easy to see that I can't just create a poll with a question and some options, because with this design options can only exist if there are also participants who have voted. I should probably have another relation (question,option). Did I miss something important in the design?

My ideas so far:

  • Should I have considered insertion anomalies before using the decomposition algorithm?
  • Are there any other functional dependencies I forgot to specify?
  • Is BCNF not sufficient for my requirements? Do I need 4NF or higher normalization?


Related Questions


How do you perform the chase test?

Updated July 13, 2017 18:06 PM



Questions Concerning the Chase Test

Updated July 10, 2018 05:06 AM

First Normal Form: Definitive Definition

Updated July 14, 2017 18:06 PM