Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: Pizza Example

Re: Pizza Example

From: Anthony W. Youngman <>
Date: Wed, 7 Apr 2004 14:25:11 +0100
Message-ID: <>

In message <>, Tony <> writes
>Jerry Gitomer <> wrote in message
>> On Mon, 05 Apr 2004 11:19:56 -0500, Dawn M. Wolthuis wrote:
>> [snip]
>> > What I have seen with RDBMS's is that the size constraint is placed on
>> > everything while more important constraints
>> > related to the allowable values are not as often applied, especially
>> > when it is a "yellow flag" type of data value for an attribute rather
>> > than obviously not permitted. I'll put this in a separate thread at some
>> > point (but have several other questions prior to that one).
>> >
>> I prefer that the "more important constraints" be handled by the
>> application programs rather than incorporated into the RDBMS. This is
>> because the application programs are specified and written by developers
>> and analysts who are working with the users and have first hand knowledge
>> of the business rules while the database is cared for by the DBAs who are
>> concerned with the efficiency and effectiveness of the database in meeting
>> the needs of many different applications.
>I was agreeing with your post right up to this bit. Yes, the database
>is cared for by DBAs, but the constraints are specified by analysts
>and written by database designers (who may or may not also be the
>DBAs). The very fact that the database meets the needs of many
>applications is the reason for handling "more important constraints"
>(especially) in the DBMS. Otherwise the DBMS is just a glorified file
>system, not a Database Management System.


This is where I think both the Relational, and Dawn's/my Pick model, both fall down.

All RDBMSs I know use the data metadata to PREscribe how data is stored in the data store. Pick/MV uses the metadata to DEscribe the data in the data store. Both are flawed, in that Pick does not enforce integrity at the DBMS level, while Relational enforces arbitrary constraints that do not necessarily have any connection with reality.

What we need, is a layered model of the DBMS. The data store should not (as in Pick) give a damn about the data it is given. It just stores it. The integrity layer should be separate - it sits *above* the data store and makes sure that invalid data cannot be passed through. Pick pushes this up into the application layer, where it can easily be inappropriately bypassed. Relational pushes this down into the data store layer, where the DBMS rejects valid data because it doesn't know how to store it.

Actually, SQL's trigger layer is probably a very good approximation to this. Take the Pick "metadata DEscribes the data" approach and then have a trigger that says "for fields x, y, and z, actually it PREscribes the data".

That way, I can define NAME as 30 characters long (DEscribe) but I can define POSTCODE as 2A?N2N2A (PREscribe). (UK postcodes are two letters, two or three numbers, two letters - dead easy to understand and/or guess if you know the system).

So if an app tries to store a name that's 40 characters long the datastore will accept it (maybe flag a warning), but a 9-character postcode, or one with three consecutive letters, will be rejected with an "invalid data" message.
>It is true that current SQL products don't allow you to enforce all
>constraints declaratively, and that's a great pity. In an ideal RDBMS
>the user should be able (theoretically) to fire DML statements
>directly at the database and be sure of violating no integrity or
>security rules; the application is then just a way of making that task
>(much) easier. With current SQL products the best you can do is
>protect your tables behind stored procedures that encapsulate the
>rules that can't be done declaratively; still, that keeps the
>user-interfacing application(s) free of responsibility for enforcing
>business rules.

Yup. Although wasn't SQL originally intended to be a user-level DML? And ENGLISH (or ACCESS) is Pick's user-level DML - a "read-only" DML because when they tried to add update capabilities the complexity spiralled out of control.


Anthony W. Youngman - wol at thewolery dot demon dot co dot uk
HEX wondered how much he should tell the Wizards. He felt it would not be a
good idea to burden them with too much input. Hex always thought of his reports
as Lies-to-People.
The Science of Discworld : (c) Terry Pratchett 1999
Received on Wed Apr 07 2004 - 08:25:11 CDT

Original text of this message