From Newsgroup: comp.databases.theory

where the RFS was physical only (and therefore the
pointers are physical), the RM is entirely logical.
[rCa]
We do not create a model of what
we want, because that is limited to what we understand based on the
app that we are trying to build. That is one of the biggest and
common mistakes that the OO/ORM crowd make. It leaves them with no understanding of the data, as data.
[rCa]
There are huge advantages in modelling the data, as data, and nothing
but data, as extracted from the perception of the real world. - That,
and only that, makes your model immune to [structural] change.
[rCa]
Eg. All definition of the data must be in the database, not outside.
That means all constraints

Not sure you've appreciated the irony in my responses. But you make good points, with which I agree entirely. I'd add that too often too much
emphasis is put on making the life of developers easier, regardless
of the consequences for the users, and this trend, unfortunately,
encompasses the whole computer industry.

So, a qualifying question, using the simplest DDL, so that the
issue is exposed and not detracted from: in this RDM (which is 80%
complete, so please do not argue that it is not perfectly
Relational, we know it is not)

http://www.softwaregems.com.au/Documents/Documentary%20Examples/Order%20DM%20Advanced.pdf
Keyword
Part
PartDescription
Object_V (View with computed columns)
Do you understand that Object_V.Description is the concatenated
list of Keywords, that describes a PartCode ? That is derived by
calling a recursive Function, eg. Part_Description_fn( PartCode ).

Just a request for clarification: such function should concatenate
the keywords associated with the provided PartCode, or those keywords
*plus* the keywords associated to any (sub)component of the provided
PartCode?

What ? What has a component or sub-component have to do with it ?

Your model has an Assembly entity, so I was wondering whether the
function would traverse the part-component hierarchy (which would
require recursion) to retrieve the keywords associated to the components
of a given part, too.

To get the keywords directly associated with a given PartCode X, I would
not use recursion:

select PartCode, string_agg(Keyword, ', ' order by Keyword)
from PartDescription
where PartCode = X
group by PartCode;

(The app should not (does not need to) access tables, that would be
very silly. It should access Views.)

Yes.

This will probably be the subject of questions, so let me take it from
the top, if you do not mind. This is the second of the three types of Hierarchies in the RM. The example is the Unix Node (file & directory hierarchy), the data is: http://www.softwaregems.com.au/Documents/Tutorial/Recursion/Hierarchy%20Inline.pdf

The data model is: http://www.softwaregems.com.au/Documents/Tutorial/Recursion/Directory%20DM%20Inline.pdf

We need to produce the Path, as a column. Perfect candidate for
a Function, that is recursive.
Node_Path_fn (
NodeNo, -- Starting point
ReturnNodeNo -- Boolean: if set, return CSV NodeNos,
else return "/" separated list of Node.Names
)
Returns Path ( CHAR(255) not TEXT) -- list of NodeNos/Node.Names

Yes, for that I'd use a recursive function, which in PostgreSQL I'd code
as follows, modulo some omitted details:

create function Node_Path_fn(_NodeNo integer)
returns char(255)
language sql as
$$
select case
when ParentNodeNo = _NodeNo then '/'
else Node_Path_fn(ParentNodeNo) || Name || '/'
end
from Node
where NodeNo = _NodeNo;
$$;

The same Function is used in the CONSTRAINT to CHECK that the NodeNo attempted on INSERT is not in the list of ancestors (circular
reference).

Mmh, no. A newly inserted node cannot be in the list of its ancestors.
Such constraint would be useful to avoid circularity on UPDATEs.

If you want to prevent multiple roots (i.e., more than one record with
NodeNo = ParentNodeNo), I would define a separate constraint.

Simple and straight-forward in SQL.

Yes.

If PusGrossNONsql has no recursion,

It does. Both recursive functions and recursive queries. And yes, we can
use them.

In SQL, the latter (either the Assembly tree, or the Component tree)
is serviced by a Stored Proc, not a Function. Last time I looked at
the bastard grandson of Stonebraker, it did not have Stored Procs.

They were introduced very recently:

https://www.postgresql.org/docs/12/sql-createprocedure.html

You are right that functions are used for both purposes in PostgreSQL
and they can return anything, from nothing to tables.

In normal SQL, the Assembly or Component tree would be produced by
a recursive Stored Proc that contains a single SELECT. With full
indentation to show the levels in the tree, or a LevelNo which is
derived, etc. Executes itself until it reaches the leaf level across
all branches.

You'd achieve that in PostgreSQL with a function returning a table.

Second option: just code a single pure SELECT, in a WHILE (forever)
loop that traverses the tree.

I prefer to avoid this kind of coding, except when it is needed because
it provably improves performance.

You have asked to keep the content of these posts to the subject, but
this "qualifying test" is a digression. Please move it to a different
thread if you want to continue discussing it.

The main question of this thread is (a genuine question of mine): How do
you identify a movie?

Nicola

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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

That was a great walk down memory lane. Thank you !

Thanks for the links. Bookmarked.

I beg to disagree. Don't generalize. Academic work is not different from
any other human endeavor: many people do it, some do it well, only a few
are exceptionally good at it. But let's not get sidestepped: your
opinion about academia is widely known in this group.

Labelling a person and attacking the person is a lame, /ad hominem/
attack,

I didn't mean that as a lame personal attack, sorry if it sounded so.
I was just trying to limit the scope of this thread, into which we have
already mixed database history, concurrency control, general questions
about hierarchies, etc. (my fault, too). Nothing against discussing
a wide range of topics, but please let's stick to one topic per thread.
Time is limited for everyone (especially now that holidays are over),
and having to discuss several topics at once may become infeasible for
me.

