Nicola

> On Wednesday, 16 June 2021 at 19:35:35 UTC+10, Nicola wrote:
> > On 2021-06-15, Derek Ignatius Asirvadem wrote:
> >
> > AFAIC, the data modelling is not quite complete, not resolved. I was
> > expecting a response from you to continue, and bring it to
> > a conclusion. Eg. the obvious evaluation as the DMs stand is, to
> > progress both yours and mine. Not is the sense of competition, but
> > for understanding.
> >
> > Eg. in mine, if Composer and Solution have common attributes (other
> > than that in Person), that would be Normalised into a Subtype cluster.
> > Which leads to yours. If not, four tables are the irreducible
> > (“non-redundant”) set.
>
> I think I have already justified my preference for my version, which is
> what you are saying: I'd use a cluster because that allows me to extend
> the model with information common to both composers and solvers.
>
> Another reason is that the exclusivity between composers and solvers is
> more explicit (because of exclusive subtyping), although
> implementation-wise Solver_Not_Composer would not be a very different
> constraint.

Exactly. We have proved, at least in this classroom exercise, that there is more than one way to model the data correctly, the difference being how each of us perceives the facts in reality (not the “universe of discourse” God help me, not our glorious perception [such as the OO/ORM/OOP crowd) ).

I doubt there will be common attributes for Composer and Solution, because each of those are roles; acts of Persons, which is where there commonality is.

Let’s say we add Critic. In both yours and mine, we add one table, that is an easy extension of the existing facts.

> Btw, wouldn't you also need a Composer_Not_Solver constraint
> associated to Composer?

Why ?

Cheers
Derek

On 2021-06-16, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:

>> Btw, wouldn't you also need a Composer_Not_Solver constraint
>> associated to Composer?
>
> Why ?

Consider this:

1. Insert Alice and Bob.
2. Insert bridge problem B with (primary) composer Alice.
3. Insert solution of B by Alice.

(Prevented by Solver_Not_Composer)

4. Insert solution of B by Bob.

5. Insert secondary composer Bob for problem B

What does prevent 5?

Nicola

> On Thursday, 17 June 2021 at 05:02:53 UTC+10, Nicola wrote:
> > On 2021-06-16, Derek Ignatius Asirvadem wrote:
>
> >> Btw, wouldn't you also need a Composer_Not_Solver constraint
> >> associated to Composer?
> >
> > Why ?
> Consider this:
>
> 1. Insert Alice and Bob.
> 2. Insert bridge problem B with (primary) composer Alice.
> 3. Insert solution of B by Alice.
>
> (Prevented by Solver_Not_Composer)
>
> 4. Insert solution of B by Bob.
>
> 5. Insert secondary composer Bob for problem B
>
> What does prevent 5?

Chronology and Sanity.

Well, the bridge problem was composed, and published, and only then solved. One can’t go back and change history (assert that there was a oh, oh, oh, second composer that was not recorded). It is invalid. That is the same as saying that the bridge problem (in Problem) has changed after the solution has been registered, just as invalid. Try registering a child to a parent and then asserting that the parent is childless.

If [5] is allowed that would make the composition (Problem + Composers] that Bob solved *NOT* the composition that Bob solved. Done properly, you would have to
-- delete [4] -- to allow a valid change to composition
-- insert [5] -- now the composition is correct
-- if the new composition is in fact solved by Bob
---- insert [4]

This is a common problem with academics, due to their programming, and it shows up in the entire PusGress world. They are so programmed to think in (a) fragments), and (b) that a thing is only-one-way, therefore you must implement the thing the other way in order to be complete. Date & Darwen specifically teach that a FK relation must be implemented in the child (correct) and in the parent (hysterically incorrect, and even SQL is not so stupid).. That forms a circular reference on every relation between tables. Now they have trained the masses to think that (c) SQL is stupid, and (d) circular references are normal. Of course, the insane say that only “deferred constraint checking” can solve that problem, that the sane do not have. So then (e) they petition the SQL Committee to include “deferred constraint checking” as a requirement.

Typical snake oil racketeer. First they sell you the cancer, and then they sell you the one-and-only cure-all, that they themselves manufacture. Don’t buy the cancer, and you won’t need the cure-all. But if you do buy the cancer, when the pain is great enough, determine the cancer, and remove it. Don’t buy the cure-all.

(The “vaccine” CCP Virus is not vaccine by the definition of vaccine, the use of the label is fraudulent. It is an mRNA Transmitter, a completely new kind of treatment: gene therapy. When that is understood, the side-effects can be understood, because they are not side-effects of a vaccine.)

Obviously, I am not saying that you are doing that particular erroneous thing. I am saying that all academics including you are trained in that insane thinking: that things are fragments; that things are one-way, not safe; that you always have to look for completing the thing by doing it the other way.

The cure is understanding that things are not fragments, they are atoms, made up of fragments. As per the previous discussion. A bridge problem is not a fragment in Person, nor a fragment in Composer, but an Atom in (Person + Composer). You can’t change that Atom after you publish it and open the problem to solutions: if you do, you have to retract and re-publish. And you definitely can’t change the Atom after you have accepted even one Solution.

Unless you have been poisoned by the insane mindset of the droolers: Date & Darwen, and degraded, to think in terms of (i) denying the Atoms, and (ii) seeing only isolated fragments, that (iii) need double and triple definition.

The set of ACID Transactions that make up that database is not shown. As discussed severally, the database is incomplete without them. All such (as above) issues and nuances are easily covered in the Transactions that have to exist.

Let’s say the bridge problem has a textual Description.

(Composition_Add_tr is the same structure as OrderSaleItem_tr discussed previously. Item lines are added singly, not en masse. For both GUI sanity, and OLTP [low contention], and ACID reasons. There is no “add header” transaction or concept, that is done with the first line. As per the “1” in Predicate[ Problem has 1-to-n Composers ], which constraint is a Transaction constraint.)

