Sequential Update

Updating

Information that is kept on files needs to be modified as changes to the information on the file occur. This process is called UPDATING and the files that are being update are usually called MASTER FILES. Updating allows the company to keep a file up-to-date, without that capacity, the file would be obsolete shortly after it was created

Updating a file can involve the following:

· adding records to the file

· changing records on the file

· deleting records from the file

The add, change and delete transactions that will be used to update the master file are keyed in and saved on a transaction file. That transaction file will be processed with the master file to update the file.

There are two kinds of business updating. One is the classic concept of maintenance updating where information is added, changed and deleted. For example at a college, maintenance updating is done to add new students to the file, delete students that have dropped out and make changes to students records when the student changes majors, changes a course etc. Another kind of updating is production updating that occurs during processing and usually involves just changes. For example when a sales is made inventory for the item is update to reflect that there are less items on hand or when a payment is received the amount owed is updated to reflect the payment. This handout will deal with maintenance updating because it is the most comprehensive type.

Sequential Updating

Maintenance updating is the process by which files are kept current. In the sequential maintenance updating process, there are a series of three programs that compose the update maintenance cycle.

EDIT Program	The transactions that will be used in the update must be as error free as possible to prevent corrupting the master file. To assure the integrity of the data, the transactions should be processed in an edit program and only valid transactions should be allowed to update the master file. The output of the edit file includes the valid (good) transaction file which will be processed in the next step.
SORT Program	The master file is in some kind of order - the usual order is by identification number. The transaction file must be in the same order as the master file. The sort program sorts the valid transactions and creates a sorted valid transaction file.
UPDATE Program	The update program uses the sorted valid transaction file to update the master file, creating a new master file and reports. The update program completes the updating maintenance cycle.

Sequential Update Program

The file that is to be updated is a master file that contains information the company needs for example the payroll master file, the inventory master file, the student master file. Updating can be done either sequentially or randomly. Random update requires that the master file be indexed and so the code is beyond the scope of this handout.

Sequential processing involves a master input file, a transaction input file, a new master output file and one or more report files. The master file contains the master records for the current version of the file. The transaction file contains the activity that will be processed against the master file. This means that the transaction file contains information about the new records that should be added to the master file, the changes that need to be made to the master file, and the records that need to be deleted from the master file. The new master output file will be the result of the processing that added new records, changed existing records and deleted old records. An error report must be produced and usually there is also a report of the successful processing.

When a record is to be added, a new record is written to the new master file. A record that is to be deleted will either not be copied to the new master file thereby deleting from the newest version of the master file or it will be flagged in some way to indicate that it is an inactive record. Note that sometimes records need to be kept even though they are no longer active and have been set for deletion. An example of this is a payroll file where employees that have quit must be maintained on the file for end of year processing, but they are inactive since they are no longer an employee and no longer getting paychecks. These records would be flagged in some way for deletion. One way to flag the record is to have a field called active and change the code in that field to indicate an inactive record.

When a record is changed the data in one or more fields is changed. The processing involves adding, changing or deleting data in an individual field:

· data is put in a field that was previously empty (data was added)

· data in the field is replaced with other data (data was changed)

· data in the field is deleted and the field is set to blanks or zeros (data was deleted)

To add or change data, the user would put the data in the appropriate field on the transaction and the program will make the change to a copy of the existing master record in memory. Deleting data from a field involves a little more work. The programmer must determine how the user will indicate that data in a particular field is to be deleted. One possibility would be to put a hyphen in the last character of the field on the transaction. The program can then check the last character and if it is equal to a hyphen then spaces or zeros are moved to the field on the copy of the existing master record in memory. When all changes to fields are complete, the copy of the existing master record which has had the changes made will be written on the new master file.

Note that in making changes to the file, the identification number is usually not allowed to change because this would distroy the sequence of the records in the file. If the identification number is to change, the record should be deleted and then added.

Depending on the design of the update transactions, the change can take many forms. Three frequently used designs are explained here:

· A transaction can have a code indicating this is a change transaction for a specific field. For example, there might be a field called field to change. A code N in that field might mean this transaction will be used to change a name. In this case, only the name field is to be changed. If the address also needs to be changed, another transaction will be used.

· The transaction can be used to change several related fields. This is similiar to the first example in this list except that several related fields are carried on the same transaction. For example, since if street address changes, frequently city, state, and ZIP as well as phone number also change. Therefore all these fields could be carried on a single transaction.

· A transaction can carry all of the data to be changed in a layout similiar to the master record. In this case, all of the fields must be checked to see if a change is needed. Using this method, blanks in a field on the transaction would mean that no change is to be made to that field Data in a field on the transaction would mean that whatever in currently in the field should be replaced by the data in the field on the transaction (this could result in either adding or changing data - if the field on the master is blank, the program will add data - if the field on the master contains data, the program will replace and therefore change the data). A code such as a hyphen in the last character of the field could mean that the data in the field on the master is to be deleted (replaced by blanks or zeros).

FILES FOR A SEQUENTIAL UPDATE:

INPUT MASTER FILE	Main or master file containing company records for a particular application
INPUT TRANSACTION FILE	Transaction file containing the information to add new records to the file, change existing records, and delete records from the file
OUTPUT NEW MASTER FILE	New version of the main or master file containing all the old information that did not change or was not deleted, plus the added information and the changed information
OUTPUT TRANSACTION ERROR REPORT	Report of errors that could not be processed - this usually involves: · records that were supposed to be changed but aren’t there · records that were supposed to be deleted but weren’t there · records that were supposed to be added but a record with the same id was already there
OUTPUT GOOD TRANSACTIONS REPORT	This report is mainly a paper trail - it contains a list of the transactions that were successfully processed and any other information the company wants to record