I am still replying to your comments below, but it may become too time-consuming for me to keep doing so. So, again, nothing personal, but
let's move tangential topics, like you did for the PostgreSQL thread.

Name one single articulation of RM (Not RM/T) concepts that any
academic since Codd (fifty years and counting) has written.

I think that, whatever I name, you won't accept it :) Off the top of my
head: Datalog.

But I must agree that most database theory has remained such. Besides,
I'd say that research on database theory has declined sharply since the
'90s, or maybe even earlier. AFAICS, current research in the database
field is primarily related to database *systems* (e.g., physical
optimization).

ERD [...] is totally inappropriate

Agreed.

Its bastard child, its resurrected ghost, is Postgres*NON*sql.

The natural child being Sybase, I guess, given that it was made by
members of the Ingres' team :)

Not sure what you mean.

I meant PusGross is the bastard of Ingres.

Sybase is the natural child of Britton-Lee, a genuine pre-Relational
database machine that competed against IBM, Cincom, etc.

I quote from "Readings in Database Systems", Fourth Edition, p. 98:

"Perhaps the most commercially successful and technically impressive of
these systems was from a company called Britton-Lee, which was founded
by a number of alumni from the INGRES research group."

Nicola

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On 2020-01-09, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:

I do not wish to limit what you say. But at least for part of
evaluating this submission, please play the role of a real world archivist/librarian/cataloguer.

http://www.softwaregems.com.au/Documents/Article/Database/Movie%20Title/Movie%20Title%20Progression%20V0_1.pdf

---------------------------------------
User Progression (Early Workflow)
---------------------------------------

Register Collection
Register Items, which are Undetermined
Physical properties ... leading to logical properties
As and when the Realisation is determined ("abstraction" is identified), register Realisation
As and when the Concept is determined ("abstraction" is identified), register Concept

That's fine: archivists usually start from the physical media, sometimes without even knowing what it contains. So, working "bottom up"
definitely makes sense. The distinction between determined and
undetermined items is also sensible, from such perspective. The bottom
part of you diagram looks good to me so far.

Variant is a many-many association: that's ok. It's interesting that you
are taking the stance that it is an association between Realisations
rather than Concepts, so moving it down at the concrete level rather
than keeping it at the intellectual level, contrary to what FIAF, FRBR,
etc. do. This is debatable: probably, yes, you first recognize Variants
between Realisations, but later you may want to promote them as
different (abstract) Expressions of the same Concept. Note that FRBR
uses the term Expression rather than Variant (as you say, naming is very important).

I have more comments on that, but I have run out of time.

Nicola

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On 2020-01-09, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:

On Thursday, 9 January 2020 23:17:06 UTC+11, Nicola wrote:

On 2020-01-09, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote: >>

[...]

I have more comments on that, but I have run out of time.

I will wait for the rest before responding.

Well if you're going into all those aspects, then I will give you what
I have. I previously gave you just the introduction to the basic
structure, because it IS different to FRBR; FIAF; etc, and i I thought
that THAT would be the focus of the initial discussion.

http://www.softwaregems.com.au/Documents/Article/Database/Movie%20Title/Movie%20Title%20Progression%20V0_2.pdf

Non-Identifying relations are not shown (clutter at this stage, next
stage is choosing Keys, relevant after that).

I am showing the following as separate SubjectAreas:
Agent ... Person ... Corp ... Collective
Country ... Address

Ok, that's much more that I have asked for.

One question is whether establishing Variants can be done without an overarching Concept already established. If that weren't the case (as
I believe), probably Variant should be moved under Concept Realisation.
As the model stands now, you may relate Variants of Realisations of
distinct Concepts.

In your model, would the FIAF example we have talked about:

Dracula (USA, 1931, Tod Browning, Spanish and English)

be a single instance of Concept, with two instances of Realisations (the Spanish and English versions)? That would make sense to me, given the circumstances in which it was filmed.

As another example (p. 29 from FIAF manual) various re-edited releases
of Blade Runner (1982, 1986, 1992, 2007) would be different Realisations
of the same Concept, each a Variant of another (or of the others),
right?

But then, each such Realisation would have also have different
"manifestations" (using the FIAF term for now): theatrical releases,
VHS, DVD, Blurays, etc., each with their own technical specifications
and possibly other minor differences, e.g., subtitles, extra dubbing,
different titles, etc.) It seems to me that currently your model cannot accommodate for those, unless you consider each and every change a new Realisation. I'd say that you need another entity between Realisation
and Item ("Medium/Media"?).

Which would make the backbone of your model a four-level hierarchy as
the original (Concept -> Realisation -> "Medium" -> Item), but with
a few important differences:

- only one "intellectual" entity (Concept); everything else is backed by
something concrete (this would remove the confusion existing between
Work and Variant in the FIAF manual);
- all entities (possibly except for "Medium") are independent (well, the
FIAF manual does not mention this explicitly, but I'd say that it
implies that the only independent entity is the top-level Work);
- Variant is a fifth key entity, and is an associative entity at the
"concrete", not "intellectual", level.

Do you agree to the above?

Nicola

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

Derek,
quick reply on only a couple of the points you raise. I will come back
on the rest later.

On 2020-01-10, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:

I would ask, what is the precise distinction between Item and the
proposed Medium ? It seems to me to be 1::1.

You must distinguish between a particular edition of a movie (e.g., the
Final cut's version of Blade Runner distributed by, say, Warner Bros in
2007 on DVD featuring also extra material such as interviews with the
actor and backstage footage), and the single items of that edition (my
copy of the DVD and your copy of the DVD). The former is a Manifestation
in FIAF's terminology and the latter is an Item. Such distinction is
important.

