Re: Designing DB in accordance with the relational model

On 9 nov, 16:03, Kentauros <joker..._at_gmail.com> wrote:
> 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.

Knowledge about this subject (I mean the formal, precise, worthy-of- the-label-'scientific' kind of knowledge) of HOW is very much nonexistant, and what does exist all seems very fragmented. Database design is still more art than science. Date's own words, and I don't believe he has since seen any reason to change that position. Nor has anyone else, probably, for that matter.

> Maybe Date also wrote a book mostly concerned on
> this topic, but unfortunately I haven't heard of it.

No. A book on that subject, and with the scientific underpinnings at the same level of Date's writings, does not really exist. Something that might come close is "Practical issues in database management" by Fabian Pascal.

> So, maybe someone could explain what do I do with the next situation?
>
> There are entities with attributes (yes, very trivial).

I take this to mean that there is an informal model (an ER model, perhaps) of the information being managed. I also presume this informal model has been translated into a (formal) logical model, consisting of a very precise statement of all the relvars (names & headings) and all the database constraints (relvar keys, inclusion dependencies, and all the others too). If some of the "attributes" of your "entities" were "optional", then under a strict TTM system, this means that your "entities" will have been decomposed into several distinct relvars. The ultimate point being 6NF, with all relvars having only a key plus (zero or) one attribute.

> One day, however, the entities change:
> several attributes are thrown out, several new attributes are
> introduced, and for some attributes, their domain are expanded.

Adding new attributes to "entities" can more easily be done by adding new relvars. It will require no physical conversion/reorg at all, unless the new relvars must immediately be initialized to a non-empty value.
Dropping existing attributes from "entities" will always require immediate database conversion/reorg, but (a) some techniques exist for avoiding having to do this with a 'big bang' scenario, and (b) legislation is likely to apply to you, forcing you to keep historical records nonetheless of the information that was recorded in those dropped attributes, and perhaps even preventing you altogether from dropping any attribute what so ever.

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

Do you really think that this sweeping generalisation of an approach is really a solution to your problem ? Think again.

> Domains? Ha, I was using a special table of "id# -- describing text",
> so I just add some new records in it.

TTM doesn't prevent you from doing this if you want 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,

Why do you dislike the idea of applying the solution to your problem ?

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

The problem you depict is not so much different from changing a previously existing public API in such a way that the new version is backward compatible.

If a formerly existing method is now deprecated, then programmers using that (method of the) API must start changing their programs eliminating the use of that deprecated method.

If a formerly existing relvar has changed structure, then programmers accessing/updating that relvar must change their programs to reflect the requirements imposed by the new relvar structure/heading. Received on Wed Nov 10 2010 - 14:20:06 CET