Re: Designing DB in accordance with the relational model

From: Kentauros <joker.vd_at_gmail.com>
Date: Thu, 11 Nov 2010 04:49:26 -0800 (PST)
Message-ID: <1723da55-2d7e-40f0-aa52-40364689421c_at_t13g2000yqm.googlegroups.com>


On Nov 11, 1:48 pm, Erwin <e.sm..._at_myonline.be> wrote:
> On 11 nov, 00:54, Kentauros <joker..._at_gmail.com> wrote:
>
>
>
>
>
> > On Nov 11, 12:39 am, Hugo Kornelis
>
> > <h..._at_perFact.REMOVETHIS.info.INVALID> wrote:
>
> > 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,
>
> Then you need a relvar(/table) for the predicate "<character> has
> characteristic <characteristic> in game <game>.".
Yes. And what is the point of it? It won't allow me to make "Select all appliable characteristics for a given <Character#> in a given <Game>" request. Or "Select all mandatory characteristics for every <Character#> appeared in a given <Game>".

> Plus perhaps an inclusion dependency (aka FK) to the relvar that holds
> the true instances of the predicate ""<character> appears in game
> <game>.".  If you have a need for such a relvar (which will
> essentially be the case if characters can appear in a game without any
> characteristics).
No, it's not the case. Every game has a "format" for characters: list of their characteristics with note whether they are mandatory or optional. So, every character appeared in the game must have values for every mandatory characteristic.

> BTW, did you consider that you didn't mention at all what it means
> _precisely_ for you to "be a game" ?  And that you might be causing
> confusion in doing so ?  By "a game" do you mean something like
> "Monopoly" (meaning it means more something like "a type of game"), or
> do you mean "the game of Monopoly that was played between x, y and z
> on april, 1 at 2PM" ?
>
> To you, it's probably obvious.  Learn to realise that to others, it
> usually isn't.
Oh. I am sorry. I mean "something like "Monopoly" (meaning it means more something like "a type of game")". And the "subsequent games" are like "Monopoly Extra" or something -- they have every in-game thingie that were introduced before, but with maybe some altered game rules and mechanics (so several characteristics disappear or appear). Well, it's a pretty vague description, I admit.

So, the design with decomposing Characters into relvars in 6NF is a good idea, isn't it? But again, it won't allow me to make the aforementioned queries... or maybe I can't just figure out the way it would allow to do it. Received on Thu Nov 11 2010 - 13:49:26 CET

Original text of this message