Each Realisation may have many editions through time and on different
media. And, in general, the single physical items of a particular
edition (my copy of the DVD and your copy of the DVD) may have different characteristics for some reason, which I may want to record (e.g., the
state of conservation of the physical disc, the fact that my copy has
a torn cover while your copy is still intact in its envelope, etc.).

Item is no longer the FRBR/FIAF Item. For a better name for Item,
would you prefer Product, or Article ?

With the distinction above, Item would retain its meaning. So, perhaps:
Edition and Item.

Concept -> Realisation -> Edition -> Item
| |
Variant

Nicola

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On 2020-01-10, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:

One question is whether establishing Variants can be done without an
overarching Concept already established. If that weren't the case (as
I believe), probably Variant should be moved under Concept Realisation.

(I have already responded to that, awaiting your response.)

No need for change, at least for now. I agree that it's not the main
focus of the discussion.

As the model stands now, you may relate Variants of Realisations of
distinct Concepts.

Yes. That is unlikely, but possible. There are several ways to
prevent that

Sure.

But do not let that subtract from the discussion (that Variant is more correct under Concept or ConceptRealisation).

Ok.

General point. [Details...]

Agreed.

So why is the finished product (one DVD or one movie reel) not an Item
? (We need to go beyond the definitions in the FRBR/FIAF docs, to
take them only as ambiguous and confused, unresolved, considerations.)

I think I have clarified that in my previous post. The finished product
(the Product, or the Edition) may exist in multiple physical copies (the Items).

At this point we could say, oooh. A dubbing in another language (with
no other changes) is not a separate Realisation, but a second Item.

A second Product/Edition, yes. E.g., the Final cut version of Blade
Runner (Realisation) may be distributed as a Product/Edition (in
English) in the US (as many physical Items that you can buy at a store)
and as a second Product/Edition (dubbed in Italian, French, Spanish,
German) in Europe.

Which makes a hell of a lot more sense than the FRBR/FIAF definition.

Yes.

I would go so far as to say, they can edit as much as they like, but
unless they actually create a finished product (an Item) all those
edits are irrelevant to us (they are relevant to the production
company; their inventory; their workflow).

Yes.

I would ask, what is the precise distinction between Item and the
proposed Medium ? It seems to me to be 1::1.

Answered before.

To sum up this point.
- Realisation is the fullness of a defined project; a production, that
did take place. It is concrete but excludes the product (hah, I just
figured out a better name for Item !)
- Item (new name Product) is the finished product, actually the
product of post-production.
- Item is classified by Medium (not what you meant, but a simple
reference table).

Fine. I interpret "Item" in the last sentence as Product/Edition.

Dracula would still be two Realisations because (a) they are two
languages (b) two separate takes (productions done at the same time).
With one Item (Product) each. If an Item comes out in three different
media, that would be three Items.

