Re: theory and practice: ying and yang

"Paul" <paul_at_test.com> wrote in message news:429bad41$0$2349$ed2619ec_at_ptn-nntp-reader02.plus.net...
> mountain man wrote:
>>>Do you have a concrete example of a simple stored procedure, and how
>>>you'd want this to be handled by your ideal DBMS?
>>
>> My claim is that the entire application software environment
>> can be reduced to a series of stored procedures. Here is
>> the classic example, for the Northwind Trading Company:
>> http://www.mountainman.com.au/software/southwind/
>>
>> In this example, we have a database (southwind) containing
>> a series of stored procedures which act as the application
>> software components for the Northwind data.
>>
>> In this arrangement both the data and *all* programs (ie: apps)
>> can be confined to the environment of the database system.
>
> OK, but the stored procedure *are* stored within the DBMS, so doesn't
> this contradict your claim that the relational model can't handle the
> stored procedures?

The relational model speaks about data and its schema but is mute about the application software layer, which has been sitting in the s/w protocol stack above the DBMS software, since the year dot.

My observation is that, since the emergence of addressable stored procedures within the (R)DBMS layer, systems are using this approach more and more.

Essentially you can generalise this observation, with reference to the protocol stack and say that industry is following an increasing trend in that the (application) code is being migrated from the (client and/or server) application software layer, and into the (R)DBMS layer.

My thesis is that if you take this migration to its logical conclusion there may be achievable an optimal stage in which *all* application software in internal to the DBMS in the form of stored procedures.

That is precisely what this tool does. It enables a service level portal to the RDBMS for the user which is based solely on the execution of stored procedures, and the ability to handle the consequent sets of data being returned to the user.

> Isn't what you've done standard practice in designing enterprise
> database reporting systems? i.e. to have a stored procedure (or in an
> ideal world, a view) for each report?

Yes. However, the tool enables dynamic drill-down from summary to intermediate summary to detail to log levels on the simple basis that one stored procedure can be called from another. Therefore there are a series of stored procedures for each report, dependent upon the analytical solution being provided.

> Are you saying that this method is preferrable to the "fat client" or
> "middleware" method, where you have business logic held outside the
> database in things like Cognos, Business Objects, or Crystal Reports? I
> thought this was pretty much the same as most other people think.

Absolutely preferable, because it entirely OBVIATES the client. End of story. There is no client required, and therefore no middleware required, at all. Minimal number of moving parts in that all the code and data are being defined once and for all in ONE SOFTWARE LAYER.

This is financially preferable because the change management of multiple software layers is an ongoing huge expense, and really all that is being done is the coordination of schema-related tasks between the database and anything external to it (ie: the application layer).

For an in-depth discussion of such tasks and change management have a look through these tow brief articles:

What is change management.
http://www.mountainman.com.au/software/history/it5.html

What is the generic change management development cycle? http://www.mountainman.com.au/software/history/it6.html

> Is the point you're making that the procedural language used in stored
> proecedures isn't part of the relational model?

Not in the least, I believe that SQL is computationally complete and there is in fact no problem -- however difficult -- that cannot be overcome using SQL and other native utilities that are available to the current industry RDBMS machinery.

The RM as proposed by Codd required such a language by which the data could be manipulated, and in subsequent years after Codd published his theory, the SQL standard emerged.

> In which case all the
> stored procedures you have could be replaced with views, which are part
> of the relational model. This approach would only break down for the
> cases I mentioned before i.e. when you have several data manipulation
> statements in a single transaction. But I don't see any of these cases
> in your example application.

The dynamic drill-down information reporting obtained by chaining multiple series of stored procedures together cannot be replaced by a view.

You dont see any views in my example because I have never yet found an instance where a view is necessarily a better implementation that by performing the task without use of a view.

Theoretical limitations about SQL and Views by Date et al are not limitations at all from my perspective, because views are not essential. End of story. ;-)

-- 
Pete Brown
IT Managers & Engineers
Falls Creek
Australia
www.mountainman.com.au