2

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)?

Improve this question
10
  • 1
    Re insertion anomalies: Find a definition & determine/find an algorithm to find them all & apply it. Ask re where you are stuck. Re FDs: Ditto. Re "Is BCNF not sufficient for my requirements? Do I need 4NF or higher normalization?" What you seem to mean is, what is a good design & what NFs might that involve--or what? Show work following a published academic textbook on information modeling & DB design. (Always normalize to 5NF then for certain reasons possibly denormalize.) Re NULL. Re "1NF".
    – philipxy
    Commented Nov 15, 2018 at 7:39
  • 1
    @philipxy I converted the table to text. Also I put my question in bold text and clarified it to prevent further confusion.
    – taffer
    Commented Nov 15, 2018 at 18:54
  • @philipxy 'When are there NULLs?' There are nulls when we want to create a poll but no participants for that poll and has assigned stars for an option.
    – taffer
    Commented Nov 15, 2018 at 18:59
  • @philipxy 'Why do you say that?' I was assuming no NULLs.
    – taffer
    Commented Nov 15, 2018 at 19:01
  • 2
    A free online university DB course. (Presumably source of your video. Stanford materials I have seen are of the highest quality. Still, needless fuzziness.) ("Detailed lecture notes are provided. Having a textbook in addition to the notes is not necessary, but [...]" By the professor: Database Systems: The Complete Book (2nd edition 2009) Garcia-Molina, Ullman, Widom (Pearson). Google with 'pdf'. Dozens of others are also free online in pdf.) See my earlier (network of) (anti-fuzziness) links re 1NF & null.
    – philipxy
    Commented Nov 16, 2018 at 1:28

1 Answer 1

Reset to default
2

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
Improve this answer
8
  • 1
    I read the article from Date and Fagin, but I'm not quite sure I understood it. Does the article suggest that I should only have relationships that have a single attribute as a key? The reason I'm asking is that I'm looking for a methodical approach to normalize my design.
    – taffer
    Commented Nov 13, 2018 at 23:08
  • Is it possible that the relationship I started with (poll_id, question, option, participant_id, stars) was not in first normal form and that this was the cause of all the problems? Because if it is a requirement that a survey must be possible without participants, then the data can only be inserted into the original relation by setting participant_id and stars to NULL, which is not allowed in first normal form.
    – taffer
    Commented Nov 14, 2018 at 1:34
  • @taffer, In general for first normal form it is required that a relation has only elementary values, not compound values, while NULL values are not part of the relational theory. The article says which are the “good” properties of relations that include, among the candidate keys, one with a single attribute. In many cases this already happens for a “natural” key. In some other cases one could add a so-called “surrogate” key. This does not implies, however, that one should always add a surrogate key for normalizing a relation. And this does not solve automatically all the normalization issues!
    – Renzo
    Commented Nov 14, 2018 at 7:58
  • 1
    @taffer votes is rows satisfying predicate "[participant_id] voted [stars] on [option] of [poll_id]". options_of_poll ("[poll_id] has [option]") is like project {poll_id, option} (votes) ("for some values for [participant_id] & [stars], [participant_id] voted [stars] on [option] of [poll_id]") but can have more rows--its rows with [poll_id] & [option] where there aren't values for [participant_id] & [stars] satisfying vote's predicate. We could find we need it while methodically information modeling or by haphazardly noticing an insertion or deletion anomaly while normalizing.
    – philipxy
    Commented Nov 16, 2018 at 3:32
  • 1
    @taffer Let's use t to name the single table with nullable columns from your original edited-out apparently correct intuitive SQL design. It is rows where "[poll_id] has [question] & [option] and either [participant_id] voted [stars] or no participant voted on that option & poll & [participant_id] is null & [stars] is null". Then t = polls join options_of_poll left join votes, polls = project {poll_id, question} (t), options_of_poll = project {poll_id, option} (t) & votes = restrict (participant_id is not null) (t). (Nulls give fewer but more complex tables/predicates.)
    – philipxy
    Commented Nov 16, 2018 at 3:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.