Re: Designing DB in accordance with the relational model

On Wed, 10 Nov 2010 11:37:25 -0800 (PST), Kentauros <joker.vd_at_gmail.com> wrote:

(snip)
>So, naturally, I'd like to simply add several new relvars and
>constraints into the DB without touching existing ones (or touching
>them as little as possible. Yes, they have to be designed with several
>things in mind, I don't argue with this). But! Let assume that in the
>second game a) new characters were introduced, b) Characteristic
>Special has gone (i.e. older characters don't have this characteristic
>in this game), c) Two new characteristics SpecialAttack and
>SpecialDefense were introduced (older characters got these
>characteristics too), d) The domain ELEMENT was extended -- two new
>values were added; e) Several older characters got new Type2 (not
>Type! Type2 only).

At that point, you have to conclude that your original design was flawed. Or rather, it was correct for the single-game UoD, but not for the multi-game UoD.

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

(snip)
>This design, however, has a certain flow: There
>will be new games III, IV, V, and so on. And I can't write a universal
>query to answer a question "How many characters is in every game?"

Indeed, you can't. Because you forgot to model the predicate "<Character> appears in <Game>". Add that predicate, and the query is obvious.

>Second, how do I declare ELEMENT2?

Since some elements don't exist in all games, the obvious predicate to include in your model would be "<Element> exists in <Game>".

-- 
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis