From Newsgroup: comp.databases.theory

Nicola

On Monday, 14 June 2021 at 01:13:54 UTC+10, Nicola wrote:

On 2021-06-13, Derek Ignatius Asirvadem wrote:
So in the previous model 12.2 Jun 21, I as DBA policeman re what goes
into the db, allowed your Role, but insisted on the required
Discriminator Staketype. In the next iteration, I will give
ComposerType. (This is modelling, yes, we have many iterations). You
tell me what you want.

The latest revision, with ComposerType, is fine.

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 (rCLnon-redundantrCY) set.
Please comment.
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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

Nicola

On Monday, 14 June 2021 at 01:13:54 UTC+10, Nicola wrote:

On 2021-06-13, Derek Ignatius Asirvadem wrote:

So in the previous model 12.2 Jun 21, I as DBA policeman re what goes
into the db, allowed your Role, but insisted on the required
Discriminator Staketype. In the next iteration, I will give
ComposerType. (This is modelling, yes, we have many iterations). You
tell me what you want.

The latest revision, with ComposerType, is fine.

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 (rCLnon-redundantrCY) 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. Btw, wouldn't you also need a Composer_Not_Solver constraint associated to Composer?

Nicola

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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 (rCLnon-redundantrCY) 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 rCLuniverse of discourserCY 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.
LetrCOs 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
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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 canrCOt 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 rCLdeferred constraint checkingrCY can solve that problem, that the sane do not have. So then (e) they petition the SQL Committee to include rCLdeferred constraint checkingrCY 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. DonrCOt buy the cancer, and you wonrCOt need the cure-all. But if you do buy the cancer, when the pain is great enough, determine the cancer, and remove it. DonrCOt buy the cure-all.
(The rCLvaccinerCY 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 canrCOt 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 canrCOt 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.
LetrCOs 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 rCLadd headerrCY transaction or concept, that is done with the first line. As per the rCL1rCY 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 CoddrCOs 3NF (FFD only), and Atomicity. I donrCOt think you understood it then. I think the understanding is growing in you now. The above is just an example.
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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 rCLuniversity educatedrCY 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 rCLside-by-siderCY 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 rCLtwo-wayrCY 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
--- Synchronet 3.21d-Linux NewsLink 1.2