The Forum for Discussion about The Third Manifesto and Related Matters

You need to log in to create posts and topics.

Codd's remainder

Quote from Dave Voorhis on July 5, 2019, 8:49 pm
Quote from p c on July 5, 2019, 8:32 pm
Quote from Dave Voorhis on July 3, 2019, 2:15 pm
Quote from p c on July 3, 2019, 1:39 pm
Quote from Dave Voorhis on July 3, 2019, 1:24 pm
Quote from p c on July 3, 2019, 1:20 pm
Quote from Dave Voorhis on July 3, 2019, 7:26 am
Quote from p c on July 3, 2019, 1:17 am

I have thought more than once that a class action suit might fly against the vendors who provide Insert to difference and call it join deletion.

Which vendors do that?

Can you provide specific examples of how their products do that?

You asked the same question years ago. This should be old hat. I remember digging through documentation from several vendors and quoting some of it exactly. I posted an example or two and you ended up acknowledging yes indeed what they described as join delete in fact required users to address the base operands directly. The vendors included all the big names like Oracle, IBM, Microsoft. I'm not going to go through that again. It is not my problem if you do not understand how that breaks the correspondence between the algebra and formal logic and the potential damage that could result.

The only products I recall discussing implementation of update-through-views are Microsoft SQL Server and Microsoft Access, both of which allow deletion through a join in some cases but not others, under perfectly reasonable and documented cases. I see nothing there that warrants a "class action suit", do you? (And if there was, who would launch it and why?)

All of the big names in database do the same thing. They require the names of the pertinent Base relations or tables to be given explicitly. In other words they are talking about update through base not update through join. Why is that difference so hard for you to understand? The most Elementary understanding of data Independence should tell you that only the name of the join view should need to be mentioned. What those vendors do is worse than false advertising because it has brainwashed I generation of coders who probably number in the Millions.

I'm sure all the big names do the same thing; the only discussions I recall were based around documentation for and experiments with MS SQL Server and MS Access.

I'm not clear what you think is hard for me to understand. The distinction between update-through-views and updates to base tables?

That I understand well. What I don't understand is what you are arguing for. What "join view" should need to be mentioned?

Do you mean something like the PartsSuppliers view in the following?

VAR MySuppliers REAL RELATION {SupplierID CHAR, Name CHAR, Address CHAR} KEY {SupplierID};

VAR MyParts REAL RELATION {PartID CHAR, SupplierID CHAR, Description CHAR} KEY {PartID};

CONSTRAINT MyParts_MySuppliers  MyParts {SupplierID} <= MySuppliers {SupplierID};

VAR PartsSuppliers VIRTUAL  MySuppliers JOIN MyParts;

INSERT MySuppliers RELATION {
  TUPLE {SupplierID 'S001', Name 'Dave', Address 'Derby'},
  TUPLE {SupplierID 'S002', Name 'Bob', Address 'London'}
};

INSERT MyParts RELATION {
  TUPLE {PartID 'P001', SupplierID 'S001', Description 'Screws'},
  TUPLE {PartID 'P002', SupplierID 'S001', Description 'Nails'},
  TUPLE {PartID 'P003', SupplierID 'S002', Description 'Screws'}
}; 
  1. VAR MySuppliers REAL RELATION {SupplierID CHAR, Name CHAR, Address CHAR} KEY {SupplierID};
  2. VAR MyParts REAL RELATION {PartID CHAR, SupplierID CHAR, Description CHAR} KEY {PartID};
  3. CONSTRAINT MyParts_MySuppliers MyParts {SupplierID} <= MySuppliers {SupplierID};
  4. VAR PartsSuppliers VIRTUAL MySuppliers JOIN MyParts;
  5. INSERT MySuppliers RELATION {
  6. TUPLE {SupplierID 'S001', Name 'Dave', Address 'Derby'},
  7. TUPLE {SupplierID 'S002', Name 'Bob', Address 'London'}
  8. };
  9. INSERT MyParts RELATION {
  10. TUPLE {PartID 'P001', SupplierID 'S001', Description 'Screws'},
  11. TUPLE {PartID 'P002', SupplierID 'S001', Description 'Nails'},
  12. TUPLE {PartID 'P003', SupplierID 'S002', Description 'Screws'}
  13. };
VAR MySuppliers REAL RELATION {SupplierID CHAR, Name CHAR, Address CHAR} KEY {SupplierID};

VAR MyParts REAL RELATION {PartID CHAR, SupplierID CHAR, Description CHAR} KEY {PartID};

CONSTRAINT MyParts_MySuppliers  MyParts {SupplierID} <= MySuppliers {SupplierID};

VAR PartsSuppliers VIRTUAL  MySuppliers JOIN MyParts;

INSERT MySuppliers RELATION {
  TUPLE {SupplierID 'S001', Name 'Dave', Address 'Derby'},
  TUPLE {SupplierID 'S002', Name 'Bob', Address 'London'}
};

INSERT MyParts RELATION {
  TUPLE {PartID 'P001', SupplierID 'S001', Description 'Screws'},
  TUPLE {PartID 'P002', SupplierID 'S001', Description 'Nails'},
  TUPLE {PartID 'P003', SupplierID 'S002', Description 'Screws'}
};
  1. VAR MySuppliers REAL RELATION {SupplierID CHAR, Name CHAR, Address CHAR} KEY {SupplierID};
  2. VAR MyParts REAL RELATION {PartID CHAR, SupplierID CHAR, Description CHAR} KEY {PartID};
  3. CONSTRAINT MyParts_MySuppliers MyParts {SupplierID} <= MySuppliers {SupplierID};
  4. VAR PartsSuppliers VIRTUAL MySuppliers JOIN MyParts;
  5. INSERT MySuppliers RELATION {
  6. TUPLE {SupplierID 'S001', Name 'Dave', Address 'Derby'},
  7. TUPLE {SupplierID 'S002', Name 'Bob', Address 'London'}
  8. };
  9. INSERT MyParts RELATION {
  10. TUPLE {PartID 'P001', SupplierID 'S001', Description 'Screws'},
  11. TUPLE {PartID 'P002', SupplierID 'S001', Description 'Nails'},
  12. TUPLE {PartID 'P003', SupplierID 'S002', Description 'Screws'}
  13. };
VAR MySuppliers REAL RELATION {SupplierID CHAR, Name CHAR, Address CHAR} KEY {SupplierID};

VAR MyParts REAL RELATION {PartID CHAR, SupplierID CHAR, Description CHAR} KEY {PartID};

CONSTRAINT MyParts_MySuppliers  MyParts {SupplierID} <= MySuppliers {SupplierID};

VAR PartsSuppliers VIRTUAL  MySuppliers JOIN MyParts;

INSERT MySuppliers RELATION {
  TUPLE {SupplierID 'S001', Name 'Dave', Address 'Derby'},
  TUPLE {SupplierID 'S002', Name 'Bob', Address 'London'}
};

INSERT MyParts RELATION {
  TUPLE {PartID 'P001', SupplierID 'S001', Description 'Screws'},
  TUPLE {PartID 'P002', SupplierID 'S001', Description 'Nails'},
  TUPLE {PartID 'P003', SupplierID 'S002', Description 'Screws'}
};
  1. VAR MySuppliers REAL RELATION {SupplierID CHAR, Name CHAR, Address CHAR} KEY {SupplierID};
  2. VAR MyParts REAL RELATION {PartID CHAR, SupplierID CHAR, Description CHAR} KEY {PartID};
  3. CONSTRAINT MyParts_MySuppliers MyParts {SupplierID} <= MySuppliers {SupplierID};
  4. VAR PartsSuppliers VIRTUAL MySuppliers JOIN MyParts;
  5. INSERT MySuppliers RELATION {
  6. TUPLE {SupplierID 'S001', Name 'Dave', Address 'Derby'},
  7. TUPLE {SupplierID 'S002', Name 'Bob', Address 'London'}
  8. };
  9. INSERT MyParts RELATION {
  10. TUPLE {PartID 'P001', SupplierID 'S001', Description 'Screws'},
  11. TUPLE {PartID 'P002', SupplierID 'S001', Description 'Nails'},
  12. TUPLE {PartID 'P003', SupplierID 'S002', Description 'Screws'}
  13. };
VAR MySuppliers REAL RELATION {SupplierID CHAR, Name CHAR, Address CHAR} KEY {SupplierID};

VAR MyParts REAL RELATION {PartID CHAR, SupplierID CHAR, Description CHAR} KEY {PartID};

CONSTRAINT MyParts_MySuppliers  MyParts {SupplierID} <= MySuppliers {SupplierID};

VAR PartsSuppliers VIRTUAL  MySuppliers JOIN MyParts;

INSERT MySuppliers RELATION {
  TUPLE {SupplierID 'S001', Name 'Dave', Address 'Derby'},
  TUPLE {SupplierID 'S002', Name 'Bob', Address 'London'}
};

INSERT MyParts RELATION {
  TUPLE {PartID 'P001', SupplierID 'S001', Description 'Screws'},
  TUPLE {PartID 'P002', SupplierID 'S001', Description 'Nails'},
  TUPLE {PartID 'P003', SupplierID 'S002', Description 'Screws'}
};

So if I execute DELETE PartsSuppliers WHERE Name = 'Bob';DELETE PartsSuppliers WHERE Name = 'Bob';DELETE PartsSuppliers WHERE Name = 'Bob';DELETE PartsSuppliers WHERE Name = 'Bob';what should happen, and why?

 

You wrote:

"I'm not clear what you think is hard for me to understand. The distinction between update-through-views and updates to base tables?

"That I understand well. "

If you actually understood it you would use consistent language. Instead you write update through views and updates to base as if they are never the same thing. Whereas often they are the same thing for example join delete is base delete and vice versa just as Dorothy Parker used vice versa.This is not the first time I've noticed that as you soon as I mention join deletion people for some strange reason start chirping about view updates which is a far larger problem and an impossible one unless a number of arbirary conditions are tacked on to the basic algebra.

Then you give an example that is full of unnecessary environmental clutter, with words like relvar and real not to mention verbose names. This is symptomatic of the coder mentality that associates embroidery with precision, a mentality that prefers utilize and use case over use. I'll never understand why it stops there and why don't people talk about utilisation cases too?

If I were interviewing coders for a data design job let alone a dbms design job most of them would flunk my questions.

As for your question I'm not here to do anybody's work for them . Anybody who's truly interested need to strip the verbiage down to the essentials , just enough to form a formal argument. Sometimes the easiest kind of formal argument is simply a truth table. First decide on your domains, then your argument will be clear as to what it is talking about. Two obvious ones are suppliers named Bob that supply parts and parts that are supplied by Bob. To reduce the verbosity you could label them as S and PS. Almost as obvious are suppliers that don't supply parts and parts that aren't supplied by suppliers. To prove the more general case you could label them as SNP and PNS but they are not needed to prove the relation values of this specific join deletion.

Another possible domain is just suppliers named Bob but you could skip that domain for starters to make the argument as simple as possible by ignoring suppliers named Dave and parts 1 and 2. If you are determined to use phrases such as delete from join or insert to join rather than join delete or join insertion then you could include that domain in your argument but all that will do is add more rows to your truth table because it will have to specify what is being deleted from the overall join structure, in other words delete what domains from what domains?

