Re: "Structured" Entity-Relationship Model?

> 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 Received on Tue Feb 25 2014 - 13:25:43 CET