On Aug 28, 12:42 pm, paul c <toledobythe... (AT) oohay (DOT) ac> wrote:
(snip)

Perhaps I'm missing something. But given EMP{ENO, ENAME, SAL}, and
business rules that allow a user to assign ENAME but forbid that user
from assigning SAL, it is impossible for that user to express:
UPDATE EMP SET ENAME = 'JOHN DOE' WHERE ENO = 'E123';
as a relational assignment which didn't violate the security business
rule.


I only brought up triggers as an analogy - an example of a tool which
attains ends that, IMO, can always be accomlished by preferable means
(e.g. better data modeling and better constraint support of the
DBMS). My point was to say that perhaps "updatable views" are similar
- perhaps they are being used to accomplish tasks which might be
better addressed in a manner that avoids the nonsensical notion of
"asserting conclusions".


Great question, cuts to the heart of the matter: It can't be updated
because it is a view. It returns an conclusion, and it is not (IMO)
valid to assert conclusions. In this case, the view returns the
*conclusion* that the current user of the database connection has been
authenticated as <ENO>. Perhaps CURRENT_USER is based on the join of
two base relvars

DBUSER {USERNAME} -- in many DBMS's, this is actually a global
variable, e.g. "USER" in Oracle, but for our purposes, we'll assume it
is a base relvar
EMP_USER {USERNAME, ENO} -- this would associated each username with a
specific employee who is allowed to log in as that database user.

So CURRENT_USER is defined as the view {ENO} JOIN (DBUSER, EMP_USER)

So if I logged in as USERNAME = 'U53', and 'U53' is the authenticated
user id of employee 'E143', a query of CURRENT_USER would return
'E143'. Now, if I suddenly wanted the database to conclude that I was
employee E144, I might execute the statement:

UPDATE CURRENT_USER SET ENO = E144;

Let's assume this works, and I get the message such as "1 row
updated". Furthermore, I query CURRENT_USER, and I get a response
'E144'. Now, what has been asserted behind the scenes?

1) The USERNAME with which I've logged in changed from 'U53' to
'U55' (the USERNAME assocated with 'E144') ?
2) The USERNAME stays 'U53', but 'E144' become the ENO associated with
'U53', and 'E143' is switched to the USERNAME 'U55'?

Either of these changes are ways that the DBMS *could* modify the
propositions of the database such that my desired conclusion will be
reached. But I don't need to dig into the relational theory to know
that, from the end-user perspective, neither is the *sole* correct
approach to this - if forced to change the CURRENT_USER without an
updatable view, different users might use either of these
tactics.


Consider another example - imagine you have a just a single table in
your model which deals with BOSS information:

BOSS {ENO, BOSS_ENO} "<ENO> works for <BOSS_ENO>"

Now, imagine an end-user says, "I want to be able to update the
database and store the proposition 'ENO=E12 either works for ENO=E43
or ENO=E44'." What is the appropriate response? Hopefully, you'd not
miss a beat in saying, "The data model does not support propositions
of that form", perhaps followed by an helpful, "Do we need to modify
the model such that it can accomodate statements like that?".
Basically, it's the same response as you'd give for "Can I enter an
employee's marital status?", given a data model with no notion of
marital status.

Now imagine a second end-user approaches you and says, "I have a query
which returns all employess who report to either E43 or E44. I'd like
to see Jane (ENO='E12') to appear in the query results, but I'm not
sure which of those two she works for. How can I update the database
so she appears in my query, without assigning her to one or the
other?" Does this end-user get a different answer? Of course not.
One simply can't assert propositions that don't fit predicates of the
underlying data model.

So if a third end-user says, "I created a view that shows me all
employees who report to either E43 or E44 and I want to insert tuple
{ENO='E12'} into this view", why is it that we suddenly have an
interesting problem in need of deep theoretical analysis? After all,
if nobody is talking about a "correct" way for the DBMS to handle a
statement like this:

INSERT INTO (SELECT ENO FROM BOSS WHERE BOSS_ENO IN ('E43','E44'))
VALUES ('E12').

And nobody is talking about a "correct" response to this:
INSERT INTO (SELECT ENO FROM BOSS WHERE BOSS_ENO = 'E43' UNION SELECT
ENO FROM BOSS WHERE ENO = 'E44')
VALUES ('E12').

