Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: 3vl 2vl and NULL

Re: 3vl 2vl and NULL

From: Frank Hamersley <>
Date: Thu, 16 Feb 2006 12:39:18 GMT
Message-ID: <Wn_If.9546$>

dawn wrote:
> Frank Hamersley wrote:

>>dawn wrote:
>>>Frank Hamersley wrote:
>>>>dawn wrote:

>>> I suspect that over the past decade,
>>> I have identified and talked about more things wrong with MV than with
>>> RDBMS's.
>>For my benefit can you identify the top 5 (in your opinion)?

> 1) The query language has no corresponding update language ever put
> into production

Was there a plan to do so, or was PickBasic deemed capable enough? So every update is a programme? Thats not so bad if you have a "copy deck" of standard templates and access/update methods. Of course with SQL large portions of the query statement can be used in forming the update.   This greatly helps the assessment of correctness as any update statement can be transformed back to a select to confirm it will act as expected.

> 2) The DataBASIC language often used in MV solutions at least as the
> language for the equivalent of stored procedures is a great procedural
> language and is loved by many (and I have not coded much in it), but it
> isn't quite the state of the art in programming languages in 2006 that
> the pretty-much exact same language was in 1976. It has not had 30
> years worth of keeping up, but the good news is that it is reasonably
> standard across vendors (with zero standards bodies).

So is C :-). What sort of library support does it offer?

> 3) In some ways, it would be as if you had a SQL language without the
> ability to specify a join within the statement. For many (not all) of
> the MV vendors, every attribute required for a query in the query
> language must be defined as the vocabulary of a single logical view of
> the data. Some tools are better than others in being able to navigate
> (I know that word trips buttons) via foreign keys. The flip side on
> this is that it is easy for end-users to write queries since they only
> have the option of writing the query against a single logical view
> (which can, of course, span files).

How do you predict all of the required logical views for ad hoc queries - is this an important additional phase in the design phase or is it simple to retrofit on demand?

> 4) There is no standard across vendors for a data source definition.
> There is no standard for client-server connectivity. This gives third
> parties the floor to write products like the jBASE mv.NET product that
> works with many different flavors of MV.

So each programme instance latches on to the files directly - no daemon request-response mechanism possible?

> 5) Third-party products for Business Intelligence and practically
> everything else speak ODBC often with direct access to SQL databases,
> but SQL is a second language (at best) for MV solutions and not the
> best way to access the data since you take a flexible model and
> constrain it with SQL (which adds fixed lengths, for example). This
> lack of being able to play easily with the SQL industry is one of the
> biggest reasons that many companies have moved or attempted to move to
> SQL Server or other SQL-based solutions over the years.

Is it easy to dump (aka bcp out) to another (SQL?) environment for ad hoc queries, routine reporting or data warehousing?

> I'm not sure these are my top 5, but they are 5 of the top issues I
> have with MV.

Ouch - in my current business space, any of those would hurt like amputation without anaesthetic!

>>>When talking about data modeling, however, that is one area
>>>where I cannot leave MV behind unless I can find a better data model.
>>>That was frustrating given that it is the data model part of the RDBMS
>>>that was touted from the start and the reason it was supposed to be so
>>>far superior to other databases.  It is less of a mystery to me now
>>>than when I came here, however.

Even if your data model ambitions were accepted, I doubt I could cop any of your top 5 to get that.

>>OK - but to summarise our discussion to date your problem with the RM is
>>the inherent constraints prevent you from having a visually aligned data
>>model that correlates exactly with the user view of the data model as
>>particularly expressed by the UI.
>>Is this a (relatively) succinct statement of your view?

> I don't think so because I think your defs sound different from mine.

Thats why I restated in just 4 lines - on rereading them and my next para is it the nub of your opinion?

> By "correlates exactly with the user view" it sounds like you think I
> want the data from a single screen to be stored as a single entity or
> something like that. I would not look for the logical data model of UI
> screen to be identical to the logical data model of the database.
> Surely one screen might collect data that updates multiple entities.

I didn't (except for the need of brevity) aim to make it all or nothing, more that the alignment is very obvious in the MV situation and can be apparently less clear (to some) in the RM form.

I accept that if something is not obvious it always poses a risk in that an incorrect translation might arise. I personally however do not see it as significant enough to make me jettison the other "benefits" the RM confers. Given that this is a development activity if the business requirements (both UI and subsequent process) are adequately defined I would be confident of resolving any inconsistencies at either unit or system test stages.