Now form all possible true-false combinations for the propositions that join represents.

Of the four basic operations, union difference, join and projection, the truth table needs to use only union and join in the form of conjunction. Four rows and seven columns S, PS, NOT PS, NOT S OR PS, NOT PS, NOT PS OR S and ( NOT S OR PS ) AND ( NOT PS OR S ) suffice to show that the boolean expression ( S is false OR PS is true ) AND ( PS is false OR S is true ) is true only when both of S and PS are true and false when both are false. The expression is equivalent to the conjunction of two material implications.

Note that only two rows evaluate to true.

In relation terms, for example S is false means that a relation extension that enumerates the S domain is empty. Suppose the common attribute S# names the S domain. You can write a relational expression that corresponds to the Boolean expression in the form ( S{S#} implies PS{S#} ) join ( PS{S#} implies S{S#} ). Now you can replace the true-false table with an empty-nonempty table where the rows and columns replace Boolean values with relations. But you can skip the rows that don't evaluate to true because it is not true that either can represent a join.

If you don't drop those two rows you end up in what is logically the wild blue yonder. This is what happens when people think deMorgan proves a join deletion result rather the union of several results, for example the union of domain intersection deletion and domain difference insertion. Relational logic is quite simply not as general as deMorgan's logic. It is more precise so it requires more precise use.

The two-row empty non empty table shows whether each of those four projections is empty or not when you delete the join given by S join PS join suppliers named Bob. It is a formal proof that when the intersection of the two domains is empty each domain is also empty.

When you delete the join given by S join PS join suppliers named Dave that Supply part 1 you introduce two more domains and their intersections with the original two in other words a four-way join. The truth table for this is much more intricate needing 16 rows and more columns. Such a table will show that when the intersection of the four domains is empty each of them is also empty.

So for this specific join deletion you end up with no database rows that mention Bob.

A frequent mistake is to conflate the Domain of suppliers Named Dave that Supply part 1 with the domain of suppliers Named Dave that Supply part 2. A proof for this case needs to show the relation value for both domains. Obviously if the truth table method is to be used this is more easily done with mechanized truth tables. I wouldn't be surprised if Dorothy Parker had explained this long before Codd did.

I have seen people get quite angry after they have supposedly deleted a join but Bob disappears so to speak from the database. The reason is simply that there is only one join structure in the database that mentions Bob whereas if there were more than one such join and only one were deleted he wouldn't disappear so to speak. They think there is something wrong with join rather than their data design and choice of operations and that joins should never be updated or somesuch kneejerk reaction. Instead they should think about domains and projections and ask themselves why are they not deleting a difference if preserving Bob is the result they want. Beyond that they should ask themselves whether a structure apart from simple join is possible that will give the effect of asymetrically deleting a difference between two relations but symmetrically inserting their join. To be precise, deleting such a structure could not logically be called join deletion given the usual definition of join.

They could also think a little harder about what the casual characterizations of data independence mean in formal terms, relative to whatever elementary declarative connectives their algebra parallels. It isn't hard to see that join isolates domains it is concerned with from ones it isn't concerned with.

Sorry, I'm having difficulty understanding your response. My example is illustrative of the sort of thing that appears all the time in real-world database-driven applications. There are even (numerous) real ones as simple as my example, with two tables/relvars and a one-to-many relationship between them.

Please explain what should happen if I execute DELETE PartsSuppliers WHERE Name = 'Bob';DELETE PartsSuppliers WHERE Name = 'Bob';

Assume I have a coder mentality and need things spelled out.

Most coders I've known would understand what is meant by Bob disappears from the database.  Some of them would understand what S{S#} = PS{S#} = S{S#} join PS{S#} means when S# is the domain of suppliers named Bob that supply part P3. Either that domain has one element or none.  Same result.  Depending on the operations at their disposal they might remove the row in your suppliers relvar, the one that is "REAL" assuming that means base or base and stored that mentions Bob or they might recalculate the domain of all suppliers by taking the difference of the old domain and S# and then join that with the other domains or attributes tuple by tuple of the suppliers relation then assign the result to the suppliers relvar.  Same goes for the parts suppliers relvar.  Not all languages would require the relvar step though, just ones that call themselves TD compatible. Some coders would even understand how to optimize the calculation.

What I did a lot of typing to say amounts to no more than what Codd put in a single sentence. Search the 1970 pdf for the sentence that starts with "It is immediate that ...". It's in the operations section, the part about natural join. I don't quote it here because it is just too cumbersome compared to any possible benefit to duplicate his subscript fonts or emulate them.

Quote from p c on July 5, 2019, 8:32 pm

If you actually understood it you would use consistent language.

It is hardly for you (or any of us) to bandy words with the forum administrator about his use of the English language.

Whereas often they are the same thing for example join delete is base delete and vice versa just as Dorothy Parker used vice versa.

It doesn't sound to me like you got her joke.

This is not the first time I've noticed that as you soon as I mention join deletion people for some strange reason start chirping about view updates which is a far larger but much less important problem and an impossible one unless a number of arbitrary conditions are tacked on to the basic algebra.

Try to calm down and write grammatical English.

As for your question I'm not here to do anybody's work for them .