Then why do we have endless conversations about "correctly" handling
this:

CREATE VIEW A_NAME_FOR_A_QUERY AS SELECT ENO FROM BOSS WHERE BOSS_ENO
= 'E43' UNION SELECT ENO FROM BOSS WHERE ENO = 'E44';

INSERT INTO A_NAME_FOR_A_QUERY VALUES ('E12');

?

It's kind of an aside to this conversation, but my underlying idea
here is that if a business rule differentiates between two parts of a
predicate, e.g. "Some people can update this column, but not that
column", then the data model should treat them as two separate
predicates. I believe it's the same basic logic used by Darwen & Date
when they argue for using 6th normal form when dealing with parts of
predicates which may have different date ranges (if i recall
correctly, they'd have you decompose EMP{ENO,ENAME, SAL} into EMP_NAME
{ENO, INTERVAL, ENAME} and EMP_SAL {ENO, INTERVAL, SAL} if names and
salaries might have different temporal inverval values).

Anyway, as I said, it's kind of an offshoot from the conversation
about updatable views; perhaps we can pick it up in a different thread.

paul c wrote:

I believe it was Bill Murray who long ago established that you don't
want POOP in the POOL.

Kevin Kirkpatrick wrote:
....
So A UNION B is a conclusion when assigned to a view, but not a
conclusion when assigned to a base. Where does this idea come from and
what is it good for, apart from appearing to be a spurious reason to say
that views aren't updateable? Even if I were to accept that views
aren't updateable, I'd ask why is CURRENT_USER necessarily a view?