> I also do not claim that the data model (abstraction of the language)
> of the user interface must be the same as the data model of the
> database. Clear that is not the case. My claim is that the data model
> of the user interface cannot be the RM (as it stands today)

We discussed and disagree that point before. Of course you could easily produce a spec that mandated an "ugly and unintuitive" RM solution, but I remain convinced it would still be RM compliant.


>>>There is some risk you can eliminate, but I'm not sure the risks, for
>>>example, of bad data or poor programmers (or good programmers sometimes
>>>producing poor code) can be eliminated with reasonable cost.  I do
>>>think that the lock-down vs. enough-rope-to-hang-themselves approaches
>>>each have different risks.
>>Definitely - I prefer the former because it increases transparency by
>>reducing the possibility of a clever type obfuscating the true situation
>>by insisting you "never mind the quality, just feel the length".  Yet
>>again a dailywtf demonstrates this sad trait is alive and kicking in the

> agreed
>>>>>>Getting the union is the trick.  Where I see the RM
>>>>>>playing a part is in assisting in retention of the union even as
>>>>>>evolution occurs.
>>>Is this intrinsic in the RM or part of the lock-down approach?  If you
>>>were to add in ordered lists as a viable type (which I think some SQL
>>>tools might do?), thereby ignoring the Information Principle and no
>>>longer using RM, would you lose the feature you want?
>>RM is to the extent ppl accept and use it is a "lock down" approach.  I
>>recall the latest SQL standard does have sets as a domain type - but I'm
>>not sure they are ordered.

> Yes, I did a little dance of joy when I learned that a while back.
> There is no consistency in vendors implementing this, however. I
> played with PostgreSQL at one point and liked their implementation of
> parent and child tables. IIRC, you could do select * and get all
> attributes from parent and child.

I must admit I didn't warm to their sets at all. Perhaps if I had a specific need and the implementation assisted brevity in the code I might be swayed. However my money is on it going the way of the BIT data type which IIRC is gone in the latest spec!

>> Regardless, as you imply, to adopt features
>>like those you tendered does weaken the outcome.  My response is not to
>>throw out the baby, but prohibit the use of these non compliant features
>>in my shop.  Anybody report who thinks differently would find it career
>>limiting first and sayonara soon thereafter!
>>For the record my pet hates at the moment (but not a comprehensive list
>>at all) are:
>>a) on delete set null

> good call

I just can't see what ppl would see in it to get it into the spec?

>>b) CLR

I think the Lazza's O and others have some similar facilities - Sybase opts for Java, but Bill is going for the whole sorry .Net kitchen sink!

> Are these both SQL Server only? I don't know what the downside of CLR
> is.

RISK - like you have with MV (only joking, well not really, but far worse). Every half wit who finds regular SQL too taxing will start building bits and pieces of smart alec code and then ram it into the database - that like all M$ stuff will fail from time to time. Argggh - all the mayhem you can handle and then more! As a foretaste I have already heard of ppl shelling out of sprocs to instantiate an object that invokes a DTS package to munge the very data they are interested in. So what if you lose a bit on the way.


>>This simply confirms what Canute discovered.  His goal may be correct
>>but his method not.  That doesn't mean it is unachievable as the Dutch
>>have showed.

> Unless you are talking about King Canute and the Dutch holding back the
> sea? That is the only thing that comes to mind, otherwise I'm clueless
> on the allusion.

Couldn't be any other conclusion could it? He didn't and they did!

>>>MV developers are inclined to prototype in the target environment and
>>>then migrate that into the solution.  I recall this being a no-no from
>>>at least the early 80's so I was surprised to find this happening in
>>>'89 when I landed as a manager in a PICK shop and even more surprised
>>>to find I was encouraging it in no time.  This flexibility carries on
>>>even once deployed.  Is it possible to get the features you are looking
>>>for along with this type of flexibility?
>>Yes - but it must be self imposed.

> Hmmm. It seems like the changes required if you were to add
> cardinality alone would make it hard to do iterative prototyping and
> take it all the way into production. I haven't worked with SQL Server,
> however, and the toolset might be good enough to make this feasible if
> managed well. Cheers! --dawn

Its not hard at all - all my changes are standard DDL which are scripted, rerunnable, cumulative and conditional. This means I can bring any past version of any relation up to date in a guaranteed to be repeatable way because the same code is executed in development, testing, acceptance and production. Of course this is not the M$ way - they would prolly capture the mouse clicks!!

Cheers, Frank. Received on Thu Feb 16 2006 - 06:39:18 CST

Original text of this message