Agreed, with "Item" replaced by "Product"/"Edition". Besides, that
Dracula would be one Concept (it's basically the same script)rCowhich,
again, makes way more sense than considering it two distinct Works as
FIAF suggests.

Blade Runner would still be four Realisations, with one Item (Product)
each.

Yes.

Which would make the backbone of your model a four-level hierarchy as
the original (Concept -> Realisation -> "Medium" -> Item),

Yes. And the backbone is visually rendered by Identifying relations,
the Keys, the solid lines.

but with
a few important differences:

- only one "intellectual" entity (Concept); everything else is backed by
something concrete (this would remove the confusion existing between
Work and Variant in the FIAF manual);

No kidding. Purposely done.

- all entities (possibly except for "Medium") are independent (well, the
FIAF manual does not mention this explicitly, but I'd say that it
implies that the only independent entity is the top-level Work);

If /Independent/ has the normal meaning, not the RM/IDEF1X meaning,
yes. But why is independence relevant ?

If /Independent/ has the RM/IDEF1X meaning, no. Only Concept
& Realisation are Independent.

I mean independent in the IDEF1X sense. In your model (Movie Title
Progression V0_2) Item is independent, (Item Determined is not).

- Variant is a fifth key entity, and is an associative entity at the
"concrete", not "intellectual", level.

Associative at the concrete level, yes.
Not sure what you mean by the rest. What is a "fifth key entity":

I meant a fifth entity to the four.

All this reasoning makes sense to me. I wait for your answer re the Product/Edition thing. If we converge on that, I think we can finally
start talking about the keys.

Nicola

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On 2020-01-10, Nicola <nicola@nohost.org> wrote:

Blade Runner would still be four Realisations, with one Item (Product)
each.

Yes.

Correction: Blade Runner would still be four Realisations, with one *or
more* Products each.

One of those Products may be considered "the master" (the 42 reels
etc.), but I wouldn't insist much on such a notion, because there are
cases (mostly outside the feature film context) where it's difficult or
even impossible to identify a "master" product. That might be an
additional, optional, fact. As one might add additional facts to relate Products among each other.

Nicola

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On 2020-01-10, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote: >Please confirm

- the ER diagram I sent you is /semantic/
- you can //read// all the semantics from the diagram

Yes and yes.

Concept. In V0.2 a Realisation can be registered without having
determined its Concept. My understanding has progressed. Is that
realistic ? When would a Realisation NOT have a determined Concept
? Even including the non-feature film registry.

I don't have a definitive answer. The very term "realisation" prompts
the question: a realisation... of what? I think you can make
a Realisation subordinate to a ConceptrCoperhaps one that is described as tentative/draft/not validated.

On Saturday, 11 January 2020 03:37:11 UTC+11, Nicola wrote:

I've taken all your comments into account, the next iteration is
ready. I don't have the words in response. Tomorrow. Since we are
flipping night-day, instead of losing a cycle, here it is.
- I have changed Concept to Story
- since we are ready for Keys, I have included the elements for it.

http://www.softwaregems.com.au/Documents/Article/Database/Movie%20Title/Movie%20Title%20Progression%20V0_3.pdf

* Realisation.PK ( Country, Creator, Year, Title )

That wouldn't be enough for the Dracula movie, unless Title doesn't
include some differentiating element (e.g., the primary key of Title
includes a TitleComment attribute):

Country Creator Year Title TitleComment
US Tod Browning 1931 Dracula [English cast]
US Tod Browning 1931 Dracula [Spanish cast]

TitleComment would be an empty string for most movies.

Titles are complex (see FIAF, -o1.3.2 and App. A). You have Alternate
Title at the Realisation level, but maybe you need something similar at
the intellectual level. E.g., if you're recording data about Russian or
Chinese movies, perhaps you want the transliterated title along with the
one in the original alphabet.

Other comments:

- Country: although determining "the" country of a movie may be
difficult (think multi-national productions), FIAF gives several
criteria (p. 39rCo41) to choose one. So this attribute seems fine.
- Creator: that may be individual or collective. Ok.
- Year: let's say it's ok, but keep in mind FIAF, -o1.3.4. Quoting:

"For Works, the date is typically related to events such as its
creation, availability (i.e., publication, release, distribution,
broadcast or transmission) or registration (e.g., for copyright or
intellectual property purposes)."

And also:

"a Work may have a production date of 1962, a copyright date of
December 1963, and a first release date of January 1964."

So, which year is that? A conventional one? Or would you qualify it
with a DateType?

* Realisation.PK ( Language, Creator, Year, Title )

Language as part of the primary key is questionable, IMO. There are
silent movies, movies in multiple languages, etc. Country is better: at
least there are already detailed directions from FIAF on how to choose
one.

Nicola

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On 2020-01-14, Nicola <nicola@nohost.org> wrote:

Thus Title, wherever it is stored, is Identified as [ Language,
TitleReduced ]

And script? How would you distinguish "-E-+-|-+-i-|-+-| -a-#-|-e-+-c-|-|" and "Alenkiy tsvetochek"?

Sorry, forget this. Lack of coffee struck back.

Nicola

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On Tuesday, 14 January 2020 21:51:51 UTC+11, Nicola wrote:

Ok. After reflecting on how Titles and Stories should be related and
comparing your v0.3 and v0.4 models, I think that you should reintroduce
an independent Title entity in v0.4.

On a second thought, no, I agree that Titles should be modelled as
dependent entities.

In V0.4, the Predicates touching Story are as follows.

I understand those.

Taking each in turn:

0. reintroduce an independent Title entity

The predicate for that is false. A Title does not exist
independently. It exists only because a Story [with that Title]
exists.

Agreed.

1. A Title identifies (is the "main"/"reduced" title of) of 0-N Stories.

Here I did not explain clearly: what I meant to say is that the Title
entity is identifying for Story (i.e., Story is dependent on Title). But
we have already agreed that the opposite makes more sense.

A ( Language, TitleReduced, Creator, Year ) identifies 1 Story

Ok, that's your current choice for the PK. We'll come back to that.

2. A Story has one identifying/main/reduced title.

Each Story is primarily identified by ( Language, TitleReduced, Creator, Year )
Each Story is known as 1-to-n StoryTitles
One StoryTitle is the TitleReduced in long form

By TitleReduced, do you mean with less attributes (e.g., without
the specification of the title's language?). Also, the language of
a Story and the language of a title are distinct, so perhaps we could
call the latter TitleLanguage for clarity.

3. A Title may be an Alternate Title;

The /rest/ in StoryTitle are Alternate Titles

Ok.

4. A Story has zero or more Alternate Titles.

Yes. The 1 in the /1-to-n/ is the main Title un-reduced.

Ok.

Nicola
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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

Nicola

On Wednesday, 15 January 2020 20:59:15 UTC+11, Derek Ignatius Asirvadem wrote:

[...]

Language again. Title is dependent on Language. So wherever Title
is stored, the Atom must be [ Language, Title ]. Which is why I had
Language not Country in the PK. Response please.

Before responding, I would ask that you obtain familiarity with SQL
language and CharacterSet implementation, both server-side (storage)
and client-side (representation). The concept of Locales.

( I don't know how the NONsql suites do it, and I really don't want to
know. I trust you will agree, since we are working with the RM, not
the pig poop marketed as "relational", we should concern ourselves
with the data sub-language that is defined in the RM: SQL, and not
anything else. As always, the high-end suppliers implemented language
and charset support decades before the SQL Standard defined it.)

Note also, my design for Keyword. I allow Keywords to be specified for:
a. { Concept | Realisation }, and
b. { Concept | Realisation }.Title, and
c. I ensure they are not repeated for a given { Concept | Realisation }.

That's fine.

Since Title is Language dependent, any Keyword that is related to
a Title will thusly be Language dependent. Default Locale, etc.

Again, I believe that you are making too strong assumptions about
titles.

Nicola

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On 2020-01-25, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote: >===================

This post introduces V0.6
===================

http://www.softwaregems.com.au/Documents/Article/Database/Movie%20Title/Movie%20Title%20Progression%20V0_6.pdf

A few thoughts:

- Does Concept need to be identified by Country? Couldn't that just be
[TitleReduced, Creator, Year]? Each Realisation of a Concept, but not
the Concept itself, is "produced by" a (principal) country. The
Concept itself is a creation of its Creator at some point in time.
How about:

Concept [TitleReduced, Creator, Year] -> Title
Realisation [TitleReduced, Creator, Year, Differentiator] -> Country

?

- Besides, I would remove Title from Concept, because that information
belongs to ConceptTitle. A ConceptTitle can additionally provide all
the required information about each title.

- Most of the non-identifying relations you have marked with L do not
need to be explicitly modelled, because, I think, they are implied by
the various referential integrity constraints.

What does "SG" stand for in "SG Relational Notation"?

In the same way that Edition is a manifestation of 1 RealisationTitle
(not Realisation), and therefore a single Language, in this model,

Ok.

I expect to clarify whether Realisation is rendition of 1 ConceptTitle
(not Concept), and therefore a single Language.

I'd lean towards answering affirmatively, if we regard a movie dubbed in
a different language as a different Realisation.

Nicola

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On 2020-01-25, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:

Just as
Table[ Key ] raA Attribute
If 3NF/"Full" FD is understood AND a Relational Key is used:
Table[ Key ] raA Parent.Attribute

Sure, FDs are not necessarily intra-relational.

- Besides, I would remove Title from Concept, because that information
belongs to ConceptTitle. A ConceptTitle can additionally provide all
the required information about each title.

Disagree. This is correct:
Concept [TitleReduced, Creator, Year] raA 1 Title (local attribute)
And
Concept [TitleReduced, Creator, Year] raA 0-to-n ConceptTitles (subordinate table)
Not 1-to-n as I have explained elsewhere.

Well, let it be then.

Ok, so we have:
Concept[ TitleReduced, Creator, Year ] raA 1 Title (local attribute)
Concept[ TitleReduced, Creator, Year ] raA 0-to-n ConceptTitles (subordinate table)
ConceptTitle[ TitleReduced, Creator, Year, Language_CT, TitleReduced_CT ] raA Language_CT[ Title ]

Where [TitleReduced_CT, Language_CT, Creator, Year] is also a key. Nice!

Realisation[ TitleReduced, Creator, Year [, Differentiator ], Language_CT, TitleReduced_CT ]

Ok.

Time for another one of those progression-of-Codd inventions that is fully-integrated-with-science-and-logic. That PK is getting silly,
because both TitleReduced and TitleReduced_CT have a Language. Title
is duplicated, only one is required. Resolving all that, this becomes
the Primary Key:
Realisation[ Language_CT, TitleReduced_CT, Creator, Year [, Differentiator ] ]

Fine, by migrating an AK (this is one aspect where the IDEF1X standard
should relax its rules, IMO).

That's nice, because each Realisation is naturally identified by its own
title, even if they realise the same Concept.

Nicola

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On 2020-01-27, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:

Nicola

===================
This post introduces V0.7
===================

http://www.softwaregems.com.au/Documents/Article/Database/Movie%20Title/Movie%20Title%20Progression%20V0_7.pdf

That looks good to me.

Nicola
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On 2020-02-17, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:

Nicola

===================
This post introduces V0.9
===================

I need a few days before coming back to this at full throttle. Life
sometimes gets in the way.

At a quick glance: Agent is an interesting case, because apparently it *requires* the use of a surrogate (AgentNo)...

Nicola

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On 2020-02-20, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:

On Thursday, 20 February 2020 07:20:34 UTC+11, Nicola wrote:

On 2020-02-17, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:

When you do come back, please evaluate just the last model, eg. V0_10,
those in-between can be safely skipped.

Not yet there, but I'll answer a few points you have raised (for
feedback about the model, I need more time).

At a quick glance: Agent is an interesting case, because apparently it
*requires* the use of a surrogate (AgentNo)...

Yes, of course.

If you think I have declared surrogates are banned, period, that is
false. I have declared that surrogates are banned AS A STARTING
POIINT in the exercise of modelling a database that is intended to
comply with the /Relational Model/.

Good.

There are only two scientifically valid reasons for a surrogate (as
opposed to the anti-scientific, schizophrenic "reasons" of the Date;
Darwen; Fagin; et al Gulag). In both cases, because it is
a surrogate, it still constitutes a Relational Breach, specifically a.
a breach of the Independent Access Path Rule. It cuts of access from
the tables below the breach, to their genuine ancestors (above the
breach). b. and of course the Relational Key Normal Form.

Agreed.

The surrogate breaks the natural Hierarchy, and creates a new Hierarch
(in my IDEF1X models, I show this visually, because I render all
Hierarchies visually, vertically). Please feel free to discuss in
detail.

That's clear to me, both the fact that surrogates force you to
"navigate" a model and that a hierarchical visual layout is a good idea.

For the former, I recently had to evaluate an "optimization" of
a somewhat acceptable Relational model for an ideal telephony company,
which consisted in adding an ID attribute to almost every table ("so
that less attributes have to be stored several times"). As
a consequence, queries that would require joining two tables ended up
requiring joining six tables, and inserting data about each new call
passed from a single INSERT to a transaction with a few SELECTs and one
INSERT.

For the latter, circularity in a data model is undesirable for several
reasons (by circularity I mean cyclic referential integrity constraints,
i.e., a FK from R1 to R2, a FK from R2 to R3, ..., a FK from Rn back to
R1):

1. conceptual: a child instance is a descendant of itself;
2. computational: problems such as implication of dependencies easily
become highly complex (even undecidable) in the presence of circular
dependencies;
3. pragmatic: an "egg and chicken" problem requires defining tables in
more steps (e.g., create table A, create table B with FK to A, add FK
from A to B), plus transactions with deferred constraints for
inserting data.

Such things are well studied. Circularity can always be eliminated with
minimal impact on the semantics of a model. Once eliminated, a data
model, viewed as a graph (nodes = entities, edges = associations), is
a DAG (associations are oriented from parent to child). So, it's only
natural to use a hierarchical layout to display such a model. I see that
you make finer distinctions, e.g., by drawing categorizations
(specializations) on the side, and that's fine, too. I am not sure
I understand your usage of colors, though.

I see that you don't eliminate circularity completely, because you
retain "self-loops" (FKs from an entity to itself). Such foreign keys
don't cause problems of type (3) above (and most likely not even
problems of type (2)), but they may be eliminated as well, and it's
often desirable to avoid the unnatural situation of having a tuple
reference itself. For instance, instead of:

Employee(EmployeeNo, Name, ManagerNo)

which forces at least one employee to be her own manager, one may
define:

Employee(EmployeeNo, Name)
Manager(EmployeeNo, ManagerNo)

1. The PK has become too wide to be used (migrated) throughout the hierarchy, and so a surrogate is /substituted/ for the PK. This is exemplified in Address and Person. I think you understand that usage.

Yes, that's a pragmatical (not logically necessary) use of a surrogate,
which is introduced out of convenience: a trade-off that should be
carefully evaluated, for the reasons mentioned above.

2. The table is a Basetype, and the Subtypes have quite different
PKs, such that a common PK cannot be established. Thus the common PK
in the Basetype is manufactured, it is a surrogate. Please feel free
to discuss this usage in detail.

That is the Agent case, where introducing a surrogate is necessary to be
able to reference heterogeneous entities types. That's a textbook use
case for surrogates, e.g., Elmasri and Navathe's book discuss it (they
call it a "union type", although I don't like the name).

Starting the exercise by stamping a surrogate on every file (they are
not tables) cripples the modelling exercise, the exposure;
determination; and deployment of discrete Facts.

Sure.

--------------------------------------
-- This post introduces V0.10 --
--------------------------------------

I'll come back to that.

As for the format, please know that I am not going to print anything
larger than A3. I don't commute, I am (still) comfortable viewing large
models on a big screen, and I can easily read (good) models split on
several pages, with "collapsed" entities (as you call them) or "shadow" entities (as the recent IDEF1X revision calls them) that reference
entities from different pages.

Nicola

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On 2020-02-20, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:

https://www.zerohedge.com/geopolitical/coronavirus-deaths-outside-china-spike-who-team-visits-wuhan

God bless you and your family

Thanks. Things will be quiet this week (universities, schools, gyms,
libraries and other public places are all closed).

I am coming back to discussing the model.

--------------------------------------
-- This post introduces V0.10 --
--------------------------------------
Table-Attribute http://www.softwaregems.com.au/Documents/Article/Database/Movie%20Title/Movie%20Title%20TA%20V0_10.pdf

First, some minor details:

- Typos:
- "Project Constranits" (bottom left);
- "Is Known As" is identifying (Concept -< ConceptTitle and Project -< ProjectTitle);
- "AK.i" (ConceptIdentifier).
- Year and/or TitleReduced are not marked as FKs (in bold) in
ConceptTitle, ConceptCharacter, and ConceptIdentifier.
- Key migrations:
- ComponentType not migrated into ProjectComponent
- missing migrated key for "Project Is Varied As 0-N Projects";
- ProjectTitle should include LanguageCode_P (or TitleReduced_P) in the key.
- ConceptTitle allows one Title per LanguageCode (in the scope of the
same Concept): I don't think that's intentional (see below).
- Others:
- KeywordPermitted is 1:1-N with both ConceptKeyword and
ProjectKeyword: I think that should be 1:0:N (a simple reason is
that a Concept may have no associated Projects).

The rest looks fine to me.

One issue with the term "Project" is that a full database model would
likely include "cataloguing projects", so the term is at risk of being overloaded. But for this thread I believe it's fine.

I want to propose a few changes to the model, but first I need a better
grasp on titles and languages (again!) as they are currently modeled.

First, when one populates the database, are "reduced" titles assumed to
be in their original language or in the cataloguer's language? For
instance, should an Australian cataloguer insert the title of a new
Concept "Godzilla" as (a) "Gojira" (in Japanese, I assume), (b) "pe|pe+pa-" (which *should* be the Japanese script for Godzilla, at least according
to Google Translate) or (c) "Godzilla" (in the cataloguer's language)?
Or doesn't it really matter?

I ask because there is no LanguageCode in Concept, so I assume you mean
(c). IMO, that's an unneeded restriction (why not adding LanguageCode?).

Second, according to IMDb, the movie "Gojira ni-sen mireniamu" is
translated into English as "Godzilla 2000" and also as "Godzilla 2000: Millennium". In my understanding, those would be two titles with the
same LanguageCode. So, shouldn't the key of ConceptTitle include
TitleReduced_C rather than LanguageCode?

So the question is this. While we are going back and forth with the modelling, can I give you just the whole model on one page ? Right
now it is A1.

If you work more comfortably on one page, sure.

Nicola

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On 2020-02-25, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:

- "Is Known As" is identifying (Concept -< ConceptTitle and Project -< ProjectTitle);

That is not a clerical level mistake. That is unchanged from V0.9.
So that is either a matter of understanding or modelling issue (not
an error) that needs to be progressed in the next iteration.

In case it is a matter of understanding. First, note that that /difference/ ie.
/Concept is known as ConceptTitle/ (Non-Identifying)
vs the other children of Concept being Identifying, was a conscious discussed choice.

Second, when the **full** PK
Concept[ Creator, Year, TitleReduced ]
is **not** used to Identify the child
ConceptTitle[ Creator, Year, LanguageCode, TitleReduced ]
the relation is Non-Identifying. Of course, the FK is migrated as normal:
/Concept[ Creator, Year, TitleReduced ] is known as ConceptTitle[ Creator, Year, TitleReduced_C ]/

That naming of the attributes has got me confused. Now, I've got it.
Ditto for Project/ProjectTitle.

- Others:
- KeywordPermitted is 1:1-N with both ConceptKeyword and
ProjectKeyword: I think that should be 1:0:N (a simple reason is
that a Concept may have no associated Projects).

The cardinality declared in V0.10 states, a Keyword will not be added
unless it has a child in { ConceptKeyword | ProjectKeyword }.>

...unless it has a child in ConceptKeyword **&** ProjectKeyword }. To
express "one or the other or both (but at least one)", you should make
the associations 0rCoN and add a footnote to explain that at least one association must hold for each keyword. The way I understand your model
is that you cannot add a Keyword unless it is used as both
a ConceptKeyword and as a ProjectKeyword.

One issue with the term "Project" is that a full database model would
likely include "cataloguing projects", so the term is at risk of being
overloaded. But for this thread I believe it's fine.

(I would much rather model a real world database than a purely
academic one for this thread. The former has a veracity that the
latter does not have, and the former covers the latter plus more, not
less.)

How about just... Movie (or Moving Image)?

First, when one populates the database, are "reduced" titles assumed to
be in their original language or in the cataloguer's language?

(The "un-reduced" Title is in the row, so the question applies to both
Title and TitleReduced.)

For Concept, in the cataloguer's Language (implicit).

IMO, that's an unneeded restriction (why not adding LanguageCode?).

Ok. Next iteration.

That means, instead of:
/Each Country produced 0-n Concepts/
/Each Country produced 0-n Projects/
we would have (more logical, less restricted; maintains intent):
/Each CountryLanguage produced 0-n Concepts/
/Each CountryLanguage produced 0-n Projects/
Much nicer.

Ok.

Second, according to IMDb, the movie "Gojira ni-sen mireniamu" is
translated into English as "Godzilla 2000" and also as "Godzilla 2000:
Millennium". In my understanding, those would be two titles with the
same LanguageCode. So, shouldn't the key of ConceptTitle include
TitleReduced_C rather than LanguageCode?

That is a discussion point, that leads to a decision, as opposed to
a mistake. The current intention is, we have just one ConceptTitle
(and ProjectTitle) in any one Language. So if we need multiple titles
per Language ... I would say, the determinant is ...

TitleType, and move that into the PK, rather than TitleReduced_C (the title itself).
ConceptTitle[ ?, ?, en, Original, Godzilla 2000 ]
ConceptTitle[ ?, ?, en, Alternate, Godzilla 2000: Millennium ]

Do you need LanguageCode in the key, though? I'd say that both
LanguageCode and TitleType are determined by the title.

Nicola
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On 2020-02-25, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:

- Others:
- KeywordPermitted is 1:1-N with both ConceptKeyword and
ProjectKeyword: I think that should be 1:0:N (a simple reason is
that a Concept may have no associated Projects).

The cardinality declared in V0.10 states, a Keyword will not be added
unless it has a child in { ConceptKeyword | ProjectKeyword }.>

...unless it has a child in ConceptKeyword **&** ProjectKeyword }. To
express "one or the other or both (but at least one)", you should make
the associations 0rCoN and add a footnote to explain that at least one
association must hold for each keyword. The way I understand your model
is that you cannot add a Keyword unless it is used as both
a ConceptKeyword and as a ProjectKeyword.

