"Structured" Entity-Relationship Model?

[Wolfgang Keller]

Hello,

coming from the "practitioner" side, I wonder what relational theorists
think about "structured entity relationship" modelling:

http://en.wikipedia.org/wiki/Structured-Entity-Relationship-Model

For me the essential aspect seems to be the rule (which doesn't even
seem to be mentioned in the english-language Wikipedia article) that the
dependency graph of all entities must not contain directed circles,
i.e. there must be no "hen and egg"-type dependencies between entities.

Which appears to be a perfectly reasonable rule to me, since such
dependencies create horrible headaches for working with such databases.
Just imagine loading or deleting bulk data with circular dependencies.

Now what do theorists think:

Is it perfectly evident that this requirement must be enforced, since a
model with cyclic dependencies is plain "spaghetti", maybe even
violating some normal form? Is it plain nonsense? Or does it depend on
the specific case, since there may be situations where it can't be
achieved, and a model that violates this rule can be perfectly valid?

I've noted that in practice, graphical modelling tools seem to be prone
to making users produce models where this rule is violated. There
don't seem to be modeling tools that would allow checking for such
dependencies resp. preventing them.

TIA,

Sincerely,

Wolfgang

[James K. Lowden]

On Sat, 20 Apr 2013 22:02:46 +0200
Wolfgang Keller <feli...@gmx.net> wrote:

> the essential aspect seems to be ... that the dependency graph of all

> entities must not contain directed circles

That might be usually true of BCNF designs, but it's not a rule
per se. Normalization forms are defined in terms of redundancy and
projection analysis, not graph theory.

Using the SERM example on the wikipedia page, the graph says every
order has N order items, and every order item has 1 order. Although
drawn as just one line, that line states two rules, mutually dependent:
a cycle. (Try using DRI to enforce them.)

--jkl

[Norbert_Paul]

Wolfgang Keller wrote:
> Hello,
>
> coming from the "practitioner" side, I wonder what relational theorists
> think about "structured entity relationship" modelling:
>
> http://en.wikipedia.org/wiki/Structured-Entity-Relationship-Model

As a relational theorist that model is completely new to me. But
from the theoretical viewpoint I'd say it doesn't matter if you draw boxes,
diamonds or decorated boxes. It is also more a matter of taste than one of
theory if you decorate edges by arrowheads and double strokes, or if you
use, say, the UML notation.

> For me the essential aspect seems to be the rule (which doesn't even
> seem to be mentioned in the english-language Wikipedia article) that the
> dependency graph of all entities must not contain directed circles,
> i.e. there must be no "hen and egg"-type dependencies between entities.
>
> Which appears to be a perfectly reasonable rule to me, since such
> dependencies create horrible headaches for working with such databases.
> Just imagine loading or deleting bulk data with circular dependencies.

Actually, headaches in not a theoretical issue ;). Of course DAG-shaped
dependencies are less likely to cause problems but even DAG-dependencies
may lead to problems, like, say, deadlocks.

> Now what do theorists think:
>
> Is it perfectly evident that this requirement must be enforced, since a
> model with cyclic dependencies is plain "spaghetti", maybe even
> violating some normal form? Is it plain nonsense? Or does it depend on
> the specific case, since there may be situations where it can't be
> achieved, and a model that violates this rule can be perfectly valid?

Whereas, at the moment, I cannot imagine a case where cyclical dependencies
are useful I would not prohibit them entirely. A warning upon their detection
would do, too. I am not a friend of "hard-coding" enforcement of design rules
into design tools.

> I've noted that in practice, graphical modelling tools seem to be prone
> to making users produce models where this rule is violated. There
> don't seem to be modeling tools that would allow checking for such
> dependencies resp. preventing them.

