Re: MV and SQL

From: David Cressey <dcressey_at_verizon.net>
Date: Tue, 24 Jan 2006 23:05:35 GMT
Message-ID: <3pyBf.3993$Jn1.2119@trndny01>

"JOG" <jog_at_cs.nott.ac.uk> wrote
> David Cressey wrote:
> > "JOG" <jog_at_cs.nott.ac.uk> wrote
> >
> > > Ok, I have a question, with a bit of a build up: RM is based on the
> > > satisfaction of a given predicate.
> >
> > [snip]
> >
> > > 1) Incomplete Data at entry time.
> > > 2) Incorrect Design.
> > > 3) Static Design which no longer matches how the real world has
> > > shifted.
> >
> > [snip]
> >
> > >
> > > In my eyes the fact that these 3 things happen so frequently indicates
> > > that a system based on the satisfaction of a fixed predicate has poor
> > > application to the real world and that the RM's solid mathematical
> > > basis might be yet improved upon through integration of these issues.
> > > Data is almost always incomplete, a designer cannot be omniscient and
> > > will always make errors, but most importantly situations change.
> > > Continually. It is preferable that we have a database model that
> > > accomodates these factors: they are not orthogonal to data modelling
> > > processes in the real world.
> > >
> >
> > Without addressing your question about Pick, let me just comment on the
> > above excerpts from your build up.
> >
> > First, the big to-do about nulls basically arises from the use of a
single
> > table row to encode more than one predicate. If one of the predicates
is
> > present, and the other one is absent, you get nulls.
> > In theory, you could avoid nulls altogether by implementing a fully
> > normalized design. In practice, it's better to live with nulls.

>

> Hi david,

>

> Yes, if I've followed the line of thought correctly, you are referring
> to predicate decomposition, where all rows are broken down to
> irreducible tuples. This makes perfect sense to me as someone focused
> on theory - but I'm obviously aware this isn't how we use RM in
> practical circumstances and afaik not how Codd envisioned such a system
> (hence his support for 3/4VL). The arguments I have heard against it
> are wholly practical - the colossal logistical weight of joining all
> those decomposed predicates to return appropriate results.
>

You are correct. However, when I was referring to "The big to-do about nulls", I was referring to discussions in this newsgroup and not to actual practical difficulties in database design. In practice, I have never decomposed a relational table solely to avoid nulls. It isn't worth it.

> But then that is exactly my point - If RM at this decomposed level
> cannot function as desired in the practical world is this a weakness in
> the model? (perhaps a weakness that is satisfactorally bandaged with
> nulls) But is it something that other systems such as Pick have an
> advantage in?

No, it isn't. The RM has been converted into some kind of mystical religion by some people. For those of us who use RM and/or SQL as a tool, we decide when to decompose and when not to. It depends on what you intend to do with the data.
If not cecomposing results in update anomalies, knowing normalization theory allows us to know where the update anomalies are going to appear, and plan our programming strategy accordingly.

>

> I ask this as I am also particularly interested in schema evolution (or
> perhaps just design evolution). All models we currently use seem to
> have a rather static view of the world - they of course can adapt to
> new schema through structural change, but certainly don't seem
> particularly amenable to doing so. Are these MV models any different?
>

I'm not following you here. You can always create a new table, or alter an existing table by adding a new column.

This requires DDL, and making use of the new structure requires revising or extending existing DML. But isn't that inherent in evolution itself?

>

> Again I agree. When I refer to incompleteness I am referring to the
> mismatch that you've highlighted, between the designers idealized
> interpretation of the requirements, and the actual real world (and no
> doubt ever-changing) circumstances of data entry. This mismatch appears
> to happen so much that I wonder if should be a key consideration in the
> features of a model, rather than an afterthought. Again I was wondering
> if there are models out there which are notably advantageous in this
> area

>

It depends. Most of the databases I have worked with were the creation of the enterprise that owned the data. Databases that are built to be part of a licensed product seem to run into different levels of mismatch between offering and need. That's a problem in human communication and commerce that I couldn't really say much about.

> > As far as incorrect design and obsolescent static design goes, that's a
much
> > longer subject.
> >
> > Two facets of that subject are: early binding vs late binding of design
> > decisions, which is where my interest is going now, and the evolution
of
> > the schema as set forth by mountain man.
> >
> > Basically, you can't know what you are doing without fairly thorough
> > analysis, and the art of analysis is still quite primitive, compared to
> > design and implementation. If you aren't sure of what you're doing,
late
> > binding nearly always gives you better payback than early binding.

>

> Sounds interesting - are there any good reference sources for this
> topic?

>

If someone else has any, I'm eager to know it!

regards, Received on Tue Jan 24 2006 - 17:05:35 CST