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 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?

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 3, 2019, 1:44 am
Quote from Erwin on July 2, 2019, 8:17 pm
Quote from p c on July 2, 2019, 6:45 pm

The pop argument mentions Ua and Ub but not Uab. That is why I asked for clarification for the definitions of the two universes you gave. Thank you for the elaborate explanation but you are still missing one universe. So in effect you end up conflating different predicates, for example conflating Ua with Uab. That conflation would be okay if you accompanied it with the conflation of Ub with Uab but your argument would have to make both conflations explicit.

Good excuse to try this latex thing.

...

\overline{A \bowtie B} \equiv ((A \cup{ \overline A}) \bowtie {\overline B}) \cup ({\overline A} \bowtie (B \cup {\overline B}))   (join distrib union)

The last step is left as an exercise.

As for the latex thing : who on earth calls "intersect" "cap" and "fi" "oslash" ???

Sorry, I don't know what oslash means.  I'm guessing that it might be to do with what the bartender said when a mullah, priest and rabbi walked into a bar: "what is this, some kind of joke?" Other than that I think your experiment with unusual fonts is not very useful. Some of it appears mangled when I receive it.

This forum supports \LaTeX markup, which is a typesetting language (developed by Leslie Lamport and Donald Knuth) that is particularly suited for writing mathematical formulae. Erwin used it in the post you quoted, and remarked on the markup for the \oslash symbol being written as \oslash.

See https://forum.thethirdmanifesto.com/forum/topic/latex-test/ for more information.

Erwin's post appears to format correctly under Chrome on Windows, MacOS and Android. I see no mangling. What did you use to view it?

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 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.

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?)

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 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.

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'}
}; 

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

 

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 Dave Voorhis on July 3, 2019, 7:36 am

Erwin's post appears to format correctly under Chrome on Windows, MacOS and Android. I see no mangling. What did you use to view it?

I'm using Edge and Firefox on Windows 10 and it looks fine.

But the emails don't format Latex at all. Is that an inherent limitation? I know it could be done, but would anyone bother?

Andl - A New Database Language - andl.org
Quote from dandl on July 4, 2019, 12:13 am
Quote from Dave Voorhis on July 3, 2019, 7:36 am

Erwin's post appears to format correctly under Chrome on Windows, MacOS and Android. I see no mangling. What did you use to view it?

I'm using Edge and Firefox on Windows 10 and it looks fine.

But the emails don't format Latex at all. Is that an inherent limitation? I know it could be done, but would anyone bother?

I don't know. There might be a switch to enable it. I'll look when I get a moment.

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 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'}
};

So if I execute 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 but much less important problem and an impossible one unless a number of arbitrary 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 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.

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'}
};

So if I execute 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';

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

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