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

  • 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 Nov 15 '18 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 Nov 15 '18 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 Nov 15 '18 at 18:59
  • @philipxy 'Why do you say that?' I was assuming no NULLs. – taffer Nov 15 '18 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 Nov 16 '18 at 1:28
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
  • 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 Nov 13 '18 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 Nov 14 '18 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 Nov 14 '18 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 Nov 16 '18 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 Nov 16 '18 at 3:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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