Re: Designing DB in accordance with the relational model

On Nov 11, 10:32 pm, Hugo Kornelis
<h..._at_perFact.REMOVETHIS.info.INVALID> wrote:
> On Wed, 10 Nov 2010 15:54:26 -0800 (PST), Kentauros
>
> <joker..._at_gmail.com> wrote:
> >On Nov 11, 12:39 am, Hugo Kornelis
> ><h..._at_perFact.REMOVETHIS.info.INVALID> wrote:
>

> >The problem is, many characteristics never change at all.
>
> But why is that a problem?
> "Wario has defense 17 in Super Mario World"
> "Wario has defense 17 in Super Mario World 2"
> "Wario has defense 17 in Super Mario World 3D"
> "Wario has defense 17 in Super Mario World - the last part"
> "Wario has defense 17 in Super Mario World - the sequel to the last
> part"
>
> There is nothing wrong with this extension. The candidate key for this
> relvar is (Character, Game), and there are no violations of this key,
> or of any other constraint.
The redundancy. That is what wrong.

> (snip)
>
> >TABLE CharactersExistence{Character# CHAR_ID,  CharacterName CHAR,
> >Game GAME}
> >  PRIMARY KEY {Character#, Game},
> >  FOREIGN KEY {Game} REFERENCES TO Games;
>
> I'm wondering why you have introduced a character number, and why the
> CharacterName is not a candidate key. Since this is all about tracking
> different characteristics of the same character in different games, I
> would have expected the character name to be the most obvious
> identifier for characters - or are there indeed any games with a
> "Sonic" character that is not derived from the original hedgehog?
Because these CharacterNumbers exist in the games, they're visible to players, and they behave just like AUTOINCREMENT. Well, if the game characters have such wonderful characterstic, I surely pick it as a primary key.

> >But it seems that the table Characters has to go characters have
> >different sets of characteristics in different games, and I can't
> >predict what will (dis)appear or which remained characteristic change
> >-- except for name.
>
> But why would that impact the table Characters? If a character exists
> in a game, you can represent that fact in this table, regardless of
> his/her characteristics.
It's the work for CharacterExistence. The table Characters were intended to store characters' characteristics. Maybe CharactersCharacteristics would be a better name.

> If your UoD ensures that each character in each game has at least one
> characteristic (and only then), you could consider regardig this table
> as redundant and dropping it - though I'd probably still keep it,
> along with appropriate foreign keys. (You would not drop the table
> Orders because each order has at least one OrderItem either, would
> you?)
In a given game, characters have list of characteristics. Some of those characteristics are mandatory, every character in the game has them. Some of them are optional. Between the games, these lists of characteristics changes. Some characteristics disappear, some new appear. Mandatoriness/optionality may change.

> >TABLE CharacterSpecial {Character# CHAR_ID,  Game GAME,  Attack INT}
> >   PRIMARY KEY {Character#, Game},
> >   FOREIGN KEY {Character#, Game} REFERENCES TO CharactersExistence,
> >   CONSTRAINT IS_EMPTY (CharactersExistance {Character#, Game} WHERE
> >Game = GAME("First Game") XMINUS CharacterSpecial{Character#,
> >Game}); // Special is mandatory in the first game, but is absent in
> >all the later ones.
>
> This is not a good constraint. What will you do if a new game is
> relased next month (just in time for Christmas), where special is
> mandatory again? Change the constraints?
Yes. Just change the constraints. So the constraints will mean "Special is mandatory in the first and the (currently) last games, but is absent in any other game". It's pretty simple and won't take any changing in table.

> If special is mandatory on one game and optional in another, than you
> can do one of two things:
> 1) Leave it optional - after all, there clearly is no generic rule
> that each character must have special in each game.
> 2) Add the unary preciate "In <Game>, special is mandatory" to your
> model and use that instead of the name of the first game in the
> constraint.
I repeat again, "special" is a mandatory attribute in the first game, but in any other game, attribute "special" doesn't exist at all.

> I'm also wondering about your choice to use seperate tables for each
> characteristic (attack, defense, special, ...). Not that this is
> really WRONG, but it might not be the best choice either. Have you
> considered the predicate "<Character> has <Characteristic> <score> in
> <Game>" to replace all these?
Yes. And I don't think it will work. What will the domain of <score> be? Alpha? No, thank you very much. Such relation would require an enormous number of constraints. Received on Thu Nov 11 2010 - 21:29:08 CET