Re: Designing DB in accordance with the relational model

On Nov 11, 12:39 am, Hugo Kornelis
<h..._at_perFact.REMOVETHIS.info.INVALID> wrote:
>
> Since some elements don't exist in all games, the obvious predicate to
> include in your model would be "<Element> exists in <Game>".

Well, it seems to be reasonable solution. Maybe "<Element> exists starting from <Game>" would be even better. Although it means I cannot rely on domain constraints... well, DCNF is pretty useless anyway, so actually there is no big problem.

> The predicate "<Character> has <Defense>", for instance, is
> incomplete. To complete it, you have to change it to "<Character> has
> <Defense> in <Game>".

The problem is, many characteristics never change at all.

On Nov 11, 12:10 am, Erwin <e.sm..._at_myonline.be> wrote:
> ??????
>
> By including a relvar for that purpose in your DB design. Plus the
> needed relvars or attributes for documenting how characters relate to
> games.

Allright. The second approach.

TYPE GAME POSSREP GAME {S CHAR};
TYPE ELEMENT POSSREP ELEMENT {S CHAR}; TABLE Games {Game GAME, OrdinalNumber INT}   PRIMARY KEY {Game};
TABLE CharactersExistence{Character# CHAR_ID, CharacterName CHAR, Game GAME}
  PRIMARY KEY {Character#, Game},
  FOREIGN KEY {Game} REFERENCES TO Games; TABLE ElementsExistence{Element ELEMENT, Game GAME}   PRIMARY KEY {Element},
  FOREIGN KEY {Game} REFERENCES TO Games;

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. I think I'd want to have a relation with predicate like "Characteristic <Name> with type <Type> existed in game <Game>", and I know how to do this... but I'm afraid of it and won't do it.

Okay, so Characters was decomposed (I am not sure it is a good design, but I'm just training, right?) and now it looks like this: TABLE CharacterAttack {Character# CHAR_ID, Game GAME, Attack INT}

   PRIMARY KEY {Character#, Game},
   FOREIGN KEY {Character#, Game} REFERENCES TO CharactersExistence,    CONSTRAINT IS_EMPTY (CharactersExistance {Character#, Game} MINUS CharacterAttack{Character#, Game}); // so Attack is mandatory in every game
...
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.
...
TABLE CharacterType {Character# CHAR_ID, Game GAME, Type ELEMENT}

   PRIMARY KEY {Character#, Game},
   FOREIGN KEY {Character#, Game} REFERENCES TO CharactersExistence,    CONSTRAINT IS_EMPTY (CharacterType {Character#, Game} MINUS CharacterExistence {Character#, Game} ),

   CONSTRAINT IS_EMPTY (CharacterType {Game, Type AS Element} MINUS ElementsExistence); // Mandatory + domain constraint

TABLE Character2ndType {Character# CHAR_ID, Game GAME, Type ELEMENT}
   PRIMARY KEY {Character#, Game},
   FOREIGN KEY {Character#, Game} REFERENCES TO CharactersExistence,    CONSTRAINT IS_EMPTY (CharacterType {Game, Type AS Element} MINUS ElementsExistence); // Non-mandatory + domain constraint

Also I might need some other constraints to enforce, umm, the "mandatoriness" of several characteristics in certain games and the absence of several others in certain games. But this design is as ugly as hell. Besides, it has redundant information, for example, in CharactersExistence -- if a character exists in game with <OrdinalNumber> = N, it will exists in game with <OrdinalNumber> = N +1. Well, this last problem can be addressed with using INTERVALs (though I am not very common with temporal model of data) or just with changing predicate to "Character started to exist" as I've mentioned before (though it complicates the constraints).

So, maybe I should really look into a relation with "Characteristic <Name> with type <Type> existed in game <Game>" predicate? But I feel that using such relation will ruin all relational model :( Received on Thu Nov 11 2010 - 00:54:26 CET