BCNF decomposition does not prevent insert anomaly

database-designnormalizationrelational-theoryrelations

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 (dependencies, normalization rules)?

Best Answer

Edited

I think that the relation is in BCNF, but it is known that this fact does not always solve all the anomalies. For this reason other normal forms, like 4NF, 5NF, etc., for instance those based on elementary keys (Elementary Key Normal Form, Key-Complete Normal Form, etc.) have been defined.

I am not sure of what other kind of dependencies are present in your schema, but I want to show a decomposition, as pointed in a comment below by @philipxy (correcting my previous version), to eliminate redundancies and anomalies from the schema.

polls (poll_id, question)
   poll_id → question
options_of_polls (poll_id, option)
   no non-trivial functional dependencies, so the key is (poll_id, option)
votes (partecipant_id, poll_id, option, stars)
   partecipant_id, poll_id, option → stars
Related Question