Logic

The logic of the sequential update program starts by reading a master file record and a transaction record. Two things are checked to determine the processing that can be done:

· the identification number on the master file and the identification number on the transaction file are compared - the logic of the update dictates that the record with the smallest identification number is processed first - if the identification numbers are equal, the records are processed together

· the processing code (Add, Change, or Delete) on the transaction file is checked

If the transaction is a change and there is a master record with the same identification number as the transaction, the change will be made and the new record will be written on the output new master file. If the transaction is an add and no record exists on the master file with that identification number, a new record will be written on the output new master file. If the transaction is a delete and the record exists on the master file, the record will not be written on the output new master file, thereby deleting the record. If the master has no transactions that effect it, it is simply copied on to the output new master file.

A summary of the processing that will result from comparing the identification numbers and checking the transaction code is shown in the table below (Assume that MID means master identification number and TID means transaction identification number.)

	Tran Code = A	Tran Code = C	Tran Code = D
MID < TID	Since the master is less than the transaction, there is no activity for that master. The master is simply written on the new master file. A read is done on the input master file to get the next master record.
MID = TID	Invalid add transaction - error will be written on the report (can’t add a record that is already there). Since the transaction was processed and the master was not, a new transaction record is read.	Valid change transaction - changes are made to the master record in memory, record is written to valid transaction report and another transaction is read (this will allow for multiple changes to the same master record - see note below)	Valid delete transaction - the master record will not be written on the output new master file (see note below) - record is written to valid transaction report - another master record and another transaction record will be read
MID > TID	Valid add transaction - information from add transaction is written on the output new master file and valid transaction report. Since the transaction was processed, a new transaction record is read	Invalid change transaction - error will be written on the report (can’t change master that isn’t there). Since the transaction was processed, a new transaction record is read.	Invalid delete transaction - error will be written on the report (can’t delete master that isn’t here). Since the transaction was processed. a new transction record is read.

Notes:

· Note on valid change - if the programmer is certain that there is only one change per transaction, the change could be made and the changed record written on the new master file followed by the transaction read. However, usually the possibility of multiple changes is a reality. Consider for example a payroll file where a change of address is entered and then later the person gets a pay raise another transaction is therefore entered. Safe processing usually allows for multiple changes.

· Multiple transactions - as an extension to the concept of multiple changes, think about the situation where there are multiple transactions of different types. For example if there is an add and a change with the same id you would want the transactions sorted so that you process the add before the changes. Usually transactions are not just sorted by identification number but by code as well. The codes of A, C, and D accomodate the sorting by code.

· Note on valid delete - in some cases the record cannot be physically deleted so it is written on the new master file with a flag (a code that would indicate the record has been logically deleted). An example of this is a payroll file, if an employee quits in February, the system still needs to keep the employee to prepare W2 forms at the end of the year etc.

Summary of the chart above:

	Tran Code = A	Tran Code = C	Tran Code = D
MID < TID	· Write old master record on the new master file · Input master file is read
MID = TID	INVALID ADD · Invalid add written to error report · Transaction read	VALID CHANGE · Change is made in memory · Valid change tranaction written to valid report · Transaction read	VALID DELETE · Nothing is written to new master file · Valid delete transaction written to valid report · Transaction read · Input master file read
MID > TID	VALID ADD · Valid add written to new master file · Transaction written to valid report · Transaction read	INVALID CHANGE · Invalid change written to error report · Transaction read	INVALID DELETE · Invalid delete written to error report · Transaction read

End of file considerations

Sequential update processing involves processing two files against each other. If the transaction file reaches end of file before the master file that means there is no activity against the remaining master records and they should be simply copied to the new master file. If the master file reaches end of file before the transaction file, the only valid transactions are adds since you need a master to change or delete.

Remember that in sequential updating design logic so that we always process the file with the smaller identification number. To continue processing when end of file has been reached on one of the files, the file that still contains records should process as having the smallest identification number. To guarantee that this will happen, the programmer can move all binary 1s to the identification number of the file that has run out. If something is compared to all binary 1s it will always compare less so processing will continue.

Pseudocode for sequential update

This is a very “generic” pseudocode, for many updates certain sections will need to be modified to accomodate the specific processing that is required. This pseudocode covers the top level logic, it does not show many of the lower level routines that write the reports etc.

Initialize

· Open input master file, input transaction file and output new master file, output error report file, output valid transaction report file

· Accept date and time if needed

Processing Control

· Initializing read for the master file - if end of file is reached, move high values to the master identification number so that from now on the transaction file will always compare less than the master file

· Initializing read for the transaction file - if end of file is reached, move high values to the transaction identification number so that from now on the master file will always compare less than the transaction file

· Perform the B-200-LOOP until move files reach end of file (both files have high values in their identification number field)

· Perform any end of file processing including totals

Processing Loop

· Processing would be controlled with the following IF statement

IF MID < TID

Write the input master record on the output new master

Read input master file

ELSE

IF MID = TID

IF transaction code is C

Process the change routine

Write valid change transaction

Read transaction file

ELSE

IF transaction code is D

Write valid delete transaction

Read input master file

Read transaction file

ELSE

Write invalid add transaction

Read transaction file

ELSE

IF transaction code is A

Perform the add new record routine

Write valid add transaction

Read transaction file

ELSE

IF transaction code is C

Write invalid change transaction

Read transaction file

ELSE

Write invalid delete transaction

Read transaction file