Discussion. I am trying to get you to appreciate higher meanings in cardinality when the DM is taken as a whole, not each relation in
isolation. What you are saying is perfectly reasonable in the latter perspective, but not in the former.

(Consider, no need to answer each /why/.)

The relevant predicates corresponding to your model (v.10) are:

1. Each KeywordPermitted recalls one or more ConceptKeywords;
2. Each KeywordPermitted recalls one or more ProjectKeywords.

At the end of a transaction inserting (in order) into:

- Concept
- ConceptTitle
- Keyword
- KeywordPermitted
- ConceptKeyword

would result in predicate (2) to be violated. Such a transaction should
be considered correct, though. Hence, the constraint imposed by the
model is too strict. That is easily fixed as you suggest below.

To
express "one or the other or both (but at least one)", you should make
the associations 0rCoN and add a footnote to explain that at least one
association must hold for each keyword.

Separate point. Generic answer. No. The correct way is to add
a pair of Non-Exclusive Subtypes for KeywordPermitted { Concept
| Project }.

Right. I stand corrected.

Second, according to IMDb, the movie "Gojira ni-sen mireniamu" is
translated into English as "Godzilla 2000" and also as "Godzilla 2000:
Millennium". In my understanding, those would be two titles with the
same LanguageCode. So, shouldn't the key of ConceptTitle include
TitleReduced_C rather than LanguageCode?