This is interesting. That would then be a good extension of such tools.
However, "prevention" should only consist in warning the user and leaving
it up to his decision if he needs cyclical dependencies in his case, say
by a Java-annotation-like @SuppressWarnings("cyclical"). Note that
"being part of a cycle" is an equivalence relation:
http://en.wikipedia.org/wiki/Kolmogorov_space#The_Kolmogorov_quotient


Norbert

[Jan Hidders]

On 2013-04-20 20:02:46 +0000, Wolfgang Keller said:

> Hello,
>
> coming from the "practitioner" side, I wonder what relational theorists
> think about "structured entity relationship" modelling:
>
> http://en.wikipedia.org/wiki/Structured-Entity-Relationship-Model

Right. SERM. Part of Aris, a SAP thing. The hierarchical database
model, once again trying to make a come back in a new disguise. :-)

> For me the essential aspect seems to be the rule (which doesn't even
> seem to be mentioned in the english-language Wikipedia article) that the
> dependency graph of all entities must not contain directed circles,
> i.e. there must be no "hen and egg"-type dependencies between entities.

The German page is more explicit and does mention this rule.

> Which appears to be a perfectly reasonable rule to me, since such
> dependencies create horrible headaches for working with such databases.
> Just imagine loading or deleting bulk data with circular dependencies.
>
> Now what do theorists think:
>
> Is it perfectly evident that this requirement must be enforced, since a
> model with cyclic dependencies is plain "spaghetti", maybe even
> violating some normal form? Is it plain nonsense? Or does it depend on
> the specific case, since there may be situations where it can't be
> achieved, and a model that violates this rule can be perfectly valid?

It has nothing to do with normalization in the classical relational
theory sense of the word.

In theory it can always be achieved: you can take any ER diagram (or a
dialect that also has the associative entity concept) and turn it into
a SER diagram by suitably picking the direction of the connections
between entitties and relationships. However, that direction might not
always be natural or intuitively clear.

You should not think of SERM as a replacement of ERM, rather it is a
slightly specialized form of it that introduces certain modelling
artifacts that help with certain practical implementation issues, but
can be conceptually meaningless. This can work well if you have a data
management layer that offers you a SERM interface. Less so if you don't
and will ultimately map it to a relational or SQL-based data management
system.

So summarizing, the answer to your question is:
- At the conceptual level this restriction is nonsensical.
- At the logical level it can be useful.

-- Jan Hidders

[Roy Hann]

Wolfgang Keller wrote:

> Hello,
>
> coming from the "practitioner" side, I wonder what relational theorists
> think about "structured entity relationship" modelling:
>
> http://en.wikipedia.org/wiki/Structured-Entity-Relationship-Model
>
> For me the essential aspect seems to be the rule (which doesn't even
> seem to be mentioned in the english-language Wikipedia article) that the
> dependency graph of all entities must not contain directed circles,
> i.e. there must be no "hen and egg"-type dependencies between entities.

I can't think why that would be important.

> Which appears to be a perfectly reasonable rule to me, since such
> dependencies create horrible headaches for working with such databases.

Really? Like what? I suppose if the DBMS insists of testing the
referential integrity contstraints before you assert that you have
completed a transaction that has left the database consistent (i.e. you
COMMIT) that would be a problem, but that's just a broken DBMS. Indeed
most SQL DBMSs seem to want to test constraints after every blessed
statement, which is silly.

I don't really applaud products/methodologies that try to elevate the
workarounds for implementation mis-features to "best practice", as it
appears SERM does.

> Just imagine loading or deleting bulk data with circular dependencies.

I have no problem imagining that at all. SQL applies a series of
tiny, local, sequential changes to the database. It is absurd to
expect every such incremental change must leave the database
consistent. It is absurd that one could not do "bulk loading" or "bulk
deleting" or any other series of things, in any random order, deferring
the question of consistency until you, the programmer, want to make that
claim.

> Now what do theorists think:
>
> Is it perfectly evident that this requirement must be enforced, since a
> model with cyclic dependencies is plain "spaghetti", maybe even
> violating some normal form?

