Re: organizing tables?
Date: Wed, 16 Jul 2003 14:07:33 -0400
Message-ID: <3shRa.895$uN3.149846029_at_mantis.golden.net>
"Paul Vernon" <paul.vernon_at_ukk.ibmm.comm> wrote in message
news:bf3pud$1ue2$1_at_gazette.almaden.ibm.com...
> "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.
I don't think I agree with your interpretation of the information principle. I don't recall seeing the "for any given user" part in any other statement of the principle.
Ideally, the dbms would express every constraint on a relvar as a visible predicate rephrasing each constraint as necessary to avoid references to hidden items; however, the dbms would have to solve provably hard or even unsolvable problems to do so. Compromises to avoid provably hard problems are pragmatic applications of theory.
(I miss Jan. I wonder whatever happened to him. I am sure he could rhyme a half-dozen relevant references off the top of his head.)
> 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 disagree with "must not." This is a design issue. If one does not allow users to update relvars that might have "hidden" constraints, the user will never encounter any surprising constraints or surprising behaviour. However, the restriction may prevent users from many desirable or even necessary interactions. Under the right circumstances, I would happily give users a small chance to surprise themselves.
> I absolutely reject the idea that a user cannot know a-priori that a given
> update will 'work' or not.
Until you present a solution for the computationally hard problems you have given yourself, I suggest you refrain from imposing them on others.
> Hidden constraints remove the possibility of giving users such confidence.
Disallowing updates to any relvar that might have a chance of imposing a hidden constraint removes the possibility of giving users adequate logical independence.
> 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.
I disagree. This is an issue for a designer to decide in the field and not an essential requirement to impose on the designer a priori.
> 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.
Once again, this is an issue for a designer to decide in the field. The user may be required to expire customers as part of his or her job function, and the flow of information when correctly processed may imply the user never attempts to expire a customer with an open order.
Exceptional circumstances might cause disruptions to the flow of information resulting in the user attempting to expire a customer with an open order, but this is an exceptional case for which I am willing to accept exceptional behaviour like a surprising update rejection that generates a call to support.
> 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?!
Yes, "no hidden updates" is arbitrary just as "no hidden constraints" is arbitrary. One can understand the properties and the consequences of hidden updates and hidden constraints and still choose to have them.
> 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).
A constant is a constant. A variable is a variable. I don't see how those observations relate to the arbitrary restrictions you described earlier.
> What on earth is the point of constraints if they don't "do what they say
on
> the tin"?
I never said they shouldn't do what they say on the tin. I said constraints should function correctly even if they say nothing 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.
I still do not see what this has to say about the arbitrary restrictions you described earlier.
> >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.
Presumably, the hidden audit trail has many constraints: domain constraints, one or more candidate keys at a minimum and very likely transition constraints referring to the relvars the manager was changing.
Given that the hidden audit trail was hidden, constrained and updated, how does it fail to provide a counter example to the rules "no hidden updates" and "no hidden constraints"?
> 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.
Whether the mapping rule is a set of triggered actions or a view definition seems irrelevant. The hidden audit trail has transition constraints referencing the relvars the manager directly updated to cause the hidden updates. The arbitrary restrictions you want to impose basically say that the designer may not declare the transition constraints to the dbms without either revealing the hidden audit trail or making the visible relvars read-only.
> 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.
So far, your description of the mechanism makes it sound overly confining.
> 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 might not even know that another user exists.
> A user John, can know in principle the complete set of valid database
updates
> that he can perform.
But not in all cases for all users.
> 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)
What the user can and cannot see with regard to triggered actions is a matter of design. A designer may confront a situation where no design completely avoids undesirable consequences, and this designer must then make compromises and tradeoffs.
Even a simple "on delete cascade" might cascade either to hidden or to visible relvars. Received on Wed Jul 16 2003 - 20:07:33 CEST