That is a discussion point, that leads to a decision, as opposed to
a mistake. The current intention is, we have just one ConceptTitle
(and ProjectTitle) in any one Language. So if we need multiple titles
per Language ... I would say, the determinant is ...

TitleType, and move that into the PK, rather than TitleReduced_C (the title itself).
ConceptTitle[ ?, ?, en, Original, Godzilla 2000 ]
ConceptTitle[ ?, ?, en, Alternate, Godzilla 2000: Millennium ]

Do you need LanguageCode in the key, though? I'd say that both
LanguageCode and TitleType are determined by the title.

I'd say that ...
LanguageCode ... are determined by the title.

1. From our previous discussions, LanguageCode has to go with a Title
where the Title may not be in the cataloguer's language.

2. How can LanguageCode be determined by theTitle ?
For uneventful presentation (any GUI, any platform ... Localisation),
I thought it is the reverse.

Upon reflection, yes, I am wrong. To properly track titles in multiple languages, LanguageCode must be part of the key.

I'd say that ...
TitleType are determined by the title.

Please explain. Context is of course, our previous discussion: that
there are more than one ConceptTitles in a particular Language for
a given Concept:

The title type is a discriminator for titles (a title is "original" xor "alternate" xor "preferred" ...).

Now I see that there's a v11. I'll continue the discussion there.