(Personally, I would prefer an engine that allows a user to log himself
off by means of a simple delete rather than the usual arcane engine
plumbing that introduces various environmental commands. That way, the
environment is forced to react to db changes rather than the other way
around. The engine becomes much simpler if this approach is followed
and this is important if there's ever to be any progess in the aspects
that today's engines slough off.)
....
Well, if you have two predicates and one isn't logically implied by the
other, it's pretty much inescapable that you will need two relations
even though the null advocates think not. There is much in a user's
predicate that the RM doesn't record, all it records are the variable
names and constraints. These and its chosen logical rules are all an
execution engine has to go by. I get the feeling that when many
people talk about predicates or constraints, they don't bother to first
try to write down an algebraic equivalent. If they don't do that,
really they are just throwing words around.

On Aug 28, 4:32*pm, paul c <toledobythe... (AT) oohay (DOT) ac> wrote:
My point, phrased another way, is: given base relvars A, B, and C with
identical headings, this does not make sense:

(A UNION B) := (B UNION C)

in the exact same way that this does not make sense:

int x, y;
x+y := 3;


Sure, in either case, the assignments *could* be carried out - in the
former, A could just be assigned a value of all tuples in C... in the
latter, x could be set to 1 and y could be set to 2. But nobody
spends any amount of time writing a compiler that attempts to do so,
for obvious reasons.

Even in cases where there are is no disambiguation, e.g.:

int x;
x+3 := 4;

we don't write compilers to evaluate that assignment by setting x:=1.
For the same reason, just because sometimes evaluations like

(A UNION B) := {}

only have one "solution", is no reason to allow them into the
programming language so some super-genius compiler can assign A := {};
B:= {} whenever they are 1-solution solvable.



Anyway, perhaps the disconnect can be addressed better if you simply
respond directly to this:


Does this make sense?
INSERT INTO (SELECT ENO FROM BOSS WHERE BOSS_ENO = 'E43' UNION SELECT
ENO FROM BOSS WHERE ENO = 'E44') VALUES ('E12')


If not, then how can you say that this makes sense:

CREATE VIEW A_NAME_FOR_A_QUERY
AS
SELECT ENO FROM BOSS WHERE BOSS_ENO = 'E43'
UNION
SELECT ENO FROM BOSS WHERE ENO = 'E44';

INSERT INTO A_NAME_FOR_A_QUERY VALUES ('E12');

?

Kevin Kirkpatrick wrote:

But computers do solve systems of equations:

x+y = 3
x-y = 1

Kevin Kirkpatrick wrote:
....
Even though I like JOIN as an easier problem to see through, these are
good examples because they cut away all the sql fluff. First, "does it
make sense?" is a question people often ask without context. Let me
give it a context: "does it make sense in a chosen logic?", which also
means, I think equivalently, does it make the logic inconsistent or lead
to contradictions?

I've heard lots of people say the rm is based on logic because logic is
"good" which is also nebulous. We must ask what is the purpose of
basing an implementation on a formal logic. I would say there are two
reasons which are practical ones, not mystical ones: 1) so we can ask
does a result make sense in the sense that it obeys or doesn't violate
the logic (usually much easier than referencing code let alone the sql
spec', and 2) so that we can use logical optimization techniques in the
construction of an implementation.

I would ask anybody who thinks those assignments don't make sense to
show an inconsistency or contradiction in assigning B UNION C to A and B
UNION C to B or 3 to x and 0 to y. If these assignments were equations,
the left-hand-side would equal the right-hand-side and in the relational
algebra example, both sides would be interpreted as 'true'. In both
cases, the result obeys the assignment but perhaps there are some
unwritten constraints that make the results contrary to the system
users' expectations. You could say that these results "don't make
sense" because it is a system requirement that all recording forms, such
as tables or integer variables must be able to record values for all
possible truth combinations. One might say that neither a single
relation nor the programming variables allow that, in which case one
should admit the system requirement that all possible true values must
be representable. Or you might say that it's because of a cultural
attachment to certain programming styles. But if those are their
reasons, people should say so, and not hint that the result doesn't make
sense according to the logic. Whether I would want an arithmetic system
to treat integers this way is one question but as for the relations,
show me the logical inconsistency or contradiction, eg., if A AND B is
true, so are A AND NOT B and NOT A AND B, what's the problem, really?

If one feels that these results are just somehow too distasteful, one
could devise a language that stored all integers in infinite arrays or
one could invent a version of the RM where results could be multiple
relations, both of which have big practical problems and I don't see
the practical need. I don't see any inconsistency in assigning non-loss
projections of a relation to the operands of join or union because I
don't see any violation of predicate logic by doing so. Maybe the
result should be more accurately called relational logic. (Inserting to
projection is a different problem because it involves predicates that
aren't mutually implied.)

As far as updating in the RM is concerned, it is a matter of excising
all unstated requirements and possibilities that the chosen algebra
doesn't allow, aka mysticism. The essential fact here is that every
relation logically implies the truth of its non-loss projections and
their conjunction. This should be patent to anybody who's played with
the algebra, so I think it is not mysticism. Doesn't matter whether
it's base or view. I started this thread to examine not the possibility
of updating but the implications of updating to both, should have known
better than to expect no objections, but some of those are still useful
to me for helping to clarify my thoughts.

It is remarkable that there are people who advocate nulls as a value
sometimes and not a value other times, such as many sql implementers, at
the same time as they suggest there is some logical problem with certain
view updates. I believe even Codd fell into this trap. I reconcile
this simply because it shows even the most learned people can't be right
all the time.

paul c wrote:
.... Whether I would want an arithmetic system
Oops, should have said (A <AND> B) OR (A <AND> <NOT>(B)) OR (<NOT>(A)
AND B) is true. Question remains, what's the (logical) problem?

"paul c" <toledobythesea (AT) oohay (DOT) ac> wrote

I don't need to look. <AND> is a relational operator; AND is a logical
operator. They are related only because the predicate of the result of
<AND> is the conjunction of the predicates of its operands.

Mr. Scott wrote:
....
Actually, <AND> is not the logical conjunction of predicates, but the
join or 'conjoin' (of relations) as D&D carefully put it, I think to
make clear that it is not conjunction of logical predicates. I think
they call it a counterpart. This recognizes that the relation form
dispenses with some of the details in FOL notation. I suspect that
people who don't master the distinction as well as the indirection
needed to interpret results will never be able to see through certain RM
problems.

paul c wrote:
Oops again: should have said (A <AND> B) <OR> (A <AND> <NOT>(B)) <OR>
(<NOT>(A)
<AND> B) is true. Question remains, what's the (logical) problem?