No, you are evidently here to exhibit your oversized ego.  At your age (whatever that may be) you have evidently not learned that (given goodwill on the listener's part) it is the speaker's responsibility to be clear to the listener, not merely to himself.  It is not the listener's responsibility to decipher the mysterious utterances of the speaker as though they were the obscure voice of God.

Anybody who's truly interested need to strip the verbiage down to the essentials , just enough to form a formal argument.

Here's a nice formal argument for you:

Une anecdote rapportée par Dieudonné Thiébault met en scène les croyances religieuses d'Euler. Le philosophe français Denis Diderot, en visite à Saint-Pétersbourg en 1773-1774, avait accepté, à la demande de l'impératrice Catherine II, de voir la preuve de l'existence de Dieu qu'Euler prétendait pouvoir produire. Les deux hommes se rencontrèrent donc et Euler, sur un ton d'une parfaite conviction annonça « Monsieur, (a + bⁿ)/n = x ; donc Dieu existe, répondez ! » . Le désarroi de Diderot, pour qui, (selon l'anecdote) les mathématiques étaient incompréhensibles, provoqua les rires de la cour. Gêné, il demanda à quitter la Russie.

[Dave, does that meet the local standard of (in)decorum?]

Quote from johnwcowan on July 5, 2019, 11:05 pm
Quote from p c on July 5, 2019, 8:32 pm

If you actually understood it you would use consistent language.

It is hardly for you (or any of us) to bandy words with the forum administrator about his use of the English language.

Whereas often they are the same thing for example join delete is base delete and vice versa just as Dorothy Parker used vice versa.

It doesn't sound to me like you got her joke.

This is not the first time I've noticed that as you soon as I mention join deletion people for some strange reason start chirping about view updates which is a far larger but much less important problem and an impossible one unless a number of arbitrary conditions are tacked on to the basic algebra.

Try to calm down and write grammatical English.

As for your question I'm not here to do anybody's work for them .

No, you are evidently here to exhibit your oversized ego.  At your age (whatever that may be) you have evidently not learned that (given goodwill on the listener's part) it is the speaker's responsibility to be clear to the listener, not merely to himself.  It is not the listener's responsibility to decipher the mysterious utterances of the speaker as though they were the obscure voice of God.

Anybody who's truly interested need to strip the verbiage down to the essentials , just enough to form a formal argument.

Here's a nice formal argument for you:

Une anecdote rapportée par Dieudonné Thiébault met en scène les croyances religieuses d'Euler. Le philosophe français Denis Diderot, en visite à Saint-Pétersbourg en 1773-1774, avait accepté, à la demande de l'impératrice Catherine II, de voir la preuve de l'existence de Dieu qu'Euler prétendait pouvoir produire. Les deux hommes se rencontrèrent donc et Euler, sur un ton d'une parfaite conviction annonça « Monsieur, (a + bⁿ)/n = x ; donc Dieu existe, répondez ! » . Le désarroi de Diderot, pour qui, (selon l'anecdote) les mathématiques étaient incompréhensibles, provoqua les rires de la cour. Gêné, il demanda à quitter la Russie.

[Dave, does that meet the local standard of (in)decorum?]

It does.

I'm the forum administrator and lead developer of Rel. Email me at dave@armchair.mb.ca with the Subject 'TTM Forum'. Download Rel from https://reldb.org
Quote from p c on July 5, 2019, 9:25 pm
Quote from Dave Voorhis on July 5, 2019, 8:49 pm
Quote from p c on July 5, 2019, 8:32 pm
Quote from Dave Voorhis on July 3, 2019, 2:15 pm
Quote from p c on July 3, 2019, 1:39 pm
Quote from Dave Voorhis on July 3, 2019, 1:24 pm
Quote from p c on July 3, 2019, 1:20 pm
Quote from Dave Voorhis on July 3, 2019, 7:26 am
Quote from p c on July 3, 2019, 1:17 am

I have thought more than once that a class action suit might fly against the vendors who provide Insert to difference and call it join deletion.

Which vendors do that?

Can you provide specific examples of how their products do that?

You asked the same question years ago. This should be old hat. I remember digging through documentation from several vendors and quoting some of it exactly. I posted an example or two and you ended up acknowledging yes indeed what they described as join delete in fact required users to address the base operands directly. The vendors included all the big names like Oracle, IBM, Microsoft. I'm not going to go through that again. It is not my problem if you do not understand how that breaks the correspondence between the algebra and formal logic and the potential damage that could result.

The only products I recall discussing implementation of update-through-views are Microsoft SQL Server and Microsoft Access, both of which allow deletion through a join in some cases but not others, under perfectly reasonable and documented cases. I see nothing there that warrants a "class action suit", do you? (And if there was, who would launch it and why?)

All of the big names in database do the same thing. They require the names of the pertinent Base relations or tables to be given explicitly. In other words they are talking about update through base not update through join. Why is that difference so hard for you to understand? The most Elementary understanding of data Independence should tell you that only the name of the join view should need to be mentioned. What those vendors do is worse than false advertising because it has brainwashed I generation of coders who probably number in the Millions.

I'm sure all the big names do the same thing; the only discussions I recall were based around documentation for and experiments with MS SQL Server and MS Access.

I'm not clear what you think is hard for me to understand. The distinction between update-through-views and updates to base tables?

That I understand well. What I don't understand is what you are arguing for. What "join view" should need to be mentioned?

Do you mean something like the PartsSuppliers view in the following?

VAR MySuppliers REAL RELATION {SupplierID CHAR, Name CHAR, Address CHAR} KEY {SupplierID};

VAR MyParts REAL RELATION {PartID CHAR, SupplierID CHAR, Description CHAR} KEY {PartID};

CONSTRAINT MyParts_MySuppliers  MyParts {SupplierID} <= MySuppliers {SupplierID};

VAR PartsSuppliers VIRTUAL  MySuppliers JOIN MyParts;

