# Managing time in relational databases- P12

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:20

0
37
lượt xem
4

## Managing time in relational databases- P12

Mô tả tài liệu

Tham khảo tài liệu 'managing time in relational databases- p12', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:

Bình luận(0)

Lưu

## Nội dung Text: Managing time in relational databases- P12

1. 204 Chapter 9 AN INTRODUCTION TO TEMPORAL TRANSACTIONS Another aspect of the semantics of a temporal insert is the interpretation of its target span. If the transaction does not sup- ply an effective end date, that date defaults to 12/31/9999. The result is that an open episode will be created. However, in the scenario shown in Figure 9.6, an open episode cannot be cre- ated, and the AVF will reject the attempt to create one. For if we tried to create an open episode with an effective begin date prior to version 7, that new episode would collide with one of the three episodes shown, and thus violate the temporal entity integrity (TEI) constraint. And if we tried to create an open epi- sode, or in fact any episode, with an effective begin date on or after May 2013, it would collide with Episode C, whose effective end date is 12/31/9999. It follows that there can be at most one open episode for an object, within any period of assertion time. No episode, open or closed, can be created later than an open episode because if it were created, it would occupy effective time already occupied by that open episode. And an open episode cannot be created earlier than any other episode, open or closed, for the same reason. What of a temporal insert that specifies a non-12/31/9999 effective end date? Here the situation is more straightforward. Such transactions satisfy TEI just in case none of the clock ticks specified in the transaction’s target span are already occupied by that object. And we should remember that because of our closed-open convention, TEI will still be satisfied if an episode already exists whose begin date has the same value as the end date of the transaction’s target span, or one whose end date has the same value as the begin date of the transaction’s target span, or if both such episodes are present. The Temporal Insert Transaction: Mechanics The scope of a conventional insert is a single row in the target table. If the transaction is successful, the result is a new row added to the table. The scope of a temporal insert is a designated period of effective time, within current assertion time. So, in Figure 9.6, that scope cannot include any of the clock ticks already occupied by any of the three episodes shown there, but can include any other clock ticks. We should note that this means that the scope of the insert cannot be any clock tick from May 2013 forward. Since Episode C is open, it extends to 12/31/ 9999; consequently any other episode for the same object any- where in the effective period starting with May 2013 would [intersect] that episode.
2. Chapter 9 AN INTRODUCTION TO TEMPORAL TRANSACTIONS 205 So a temporal insert of P861, given the state of the target table shown in Figure 9.6, can have an effective begin date which is any clock tick not occupied by any of the three episodes. What, then, of the effective end date? With a basic scenario temporal insert, both the effective begin and end dates are left to take on their default values which are, respectively, the date current when the transaction is applied, and 12/31/9999. But just as the effective begin date can be overridden on a transaction, so too can the effective end date. What this means is that when a new episode is created, it does not need to be created as an open episode. If the user happens to know the effective end date for an episode of an object she is creating, she can specify this date on the temporal insert, as long as it is at least one clock tick later than the effec- tive begin date, and does not [intersect] a time period already occupied by another episode of the same object. In the scenario shown in Figure 9.6, in fact, a temporal insert would have to specify an effective end date. The reason is that no matter what the effective begin date is, an effective end date of 12/31/9999 would [intersect] one of the episodes already in the table. For example, a temporal insert could place a new episode between the second two episodes, but given that our clock ticks only once a month, it would be a tight fit. In fact, there are only three possibilities. They are: (i) P861[Feb 2013 – Mar 2013] (ii) P861[Feb 2013 – Apr 2013] (iii) P861[Mar 2013 – Apr 2013] There are four ways in which Episode B could be {lengthened forwards}, the last listed of which would {merge} it with Episode C. They are: (i) P861[Jan 2013 – Feb 2013] (ii) P861[Jan 2013 – Mar 2013] (iii) P861[Jan 2013 – Apr 2013] (iv) P861[Jan 2013 – May 2013] And there are also four ways in which Episode C could be {lengthened backwards}, the first listed of which would {merge} it with Episode B. They are: (i) P861[Jan 2013 – May 2013] (ii) P861[Feb 2013 – May 2013] (iii) P861[Mar 2013 – May 2013] (iv) P861[Apr 2013 – May 2013] As we said earlier, no temporal insert would be valid that spe- cified an effective time period of May 2013 or later. However, any temporal insert would be valid that specified an effective time
3. 206 Chapter 9 AN INTRODUCTION TO TEMPORAL TRANSACTIONS period that ended on February 2010 or earlier because that insert would not result in a time period that [intersected] any time period for P861 already in the target table. Sometimes we have no open episode of an object, but we may want to wake up the latest closed episode and change it to an open episode. It is only the latest episode of an object that can be transformed into an open episode, of course, because if it were not the latest, it would then collide with the next later epi- sode and thus violate the TEI constraint. So if a temporal insert specifies a 12/31/9999 end date, and no effective begin date is provided, the target span is [Now() – 12/31/9999]. An insert with this target timespan will wake up an existing episode of an object only if (i) there is a closed epi- sode of the object already in the target table, and its effective end date is Now() (a situation which is very unlikely to occur when timestamps rather than dates are used to specify temporal parameters); and (ii) there are no representations of the object which begin in future effective time. So we cannot wake up a closed episode unless we supply an effective begin date on an insert transaction which matches the effective end date of an existing episode, and there are no other episodes of that object in later effective time. Waking up a closed episode is a special case of the {lengthen episode forwards} transformation. But in every case, what the user must do to lengthen an episode forwards is to specify a target span whose effective begin date matches the effective end date of the last version of a closed episode, and whose effective end date does not [intersect] that of the next later epi- sode of that object, if there is one. In this latter case, the episode is {lengthened forwards} because an unknown end date (represented by 12/31/9999) is replaced by a known end date. The Temporal Update Transaction The format of a temporal update transaction is as follows: UPDATE {tablename} [,,, ] eff_beg_dt, eff_end_dt, asr_beg_dt The validity checks work like this: (i) No oid, no business key, business key is reliable. In this case, the AVF rejects the update. The reason is that an update must attempt to find a match, and must be rejected if it cannot make that attempt. Lacking both an oid and a reliable business key, the transaction cannot be matched to what is already in the target table.
13. 216 Chapter 10 TEMPORAL TRANSACTIONS ON SINGLE TABLES Creating an Episode Figure 10.2 depicts the history of policy P861 along a frag- ment of the calendar timeline, the fragment extending from January 2010 through January 2014. Let’s recall that our examples all use, unless otherwise indicated, a clock that ticks once a month. And so, using our page-width-preserving notation, “Jan10” stands for January 1st, 2010, “May12” for May 1st, 2012, and so on. In the text, we will write, more concisely, “January 2010” and “May 2012”, not bothering to indicate that the clock always starts the tick on the first moment of the first day of each month and lasts through the last moment of the last day of that month. We emphasize here a point that we originally made in Chapter 3. A clock tick is a logical concept, not a physical one. It is the smallest unit of time that can intervene between two adjacent versions of the same object. Thus, if one version begins on the first of the month, then given the granularity for clock ticks used in the examples in this book, the next one cannot begin until the first of the next month. A clock tick gap, in physical time, is whatever the duration of a clock tick happens to be. But the duration of a clock tick, in phys- ical time, is not the issue. As far as recorded data is concerned, there is no duration. There are either clock ticks that happen between two rows of interest to us, or clock ticks that don’t. Unless otherwise indicated, these diagrams show the history of our policy as we currently believe it to be. In other words, it depicts the history of our policy in current assertion time. Here is a temporal insert transaction, which takes place on August 2011. INSERT INTO Policy [P861, C882, PPO, $30] Jan 2011, Mar 2011 This is a retroactive transaction because it is attempting to insert a representation of the policy into an effective time period that is already in the past. This transaction is directing us to insert its data into January 2011 and February 2011. Another way to express this target time period in English, using this example, would be “Starting on January 2011 and continuing up to but not including March 2011 . . . . .” Episode A Episode B 1 2 3 4 Jan Jan Jan Jan Jan 2010 2011 2012 2013 2014 Figure 10.2 Creating an Episode: Before the Transaction. 14. Chapter 10 TEMPORAL TRANSACTIONS ON SINGLE TABLES 217 Episode A Episode C Episode B 1 2 5 3 4 Jan Jan Jan Jan Jan 2010 2011 2012 2013 2014 Figure 10.3 Creating an Episode: After the Transaction. Immediately prior to this transaction, the currently asserted life history of policy P861 is as shown in Figure 10.2. After the transaction is completed, that history will be the history of the policy as asserted up to (but not including) August 2011. The new currently asserted history of the policy will be as shown in Figure 10.3. Episode C, as we have labeled it, is a single-version episode. Of course all episodes, when initially created, are single-version episodes. Using the in-line notation introduced in Chapter 6, this new episode looks like this: P[P861[Jan11-Mar11][Aug11-9999][Jan11][C882, PPO,$30] [Aug11]] This example shows our business correcting a mistake. The mistake was forgetting to record that policy P861 was in effect for those two months until eight months after the fact. What probably happened is that in August, the policy holder filed a claim against the policy. The claim was rejected, the policy holder complained, people in the company did some research, the customer service representative apologized, the policy was retroactively created and, finally, the claim was processed. New episodes can be created proactively, before they go into effect, just as easily as they can be created retroactively, after they go into effect. But in the scenario shown here, that is not possible. The reason is that Episode B is an open episode; it remains in effect until further notice. Its version 4 has an effective end date of 12/31/9999. Therefore, any attempt to proactively create a new episode, i.e. to create an episode that began sometime after August 2011, would violate temporal entity integrity. Lengthening an Episode Backwards In the previous example, the mistake our company made was to fail to record that a policy was in effect until several months after the fact. Another kind of mistake is to get the effective begin date wrong. For example, Figure 10.3 shows the first episode of
15. 218 Chapter 10 TEMPORAL TRANSACTIONS ON SINGLE TABLES our policy becoming effective on February 2010. But let’s sup- pose that the correct date was actually the month before that. How will we make this correction? Because this correction will place data about an object into a clock tick where it previously was absent, it is carried out with a temporal insert transaction. Similarly, if we mistakenly place data about an object into a clock tick where it doesn’t belong, we will need to use a temporal delete transaction to correct our mistake. So we should note that with temporal data, it is not just update transactions that can correct mistakes. Immediately prior to this transaction, the currently asserted life history of policy P861 is as shown in Figure 10.3. It is still August 2011, and we now submit the following transaction to the AVF: INSERT INTO Policy [P861, C882, PPO, $30] Jan 2010, Feb 2010 Immediately after this transaction is completed, that history will be the history of the policy as asserted from when the previ- ous transaction was applied up to August 2011. The new cur- rently asserted history of the policy will be as shown in Figure 10.4. The new version—version 6—looks like this: P[P861[Jan10-Feb10][Aug11-9999][Jan10][C882, PPO,$30] [Aug11]] Unlike our first insert transaction, this one does not create a new episode; it extends an existing episode backwards in time. But in doing so, it changes that episode’s begin date. As we can see from the schema common to all asserted version tables, the episode begin date is repeated on every version in an epi- sode. And by extending this episode backwards, this transaction changes that episode’s begin date. So as part of the atomic isolated unit of work started by this transaction, the AVF must also change the episode begin date on versions 1 and 2, the other two versions in the new Episode A. This is done by withdrawing those two versions and replacing them with versions identical to them except that they have the correct episode begin date. Those two replacement versions are versions 7 and 8, and the versions Episode A Episode C Episode B 6 71 82 5 3 4 Jan Jan Jan Jan Jan 2010 2011 2012 2013 2014 Figure 10.4 Lengthening an Episode Backwards: After the Transaction.
17. 220 Chapter 10 TEMPORAL TRANSACTIONS ON SINGLE TABLES Episode A 6 71 82 9 Jan Jan Jan Jan Jan 2010 2011 2012 2013 2014 Figure 10.6 Lengthening an Episode Forwards: After the Transaction. It is May 2010, and the following transaction is submitted to the AVF: INSERT INTO Policy [P861, C882, HMO, $25] Oct 2010, Dec 2010 Immediately after this transaction is completed, the history shown in Figure 10.5 will no longer be currently asserted. Instead, it will be the history of the policy as asserted from when the previous transaction was applied up to May 2010. The new currently asserted history of the policy will be as shown in Figure 10.6. Version 9 looks like this: P[P861[Oct10-Dec10][May10-9999][Jan10][C882, HMO,$25] [May10]] Episode begin dates are not supplied on transactions. They are always determined by the AVF. In this case, the AVF sees that there is already a version of that same policy in the table whose effective end date [meetsÀ1] the effective begin date on the transaction. Because this match means that the episode is being extended forwards, the AVF assigns version 9 the episode begin date on the version it is contiguous with—in this case, version 8. So now, on May 2010, we are claiming that we know several things about policy P861’s future. We know that it will remain in effect until December 2010, i.e. through November 30th, 2010. We know that from May 2010 to October 2010, it will have the properties ascribed to it by version 8. We also know that in October and November of that year, it will be an HMO policy with a $25 copay. We say we know this about the future. But, of course, as with any statement about the future, we may be wrong. However, since anyone viewing this data about the future will understand this, we are misrepresenting nothing. Merging Episodes We have seen how temporal inserts can create new episodes and can extend existing episodes backwards and forwards. But when any but the latest episode of an object is extended 18. Chapter 10 TEMPORAL TRANSACTIONS ON SINGLE TABLES 221 forwards, there is an episode ahead of it to “bump into”. Simi- larly, when any but the earliest episode of an object is extended backwards, there is an episode behind it to bump into. In either case, however, a successful temporal insert will {merge} the two adjacent episodes together. The resulting epi- sode will contain all the versions in both of the original episodes, and also the one version that caused them to {merge}. To illustrate a temporal transaction merging two episodes, let’s go all the way back to Figure 10.2 and assume that it is now January 2012. Immediately after this next transaction is completed, the history shown in Figure 10.2 will no longer be currently asserted. Instead, it will be the history of the policy as asserted from when the previous transaction was applied up to January 2012. The new currently asserted history of the policy will be as shown in Figure 10.7. It is now January 2012, and the following transaction is sub- mitted to the AVF: INSERT INTO Policy [P861, C882, POS,$15] Oct 2010, Apr 2011 Version 7 looks like this: P[P861[Oct10-Apr11][Jan12-9999][Feb10][C882, POS, $15] [Jan12]] The AVF sees that this transaction will {merge} two episodes. It can tell that because there are already two versions of that same pol- icy in the table such that the effective end date of the earlier one [meetsÀ1] the effective begin date on the transaction, and the effec- tive begin date of the later one [meets] the effective end date on the transaction. Because the result is a single episode, all versions in that episode must have the same episode begin date. Therefore, to com- plete the transaction, the AVF sets the episode begin date of version 7 to February 2010, and withdraws versions 3 and 4, replacing them with versions that are identical except that they remain in current assertion time and have an episode begin date of February 2010. We have now discussed all the temporal extent trans- formations that are carried out in response to a temporal insert transaction. At the end of this chapter, we will review the Episode A 1 2 7 53 64 Jan Jan Jan Jan Jan 2010 2011 2012 2013 2014 Figure 10.7 Merging Adjacent Episodes: After the Transaction. 19. 222 Chapter 10 TEMPORAL TRANSACTIONS ON SINGLE TABLES taxonomy of temporal extent transformations shown in Fig- ure 9.5 (and repeated as Figure 10.21) to be sure that we have demonstrated that each of them can be produced by one of the three temporal transactions. In the meantime, let’s turn to temporal update transactions. But before we do, let’s note that update transactions do not bring about temporal extent transformations. A temporal extent trans- formation necessarily involves adding the representation of an object to one or more clock ticks, or removing the representation of an object from one or more clock ticks. Temporal updates do neither. They simply change business data on versions that [intersect] a designated target range. The Temporal Update Transaction A temporal update transaction specifies (i) an object, (ii) a value for one or more columns of business data, and (iii) a target effective timespan, which we sometimes also call a target range for the transaction. The transaction includes the object identifier (oid), if it is known to the user. If an oid is not provided on the transaction, the AVF attempts to find or create one according to the rules described in the previous chapter. Finally, the transac- tion either accepts the default values for its temporal parameters, or overrides one or more of them with explicit values. Although a temporal update does not alter the temporal extent of an object, it is still the most complex of the three temporal trans- actions to implement. There are always existing versions that must be withdrawn. If parts of those versions do not [intersect] the trans- action’s target range, those parts must be replaced. Then the parts of versions or entire versions that do [intersect] the target range must be superceded with versions that contain the new data. As long as even a single clock tick in the transaction’s target range [intersects] the effective time period of some version of the specified object, the temporal update is valid because it means that there is data for the transaction to modify. A valid update is always carried out by creating one new version for every version found within the target range, as well as a new version for every part of a version found within the target range. These versions are the ones that supercede, in current assertion time, the versions that fall within the timespan of the transaction. A valid update will also cre- ate a new version for those parts of versions, if any, that do not [intersect] the target range. These new versions contain no new business data. They are simply replacements for those parts of withdrawn versions that were unaffected by the update. 20. Chapter 10 TEMPORAL TRANSACTIONS ON SINGLE TABLES 223 A temporal update’s target range may include part of an epi- sode or version, an entire episode or version, multiple episodes or versions, or any combination thereof. But a temporal update never creates a new episode, and never adds to or subtracts from the total count of clock ticks occupied by its referenced object. We will need only a single example to illustrate the most important variations of temporal updates. We begin with a sam- ple database whose rows 1–6 represent the versions 3–8 shown in Figure 10.4. This is the sample database shown in Figure 10.9. Row 1 represents version 7, the currently asserted replacement for version 1. Row 2 represents version 8, the currently asserted replacement for version 2. Rows 3–6 represent versions 3–6. We will show none of the past assertion history that led to this database state, but only the six versions that are currently asserted. But because this is a relatively complex transaction, we will illustrate its progress one physical transaction at a time. The mapping shown in Figure 10.8 illustrates the steps the AVF will go through to complete the temporal update. Temporal Update Physical Transaction(s) Update an object Withdraw the affected within a designated versions. timespan. Assert the before-update replacements. Assert the after-update successors. Figure 10.8 The Temporal Update Transaction: Temporal to Physical Mapping. Jan12 UPDATE Policy [P861, , ,$40] Jul 2010, Jul 2011 Jan Jan Jan Jan Jan 2010 2011 2012 2013 2014 Row oid eff-beg eff-end asr-beg asr-end epis- client type copay row-crt # beg 1 P861 Feb10 Apr10 Feb10 9999 Jan10 C882 HMO $15 Feb10 2 P861 Apr10 Oct10 Mar10 9999 Jan10 C882 HMO$20 Apr10 3 P861 Apr11 Jul11 Apr11 9999 Apr11 C882 PPO $20 Apr11 4 P861 Jul11 9999 Jul11 9999 Apr11 C882 HMO$15 Jul11 5 P861 Jan11 Mar11 Aug11 9999 Jan11 C882 HMO $20 Aug11 6 P861 Jan10 Feb10 Oct11 9999 Jan10 C882 PPO$20 Oct11 Figure 10.9 Updating a Policy: Before the Transaction.