The Forum for Discussion about The Third Manifesto and Related Matters

You need to log in to create posts and topics.

How to expand assignment shortcuts

DTATRM helpfully notes that INSERT, DELETE and UPDATE are 'shortcuts for certain relational assignments', but it's somewhat coy about specifying what they're shortcuts for.

On p161 we have these:

INSERT PQ ( SUMMARIZE SP PER ( P { P# } ) ADD ( SUM ( QTY ) AS QTY )
PQ := PQ UNION ( SUMMARIZE SP PER ( P { P# } ) ADD ( SUM ( QTY ) AS QTY ) ) ;

DELETE SP WHERE S# = S#('S4') OR S# = S#('S5') ;
SP := SP WHERE NOT ( S# = S#('S4') OR S# = S#('S5') ) ;

UPDATE S WHERE CITY = 'Paris' ( STATUS := 2 * STATUS , CITY := 'Nice' ) ;
S := ( S WHERE NOT ( CITY = 'Paris' ) )
  UNION
( UPDATE S WHERE CITY = 'Paris'( STATUS := 2 * STATUS , CITY := 'Nice' ) ) ;

This UPDATE is defined in terms of UPDATE, which seems unhelpful. I guess I had missed that TD has two completely different operations sharing the same name. Is that a good idea?

At the end of the day, is this what you get?

INSERT S INTO R expands to R := R UNION S
DELETE R WHERE C expands to R := R MINUS (R WHERE C)
UPDATE R WHERE C ( A := X, ... ) expands to R :=(R MINUS (R WHERE C)) UNION (R WHERE C UPDATE ( A := X, ... ))

 

Andl - A New Database Language - andl.org
Quote from dandl on April 5, 2020, 1:54 am

DTATRM helpfully notes that INSERT, DELETE and UPDATE are 'shortcuts for certain relational assignments', but it's somewhat coy about specifying what they're shortcuts for.

On p161 we have these:

That's not the authoritative place to look. It's illustrating the Pre's using Tutorial D examples. The language reference is a different Chapter. I use a revised stand-alone version of that Chapter 'Tutorial D 2016-09-22', from the TTM website -- which has (some of) the newer syntax you alleged wasn't documented.

INSERT PQ ( SUMMARIZE SP PER ( P { P# } ) ADD ( SUM ( QTY ) AS QTY )
PQ := PQ UNION ( SUMMARIZE SP PER ( P { P# } ) ADD ( SUM ( QTY ) AS QTY ) ) ;

DELETE SP WHERE S# = S#('S4') OR S# = S#('S5') ;
SP := SP WHERE NOT ( S# = S#('S4') OR S# = S#('S5') ) ;

UPDATE S WHERE CITY = 'Paris' ( STATUS := 2 * STATUS , CITY := 'Nice' ) ;
S := ( S WHERE NOT ( CITY = 'Paris' ) )
  UNION
( UPDATE S WHERE CITY = 'Paris'( STATUS := 2 * STATUS , CITY := 'Nice' ) ) ;

This UPDATE is defined in terms of UPDATE, which seems unhelpful. I guess I had missed that TD has two completely different operations sharing the same name. Is that a good idea?

If you read on to the top of p162, that nested UPDATE "substitute operator" is a shorthand for a combo of WITH/EXTEND/projection/RENAME/.... To cover full generality, the semantics for UPDATE must cater for changing (all of) the key attributes in the target; and for one attribute-pseudo-assignment in the commalist to mention the 'new' value of an attribute assigned on its left -- it's a Multiple Assignment in effect.

There's three forms for DELETE, of which the ones you don't mention would be more basic, I guess:

DELETE SP SP'; ≡ SP := SP MINUS SP'; -- what I call 'lax'DELETE.

I_DELETE SP SP'; also ≡ SP := SP MINUS SP';, but with the restriction SP' must be a subset of SP, therefore not 'lax'.

Then SP DELETE WHERE ( ... ); ≡ I_DELETE SP (SP WHERE ( ... )); ≡ SP := SP MINUS (SP WHERE ( ... )); ≡ SP := SP WHERE NOT( ... );.

In Parallel, there's a non-lax form for INSERTD_INSERT SP SP'; .

At the end of the day, is this what you get?

INSERT S INTO R expands to R := R UNION S
DELETE R WHERE C expands to R := R MINUS (R WHERE C)
UPDATE R WHERE C ( A := X, ... ) expands to R :=(R MINUS (R WHERE C)) UNION (R WHERE C UPDATE ( A := X, ... ))

 

Debate has raged here as to whether UPDATE must preserve the cardinality of the target; that is, whether the UNION on the rhs should be a non-lax D_UNION. This overlaps with the deeply divisive issue of whether the existing tuples in the relvar have some sort of 'identity' that persists through an UPDATE, even though the statement changes every attribute in those tuples.

I'd at least rewrite that expansion to avoid UPDATE on RHS.

Quote from AntC on April 5, 2020, 4:26 am
Quote from dandl on April 5, 2020, 1:54 am

DTATRM helpfully notes that INSERT, DELETE and UPDATE are 'shortcuts for certain relational assignments', but it's somewhat coy about specifying what they're shortcuts for.

On p161 we have these:

That's not the authoritative place to look.

So where exactly should I look for an authoritative answer to that question: the expanded versions of the shortcuts?

It's illustrating the Pre's using Tutorial D examples. The language reference is a different Chapter. I use a revised stand-alone version of that Chapter 'Tutorial D 2016-09-22', from the TTM website -- which has (some of) the newer syntax you alleged wasn't documented.

I know where to look for syntax, although in this pseudo-BNF form I find it pretty hard to decode. The TD document has a version of the UPDATE expansion, although not the same as the one in DTATRM. It has no expansion or semantics for INSERT or DELETE that I can find.

INSERT PQ ( SUMMARIZE SP PER ( P { P# } ) ADD ( SUM ( QTY ) AS QTY )
PQ := PQ UNION ( SUMMARIZE SP PER ( P { P# } ) ADD ( SUM ( QTY ) AS QTY ) ) ;

DELETE SP WHERE S# = S#('S4') OR S# = S#('S5') ;
SP := SP WHERE NOT ( S# = S#('S4') OR S# = S#('S5') ) ;

UPDATE S WHERE CITY = 'Paris' ( STATUS := 2 * STATUS , CITY := 'Nice' ) ;
S := ( S WHERE NOT ( CITY = 'Paris' ) )
  UNION
( UPDATE S WHERE CITY = 'Paris'( STATUS := 2 * STATUS , CITY := 'Nice' ) ) ;

This UPDATE is defined in terms of UPDATE, which seems unhelpful. I guess I had missed that TD has two completely different operations sharing the same name. Is that a good idea?

If you read on to the top of p162, that nested UPDATE "substitute operator" is a shorthand for a combo of WITH/EXTEND/projection/RENAME/.... To cover full generality, the semantics for UPDATE must cater for changing (all of) the key attributes in the target; and for one attribute-pseudo-assignment in the commalist to mention the 'new' value of an attribute assigned on its left -- it's a Multiple Assignment in effect.

Yes, of course I read all that, and what it actually says is "The expression denoting the second UNION operand in that expansion is an invocation of the substitute operator". That's not quite the same thing, and I wasn't going to read all of chapters 2 and 5 figure out why.

But all I ever wanted was the expansion for 3 shortcuts, and about here I started going around in circles.

There's three forms for DELETE, of which the ones you don't mention would be more basic, I guess:

DELETE SP SP'; ≡ SP := SP MINUS SP'; -- what I call 'lax'DELETE.

I_DELETE SP SP'; also ≡ SP := SP MINUS SP';, but with the restriction SP' must be a subset of SP, therefore not 'lax'.

Then SP DELETE WHERE ( ... ); ≡ I_DELETE SP (SP WHERE ( ... )); ≡ SP := SP MINUS (SP WHERE ( ... )); ≡ SP := SP WHERE NOT( ... );.

In Parallel, there's a non-lax form for INSERTD_INSERT SP SP'; .

At the end of the day, is this what you get?

INSERT S INTO R expands to R := R UNION S
DELETE R WHERE C expands to R := R MINUS (R WHERE C)
UPDATE R WHERE C ( A := X, ... ) expands to R :=(R MINUS (R WHERE C)) UNION (R WHERE C UPDATE ( A := X, ... ))

 

Debate has raged here as to whether UPDATE must preserve the cardinality of the target; that is, whether the UNION on the rhs should be a non-lax D_UNION. This overlaps with the deeply divisive issue of whether the existing tuples in the relvar have some sort of 'identity' that persists through an UPDATE, even though the statement changes every attribute in those tuples.

I recall the second debate, not the first. It looks pretty obvious that the example given (or something close to it) can cause the loss of tuples, and I confess that came as a surprise to me.

I'd at least rewrite that expansion to avoid UPDATE on RHS.

Does that mean EXTEND and a whole heap of RENAMEs, or is there a shorter way? TD p19 seems to say that EXTEND is allowed to replace an existing attribute, which means EXTEND may not extend a relation at all, but rather trigger a reduction in cardinality. I read it, and I just don't know.

 

Andl - A New Database Language - andl.org
Quote from dandl on April 5, 2020, 10:25 am
Quote from AntC on April 5, 2020, 4:26 am
Quote from dandl on April 5, 2020, 1:54 am

DTATRM helpfully notes that INSERT, DELETE and UPDATE are 'shortcuts for certain relational assignments', but it's somewhat coy about specifying what they're shortcuts for.

On p161 we have these:

That's not the authoritative place to look.

So where exactly should I look for an authoritative answer to that question: the expanded versions of the shortcuts?

It's illustrating the Pre's using Tutorial D examples. The language reference is a different Chapter. I use a revised stand-alone version of that Chapter 'Tutorial D 2016-09-22', from the TTM website -- which has (some of) the newer syntax you alleged wasn't documented.

I know where to look for syntax, although in this pseudo-BNF form I find it pretty hard to decode. The TD document has a version of the UPDATE expansion, although not the same as the one in DTATRM. It has no expansion or semantics for INSERT or DELETE that I can find.

Isn't that blimmin'obvious?

INSERT PQ ( SUMMARIZE SP PER ( P { P# } ) ADD ( SUM ( QTY ) AS QTY )
PQ := PQ UNION ( SUMMARIZE SP PER ( P { P# } ) ADD ( SUM ( QTY ) AS QTY ) ) ;

DELETE SP WHERE S# = S#('S4') OR S# = S#('S5') ;
SP := SP WHERE NOT ( S# = S#('S4') OR S# = S#('S5') ) ;

UPDATE S WHERE CITY = 'Paris' ( STATUS := 2 * STATUS , CITY := 'Nice' ) ;
S := ( S WHERE NOT ( CITY = 'Paris' ) )
  UNION
( UPDATE S WHERE CITY = 'Paris'( STATUS := 2 * STATUS , CITY := 'Nice' ) ) ;

This UPDATE is defined in terms of UPDATE, which seems unhelpful. I guess I had missed that TD has two completely different operations sharing the same name. Is that a good idea?

If you read on to the top of p162, that nested UPDATE "substitute operator" is a shorthand for a combo of WITH/EXTEND/projection/RENAME/.... To cover full generality, the semantics for UPDATE must cater for changing (all of) the key attributes in the target; and for one attribute-pseudo-assignment in the commalist to mention the 'new' value of an attribute assigned on its left -- it's a Multiple Assignment in effect.

Yes, of course I read all that, and what it actually says is "The expression denoting the second UNION operand in that expansion is an invocation of the substitute operator". That's not quite the same thing, and I wasn't going to read all of chapters 2 and 5 figure out why.

But all I ever wanted was the expansion for 3 shortcuts, and about here I started going around in circles.

There's three forms for DELETE, of which the ones you don't mention would be more basic, I guess:

DELETE SP SP'; ≡ SP := SP MINUS SP'; -- what I call 'lax'DELETE.

I_DELETE SP SP'; also ≡ SP := SP MINUS SP';, but with the restriction SP' must be a subset of SP, therefore not 'lax'.

Then SP DELETE WHERE ( ... ); ≡ I_DELETE SP (SP WHERE ( ... )); ≡ SP := SP MINUS (SP WHERE ( ... )); ≡ SP := SP WHERE NOT( ... );.

In Parallel, there's a non-lax form for INSERTD_INSERT SP SP'; .

At the end of the day, is this what you get?

INSERT S INTO R expands to R := R UNION S
DELETE R WHERE C expands to R := R MINUS (R WHERE C)
UPDATE R WHERE C ( A := X, ... ) expands to R :=(R MINUS (R WHERE C)) UNION (R WHERE C UPDATE ( A := X, ... ))

 

Debate has raged here as to whether UPDATE must preserve the cardinality of the target; that is, whether the UNION on the rhs should be a non-lax D_UNION. This overlaps with the deeply divisive issue of whether the existing tuples in the relvar have some sort of 'identity' that persists through an UPDATE, even though the statement changes every attribute in those tuples.

I recall the second debate, not the first. It looks pretty obvious that the example given (or something close to it) can cause the loss of tuples, and I confess that came as a surprise to me.

Yes. I don't like it, I prefer the non-lax forms. OTOH if you're trying to follow the debates here, nearly everybody else seems to assume the lax forms. Erwin even seems to think it's a 'feature'. Indeed way way back I got completely flummoxed in some discussion because I assumed INSERT/DELETE were non-lax. And indeed Rel complains about would-be duplicate tuples on an INSERT [good!]. Note the lax forms mean that you can't straightforwardly say INSERT is the reverse of DELETE, nor vice versa.

I'd at least rewrite that expansion to avoid UPDATE on RHS.

Does that mean EXTEND and a whole heap of RENAMEs, or is there a shorter way? TD p19 seems to say that EXTEND is allowed to replace an existing attribute, which means EXTEND may not extend a relation at all, but rather trigger a reduction in cardinality. I read it, and I just don't know.

 

Yeah another bit of lax behaviour I don't like -- although I can see that it adds to ease of programming. Why are you worried about a "shorter way"? Nothing's telling you how to implement it or worrying about 'efficiency'. It's purely to express the semantics.

For that lax form of EXTEND, I'd express the semantics as:

  • Generate a fresh attribute name, EXTEND to that as target. (You'll also need to rework any references to the 'new' value in the 'existing' attribute name.
  • Project away the existing attribute -- this is where you might reduce cardinality.
  • RENAME the fresh attribute to pre-existing name.

The clarity you get here is that the only operation that might reduce cardinality is projection. You know that if the projected-away attribute is not part of a key, you're not reducing cardinality. Note that if the target attribute is part of a key, the EXTEND to fresh attribute could add an extra key to include the fresh. At that point you might generate duplicates for that key.

I do suffer enough of a hangover from Entity-Relationship modelling to think there's something deeply smelly about UPDATE that targets (part of) a key. I do design schemas with the intent one of the keys should be permanent/persistent, so I'm happy to talk of 'the' P-Key to SQL-ers. I think TTM is just too precious.

I know where to look for syntax, although in this pseudo-BNF form I find it pretty hard to decode. The TD document has a version of the UPDATE expansion, although not the same as the one in DTATRM. It has no expansion or semantics for INSERT or DELETE that I can find.

Isn't that blimmin'obvious?

It's obvious that it doesn't. As you probably guessed, I had already worked these out for myself, this question was purely about finding an authoritative source so I could check my answers. This is not it.

Debate has raged here as to whether UPDATE must preserve the cardinality of the target; that is, whether the UNION on the rhs should be a non-lax D_UNION. This overlaps with the deeply divisive issue of whether the existing tuples in the relvar have some sort of 'identity' that persists through an UPDATE, even though the statement changes every attribute in those tuples.

I recall the second debate, not the first. It looks pretty obvious that the example given (or something close to it) can cause the loss of tuples, and I confess that came as a surprise to me.

Yes. I don't like it, I prefer the non-lax forms. OTOH if you're trying to follow the debates here, nearly everybody else seems to assume the lax forms. Erwin even seems to think it's a 'feature'. Indeed way way back I got completely flummoxed in some discussion because I assumed INSERT/DELETE were non-lax. And indeed Rel complains about would-be duplicate tuples on an INSERT [good!]. Note the lax forms mean that you can't straightforwardly say INSERT is the reverse of DELETE, nor vice versa.

There are no keys in relation values, so AFAICT they don't affect shortcut expansions. The only difference I can see is that the final assignment might trigger a key violation in the 'strict' cases.

I'd at least rewrite that expansion to avoid UPDATE on RHS.

Does that mean EXTEND and a whole heap of RENAMEs, or is there a shorter way? TD p19 seems to say that EXTEND is allowed to replace an existing attribute, which means EXTEND may not extend a relation at all, but rather trigger a reduction in cardinality. I read it, and I just don't know.

Yeah another bit of lax behaviour I don't like -- although I can see that it adds to ease of programming. Why are you worried about a "shorter way"? Nothing's telling you how to implement it or worrying about 'efficiency'. It's purely to express the semantics.

A shorthand IMO is always a simple text rewrite. I would prefer the rewrite to be as short as possible, and no shorter. Efficiency is of no concern.

For that lax form of EXTEND, I'd express the semantics as:

  • Generate a fresh attribute name, EXTEND to that as target. (You'll also need to rework any references to the 'new' value in the 'existing' attribute name.
  • Project away the existing attribute -- this is where you might reduce cardinality.
  • RENAME the fresh attribute to pre-existing name.

Or RENAME, EXTEND, PROJECT(/REMOVE).

The clarity you get here is that the only operation that might reduce cardinality is projection. You know that if the projected-away attribute is not part of a key, you're not reducing cardinality. Note that if the target attribute is part of a key, the EXTEND to fresh attribute could add an extra key to include the fresh. At that point you might generate duplicates for that key.

Since values have no keys, you can't know that until the final assignment. The projected away attribute might be restored by a subsequent EXTEND or JOIN.

I do suffer enough of a hangover from Entity-Relationship modelling to think there's something deeply smelly about UPDATE that targets (part of) a key. I do design schemas with the intent one of the keys should be permanent/persistent, so I'm happy to talk of 'the' P-Key to SQL-ers. I think TTM is just too precious.

Fair enough, but it really only affects the final assignment. My aim is to break these more complex operations down to their elements, inspired by trying to express them in Knime. There is a layer of fuzziness between the basic algebra and the final language, and my aim is to de-fuzz.

On a related note, there is really no MA, except that two or more final assignments might need to be performed simultaneously, either to satisfy a database constraint or because they are depend on each other. All the other MAs can be treated as shortcuts and expanded. The process to do that is spelled out in some detail in TTM.

 

 

Andl - A New Database Language - andl.org
Quote from dandl on April 6, 2020, 12:49 am

I know where to look for syntax, although in this pseudo-BNF form I find it pretty hard to decode. The TD document has a version of the UPDATE expansion, although not the same as the one in DTATRM. It has no expansion or semantics for INSERT or DELETE that I can find.

Isn't that blimmin'obvious?

It's obvious that it doesn't. As you probably guessed, I had already worked these out for myself, this question was purely about finding an authoritative source so I could check my answers. This is not it.

Debate has raged here as to whether UPDATE must preserve the cardinality of the target; that is, whether the UNION on the rhs should be a non-lax D_UNION. This overlaps with the deeply divisive issue of whether the existing tuples in the relvar have some sort of 'identity' that persists through an UPDATE, even though the statement changes every attribute in those tuples.

I recall the second debate, not the first. It looks pretty obvious that the example given (or something close to it) can cause the loss of tuples, and I confess that came as a surprise to me.

Yes. I don't like it, I prefer the non-lax forms. OTOH if you're trying to follow the debates here, nearly everybody else seems to assume the lax forms. Erwin even seems to think it's a 'feature'. Indeed way way back I got completely flummoxed in some discussion because I assumed INSERT/DELETE were non-lax. And indeed Rel complains about would-be duplicate tuples on an INSERT [good!]. Note the lax forms mean that you can't straightforwardly say INSERT is the reverse of DELETE, nor vice versa.

There are no keys in relation values, so AFAICT they don't affect shortcut expansions. The only difference I can see is that the final assignment might trigger a key violation in the 'strict' cases.

I take RM VSS 2 'key inference' to be a Prescription. As far as I'm concerned there are always keys in relation values, even if the key is whole-heading.

I'd at least rewrite that expansion to avoid UPDATE on RHS.

Does that mean EXTEND and a whole heap of RENAMEs, or is there a shorter way? TD p19 seems to say that EXTEND is allowed to replace an existing attribute, which means EXTEND may not extend a relation at all, but rather trigger a reduction in cardinality. I read it, and I just don't know.

Yeah another bit of lax behaviour I don't like -- although I can see that it adds to ease of programming. Why are you worried about a "shorter way"? Nothing's telling you how to implement it or worrying about 'efficiency'. It's purely to express the semantics.

A shorthand IMO is always a simple text rewrite. I would prefer the rewrite to be as short as possible, and no shorter. Efficiency is of no concern.

For that lax form of EXTEND, I'd express the semantics as:

  • Generate a fresh attribute name, EXTEND to that as target. (You'll also need to rework any references to the 'new' value in the 'existing' attribute name.
  • Project away the existing attribute -- this is where you might reduce cardinality.
  • RENAME the fresh attribute to pre-existing name.

Or RENAME, EXTEND, PROJECT(/REMOVE).

Yes. Either way the PROJECT/REMOVE is the only point you have to worry about cardinalities.

The clarity you get here is that the only operation that might reduce cardinality is projection. You know that if the projected-away attribute is not part of a key, you're not reducing cardinality. Note that if the target attribute is part of a key, the EXTEND to fresh attribute could add an extra key to include the fresh. At that point you might generate duplicates for that key.

Since values have no keys, you can't know that until the final assignment. The projected away attribute might be restored by a subsequent EXTEND or JOIN.

Do key inference at every operation. (That's why it's good to have a small set of primitive operations.) An EXTEND (non-lax form) can't influence existing keys; it might add extra (candidate) keys.

I do suffer enough of a hangover from Entity-Relationship modelling to think there's something deeply smelly about UPDATE that targets (part of) a key. I do design schemas with the intent one of the keys should be permanent/persistent, so I'm happy to talk of 'the' P-Key to SQL-ers. I think TTM is just too precious.

Fair enough, but it really only affects the final assignment.

If you do key inference along the way, there's no surprises with the final assignment. Static key (i.e. type) inference should reveal whether some update operation is liable to throw key-related run-time errors. IMO the compiler shouldn't accept code that might do that: if the programmer knows they won't get key violations (perhaps because application code has in effect already checked uniqueness), they should express that with explicit relational ops so the compiler can see what's going on.

(So the only way you might get run-time key-related errors is with interference from other users/other threads; and that's what COMMIT control is for.)

My aim is to break these more complex operations down to their elements, inspired by trying to express them in Knime. There is a layer of fuzziness between the basic algebra and the final language, and my aim is to de-fuzz.

On a related note, there is really no MA, except that two or more final assignments might need to be performed simultaneously, either to satisfy a database constraint or because they are depend on each other. All the other MAs can be treated as shortcuts and expanded. The process to do that is spelled out in some detail in TTM.

 

Yes. I said "it's a Multiple Assignment in effect"; and MAs to the same relvar target can be expanded to a non-MA. That is, "really no MA".

There are no keys in relation values, so AFAICT they don't affect shortcut expansions. The only difference I can see is that the final assignment might trigger a key violation in the 'strict' cases.

I take RM VSS 2 'key inference' to be a Prescription. As far as I'm concerned there are always keys in relation values, even if the key is whole-heading.

For the purposes of expanding shortcuts, there are no keys.

Or RENAME, EXTEND, PROJECT(/REMOVE).

Yes. Either way the PROJECT/REMOVE is the only point you have to worry about cardinalities.

Yes, any operation that includes REMOVE might reduce cardinality, because there can be no duplicate tuples. In principle you can reverse that by JOINing back to the original.

The clarity you get here is that the only operation that might reduce cardinality is projection. You know that if the projected-away attribute is not part of a key, you're not reducing cardinality. Note that if the target attribute is part of a key, the EXTEND to fresh attribute could add an extra key to include the fresh. At that point you might generate duplicates for that key.

Since values have no keys, you can't know that until the final assignment. The projected away attribute might be restored by a subsequent EXTEND or JOIN.

Do key inference at every operation. (That's why it's good to have a small set of primitive operations.) An EXTEND (non-lax form) can't influence existing keys; it might add extra (candidate) keys.

Doesn't key inference depend on inspecting actual data values? What else is there to go on?

I do suffer enough of a hangover from Entity-Relationship modelling to think there's something deeply smelly about UPDATE that targets (part of) a key. I do design schemas with the intent one of the keys should be permanent/persistent, so I'm happy to talk of 'the' P-Key to SQL-ers. I think TTM is just too precious.

Fair enough, but it really only affects the final assignment.

If you do key inference along the way, there's no surprises with the final assignment. Static key (i.e. type) inference should reveal whether some update operation is liable to throw key-related run-time errors. IMO the compiler shouldn't accept code that might do that: if the programmer knows they won't get key violations (perhaps because application code has in effect already checked uniqueness), they should express that with explicit relational ops so the compiler can see what's going on.

(So the only way you might get run-time key-related errors is with interference from other users/other threads; and that's what COMMIT control is for.)

The compiler can't inspect data values. Yes, changing the S key S# from S3 to S4 might trigger a key violation, but changing it to S99 cannot and the compiler can't know.

My aim is to break these more complex operations down to their elements, inspired by trying to express them in Knime. There is a layer of fuzziness between the basic algebra and the final language, and my aim is to de-fuzz.

On a related note, there is really no MA, except that two or more final assignments might need to be performed simultaneously, either to satisfy a database constraint or because they are depend on each other. All the other MAs can be treated as shortcuts and expanded. The process to do that is spelled out in some detail in TTM.

 

Yes. I said "it's a Multiple Assignment in effect"; and MAs to the same relvar target can be expanded to a non-MA. That is, "really no MA".

Just so.

Andl - A New Database Language - andl.org
Quote from dandl on April 6, 2020, 10:19 am

There are no keys in relation values, so AFAICT they don't affect shortcut expansions. The only difference I can see is that the final assignment might trigger a key violation in the 'strict' cases.

I take RM VSS 2 'key inference' to be a Prescription. As far as I'm concerned there are always keys in relation values, even if the key is whole-heading.

For the purposes of expanding shortcuts, there are no keys.

Or RENAME, EXTEND, PROJECT(/REMOVE).

Yes. Either way the PROJECT/REMOVE is the only point you have to worry about cardinalities.

Yes, any operation that includes REMOVE might reduce cardinality, because there can be no duplicate tuples. In principle you can reverse that by JOINing back to the original.

The clarity you get here is that the only operation that might reduce cardinality is projection. You know that if the projected-away attribute is not part of a key, you're not reducing cardinality. Note that if the target attribute is part of a key, the EXTEND to fresh attribute could add an extra key to include the fresh. At that point you might generate duplicates for that key.

Since values have no keys, you can't know that until the final assignment. The projected away attribute might be restored by a subsequent EXTEND or JOIN.

Do key inference at every operation. (That's why it's good to have a small set of primitive operations.) An EXTEND (non-lax form) can't influence existing keys; it might add extra (candidate) keys.

Doesn't key inference depend on inspecting actual data values? What else is there to go on?

At minimum, it's possible to statically infer keys from the relvar KEY specifications and the semantics of each relational operator.

There may be additional keys that can be dynamically identified in the data.

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

There's an unfortunate ambiguity in the use of the term key.  Sometimes it means 'key constraint'; that is, a constraint on a relvar that the tuples of any relation assigned to it are unique over the attributes specified in the constraint.  But sometimes it means 'candidate key'; that is, a subset of the attributes of a relation such that the tuples of the relation are unique when projected over these attributes.