TTM without tuples

#81 · July 19, 2021, 6:37 pm

Quote from dandl on July 19, 2021, 1:35 pm

... it is the algebra that determines which strings of symbols are valid WFFs, but something else is needed to determine whether a particular WFF is a view. I would be interested in your thoughts on what that something might be.

In Chris Date's textbooks, the chapter on relational algebra always included a section entitled "What is the Algebra For ?" (or similar). His answer was "to write relational expressions" that could be used for a variety of purposes; e.g. evaluate to a value for retrieval, define integrity constraints. This means that there must another part of the relational language which expresses the purposes; and it must be possible to compose the 2 parts to form an executable statement.

So the answer to your question is : there must something in the 'purposes part' which specifies that a given relational expression defines a view. Your 'purposes part' can be whatever 'style' you like.

(As an illustration, in RAQUEL I have generalised assignments, and use them to express purposes, one kind of assignment per purpose. But I just happen to like algebraic languages, so I delibarately designed it that way. That includes making Assignments composable, so they too can form expressions. Other people will have other preferences. That's fine. Makes life more interesting).

#82 · July 19, 2021, 10:38 pm

Quote from p c on July 19, 2021, 12:47 pm

Quote from David Livingstone on July 15, 2021, 10:03 pm

Quote from dandl on July 14, 2021, 12:38 am

That wasn't my question. I want to know:

Would a formal logical model include views?

Do you have such a model you can show us?

How would that resolve the problem of view updating?

IMO views are a kind of query expession, expressed as a WFF, not part of the underlying model. View updating is relational assignment, in which WFFs are evaluated and assigned to one or more relvars. The task of constructing those WFFs falls to the user/developer and cannot in general be derived mechanically from the logical model. Please confirm or refute.

-----------

"...IMO views are a kind of query expession, expressed as a WFF, not part of the underlying model. …"

According to Codd his relational data model entails the validity of certain WFFs. Two of the most elementary relational queries are ( R ) and ( NOT R ). They are also WFFs.

No. (NOT R) is not well-formed. Codd 1972 requires that NOT appear only as an operand of JOIN -- then (R2 AND NOT R) is a wff, equiv (R2 MINUS R)

If the quote is correct it must mean that the predicate "not part of" entails the predicate "does not entail".

So if all that is the case, what does entail the eligibility of those WFFs? Implementation? Or something other than a relational data model?

#83 · July 19, 2021, 10:54 pm

Quote from AntC on July 19, 2021, 10:54 pm

Quote from Dave Voorhis on July 18, 2021, 9:07 pm

Quote from David Livingstone on July 18, 2021, 4:03 pm

Another 2 days have elapsed. Has at least a broad consensus on 'view updating' arrived ?

I wouldn't presume to comment on a 'DBMS sub-language', if only because I would've thought it would depend somewhat on the character of the language underlying it.

INSTEAD OF triggers certainly seem to provide a mechanism to achieve what I had in mind. Again I wouldn't presume to comment on INSTEAD OF triggers (or just triggers) more generally.
Would you say, Dave, that "Having many applications sharing one database is pervasive in the enterprise world" (quote) is due, at least to a reasonable degree, on the availability of INSTEAD OF triggers ?

No. I can count on one hand the number of times I've used INSTEAD OF (or some equivalent) to do anything remotely like update-through-views, and I can count on the other hand the number of times I've seen someone else use it.

Hmm I think you might be looking in the wrong places. I see plenty of application code in which there's a user-facing layer that generates an update 'request' as a PossRep update, passed to a DB interface layer that in effect implements INSTEAD OF by making multiple updates to the PhysRep.

This is worse than using INSTEAD OF, because now the 'logic' (I use the term loosely) is buried in code, not explicit in the SQL.

That's quite different from triggers in general, which in some environments are used extensively for various purposes but nothing like update-through-views.

Hmm again. As you said up-thread, lots of applications get stitched together as part of systems evolution. I see lots of AFTER triggers whose purpose is to pass on the update from the source system to downstream (say Order-Processing to Warehousing/fulfillment and to accounting). And plenty of opportunities for writing AFTER processing for INSERT but forgetting for UPDATE, making an auditors' nightmare.

I doubt any of the uses of INSTEAD OF that I've used (or seen used) really warranted it, and I imagine that had the code been subjected to a group code review it would have been shot down. (Quite rightfully, I might add.)

Having many applications share a database is simply the nature of many enterprises, which inevitably generate numerous applications that all do various somethings with core data.

I ask because my impression was that in the (more distant) past there were a lot of 'data silos' where that hadn't happened. Perhaps 'INSTEAD OF triggers' have solved the problem ?

Often as not, the data is in silos and shuttled from silo to silo and system to system (and thus database to database) by various means. But each silo's database may have dozens -- maybe many dozens -- of applications using it.

There's nothing that would conceptually prevent storing Customers, or Invoices, or Staff, or Policies, or Sales, or Estimates (and/or whatever core entity data the organisation cares about) all in one enterprise database (setting aside technical issues) and sharing that with all the applications. But that doesn't happen for reasons that are rarely technical, conceptual, or even (as one might suspect) political. It's just what organically emerges when big organisations have a lot of people and a lot of projects all trying to get things done at once (i.e., typical big organisation mayhem) plus the external influence of an IT industry that goes through regular periodic phases of centralisation (or at least technical cohesion) being encouraged (avoid silos!) or discouraged (use microservices!)

Nearly all the big ERPs started life as separate modules/even separate packages. Despite several decades of evolution and periodic (alleged) complete rewrites, still their data models are in silos with data repeated in Accounting that's also held in Order Processing or Manufacturing. They make a virtue out of Accounting records being frozen the instant they're committed to disk: any amendment to Orders gets reflected as an extra transaction in Accounting. Disk is cheap, I suppose. Certainly cheaper than the development costs of building a properly normalised model.

Quote from Dave Voorhis on July 18, 2021, 9:07 pm

Quote from David Livingstone on July 18, 2021, 4:03 pm

Another 2 days have elapsed. Has at least a broad consensus on 'view updating' arrived ?

I wouldn't presume to comment on a 'DBMS sub-language', if only because I would've thought it would depend somewhat on the character of the language underlying it.

INSTEAD OF triggers certainly seem to provide a mechanism to achieve what I had in mind. Again I wouldn't presume to comment on INSTEAD OF triggers (or just triggers) more generally.
Would you say, Dave, that "Having many applications sharing one database is pervasive in the enterprise world" (quote) is due, at least to a reasonable degree, on the availability of INSTEAD OF triggers ?

No. I can count on one hand the number of times I've used INSTEAD OF (or some equivalent) to do anything remotely like update-through-views, and I can count on the other hand the number of times I've seen someone else use it.

Hmm I think you might be looking in the wrong places. I see plenty of application code in which there's a user-facing layer that generates an update 'request' as a PossRep update, passed to a DB interface layer that in effect implements INSTEAD OF by making multiple updates to the PhysRep.

This is worse than using INSTEAD OF, because now the 'logic' (I use the term loosely) is buried in code, not explicit in the SQL.

That's quite different from triggers in general, which in some environments are used extensively for various purposes but nothing like update-through-views.

Hmm again. As you said up-thread, lots of applications get stitched together as part of systems evolution. I see lots of AFTER triggers whose purpose is to pass on the update from the source system to downstream (say Order-Processing to Warehousing/fulfillment and to accounting). And plenty of opportunities for writing AFTER processing for INSERT but forgetting for UPDATE, making an auditors' nightmare.

I doubt any of the uses of INSTEAD OF that I've used (or seen used) really warranted it, and I imagine that had the code been subjected to a group code review it would have been shot down. (Quite rightfully, I might add.)

Having many applications share a database is simply the nature of many enterprises, which inevitably generate numerous applications that all do various somethings with core data.

I ask because my impression was that in the (more distant) past there were a lot of 'data silos' where that hadn't happened. Perhaps 'INSTEAD OF triggers' have solved the problem ?

Often as not, the data is in silos and shuttled from silo to silo and system to system (and thus database to database) by various means. But each silo's database may have dozens -- maybe many dozens -- of applications using it.

There's nothing that would conceptually prevent storing Customers, or Invoices, or Staff, or Policies, or Sales, or Estimates (and/or whatever core entity data the organisation cares about) all in one enterprise database (setting aside technical issues) and sharing that with all the applications. But that doesn't happen for reasons that are rarely technical, conceptual, or even (as one might suspect) political. It's just what organically emerges when big organisations have a lot of people and a lot of projects all trying to get things done at once (i.e., typical big organisation mayhem) plus the external influence of an IT industry that goes through regular periodic phases of centralisation (or at least technical cohesion) being encouraged (avoid silos!) or discouraged (use microservices!)

Nearly all the big ERPs started life as separate modules/even separate packages. Despite several decades of evolution and periodic (alleged) complete rewrites, still their data models are in silos with data repeated in Accounting that's also held in Order Processing or Manufacturing. They make a virtue out of Accounting records being frozen the instant they're committed to disk: any amendment to Orders gets reflected as an extra transaction in Accounting. Disk is cheap, I suppose. Certainly cheaper than the development costs of building a properly normalised model.

#84 · July 20, 2021, 12:58 am

Quote from David Livingstone on July 19, 2021, 6:14 pm

Quote from p c on July 19, 2021, 1:23 pm

In other words the model depends on the language implementation, so implementation determines the model.

What I had in mind was different language 'styles', which TTM explicitly permits. See https://www.dcs.warwick.ac.uk/~hugh/TTM/ (under Projects) for details. The creators of TTM explicitly did not want to specify that TTM must be implemented as an imperative language / functional language / OO language, ... (add your own preferance in here) ... .

I think of the different language 'styles' representing the same abstract relational model in different ways, with appropriate concrete syntax, according to the creator's preferances. In practice, they may not all implement absolutely the same relational model - we're all human - but I suspect there is greater variation in how much of the relational model is currently implemented.

We debated this at length some time ago. Turns out the form in which TTM is expressed is quite friendly to an imperative language with value types (like TD) and less so to existing FP and OO languages. Most implementors choose a FP-like path (including Andl) and produce a language no-one uses. The only viable path for TTM (if there still is one) is to modify it to be friendlier to widely used OO languages (Java/C#). I have put forward a proposal to do that.

Andl - A New Database Language - andl.org

#85 · July 20, 2021, 1:00 am

Quote from AntC on July 19, 2021, 10:38 pm

Quote from p c on July 19, 2021, 12:47 pm

Quote from David Livingstone on July 15, 2021, 10:03 pm

Quote from dandl on July 14, 2021, 12:38 am

That wasn't my question. I want to know:

Would a formal logical model include views?

Do you have such a model you can show us?

How would that resolve the problem of view updating?

IMO views are a kind of query expession, expressed as a WFF, not part of the underlying model. View updating is relational assignment, in which WFFs are evaluated and assigned to one or more relvars. The task of constructing those WFFs falls to the user/developer and cannot in general be derived mechanically from the logical model. Please confirm or refute.

-----------

"...IMO views are a kind of query expession, expressed as a WFF, not part of the underlying model. …"

According to Codd his relational data model entails the validity of certain WFFs. Two of the most elementary relational queries are ( R ) and ( NOT R ). They are also WFFs.

No. (NOT R) is not well-formed. Codd 1972 requires that NOT appear only as an operand of JOIN -- then (R2 AND NOT R) is a wff, equiv (R2 MINUS R)

Beat me to it!

If the quote is correct it must mean that the predicate "not part of" entails the predicate "does not entail".

So if all that is the case, what does entail the eligibility of those WFFs? Implementation? Or something other than a relational data model?

Andl - A New Database Language - andl.org

#86 · July 20, 2021, 1:02 am

Quote from David Livingstone on July 19, 2021, 6:37 pm

Quote from dandl on July 19, 2021, 1:35 pm

... it is the algebra that determines which strings of symbols are valid WFFs, but something else is needed to determine whether a particular WFF is a view. I would be interested in your thoughts on what that something might be.

In Chris Date's textbooks, the chapter on relational algebra always included a section entitled "What is the Algebra For ?" (or similar). His answer was "to write relational expressions" that could be used for a variety of purposes; e.g. evaluate to a value for retrieval, define integrity constraints. This means that there must another part of the relational language which expresses the purposes; and it must be possible to compose the 2 parts to form an executable statement.

So the answer to your question is : there must something in the 'purposes part' which specifies that a given relational expression defines a view. Your 'purposes part' can be whatever 'style' you like.

(As an illustration, in RAQUEL I have generalised assignments, and use them to express purposes, one kind of assignment per purpose. But I just happen to like algebraic languages, so I delibarately designed it that way. That includes making Assignments composable, so they too can form expressions. Other people will have other preferences. That's fine. Makes life more interesting).

Perfectly reasonable. Which emphasises that views 'live' in the DML/host language, not in the RM or the RA/DQL.

Andl - A New Database Language - andl.org

#87 · July 20, 2021, 10:55 am

Quote from Dave Voorhis on July 20, 2021, 10:55 am

Quote from AntC on July 19, 2021, 10:54 pm

Quote from Dave Voorhis on July 18, 2021, 9:07 pm

Quote from David Livingstone on July 18, 2021, 4:03 pm

Another 2 days have elapsed. Has at least a broad consensus on 'view updating' arrived ?

I wouldn't presume to comment on a 'DBMS sub-language', if only because I would've thought it would depend somewhat on the character of the language underlying it.

INSTEAD OF triggers certainly seem to provide a mechanism to achieve what I had in mind. Again I wouldn't presume to comment on INSTEAD OF triggers (or just triggers) more generally.
Would you say, Dave, that "Having many applications sharing one database is pervasive in the enterprise world" (quote) is due, at least to a reasonable degree, on the availability of INSTEAD OF triggers ?

No. I can count on one hand the number of times I've used INSTEAD OF (or some equivalent) to do anything remotely like update-through-views, and I can count on the other hand the number of times I've seen someone else use it.

Hmm I think you might be looking in the wrong places. I see plenty of application code in which there's a user-facing layer that generates an update 'request' as a PossRep update, passed to a DB interface layer that in effect implements INSTEAD OF by making multiple updates to the PhysRep.

This is worse than using INSTEAD OF, because now the 'logic' (I use the term loosely) is buried in code, not explicit in the SQL.

That's quite different from triggers in general, which in some environments are used extensively for various purposes but nothing like update-through-views.

Hmm again. As you said up-thread, lots of applications get stitched together as part of systems evolution. I see lots of AFTER triggers whose purpose is to pass on the update from the source system to downstream (say Order-Processing to Warehousing/fulfillment and to accounting). And plenty of opportunities for writing AFTER processing for INSERT but forgetting for UPDATE, making an auditors' nightmare.

What you describe is what I described above as "... nothing like update-through-views."

What update-through-views proposes (or implements, where it's implemented in SQL) is some logical equivalence -- specified by some expression -- between the expression and its source relvars/tables that could conceivably be expressed (or is expressed, in those SQL implementations that support update-through-views) by a logical set of update-through-views rules.

That's quite distinct from the diverse, ad-hoc -- and in some environments, rather pervasive -- use of triggers to perform arbitrary updates of this table or these tables when you update (or even read) that one, etc. There's nothing systematic or automatable in those.

In other words, the existence of a logical set of update-through-views rules wouldn't impact that at all.

Quote from AntC on July 19, 2021, 10:54 pm

Quote from Dave Voorhis on July 18, 2021, 9:07 pm

Quote from David Livingstone on July 18, 2021, 4:03 pm

Another 2 days have elapsed. Has at least a broad consensus on 'view updating' arrived ?

I wouldn't presume to comment on a 'DBMS sub-language', if only because I would've thought it would depend somewhat on the character of the language underlying it.

INSTEAD OF triggers certainly seem to provide a mechanism to achieve what I had in mind. Again I wouldn't presume to comment on INSTEAD OF triggers (or just triggers) more generally.
Would you say, Dave, that "Having many applications sharing one database is pervasive in the enterprise world" (quote) is due, at least to a reasonable degree, on the availability of INSTEAD OF triggers ?

No. I can count on one hand the number of times I've used INSTEAD OF (or some equivalent) to do anything remotely like update-through-views, and I can count on the other hand the number of times I've seen someone else use it.

Hmm I think you might be looking in the wrong places. I see plenty of application code in which there's a user-facing layer that generates an update 'request' as a PossRep update, passed to a DB interface layer that in effect implements INSTEAD OF by making multiple updates to the PhysRep.

This is worse than using INSTEAD OF, because now the 'logic' (I use the term loosely) is buried in code, not explicit in the SQL.

That's quite different from triggers in general, which in some environments are used extensively for various purposes but nothing like update-through-views.

Hmm again. As you said up-thread, lots of applications get stitched together as part of systems evolution. I see lots of AFTER triggers whose purpose is to pass on the update from the source system to downstream (say Order-Processing to Warehousing/fulfillment and to accounting). And plenty of opportunities for writing AFTER processing for INSERT but forgetting for UPDATE, making an auditors' nightmare.

What you describe is what I described above as "... nothing like update-through-views."

What update-through-views proposes (or implements, where it's implemented in SQL) is some logical equivalence -- specified by some expression -- between the expression and its source relvars/tables that could conceivably be expressed (or is expressed, in those SQL implementations that support update-through-views) by a logical set of update-through-views rules.

That's quite distinct from the diverse, ad-hoc -- and in some environments, rather pervasive -- use of triggers to perform arbitrary updates of this table or these tables when you update (or even read) that one, etc. There's nothing systematic or automatable in those.

In other words, the existence of a logical set of update-through-views rules wouldn't impact that at all.

I'm the forum administrator and lead developer of Rel. Email me at dave@armchair.mb.ca with the Subject 'TTM Forum'. Download Rel from https://reldb.org

#88 · July 20, 2021, 6:04 pm

"... According to Codd his relational data model entails the validity of certain WFFs. Two of the most elementary relational queries are ( R ) and ( NOT R ). They are also WFFs. "

"... No. (NOT R) is not well-formed. Codd 1972 requires that NOT appear only as an operand of JOIN -- then (R2 AND NOT R) is a wff, equiv (R2 MINUS R)

Beat me to it!..."

Actually, "(NOT R) is not well-formed." is incorrect. That 1972 relational completeness paper defines WFFs in the tuple calculus section with: "... 1. Any term is a WFF. 2; If Γ is a term, so is ¬Γ; …". In other words it defines the syntax of the negation of a symbol that is not in the preceding alphabet, such as R. So (¬R) is a WFF and so is (NOT R). Likewise ( R1 ) and ( NOT R2 ).

Naive readers and casual coders of parsers might jump to the wrong conclusions and the post might have been clearer to some of them if it had said "Two of the most elementary relational queries are ( R ) and ( R AND ( NOT R ))".

Secondly, the sentence: "Codd 1972 requires that NOT appear only as an operand of JOIN…" is also incorrect because of the customary right-associative evaluation order which is emphasized here by parentheses. Instead, the syntax rewrite rule that the NOT operator must always follow a conjunction operator is the requirement the 1972 paper states.

That rule also doesn't mention what is called JOIN in this group (a fine example of breaking the "simple as possible but no simpler" advice).

The Forum for Discussion about The Third Manifesto and Related Matters