From Newsgroup: comp.databases.theory

On Saturday, 12 June 2021 at 04:48:27 UTC+10, Nicola wrote:

On 2021-06-11, Derek Ignatius Asirvadem wrote:
That Bridge problem is from my days of hard labour at TTM gulag.

Not directly relevant to the topic of this thread, and possibly not
relevant to the formulation of that problem, but... your
Solver_Not_Composer constraint only ensures that the solver is not the
main composer. But nothing in that model prevents a solver to be the
same as the second composer (i.e., SolverID may be Composer_Id_2).

Yes.
The requirement went back and forth for a while, as the freaks proposed (eg) that you could not do this or that in the /RM/, or that this or that was not possible in SQL, which I then gave the solution for. As usual, they left it, without finishing; without closure; without acknowledgement of the truth (neither the /RM/ nor SQL is limited). So the model has a weird set of constraints, per their last set of requirements, and it it not finished.
I have alluded to the straight-forward method in the notes.

I would have designed the model differently:

Person[PersonId | LastName FirstName ...]
Problem[ComposerId.PersonId ProblemNo | Name Score ...] ProblemStakeholder[ComposerId ProblemNo StakeholderId.PersonId Role] JointComposer[ComposerId ProblemNo JointComposer.StakeholderId] Solver[ComposerId ProblemNo Solver.StakeholderId]

Ok.
(Minor point, I use round brackets for that, and square brackets only for the Key. Pipe means OR per various languages.)

where ProblemStakeholder is an exclusive generalization of JointComposer
and Solver. Then, a simple check constraint on ProblemStakeholder (ComposerId rea StakeholderId) plus the exclusive subtyping would prevent composers to be solvers of their own problems.

Very thoughtful.
The Check Constraint is fine, but it will not be simple. There are five conditions to be checked.
What in heavens name is rCLexclusive generalizationrCY, do you have a definition or link ?

An upper-bound on the
maximum number of composers or solvers for a problem could be added as
in your model.

It is already there:
- the cardinality in Composer_2 means max 1 JointComposer
- the Constraint /Solver_Max_4/ means exactly that.
The DDL link gives all DDL including the Functions called by the Constraints.

Relationships:

1. Each Person invents 0-N Problems
2. Each Problem has 0-N ProblemStakeholders
3. Each ProblemStakeholder is one of JointComposer or Solver

Fine.
You need:
__ ProblemStakeholder needs a reference for the StakeholderId FK
__ 4. Each Person claims 0-N ProblemStakeholders
Erect the model and have a good look. Although it is fine, and it works, it is just not straight-forward (a desirable element in logic).
Rather than fixing-up the Bridge model, which had a specific argumentation purpose, I would erect a logically straight-forward model. Ends up with fewer Facts.
____ https://www.softwaregems.com.au/Documents/Article/Normalisation/Bridge%202.pdf
____________
I will get to the rest later.
Regards
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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

What in heavens name is rCLexclusive generalizationrCY, do you have a definition or link ?

Ok, read: exclusive basetype.

An upper-bound on the
maximum number of composers or solvers for a problem could be added as
in your model.

It is already there:
- the cardinality in Composer_2 means max 1 JointComposer
- the Constraint /Solver_Max_4/ means exactly that.
The DDL link gives all DDL including the Functions called by the Constraints.

Sure, I didn't mean that your model was lacking those constraints, just
that in my variant they could also be easily enforced.

Relationships:

1. Each Person invents 0-N Problems
2. Each Problem has 0-N ProblemStakeholders
3. Each ProblemStakeholder is one of JointComposer or Solver

Fine.
You need:
__ ProblemStakeholder needs a reference for the StakeholderId FK
__ 4. Each Person claims 0-N ProblemStakeholders

Erect the model and have a good look. Although it is fine, and it
works, it is just not straight-forward (a desirable element in logic).

Rather than fixing-up the Bridge model, which had a specific
argumentation purpose, I would erect a logically straight-forward
model. Ends up with fewer Facts.

____ https://www.softwaregems.com.au/Documents/Article/Normalisation/Bridge%202.pdf

You may update the rendition of my version using exclusive subtyping,
because that is what I meant. Then, Solver_Not_Composer, Solver_Not_JointComposer, and JointComposer_Not_Composer all reduce to
a single check in StakeHolder.

Regarding your version, I am not sure what Composer_Primary_Max_! and, especially, Composer_Secondary_Max_! mean. I believe that the former
should enforce one ("max" = at most? Shouldn't it be "exactly"?)
primary composer for each problem.

In reply, I would change it as follows (using parentheses, and / as
a separator between the primary key and other attributes):

Person(PersonId / LastName ...)
Problem(ProblemName / Score ...)
ProblemStakeholder(PersonId ProblemName / Role)
Composer(Composer.PersonId ProblemName)
Solver(Solver.PersonId ProblemName / Solution Date)

Relationships:

Each Person acts as 0-N ProblemStakeholders
Each Problem is built around 1-N ProblemStakeholders
Each ProblemStakeholder is an excl. basetype, one of {Composer, Solver}

Additional constraints:

1. A problem must have at least one [exactly one] primary composer.
2. A problem can have at most M composers.
3. A problem can have at most N solvers.

Here, I am assuming that Role ranges over {primary,secondary,solver}.

This is slightly less straightforward, but it would allow me to record additional information about problem stakeholders regardless of their
role.

Nicola

--- Synchronet 3.21d-Linux NewsLink 1.2