INSERT MySuppliers RELATION {
  TUPLE {SupplierID 'S001', Name 'Dave', Address 'Derby'},
  TUPLE {SupplierID 'S002', Name 'Bob', Address 'London'}
};

INSERT MyParts RELATION {
  TUPLE {PartID 'P001', SupplierID 'S001', Description 'Screws'},
  TUPLE {PartID 'P002', SupplierID 'S001', Description 'Nails'},
  TUPLE {PartID 'P003', SupplierID 'S002', Description 'Screws'}
}; 
  1. VAR MySuppliers REAL RELATION {SupplierID CHAR, Name CHAR, Address CHAR} KEY {SupplierID};
  2. VAR MyParts REAL RELATION {PartID CHAR, SupplierID CHAR, Description CHAR} KEY {PartID};
  3. CONSTRAINT MyParts_MySuppliers MyParts {SupplierID} <= MySuppliers {SupplierID};
  4. VAR PartsSuppliers VIRTUAL MySuppliers JOIN MyParts;
  5. INSERT MySuppliers RELATION {
  6. TUPLE {SupplierID 'S001', Name 'Dave', Address 'Derby'},
  7. TUPLE {SupplierID 'S002', Name 'Bob', Address 'London'}
  8. };
  9. INSERT MyParts RELATION {
  10. TUPLE {PartID 'P001', SupplierID 'S001', Description 'Screws'},
  11. TUPLE {PartID 'P002', SupplierID 'S001', Description 'Nails'},
  12. TUPLE {PartID 'P003', SupplierID 'S002', Description 'Screws'}
  13. };
VAR MySuppliers REAL RELATION {SupplierID CHAR, Name CHAR, Address CHAR} KEY {SupplierID};

VAR MyParts REAL RELATION {PartID CHAR, SupplierID CHAR, Description CHAR} KEY {PartID};

CONSTRAINT MyParts_MySuppliers  MyParts {SupplierID} <= MySuppliers {SupplierID};

VAR PartsSuppliers VIRTUAL  MySuppliers JOIN MyParts;

INSERT MySuppliers RELATION {
  TUPLE {SupplierID 'S001', Name 'Dave', Address 'Derby'},
  TUPLE {SupplierID 'S002', Name 'Bob', Address 'London'}
};

INSERT MyParts RELATION {
  TUPLE {PartID 'P001', SupplierID 'S001', Description 'Screws'},
  TUPLE {PartID 'P002', SupplierID 'S001', Description 'Nails'},
  TUPLE {PartID 'P003', SupplierID 'S002', Description 'Screws'}
};
  1. VAR MySuppliers REAL RELATION {SupplierID CHAR, Name CHAR, Address CHAR} KEY {SupplierID};
  2. VAR MyParts REAL RELATION {PartID CHAR, SupplierID CHAR, Description CHAR} KEY {PartID};
  3. CONSTRAINT MyParts_MySuppliers MyParts {SupplierID} <= MySuppliers {SupplierID};
  4. VAR PartsSuppliers VIRTUAL MySuppliers JOIN MyParts;
  5. INSERT MySuppliers RELATION {
  6. TUPLE {SupplierID 'S001', Name 'Dave', Address 'Derby'},
  7. TUPLE {SupplierID 'S002', Name 'Bob', Address 'London'}
  8. };
  9. INSERT MyParts RELATION {
  10. TUPLE {PartID 'P001', SupplierID 'S001', Description 'Screws'},
  11. TUPLE {PartID 'P002', SupplierID 'S001', Description 'Nails'},
  12. TUPLE {PartID 'P003', SupplierID 'S002', Description 'Screws'}
  13. };
VAR MySuppliers REAL RELATION {SupplierID CHAR, Name CHAR, Address CHAR} KEY {SupplierID};

VAR MyParts REAL RELATION {PartID CHAR, SupplierID CHAR, Description CHAR} KEY {PartID};

CONSTRAINT MyParts_MySuppliers  MyParts {SupplierID} <= MySuppliers {SupplierID};

VAR PartsSuppliers VIRTUAL  MySuppliers JOIN MyParts;

INSERT MySuppliers RELATION {
  TUPLE {SupplierID 'S001', Name 'Dave', Address 'Derby'},
  TUPLE {SupplierID 'S002', Name 'Bob', Address 'London'}
};

INSERT MyParts RELATION {
  TUPLE {PartID 'P001', SupplierID 'S001', Description 'Screws'},
  TUPLE {PartID 'P002', SupplierID 'S001', Description 'Nails'},
  TUPLE {PartID 'P003', SupplierID 'S002', Description 'Screws'}
};
  1. VAR MySuppliers REAL RELATION {SupplierID CHAR, Name CHAR, Address CHAR} KEY {SupplierID};
  2. VAR MyParts REAL RELATION {PartID CHAR, SupplierID CHAR, Description CHAR} KEY {PartID};
  3. CONSTRAINT MyParts_MySuppliers MyParts {SupplierID} <= MySuppliers {SupplierID};
  4. VAR PartsSuppliers VIRTUAL MySuppliers JOIN MyParts;
  5. INSERT MySuppliers RELATION {
  6. TUPLE {SupplierID 'S001', Name 'Dave', Address 'Derby'},
  7. TUPLE {SupplierID 'S002', Name 'Bob', Address 'London'}
  8. };
  9. INSERT MyParts RELATION {
  10. TUPLE {PartID 'P001', SupplierID 'S001', Description 'Screws'},
  11. TUPLE {PartID 'P002', SupplierID 'S001', Description 'Nails'},
  12. TUPLE {PartID 'P003', SupplierID 'S002', Description 'Screws'}
  13. };
VAR MySuppliers REAL RELATION {SupplierID CHAR, Name CHAR, Address CHAR} KEY {SupplierID};