No.

> Is it plain nonsense?

I'm not sure what "it" is here.

> Or does it depend on
> the specific case, since there may be situations where it can't be
> achieved, and a model that violates this rule can be perfectly valid?
>
> I've noted that in practice, graphical modelling tools seem to be prone
> to making users produce models where this rule is violated.

Most modelling tools are rubbish, but in this particular respect I don't
see a problem. Quite the contrary.

> There
> don't seem to be modeling tools that would allow checking for such
> dependencies resp. preventing them.

Good.

--
Roy

[Wolfgang Keller]

> Using the SERM example on the wikipedia page, the graph says every
> order has N order items, and every order item has 1 order. Although
> drawn as just one line, that line states two rules, mutually
> dependent: a cycle. (Try using DRI to enforce them.)

And just two (mutually co-dependent) constraints wouldn't be enough
to correctly implement the semantics, as far as I understand. But
database design books don't seem to explain it either. At least those
that I have access to. Or am I just blind?

This issue (it's "just" a 1:n relationship with n>0, right?) must be
as old as the relational database model itself (fourty years now?).

I've tried to google for the relevant terms, and couldn't find a
documented example for a correct, tested solution. Or is it just that
you need to know *the* terms to search for?

Does someone in this group know of a book (or an online document) that
shows how to *correctly* implement such things? And maybe one that also
gets other things (that I haven't stumbled over yet) *right*? Something
like "database design caveats that most textbooks conveniently ignore"?

TIA,

Sincerely,

Wolfgang

[Erwin]

Op zondag 21 april 2013 17:45:11 UTC+2 schreef Roy Hann het volgende:

Is the statement-level constraint checking still "silly" if multiple assignment as per TTM is available ?

[Erwin]

Op dinsdag 30 april 2013 16:30:54 UTC+2 schreef Wolfgang Keller het volgende:

If updates to >1 table are(/may be) necessary before consistency (of the data with the rules/constraints) can be achieved, then you"ll need deferred constraint checking. Or a TTM-compliant DBMS that offers multiple assignment, which is better for reasons that have not so much to do with consistency per se.

[Jan Hidders]

Look for "deferred constraint checking". Oracle and PostgreSQL support that.

-- Jan Hidders

[com...@hotmail.com]

On Tuesday, April 30, 2013 7:30:54 AM UTC-7, Wolfgang Keller wrote:

> And just two (mutually co-dependent) constraints wouldn't be enough
> to correctly implement the semantics, as far as I understand.

Don't understand.

> database design books don't seem to explain it either.

It is not clear to me what you mean by "it".

> This issue (it's "just" a 1:n relationship with n>0, right?) must be
> as old as the relational database model itself (fourty years now?).

This focus on arity of relationships is an *ER* concern not a RM concern per se.

In the RM you associate a predicate about the world with each relation. The relation holds the tuples that give a true proposition from the predicate. The constraints are consequent true propositions about the base relation variable values; equivalently, they are truths about the world. Entities and relations are not particularly helpful concepts other than in the sense of predicate logic objects and predicates respectively.

Read Hugh Darwen's free online ebook http://bookboon.com/en/an-introduction-to-relational-database-theory-ebook . Fabian Pascal has a predicate-oriented ER-reminiscent relational approach and I suggest you check out www.dbdebunk.com. Terry Halpin's ORM is oriented towards predicated base relations. But ER is neither necessary or sufficient for proper (relational) modeling.

> Does someone in this group know of a book (or an online document) that
> shows how to *correctly* implement such things?

I'm not sure what you mean by "such things". ER models (relationally) Multiple simultaneous constraints? Circularities are logically irrelevant and arise as necessary from proper design of normalized predicates.

philip

[Roy Hann]

Erwin wrote:

>> Really? Like what? I suppose if the DBMS insists of testing the
>> referential integrity contstraints before you assert that you have
>> completed a transaction that has left the database consistent (i.e. you
>> COMMIT) that would be a problem, but that's just a broken DBMS. Indeed
>> most SQL DBMSs seem to want to test constraints after every blessed
>> statement, which is silly.

[snip]

> Is the statement-level constraint checking still "silly" if multiple assignment
> as per TTM is available ?

*Insisting* on statement-level constraint checking in *SQL DBMSs* is
still silly.

--
Roy

[Jan Hidders]

I would say: no. In theory every transaction can be split into a set of
reading operations followed by a single multi-update operation.

-- Jan Hidders

[Derek Asirvadem]

On Sunday, 21 April 2013 06:02:46 UTC+10, Wolfgang Keller wrote:

> coming from the "practitioner" side, I wonder what relational theorists
> think about "structured entity relationship" modelling:
>
> http://en.wikipedia.org/wiki/Structured-Entity-Relationship-Model

Serves you right for using wiki as a reference. The content is populated by anyone and changed by everyone. It has no sense of authority. And the ever-changing opinions of the uneducated masses is not "authority".

There are errors in the diagram as well. It is not in 3NF and it does not prevent OrderItems from being Invoiced more than once. It is a naïve model.

> For me the essential aspect seems to be the rule (which doesn't even
> seem to be mentioned in the english-language Wikipedia article) that the
> dependency graph of all entities must not contain directed circles,
> i.e. there must be no "hen and egg"-type dependencies between entities.

Yes, that is a very good rule to have. I have used that as a rule "Thou shalt not have circular references" since 1984. All the good Rdbs I have ever had to audit have had that rule. But it is a rule, a design principle, like "look left *and* right before crosiing the road". Addition of that rule to a data model or a modelling notation does not form a new modelling notation. Yes, it is more structured than an ER model without it, but it does not warrant a new name. My databases have several such rules (this is just one), for quality and performance purposes, I still call them Relational databases plus published standards, not "structured relational database". People expect that normal human being can cross the road without killing themselves, we do not need "structured human".

SAP is not exactly famous for modelling databases, and their dbms were horrible before they acquired Sybase.

IDEF1X is the established standard for modelling Relational databases, and is the only one that (a) Codd was involved in and (b) actually implements the RM (it does not prevent non-RM constructs, unfortunately). The notation is full and complete. One addition, most people use IEEE notation instead, for the relationships, because they are more explicit and everyone understands them.

Notice, in the diagram, it refers to "Customers without any orders are legal because they are independent Entities." But it fails to define "Independent" or "Dependent". Those are formal terms in IDEF1X. So even those authors have had to evaluate the model in those terms, and make the determination, even though they do not credit Codd or Chen or brown or IDEF1X.

Yet another diagrammatic notation is plain stupid.

> Now what do theorists think:
>
> Is it perfectly evident that this requirement must be enforced, since a
> model with cyclic dependencies is plain "spaghetti", maybe even
> violating some normal form?

It violates common sense. One knows that something is wrong; that it will cause problems; one does not need a theorist to come up with a rule or a hilarious "mathematical definition" to prove that the common sense God gave us is a Good Thing. One does not need a rule to prevent one from doing silly things.

> Or does it depend on
> the specific case, since there may be situations where it can't be
> achieved, and a model that violates this rule can be perfectly valid?

A rule is a rule is a rule. There is never a circumstance where breaking the rule is acceptable. "I crossed the road without looking left because I thought I was in Africa".

There is no such thing as a valid database that has an invalid article in it. The fact that it has an invalid article (here, a circular reference), makes it invalid. I have audited close to a hundred databases, and corrected problems therein. Whenever someone has implemented a circular reference, that *always* causes massive problems, and the customer *always* asks me to address that problem first (other problems may be hidden, but this one is a bleeding sore).

Correcting it is always straight-forward. The correction in the db (changing the constraint definitions) is easy. The correction in the mindset of the people who implemented it, takes more work.

The example in the wiki article is not a valid one for the chicken-and-egg problem, or resolution thereof, so let us use a valid one. In a banking system, one rule states that in order to open an account, the new customer must deposit a minimum of $10. Another rule states that every account must have a customer. Now, if you have lost your common sense, or you think that every business requirement must be implemented as stated (which makes you a clerk, not an technician), then will see this as a dilemma, and struggle with it for weeks. The looneys on TTM argued about it for a year.

But if you have been to Mass recently, and you have not excised the gifts that God gave you, you will realise this is not a banking problem or a clerical problem or a big problem. It is a database, everything is integrated with everything else. It is not two separate rules taken in isolation. Just choose an Order: identify and establish the Customer first; then create an account; then deposit $10. It is the same resolution that we have used to prevent (and to resolve) deadlocks since the 1960s. Choose the constraints carefully, and they have only one direction, never circular.

> I've noted that in practice, graphical modelling tools seem to be prone
> to making users produce models where this rule is violated. There
> don't seem to be modeling tools that would allow checking for such
> dependencies resp. preventing them.

Modelling tools are not a replacement for brains. Or for understanding the task of modelling. Some are better than others, and prevent Bad Things in general, but even that can be easily circumvented. The best they can do is warn you of what looks like a problem. ERwin (the tool, not the correspondent on this channel) is the best by far, it implements IDEF1X, ie. the RM. But even that can be circumvented or ignored by someone who is clueless.

> > Using the SERM example on the wikipedia page, the graph says every
> > order has N order items, and every order item has 1 order. Although
> > drawn as just one line, that line states two rules, mutually
> > dependent: a cycle. (Try using DRI to enforce them.)
>

> And just two (mutually co-dependent) constraints wouldn't be enough

> to correctly implement the semantics, as far as I understand. But
> database design books don't seem to explain it either. At least those
> that I have access to. Or am I just blind?

You are not blind, but you are blinded by the waffle on this noisy channel. Evidently James does not know much about Relational databases or SQL.

• that one line is one relationship
• it is defined by one (and only one) constraint
ALTER TABLE OrderItem (not Order)
CONSTRAINT xyz FOREIGN KEY (Order_PK)
REFERENCES Order (Order_PK)
• the relationship is identified by the Verb Phrase or Business Rule:
Each Order has n OrderItems.
• it is not two rules
• it is the same one , reading the relationship definition line in the mode stated in reverse (reverse the verb phrase or business rule):
Each OrderItem has one and only one Order

There is nothing mutually dependent or co-dependent about it. There is only one direction in the constraint, in any constraint. The fact that one does not add (note I did not state INSERT) an Order without adding any OrderItems, is a matter of the transaction. As long as you have platform that supports transactions, and SQL compliance requires ACID transactions. So simply make sure that the transaction code does not add a Order without at least one OrderItem.

Now here is where people who have been trained to be stupid (by reading books written by subverters or taking lessons in fragmented thinking), to run into each other like circus clowns, see an opportunity to exercise the training they acquired at great expense, that they are so attached to. They will not see that the database is an integrated unit, and that the transactions as integrated into it. No, they will get hung up at the mis-conceptual or ill-logical level, and demand a constraint to enforce the non-business rule that "every Order must have at least one OrderItem". So even where there is no circular reference, they will introduce the non-logic that demands one. And then implement the stupid thing. That is why I stated, the problem is not in the database, it is in the mindset.

> This issue (it's "just" a 1:n relationship with n>0, right?)

It is.

> must be
> as old as the relational database model itself (fourty years now?).

It is. Resolved forty years ago as well.

> I've tried to google for the relevant terms, and couldn't find a
> documented example for a correct, tested solution. Or is it just that
> you need to know *the* terms to search for?

You are expecting technically valid answers from a ever-changing pile of excreta produced by uneducated masses ?

> Does someone in this group know of a book (or an online document) that

> shows how to *correctly* implement such things? And maybe one that also
> gets other things (that I haven't stumbled over yet) *right*?

Read only Codd.

Throw out anything written by anyone else, especially if they claim to champion or explain the RM.

> Something
> like "database design caveats that most textbooks conveniently ignore"?

I will post when I write it ;}

Here's a chapter that you may be interested in, and Introduction to IDEF1X. It does handle a few caveats, but it is not a detailed how-to doc:
http://www.softwaregems.com.au/Documents/Documentary%20Examples/IDEF1X%20Introduction.pdf

>> Look for "deferred constraint checking". Oracle and PostgreSQL support that.

Interesting. The high end vendors (DB2 and Sybase) don't have it. There is a reason for that, it is well-known that both modellers and developers at the high end have the full three digits in the IQ department. They have been implementing Relational databases with a full set of Constraints for decades, such as ensuring that Orders have at least one OrderItem, but have never needed a constraint to enforce that particular database integrity. They understand the content of my post. PostgreNonSQL doesn't have SQL transactions.

But at the low end, where the clowns are very attached to running into each other, over and over again:
• ensure they never hear about the "no circular reference" rule, or understand the problem is outside the database
• give them a platform that allows circular references, and has a "method" of allegedly resolving it. Don't worry dear, everything will be alright in the end.
• good for justifying machines that need twice the CPU power
• then wail about the competition not having said absurd feature. Good for making sales by box-checkers.

First train them to be stupid, then sell them machines and software that only stupid people need. That is the theory. Which is why you need engineers, not theorists. Unfortunately few of us write books.

Cheers
Derek

[Erwin]

I would like to express my thanks to Derek for recognizing that there is a logical difference between ERWin (the software tool) and Erwin, the contributor to this discussion list.

And then I'd also like to thank him for pointing out that ...

"First train them to be stupid, then sell them machines and **********software********* that only stupid people need."

[Derek Asirvadem]

> On Monday, 30 September 2013 17:51:32 UTC+10, Derek Asirvadem wrote:
>
> > On Sunday, 21 April 2013 06:02:46 UTC+10, Wolfgang Keller wrote:
> >
>
> For me the essential aspect seems to be the rule (which doesn't even
> seem to be mentioned in the english-language Wikipedia article) that the
> dependency graph of all entities must not contain directed circles,
> i.e. there must be no "hen and egg"-type dependencies between entities.
> >
> Yes, that is a very good rule to have. I have used that as a rule "Thou shalt not have circular references" since 1984. All the good Rdbs I have ever had to audit have had that rule. But it is a rule, a design principle, like "look left *and* right before crosiing the road". Addition of that rule to a data model or a modelling notation does not form a new modelling notation. Yes, it is more structured than an ER model without it, but it does not warrant a new name. My databases have several such rules (this is just one), for quality and performance purposes, I still call them Relational databases plus published standards, not "structured relational database". People expect that normal human being can cross the road without killing themselves, we do not need "structured human".

Summary point. The Relational Model is already Structured. We do not need a Structured Relational Model, or Structured ER Model.

If you apply the Normalisation that Codd provided in the RM, for the data, which demands a "integral tree", to the definition (declarations), you get integral definitions, no circular references.

> > > Using the SERM example on the wikipedia page, the graph says every
> > > order has N order items, and every order item has 1 order. Although
> > > drawn as just one line, that line states two rules, mutually
> > > dependent: a cycle. (Try using DRI to enforce them.)
> >
> > And just two (mutually co-dependent) constraints wouldn't be enough
> > to correctly implement the semantics, as far as I understand. But
> > database design books don't seem to explain it either. At least those
> > that I have access to. Or am I just blind?
>
> You are not blind, but you are blinded by the waffle on this noisy channel. Evidently James does not know much about Relational databases or SQL.
>

> * that one line is one relationship
> * it is defined by one (and only one) constraint

> ALTER TABLE OrderItem (not Order)
> CONSTRAINT xyz FOREIGN KEY (Order_PK)
> REFERENCES Order (Order_PK)

> * the relationship is identified by the Verb Phrase or Business Rule:

> Each Order has n OrderItems.

> * it is not two rules
> * it is the same one , reading the relationship definition line in the mode stated in reverse (reverse the verb phrase or business rule):

> Each OrderItem has one and only one Order
>
> There is nothing mutually dependent or co-dependent about it. There is only one direction in the constraint, in any constraint.
>

> The fact that one does not add (note I did not state INSERT) an Order without adding any OrderItems, is a matter of the Transaction.

And not a declarative constraint.

>As long as you have a platform that supports transactions, and SQL compliance requires ACID transactions. So simply make sure that the transaction code does not add a Order without at least one OrderItem.

>
> Now here is where people who have been trained to be stupid (by reading books written by subverters or taking lessons in fragmented thinking), to run into each other like circus clowns, see an opportunity to exercise the training they acquired at great expense, that they are so attached to. They will not see that the database is an integrated unit, and that the transactions as integrated into it.

Look at it thus. People are hung up on the idea of Declarative Referential Integrity, or declarative constraints, so they want all database integrity to be declarative. Due to absence of formal IT training, they come up with circular references; they cannot sequence [implement the correct Order in] their transactions; then they want Deferred Constraint Checking. Or they don't have Transactions; their platform is not SQL-Compliant, but it has "SQL" in the name, fraudulently.

If you don't have those mental limitations, you might notice that the entire database is one integrated recovery unit. The stored procs are Transactions. The Transactions and Functions are all part of that single unit. If you take the database minus the data, it is one single declaration. And that declaration involves:
- Datatype declarations (which are the first domain declarations)
- Table declarations
- Relationship (Foreign Key) declarations
- various constraint declarations (indices; data value Checks; data value Rules), which are secondary to the first
- Function declarations
- Transaction declarations. SQL Transactions are [A]tomic. In order for these Transaction declarations to be [A]tomic, they must be bounded by BEGIN TRAN and COMMIT TRAN.

All the constraints are declarative.

All the declarations are SQL code.

Some declarations are single SQL commands, multiple lines.

Other declarations are multiple SQL commands.

It is all one integrated unit, and minus the data, one that comprises declarations only. That maintain the [C]onsistency of the data content of the one unit.

What these people are hung up about is this. They really want all declarations to be single commands; they cannot imagine or accept declarations that are multiple commands (Transactions or Functions). And then consequently, they need circular references; Deferred Constraint Checking, and all the consequential nonsense.

The problem is in the mindset, not in the database or the declarations or definitions.

There are further issues re efficient Transaction design and providing high concurrency, which are not magically delivered by the platform, but are only by the Transaction declarations. Just as with tables, that Normalised tables are far more efficient than unnormalised heaps, likewise with Transactions and concurrency. But I will leave that for now.

Cheers
Derek

[Derek Asirvadem]

> On Monday, 22 April 2013 01:45:11 UTC+10, Roy Hann wrote:
>
> I suppose if the DBMS insists of testing the
> referential integrity contstraints before you assert that you have
> completed a transaction that has left the database consistent (i.e. you
> COMMIT) that would be a problem, but that's just a broken DBMS. Indeed
> most SQL DBMSs seem to want to test constraints after every blessed
> statement, which is silly.

It might be good if you get out and get some air.

All SQL-Compliant platforms perform statement-level checking, yes. Thousands of developers, have succeeded in producing millions of databases, which maintain database [C]onsistency, without that being an "impediment". That means they do not have Deferred Constraint Checking.

Of course, that excludes PostGreNonSQL, MyNonSQL, etc, because they are not SQL compliant. The people who use them write code in isolation, and have numerous weird and wondrous beliefs, firm ones, about the real world, but they never get out to experience it.

> SQL applies a series of
> tiny, local, sequential changes to the database. It is absurd to
> expect every such incremental change must leave the database
> consistent.

Ditto. Those millions of databases that never lose [C]onsistency or integrity, have that quality. It is possible that the one who has never coded in the real world, is the absurd one, and that the real world is not. Mental patients usually think they are sane, and that the entire world, is insane. They never get out. (Until recently.)

All you need is formal education on how to write those "tiny, local, sequential changes" in such a way that:
- conform to a natural order
- they never collide
- there is no repetition of code segments
- each and "every such incremental change" is compliant with the declarations
- leaving the database [C]onsistent

> It is absurd that one could not do "bulk loading" or "bulk
> deleting" or any other series of things, in any random order, deferring
> the question of consistency until you, the programmer, want to make that
> claim.

That is not how the real world works.

We make that claim when we define the database, in the declarative phase, before any data is loaded into it. At least those thousands of us who use SQL-Compliant platforms.

For the thousands who use freeware and non-compliant platforms, there is no end to the series of "features" that they need, in order to implement their "databases". Second last was the insane MVCC. Last was DCC. Next will be whatever the next mental limitation is exposed to be. There is no end to it. We do not suffer the impediments, and we do not need the "feature" to overcome the impediments. One really has to consider the kind of "databases" that these people with such strongly established impediments will "produce".

Again, all you need is formal education. Things like "any random order" have to be exorcised from your mind. Then you can, like the rest of us, make the claim first and load second. Tell us what you are selling first, then make the sale. The reverse is the hallmark of a fraud or nutcase. You would be sitting there, all alone in the market, after everyone has gone home, separating the chaff from the wheat, by hand.

> Erwin wrote:
>
> Is the statement-level constraint checking still "silly" if multiple assignment
> as per TTM is available ?

Statement-level constraint checking is standard. It existed in platform since 1984, before the standard arrived.

Multiple assignment is a joke. A bigger joke the DCC, but from the same asylum.

TTM is an orgy of jokes. After 16 years (or whatever) of zero delivery, the jokes are sad, and tired. All they have are definitions and demands, like Roy's, which are irrelevant to the database community. The only relevance they do have is celebral, in the smoke-filled chambers of those completely isolated from the world, full of their own self-importance. The entire TTM proposition is erected on a Straw Man. Academics love their Straw Men.

By Drawne's own declaration, when faced with a serious question, hey, it is a "toy language". Subsequent to my posts there, three years ago, he was going to come up with an "industrial strength toy language".

he has no formal education in Computer Science. Of course, that doesn't stop him inflicting his insanity on university students at Warwick.

Best to increase his meds.

Cheers
Derek

[Roy Hann]

^^^^^^^^^^^^^^^^^

Welcome back Derek! It must be good to be out huh? For ten months I've
been waiting for your response to my comment above.

I shall cut you much more slack in future. I had no idea you were
unwell.

Roy

[Derek Asirvadem]

On Sunday, 2 March 2014 00:23:11 UTC+11, Roy Hann wrote:
>
> Welcome back Derek! It must be good to be out huh?

Thank you, thank you, thank you. You may be seated. I suppose it is nice to be missed.

But you really shouldn't judge others by your own standards.

> For ten months I've been waiting for your response to my comment above.

On each post, there is a header, and in that header, there is am email address. Next time, just click on that email address, and drop me a line. No need to wait so long.

> I shall cut you much more slack in future. I had no idea you were
> unwell.

Yes. Actually in October, the level of disgust I was experiencing here caused so much vomiting that it burned by oesophagus. Never happened before. So I walked. Then someone sent me an email requesting a response. So I had to drag myself back. Once I was here, I noticed no-one had responded to yours, I felt sorry for you, and I provided a response. True, I gave up responding to you ten months ago, but I thought I'd give you another chance.

Don't abuse. The sentence gets doubled each time.