Nicola
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On 2020-03-05, Nicola <nicola@nohost.org> wrote:

some logistic issues due to the current situation have forced me to
focus on other matters and I have delayed my replies (health is good,
thanks God). But I have read them all.

Eh, of course: them = *your* replies.

Nicola
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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

Can't a country and a language be used to identify a culture? I don't
grasp what alternative you are considering.

Wherever we currently have a table that references { Language | Country},
we should instead reference CountryLanguage.

Language still gets me confused. A language is described by Language
(e.g., Russian) and CharacterSet (e.g., cyrillic). Defined this way, it
seems appropriate to be referenced, say, by Titles, which are written in
a certain language and character set. But why would a Culture or Person
need to be associated to a specific character set? That does not make
sense to me. Perhaps, if you tell me which ISO LanguageCodes are
supposed to be, I will understand better.

That said, what are you proposing? That (Concept|Movie)Titles should incorporate CountryCode? I would go a different route (see below).

2. More rigourous testing re the cascade of Keys, and the alternation
based on *Title, for the ConceptraAMovieraAEditionraAInstance data
hierarchy. The more precise data hierarchies as defined are:
Concept = Agent ... Concept
Movie = Language ... TitleType ... ConceptTitle
Edition = Language ... TitleType ... MovieTitle

I am not sure that I can communicate this via IDEF1X ... but I will
try (it is explicitly communicated in my IDEF1X Extension).
http://www.softwaregems.com.au/Documents/Article/Database/Movie%20Title/Movie%20Title%20TA%20V0_13%20Annotated.pdf