VAR MyParts REAL RELATION {PartID CHAR, SupplierID CHAR, Description CHAR} KEY {PartID};

CONSTRAINT MyParts_MySuppliers  MyParts {SupplierID} <= MySuppliers {SupplierID};

VAR PartsSuppliers VIRTUAL  MySuppliers JOIN MyParts;

INSERT MySuppliers RELATION {
  TUPLE {SupplierID 'S001', Name 'Dave', Address 'Derby'},
  TUPLE {SupplierID 'S002', Name 'Bob', Address 'London'}
};

INSERT MyParts RELATION {
  TUPLE {PartID 'P001', SupplierID 'S001', Description 'Screws'},
  TUPLE {PartID 'P002', SupplierID 'S001', Description 'Nails'},
  TUPLE {PartID 'P003', SupplierID 'S002', Description 'Screws'}
};
  1. VAR MySuppliers REAL RELATION {SupplierID CHAR, Name CHAR, Address CHAR} KEY {SupplierID};
  2. VAR MyParts REAL RELATION {PartID CHAR, SupplierID CHAR, Description CHAR} KEY {PartID};
  3. CONSTRAINT MyParts_MySuppliers MyParts {SupplierID} <= MySuppliers {SupplierID};
  4. VAR PartsSuppliers VIRTUAL MySuppliers JOIN MyParts;
  5. INSERT MySuppliers RELATION {
  6. TUPLE {SupplierID 'S001', Name 'Dave', Address 'Derby'},
  7. TUPLE {SupplierID 'S002', Name 'Bob', Address 'London'}
  8. };
  9. INSERT MyParts RELATION {
  10. TUPLE {PartID 'P001', SupplierID 'S001', Description 'Screws'},
  11. TUPLE {PartID 'P002', SupplierID 'S001', Description 'Nails'},
  12. TUPLE {PartID 'P003', SupplierID 'S002', Description 'Screws'}
  13. };
VAR MySuppliers REAL RELATION {SupplierID CHAR, Name CHAR, Address CHAR} KEY {SupplierID};

VAR MyParts REAL RELATION {PartID CHAR, SupplierID CHAR, Description CHAR} KEY {PartID};

CONSTRAINT MyParts_MySuppliers  MyParts {SupplierID} <= MySuppliers {SupplierID};

VAR PartsSuppliers VIRTUAL  MySuppliers JOIN MyParts;

INSERT MySuppliers RELATION {
  TUPLE {SupplierID 'S001', Name 'Dave', Address 'Derby'},
  TUPLE {SupplierID 'S002', Name 'Bob', Address 'London'}
};

INSERT MyParts RELATION {
  TUPLE {PartID 'P001', SupplierID 'S001', Description 'Screws'},
  TUPLE {PartID 'P002', SupplierID 'S001', Description 'Nails'},
  TUPLE {PartID 'P003', SupplierID 'S002', Description 'Screws'}
};

So if I execute DELETE PartsSuppliers WHERE Name = 'Bob';DELETE PartsSuppliers WHERE Name = 'Bob';DELETE PartsSuppliers WHERE Name = 'Bob';DELETE PartsSuppliers WHERE Name = 'Bob';DELETE PartsSuppliers WHERE Name = 'Bob';what should happen, and why?

 

You wrote:

"I'm not clear what you think is hard for me to understand. The distinction between update-through-views and updates to base tables?

"That I understand well. "

If you actually understood it you would use consistent language. Instead you write update through views and updates to base as if they are never the same thing. Whereas often they are the same thing for example join delete is base delete and vice versa just as Dorothy Parker used vice versa.This is not the first time I've noticed that as you soon as I mention join deletion people for some strange reason start chirping about view updates which is a far larger problem and an impossible one unless a number of arbirary conditions are tacked on to the basic algebra.

Then you give an example that is full of unnecessary environmental clutter, with words like relvar and real not to mention verbose names. This is symptomatic of the coder mentality that associates embroidery with precision, a mentality that prefers utilize and use case over use. I'll never understand why it stops there and why don't people talk about utilisation cases too?

If I were interviewing coders for a data design job let alone a dbms design job most of them would flunk my questions.

As for your question I'm not here to do anybody's work for them . Anybody who's truly interested need to strip the verbiage down to the essentials , just enough to form a formal argument. Sometimes the easiest kind of formal argument is simply a truth table. First decide on your domains, then your argument will be clear as to what it is talking about. Two obvious ones are suppliers named Bob that supply parts and parts that are supplied by Bob. To reduce the verbosity you could label them as S and PS. Almost as obvious are suppliers that don't supply parts and parts that aren't supplied by suppliers. To prove the more general case you could label them as SNP and PNS but they are not needed to prove the relation values of this specific join deletion.

Another possible domain is just suppliers named Bob but you could skip that domain for starters to make the argument as simple as possible by ignoring suppliers named Dave and parts 1 and 2. If you are determined to use phrases such as delete from join or insert to join rather than join delete or join insertion then you could include that domain in your argument but all that will do is add more rows to your truth table because it will have to specify what is being deleted from the overall join structure, in other words delete what domains from what domains?

Now form all possible true-false combinations for the propositions that join represents.

Of the four basic operations, union difference, join and projection, the truth table needs to use only union and join in the form of conjunction. Four rows and seven columns S, PS, NOT PS, NOT S OR PS, NOT PS, NOT PS OR S and ( NOT S OR PS ) AND ( NOT PS OR S ) suffice to show that the boolean expression ( S is false OR PS is true ) AND ( PS is false OR S is true ) is true only when both of S and PS are true and false when both are false. The expression is equivalent to the conjunction of two material implications.

