Re: organizing tables?

"Bob Badour" <bbadour_at_golden.net> wrote in message news:wU%Qa.851$De1.142752828_at_mantis.golden.net... [snipalot]
> > And a database subset is any subset of database relvars and tuples that is
> > itself a valid database.
> > E.g.
> > 1) all constraints only reference relvars within the database subset
>
> This is impossible in practice without destroying integrity.

Wrong (see later)

> The dbms must
> enforce all constraints

Agreed

> even those referencing unseen relations.

By the information principle, (for any given user) nothing can be hidden unless it is completely hidden. Either 'unseen relations' have zero impact on a database (or 'database view') or the relations are not unseen at all...

> > 2) everything that is not constrained can be updated (i.e. no hidden
> > constraints)
>
> This is impossible in practice without destroying either integrity or
> security.

Wrong.

If you do not wish to include all the referneced relvars in the user's view of the database, then you must not allow users the ability to update any parts of the database that reference the hidden relvars.

I absolutely reject the idea that a user cannot know a-priori that a given update will 'work' or not.

Hidden constraints remove the possibility of giving users such confidence.

So, if the CUSTOMER table that a user can see in his database view has a FK to a OPEN_ORDER table that the user cannot see in his database view, then in general the user must have a constraint on his database view that states he cannot delete *any* customers.
If OPEN_ORDER has a constraint such that say all expired customers cannot have open orders, then the user would be allowed to delete expired customers, but would then not have the ability expire customers.

Further, if another user can see both CUSTOMER and OPEN_ORDER in her database view, and so have the ability to close orders and expire and delete all custotomers, the the first user would see that altough he cannot delete customers, there exist other users that possibly can. He might not know if the other user can actually delete any user (because he cannot see the details of the OPEN_ORDER table, which might have say confirmed orders that cannot themselves be deleted), but he does know that in principle any customer might suddenly disappear.

> > 3) everything that is constrained cannot change value (i.e. no hidden
> > updates).
>
> I see no reason for this arbitrary restriction and ample reason for trashing
> it.

Arbitrary?!

Maybe I did not state the rule clearly enough. If the database that a user sees says that *no-one* can update a certain value in that database, then that user can have 100% confidence that the value will indeed never be updated (short of the constraint itself being dropped).

What on earth is the point of constraints if they don't "do what they say on the tin"?

Now, the constraint might say, "you can't update the value, but someone else has that authority". Then they would rightly not have 100 % confidence that the value would stay the same.

>For instance, I recall a restaurant who caught one of its managers
> stealing. The manager was reducing the amounts recorded for meals paid by
> cash and pocketing the difference from the till. The manager even deleted
> the audit trail entries; however, the system maintained another audit trail
> the manager knew nothing about and had no access to. While everything always
> balanced, the general manager eventually noticed the suspicious activity.

That is not a counter example. The manager had a view of the database that included an audit trail that he could delete. His view did not include the audit trail trail that was in a super-set of his database view. The superset has the constraint that deletions from the audit trail cause insertions in the audit trail trail. This constraint does not need to be made visible to the manager because of the mapping rule between his database view and the super view. That mapping rule is basically a set of triggered actions on audit trail.

All this is tied into my 'view updating' mechanism that rather than actually updating views, you just get to update some database variable which may actually be a proper subset of a containing database variable.

It is also tied in with the concept of a *user*. Part of the model of users, is that a user does not have complete knowledge of what another user can see. A user John, can know in principle the complete set of valid database updates that he can perform. He can know the complete set of updates that another user Jane can try to perform (on the database tables that John can see), but cannot know which of those updates would be valid ones for Jane (unless he knows that she is seeing the exactly same database subset (as he is).

It is also tied in with the proper place for triggered actions in the relational model. I.e. they as OK only so long as the user cannot see their consequences (or, alternatively, user allow their own statements to be modified to include visible triggered actions)

Regards
Paul Vernon
Business Intelligence, IBM Global Services Received on Wed Jul 16 2003 - 17:08:35 CEST