Composition_Add_tr ( parm list is 1NF )
-- if Problem[ @ProblemName ] EXISTS
---- if Solver[ @ProblemName ] NOT EXISTS
------ INSERT Composer[] -- the second
-- else
---- INSERT Problem[]
---- INSERT Composer[] -- the first

Composition_Mod_tr ( parm list is PK + changeable attributes only )
-- if Problem[ @ProblemName ] EXISTS
---- if Solver[ @ProblemName ] NOT EXISTS
------ if @Description
-------- UPDATE Problem.Description

Composition_Drop_tr ( parm list is PK only )
-- if Problem[ @ProblemName ] EXISTS
---- if Solver[ @ProblemName ] NOT EXISTS
------ DELETE Composer[ @ProblemName ]
------ DELETE Problem[ @ProblemName ]

------

Years ago, we discussed that in a genuine Relational database (Codd, not the freaks), DKNF is normal, almost pedestrian, I provide it always. Not the insane R Fagin definition, but the obvious Codd intent, an ordinary progression of Codd’s 3NF (FFD only), and Atomicity. I don’t think you understood it then. I think the understanding is growing in you now.. The above is just an example.

Cheers
Derek

On 2021-06-16, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:
>> On Thursday, 17 June 2021 at 05:02:53 UTC+10, Nicola wrote:
>> > On 2021-06-16, Derek Ignatius Asirvadem wrote:
>>
>> >> Btw, wouldn't you also need a Composer_Not_Solver constraint
>> >> associated to Composer?
>> >
>> > Why ?
>> Consider this:
>>
>> 1. Insert Alice and Bob.
>> 2. Insert bridge problem B with (primary) composer Alice.
>> 3. Insert solution of B by Alice.
>>
>> (Prevented by Solver_Not_Composer)
>>
>> 4. Insert solution of B by Bob.
>>
>> 5. Insert secondary composer Bob for problem B
>>
>> What does prevent 5?
>
> Chronology and Sanity.

Ok, understood. As you say:

> The set of ACID Transactions that make up that database is not shown.
> As discussed severally, the database is incomplete without them.

Which leaves room for speculation about whether you forgot a constraint
or it was done intentionally.

>All such (as above) issues and nuances are easily covered in the
> Transactions that have to exist.

Ok.

Nicola

> On Thursday, 17 June 2021 at 18:37:51 UTC+10, Nicola wrote:
> > On 2021-06-16, Derek Ignatius Asirvadem wrote:
> >> On Thursday, 17 June 2021 at 05:02:53 UTC+10, Nicola wrote:
> >> > On 2021-06-16, Derek Ignatius Asirvadem wrote:
> >>
> >> >> Btw, wouldn't you also need a Composer_Not_Solver constraint
> >> >> associated to Composer?
> >> >
> >> > Why ?
> >> Consider this:
> >>
> >> 1. Insert Alice and Bob.
> >> 2. Insert bridge problem B with (primary) composer Alice.
> >> 3. Insert solution of B by Alice.
> >>
> >> (Prevented by Solver_Not_Composer)
> >>
> >> 4. Insert solution of B by Bob.
> >>
> >> 5. Insert secondary composer Bob for problem B
> >>
> >> What does prevent 5?
> >
> > Chronology and Sanity.
>
> Ok, understood. As you say:
>
> > The set of ACID Transactions that make up that database is not shown.
> > As discussed severally, the database is incomplete without them.
>
> Which leaves room for speculation about whether you forgot a constraint
> or it was done intentionally.

That means you do not trust me.

Intentionally. Do you think I could give such detailed reasoning for anything but a known set of problems ? Do you not accept /Chronology and Sanity../ ?

That is a typical class of errors, that “university educated” people make, I have corrected hundreds. Actually two classes of common error. The first is trying to rewrite history. Dishonesty without realising that it is. The second is as described in the post.

Eg. an Exposure table PK ( SecurityId, Date ) = ExposureOpening, ExposureClosing. The idiot did not understand that yesterdays closing Exposure is todays opening Exposure. I removed ExposureOpening, and renamed ExposureClosing to Exposure. Halved the table size. Then taught him how to write a subquery in SQL, to get yesterdays Exposure “side-by-side” with todays Exposure.

Eg. a table for the distance between to GeoLocation points (or two ZipCodes, as I did again, recently). PK ( GeoPoint_1, GeoPoint_2 ). The darling child knew he had a big problem but he was clueless as to how to fix it. When I diagnosed it as Normalisation error, he was completely baffled. I was there for just one day to fix an unrelated performance problem on the Production server, no time for explanations. So with permission, I just implemented the Constraint and reloaded the data. Halved the table size.

The point is, to catch all the errors of the totally redundant “two-way” implementations by newbies, and cancel/delete/remove/destroy-with-fire one half. Diagnosis is looking for constraints that apply to fragments rather than atoms. I gave the explanation because I thought, that is what you did. On my side, I am quite used to *NOT* doubling up in the first place.

In any case, all such errors (omissions; accidents; whatever) are exposed in User Acceptance Testing.

We have at least four formal environments in any decent corp:
- Development Server (usually doubles as Disaster Recovery server for Production)
--- Development Rdb (next version, currently being written)
--- Test Rdb (formal test environment for Dev_Rdb, loaded with some Prod data, power user access)
- Production server (heavily secured and optimised)
--- Production Rdb
--- User Acceptance Testing Rdb (next version, that passed Test, with 100% copy of current Production data)

> > The set of ACID Transactions that make up that database is not shown.

Ok. Doc updated. Some, not all Transactions shown.

Cheers
Derek