Note that only two rows evaluate to true.

In relation terms, for example S is false means that a relation extension that enumerates the S domain is empty. Suppose the common attribute S# names the S domain. You can write a relational expression that corresponds to the Boolean expression in the form ( S{S#} implies PS{S#} ) join ( PS{S#} implies S{S#} ). Now you can replace the true-false table with an empty-nonempty table where the rows and columns replace Boolean values with relations. But you can skip the rows that don't evaluate to true because it is not true that either can represent a join.

If you don't drop those two rows you end up in what is logically the wild blue yonder. This is what happens when people think deMorgan proves a join deletion result rather the union of several results, for example the union of domain intersection deletion and domain difference insertion. Relational logic is quite simply not as general as deMorgan's logic. It is more precise so it requires more precise use.

The two-row empty non empty table shows whether each of those four projections is empty or not when you delete the join given by S join PS join suppliers named Bob. It is a formal proof that when the intersection of the two domains is empty each domain is also empty.

When you delete the join given by S join PS join suppliers named Dave that Supply part 1 you introduce two more domains and their intersections with the original two in other words a four-way join. The truth table for this is much more intricate needing 16 rows and more columns. Such a table will show that when the intersection of the four domains is empty each of them is also empty.

So for this specific join deletion you end up with no database rows that mention Bob.

A frequent mistake is to conflate the Domain of suppliers Named Dave that Supply part 1 with the domain of suppliers Named Dave that Supply part 2. A proof for this case needs to show the relation value for both domains. Obviously if the truth table method is to be used this is more easily done with mechanized truth tables. I wouldn't be surprised if Dorothy Parker had explained this long before Codd did.

I have seen people get quite angry after they have supposedly deleted a join but Bob disappears so to speak from the database. The reason is simply that there is only one join structure in the database that mentions Bob whereas if there were more than one such join and only one were deleted he wouldn't disappear so to speak. They think there is something wrong with join rather than their data design and choice of operations and that joins should never be updated or somesuch kneejerk reaction. Instead they should think about domains and projections and ask themselves why are they not deleting a difference if preserving Bob is the result they want. Beyond that they should ask themselves whether a structure apart from simple join is possible that will give the effect of asymetrically deleting a difference between two relations but symmetrically inserting their join. To be precise, deleting such a structure could not logically be called join deletion given the usual definition of join.

They could also think a little harder about what the casual characterizations of data independence mean in formal terms, relative to whatever elementary declarative connectives their algebra parallels. It isn't hard to see that join isolates domains it is concerned with from ones it isn't concerned with.

Sorry, I'm having difficulty understanding your response. My example is illustrative of the sort of thing that appears all the time in real-world database-driven applications. There are even (numerous) real ones as simple as my example, with two tables/relvars and a one-to-many relationship between them.

Please explain what should happen if I execute DELETE PartsSuppliers WHERE Name = 'Bob';DELETE PartsSuppliers WHERE Name = 'Bob';DELETE PartsSuppliers WHERE Name = 'Bob';

Assume I have a coder mentality and need things spelled out.

Most coders I've known would understand what is meant by Bob disappears from the database.  Some of them would understand what S{S#} = PS{S#} = S{S#} join PS{S#} means when S# is the domain of suppliers named Bob that supply part P3. Either that domain has one element or none.  Same result.  Depending on the operations at their disposal they might remove the row in your suppliers relvar, the one that is "REAL" assuming that means base or base and stored that mentions Bob or they might recalculate the domain of all suppliers by taking the difference of the old domain and S# and then join that with the other domains or attributes tuple by tuple of the suppliers relation then assign the result to the suppliers relvar.  Same goes for the parts suppliers relvar.  Not all languages would require the relvar step though, just ones that call themselves TD compatible. Some coders would even understand how to optimize the calculation.

What I did a lot of typing to say amounts to no more than what Codd put in a single sentence. Search the 1970 pdf for the sentence that starts with "It is immediate that ...". It's in the operations section, the part about natural join. I don't quote it here because it is just too cumbersome compared to any possible benefit to duplicate his subscript fonts or emulate them.

Do you see the problem with "Bob disappears from the database"?

If you do, then there's a possibility for a rational and productive discussion about how update-through-views (or its goal of logical data independence) might be reasonably accomplished, and how alternatives to TTM might -- as your descriptions often appear to suggest -- be based around Prolog/Datalog-like assertions and retractions rather than base relvars. But to be productive and worthwhile, and to actually accomplish anything other than waste bytes on my server, the discussion will have to strive for brevity, clarity, and concise and compelling arguments whilst avoiding any hint of histrionics or bafflegab.

Deal?

I'm the forum administrator and lead developer of Rel. Email me at dave@armchair.mb.ca with the Subject 'TTM Forum'. Download Rel from https://reldb.org

 

Do you see the problem with "Bob disappears from the database"?

If you do, then there's a possibility for a rational and productive discussion about how update-through-views (or its goal of logical data independence) might be reasonably accomplished, and how alternatives to TTM might -- as your descriptions often appear to suggest -- be based around Prolog/Datalog-like assertions and retractions rather than base relvars. But to be productive and worthwhile, and to actually accomplish anything other than waste bytes on my server, the discussion will have to strive for brevity, clarity, and concise and compelling arguments whilst avoiding any hint of histrionics or bafflegab.

Deal?

No deal.  I'm not interested in discussing update-through-views, only join delete and possibly some forms of projection insert. I have no doubt that union insert is generally impossible, likewise updates of aggregates and user functions but I doubt they matter for data independence.

