Date's "Introduction to Database Systems" is an amazing book. However,
it doesn't says very much on HOW one should translate a given universe
of discourse into a database which can be treated as a sane
representation if it. Maybe Date also wrote a book mostly concerned on
this topic, but unfortunately I haven't heard of it.

So, maybe someone could explain what do I do with the next situation?
Here it is:

There are entities with attributes (yes, very trivial). Maybe they're
fiscal reports, or something.One day, however, the entities change:
several attributes are thrown out, several new attributes are
introduced, and for some attributes, their domain are expanded.

Well, if I were using SQL DBMS, I would just add new columns for new
attributes and spam them with NULLs for the older entities, and the
columns for thrown-out attributes will have NULLs in them from now on.
Domains? Ha, I was using a special table of "id# -- describing text",
so I just add some new records in it.

But what do I do if I have D DBMS, implemented strictly by The Third
Manifesto? I just can't imagine. I can't use NULLs. Making tables
"ENTITIES_1999-2010", "ENTITIES_2011-NOW" surely won't work. How do I
extend domains, again? It's not subtyping, it's supertyping, but I
dislike the idea I introduce new wider types, then rewrite the existed
ones so they will be the subtypes of the newly introduced types.

So, what should I do? And please, no "screw that moronic theory Date
invented" replies. I won't.

On 09/11/2010 7:03 AM, Kentauros wrote:

(This thread has the kind of title that I think will keep it 'alive' for
a quite a while.)


My first answer to the question, as far a relational theory goes, is a
general one. Codd intended his table names to stand for predicates. If
one desires to follow his and Date's theory, predicates can't be
ignored. They don't prevent two tables or what Codd called 'R-tables'
from standing for the same predicate, but when the attributes are
different it's clear that the predicates must be different. So for me
the question becomes "what are the application requirements for each of
the predicates?" which might (or might not) lead to recognizing what is
common, which in turn might lead to a third predicate for which the
question needs to be repeated, or it might even lead to a recognition
that as far as the application is concerned, they have nothing in common.


(Date has written at least an order of magnitude more words than Codd
did and sometimes this makes it harder to appreciate the essence of what
both of them intended. Then there is the essence of what the original
two predicates 'mean'. Coming up with a clear answer to that for a
given application often involves much soul-searching for designers.
Personally I think that more often than some people expect, the answer
is that there are in fact two distinct applications. Often there is a
huge psychological resistance to this answer and pressure on developers
to somehow make it not so. For me, a basic understanding of the algebra
makes things clearer, a few people prefer the calculus. I mean
'clearer' in the sense of what the logical underpinnings of the model
allow.)

Clifford Heath wrote:

*PLONK!* And on so many levels too.

--
Roy

Kentauros wrote:
Trivial, but wrong. Things don't have attributes. Things exist in
relationship to other things. Relationships between things can be
defined through predicates, and sometimes, the population
constraints over those predicates allows contraction of the
relationship into the form we call "an attribute". We further
require that attributes should not have attributes of their own.

The existence of an attribute therefore depends on the cardinalities
of a relationship, and the most frequent sources of disruptive change
in real information systems isn't change in the relationships, but
change in the cardinalities involved, and in the addition of
"attributes of attributes" leading to reification (new tables).

Fact-oriented information modeling separates the definition of the
predicates which define fact types from the population constraints
over those fact types. This allows *dynamic* mapping to efficient
normalised relation structures.

The procedure for constructing relational models using the fact-oriented
approach is defined and taught in Terry Halpin's book "Information
Modeling and Relational Databases".

You shouldn't need NULLs in your conceptual model. They are an
efficiency feature, added to allow a reduction in the number of
physical tables required, as a concession to the physical access
characteristics of mechanical storage devices.

A competent D would allow you to directly implement your conceptual
model without NULLs, and would hide any actual use of NULLs in its
physical layer. To my knowledge, this has never been properly done in
a generalised commercial product... but I'm working on it.

Clifford Heath, Data Constellation, http://dataconstellation.com
Agile Information Management and Design.

Okay. Thanks to everybody who answered, I greatly appreciate this!
However... Yes, the designing of a DB is more like art than science,
but I depicted a quite common situation. So, it MUST have a commonly
used solution, right? And no one has explicitly shown this solution
yet...
Hm. What would you say if I show you an example universe of discourse
+ DB attempting to represent it? Could you point at the poor decisions
and weak points?