Sorry, I am not sure I understand the purpose of that annotation.

Pardon me.
1. Codd asks us to think in terms of Sets. Here the relevant Sets to be held in mind are the Domain of each Relational
Key.
2. The Data Hierarchies of course form the components of each Relational Key. >Therefore:
- for Concept, it appears (visually, leading to meaning) that the Data Hierarchy starts at Concept ...
--- No, it actually starts at Agent. This is clear if you inspect the Concept PK
- Movie is actually dependent (not in the IDEF1X sense, but existentially dependent) on ConceptTitle
--- the hierarchy for ConceptTitle starts at (a) Language, and (b) TitleType ... ConceptTitle is a "binary relation", a
cross-over between two hierarchies
- for Movie, the Data Hierarchy actually starts at Language, and then ConceptTitle
- for Edition, the Data Hierarchy starts at Language; another at TitleType; and then MovieTitle

I am considering some (perhaps too naive) changes. Please take a look at
this:

https://send.firefox.com/download/b2c409b72171dddf/#x_7fCElwXgWmgV2-MVWETg

Sorry, there's only so much time I can devote to writing posts in this
period. I hope the diagram is self-explaining. The biggest difference is
that I am putting Movie (now Moving Image) under Concept rather than ConceptTitle. I have not redrawn the whole model: what is not there is
assumed to be unchanged, except for obvious changes, e.g., to foreign
keys.

One thing to be noted (in your model as well in my my sketched changes): consider a Concept B which is a variant of Concept A. Then, B and
A cannot have a common ConceptTitle. That may be too restrictive. One
may assume that a variant "inherits" all the titles of the Concept it is
a variant of, but that may not be satisfactory either. Something to
think about (at least for me).

Ditto for Movie/MovieTitle.

(Oops, I just spotted an error. Movie is Independent, but shown as >Dependent. Calls for another iteration. I will wait until some of
these questions get resolved.)

Correct, but see above. I'd make Movie dependent on Concept.

Consider:
- In the FIAF manual, where the definitions are loose and floppy, and anything can be any or all of { Work | Variant | Manifestation | Item
}, they have Identifiers (our StandardType) on all four elements of
their four-level hierarchy.
- In our DM, where definitions are tight, I had implemented
StandardType (FIAF Identifiers) on the three levels of the hierarchy.
But our Concept is purely cerebral, not a realisation/manifestation,
which is our Project/Movie. So the question is, can there be a FIAF Identifier/StandardType for a Concept ? I suspect it is for material
things only, for Movie & Edition, not for [our definition of] cerebral things. But I am not sure, our Concept may well be a material thing
to them.

But it is absolutely not material in our model. AFAIK, the various
standard identifiers are for what we have called Movie or Edition
(Instances of course come with their own codes, barcodes, serial numbers
or whatever).

- I have gone through the FIAF Manual again, and could not resolve
this point. Over to you.

That's because what a "Work" is not clear. They sometimes say Work is "abstract", sometimes that it is "concrete". E.g., at p. 9, Work is
labeled as an "abstract entity". Then, at p. 224 you find this pearl:
-2moving image "works" are more easily conceptualized as concrete
entities-+.

- (Btw, that little exercise re-inforced our notion that our treatment
of Variant & Derivative is superior to theirs.)

I agree.

Another reason the DM is not complete is, I have not removed the
Nulls. Which absolutely have to be removed before I let a DM be
considered complete. It is one of those second-to-last steps.

Have your marked optional attributes in some way? I have assumed that
all attributes are mandatory.

As discusse, more Constraints, please. Especially difficult ones.

I'd like to resolve the above first.

I can appreciate that you are busy, with the conditions in Northern
Italy making life very difficult. If you have any time to respond to
this thread, this subject is the one I would like you to prioritise.

As I am walking through the DM, I am still finding little errors.
I was hoping that your walk-through, and reading off the Predicates as
you go, would have caught some of those.

Things were moving fast here (locking down, etc.) and we had to adapt
quickly, e.g., to giving remote classes. I have to focus mostly on that
now. Besides, working from home bears its own issues.

Nicola

--- Synchronet 3.21d-Linux NewsLink 1.2