I would make a deal with anybody who's willing to discuss the 1990 updating chapter but the few people I have talked with about say it is too hard to understand.  I agree it's hard and it's not self-contained either so such a deal is a significant commitment for most coders.  I also don't believe it can be accurately discussed in TTM terms. For example, deletion and insertion need to be understood in terms of a limited specialized form of set theory (which I don't claim to be able to converse about any better than the average coder) and the various equivalent databases each can produce before considering environmental devices like relvars or host language features like programming types. Neither Codd nor TTM have ever given the necessary space for that. His algebra clearly amounts to only what operations he thought were needed.  He offered function and composition ops I think as a way of offering potentially productive dimensions. this is why I say that only four elementary operations are needed by the simplest dbms which I'll bet is also the most generally useful dbms. From this take it's appalling how complicated are most or all commercial implementations especially in view of their lack of support for data independence.

Note that as I've tried to make clear, I don't think most coders need to understand that Bob disappears, only what to do about it when they are told he does.  Obviously the technical problem with that order is that it is informal English but I don't think it is a problem that it is not in TTM-speak. It is no different than a coder being told to get rid of a certain supplier, completely (a problem Codd noted).  Only a relatively small minority of people need to understand how a data design needs to accomodate the problem and an even smaller minority need to understand what a dbms must do about it. The 1990 book in pdf form is a pain to transmit, about 27MB in size but I assume that I could put the updating chapter in and maybe a few others in a shareable folder using Google Drive if anybody asks for it. I bought the hardcover when it first came out but I don't think it's legally necessary to buy it.  ACM made the pdf freely available for some months, maybe it was for a year or two, after Codd died. Probably nobody will get sued for copying one or two chapters.

Among other things he acknowledges that union insert is not generally possible just as elsewhere he acknowledges that relational union is not general union. DTATRM Appendix A gives the reason as avoiding "computational difficulties" which is maybe understandable if you think, unlike Codd, that a database might involve infinite extensions but I think his reason is simpler, algebraic closure.

McGoveran has a short draft chapter on his website that is his answer to the question "can all relations be updated". Personally I'm not interested in that question because I don't think it would matter much in practice. But the chapter has some useful insights, especially the characterization of update that in his framework amounts to a definition of insert and delete in set terms. It is obvious to me that few people have ever paid much attention to that draft chapter because occasionally somebody will scoff that an update is a deletion followed by an insertion, which is not what the chapter says! The definition only requires that the deletion set and insert set be disjoint.

 

 

Quote from johnwcowan on July 5, 2019, 11:05 pm
Quote from p c on July 5, 2019, 8:32 pm

If you actually understood it you would use consistent language.

It is hardly for you (or any of us) to bandy words with the forum administrator about his use of the English language.

Whereas often they are the same thing for example join delete is base delete and vice versa just as Dorothy Parker used vice versa.

It doesn't sound to me like you got her joke.

This is not the first time I've noticed that as you soon as I mention join deletion people for some strange reason start chirping about view updates which is a far larger but much less important problem and an impossible one unless a number of arbitrary conditions are tacked on to the basic algebra.

Try to calm down and write grammatical English.

As for your question I'm not here to do anybody's work for them .

No, you are evidently here to exhibit your oversized ego.  At your age (whatever that may be) you have evidently not learned that (given goodwill on the listener's part) it is the speaker's responsibility to be clear to the listener, not merely to himself.  It is not the listener's responsibility to decipher the mysterious utterances of the speaker as though they were the obscure voice of God.

Anybody who's truly interested need to strip the verbiage down to the essentials , just enough to form a formal argument.

Here's a nice formal argument for you:

Une anecdote rapportée par Dieudonné Thiébault met en scène les croyances religieuses d'Euler. Le philosophe français Denis Diderot, en visite à Saint-Pétersbourg en 1773-1774, avait accepté, à la demande de l'impératrice Catherine II, de voir la preuve de l'existence de Dieu qu'Euler prétendait pouvoir produire. Les deux hommes se rencontrèrent donc et Euler, sur un ton d'une parfaite conviction annonça « Monsieur, (a + bⁿ)/n = x ; donc Dieu existe, répondez ! » . Le désarroi de Diderot, pour qui, (selon l'anecdote) les mathématiques étaient incompréhensibles, provoqua les rires de la cour. Gêné, il demanda à quitter la Russie.

[Dave, does that meet the local standard of (in)decorum?]

Does this mean you don't like my rationale?

Quote from p c on July 6, 2019, 1:50 am

 

Does this mean you don't like my rationale?

It means I don't like your behavior, which is childish and rude.

*plonk*

Quote from p c on July 6, 2019, 1:27 am

Do you see the problem with "Bob disappears from the database"?If you do, then there's a possibility for a rational and productive discussion about how update-through-views (or its goal of logical data independence) might be reasonably accomplished, and how alternatives to TTM might -- as your descriptions often appear to suggest -- be based around Prolog/Datalog-like assertions and retractions rather than base relvars. But to be productive and worthwhile, and to actually accomplish anything other than waste bytes on my server, the discussion will have to strive for brevity, clarity, and concise and compelling arguments whilst avoiding any hint of histrionics or bafflegab.

Deal?

No deal.  I'm not interested in discussing update-through-views, only join delete and possibly some forms of projection insert. ...

I don't mean "deal?" over what topic to discuss -- I was not asking for agreement to discuss general update-through-views or some subset of operations -- but about the style of discourse. I'm asking you to strive for brevity, clarity, and concise and compelling arguments, and stop it with the histrionics and bafflegab.

So, again... Are you willing to do that?

 

 

I'm the forum administrator and lead developer of Rel. Email me at dave@armchair.mb.ca with the Subject 'TTM Forum'. Download Rel from https://reldb.org