So. Assume there are series of video games in which we can choose from
multiple characters. In each game, there is a fixed set of them, and
there is a fixed set of characteristics every character has. Some of
characteristics are integers, some are strings, some are enums. So, if
I need to represent data only about one game from the series, there is
no trouble at all -- I define corresponding domains, create relvars
and some constraints. As to create relvars, I take this approach: if
it is possible for a character to don't have a characteristic, or to
have more than one value of it, then I create a separated relvar
{Character# CHAR_ID, CharacterisitcName SOME_TYPE} with Character# as
a foreign key + some other tweaks for primary key. If any character
has exactly one value for a characteristic, I put this characteristic
into a "main" relvar as an attribute.

TABLE Characters {Character# CHAR_ID, Name CHAR, Type ELEMENT,
Attack INT, Defense INT, Special INT, Speed INT}, PRIMARY KEY
{Character#};
TABLE Characters_2nd_Type {Character# CHAR_ID, Type2 ELEMENT},
FOREIGN KEY {Character#} REFERENCES TO Characters, PRIMARY KEY
{Character#};

Pretty simple. Characters have unique Character#, exactly one Name,
Type, HP, ..., Speed, and may have one Type2. ELEMENT is enum of
strings ("FIRE", "WATER" etc.).

But! We'd like to have info an all games beacuse every character from
earlier games also present in later games! And by "present" I mean
"Their representations are as close as it's possible".

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

TABLE CharactersII_NEW {Character# CHAR_ID, Name CHAR, Type
ELEMENT2, Attack INT, Defense INT, SpecialAttack INT,
SpecialDefense INT, Speed INT}, PRIMARY KEY {Character#};

TABLE CharactersII_ADD {Character# CHAR_ID, SpecialAttack INT,
SpecialDefense INT}, PRIMARY KEY {Character#}, FOREIGN KEY
{Character#} REFERENCES TO Characters;

VIEW CharactersII AS Characters{ALL BUT Special} JOIN CharactersII_ADD
UNION CharactersII_NEW;

TABLE CharactersII_2nd_Type {Character# CHAR_ID, Type2 ELEMENT2},
FOREIGN KEY {Character#} REFERENCES TO CharactersII, PRIMARY KEY
{Character#};

(Sorry for long post). 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?"
This is partially because DB doesn't represent the fact that games
themselves are things to be taken into regard. Unfortunately, I don't
know how to take them into regard.
Second, how do I declare ELEMENT2?

ELEMENT2 = ELEMENT UNION {"NEWELEMENT1", "NEWELEMENT2"};

or

ELEMENT2 = {"WATER", "FIRE", "NEWELEMENT1", "NEWELEMENT2"};
ELEMENT IS ELEMENT CONSTRAINTED {ELEMENT <> "NEWELEMENT1" AND ELEMENT
<> "NEWELEMENT2"};

I don't actually know.

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

(snip)
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)
Indeed, you can't. Because you forgot to model the predicate
"<Character> appears in <Game>". Add that predicate, and the query is
obvious.

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

On Nov 11, 12:39*am, Hugo Kornelis
<h... (AT) perFact (DOT) REMOVETHIS.info.INVALID> wrote:
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 problem is, many characteristics never change at all.

On Nov 11, 12:10*am, Erwin <e.sm... (AT) myonline (DOT) be> wrote:
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

On Wed, 10 Nov 2010 15:54:26 -0800 (PST), Kentauros
<joker.vd (AT) gmail (DOT) com> wrote:

I don't see why this would be better. Unless you are 100% sure that
elements are only added and never removed. And even in that case,
there is nothing wrong with sticking to "<Element> exists in <Game>",
and it will probably make your queries easier.

Also (and I'll probably regret asking and slap my forehead when you
reply), what is DCNF? I could not find any plausible entry in
wikipedia, wiktionary, dictionary.com, or onelook.com.

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.

From your posts, I get the impression that you try to minimize the
number of distinct facts in the database. In my experience, it is
often far better to minimize the number of distinct predicates. If you
are concerned about the amount of work involved with entering the same
information over and over again, then don't forget that the UI can
handle that kind of work. Let the UI create a new game as a clone of
its predecessor, then the users only need to enter the new and changed
characteristics. But the UI discussion is of course way off-topic
here.

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

(snip)
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.

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

The same logic applies here as before - though the existence of a
characteristic in a game is implied by characters from that game
having it, it does not hurt to be able to store that fact explicitly.

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?

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.

Yup. That's what you get when you mix data and metadata (as you did in
the constraint for the "sometimes-mandatory" special characteristic.

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? (So basically, move the characteristic
from the metadata level to the data level, so that you have less
tables with more rows). In a later post, you mention the need to find
all characteristics for a character in a game - with this design,
that's a breeze.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

On Nov 11, 10:32*pm, Hugo Kornelis
<h... (AT) perFact (DOT) REMOVETHIS.info.INVALID> wrote:

players, and they behave just like AUTOINCREMENT. Well, if the game
characters have such wonderful characterstic, I surely pick it as a
primary key.

intended to store characters' characteristics. Maybe
CharactersCharacteristics would be a better name.

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.

"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.

but in any other game, attribute "special" doesn't exist at all.

be? Alpha? No, thank you very much. Such relation would require an
enormous number of constraints.

On Thu, 11 Nov 2010 12:29:08 -0800 (PST), Kentauros
<joker.vd (AT) gmail (DOT) com> wrote:

It sounds as if you are getting into premature optimisation.

[snip]

Sincerely,

Gene Wirchenko