Understanding Relative File Structures: Key Concepts & Implementation

1. Relative Files

2. Relative Files • Data - • Records are accessed in a file relative to the beginning of the file • ACCT# 0001 will the first record in the file • ACCT# 0534 will be the 534th record in the file • Disk space must be reserved in advance • Some files/records the Record key readily translates into a relative file location • Customer records - ACCT # • Employee records - Employee#

3. Relative Files • Other - Record key may require some sort of conversion (hashing) to determine the relative position • SSNumber - 321301670 - 32,130,670th record ?? • Not every record location is filled with data

4. Relative - SELECT CLAUSE • ORGANIZATION IS INDEXED • ACCESS IS SEQUENTIALRANDOMDYNAMICRELATIVE KEY IS data-nameidentifies a WS item that contains the record number to be accessed • Unsigned Numeric • > 0 and <= number of records pre-allocated • Optional is ACCESS IS SEQUENTIAL

5. Relative Files- SELECT CLAUSE • [FILE STATUS IS data-name-3] • An optional working storage item to record the status of I/O operations • Common Status Codes • 00 - Successful • 22 - Duplicate Key • 24 - Boundary Violation • 30 - Permanent error

6. ALLOCATING a Relative File • Pre-format a file with number of records needed • RELATIVE file is defined for SEQUENTIAL access • Use a WRITE statement to add records • MOVE SPACES TO CUTOMER-MASTER-RECORD PERFORM 1000 TIMES WRITE CUSTOMER-MASTER-RECORD END-PERFORM

7. LOADING A RELATIVE FILE • Assumption Customer-Number is unique and sequential - input file not sorted • ACCESS IS RANDOM • Need to READ the file first to be sure a record does not exist in the location • If the record is all SPACES okay to add using a REWRITE

8. Pseudocode for Loading Relative File Open filesDO WHILE data remains READ record from sequential file AT END Indicate no more data NOT AT END Move Acct-No to WS-Relative-Key Read Customer-Master If Customer-Master-Record Spaces Move Data from input to Output REWrite Customer-Master-record Invalid Key Display error - record already exists End-Rewrite ENDIF ENDREADENDDOClose filesStop run

9. Writing a Relative File • File defined as SEQUENTIAL access • RELATIVE KEY clause optional • File OPEN OUTPUT • WRITE record-name INVALID KEY imperative statements NOT INVALID KEY imperative statementsEND-WRITE

10. Reading a Relative File • SEQUENTIAL access • File OPEN INPUT • READ file-name AT END imperative statements NOT AT END imperative statementsEND-READ

11. Reading a Relative File • RANDOM access • File OPEN INPUT • RELATIVE KEY must be set prior to READ • READ file-name INVALID KEY imperative statements NOT INVALID KEY imperative statementsEND-READ

12. Updating a Relative File • Access defined as SEQUENTIAL or RANDOM • File OPEN I-O • Be sure to initialize RELATIVE KEY • A Read must precede any write • REWRITE record-name INVALID KEY imperative statements NOT INVALID KEY imperative statementsEND-REWRITE

13. Deleting records from a RELATIVE File • If access is SEQUENTIAL a READ must precede the DELETE • If RANDOM the RELATIVE KEY must be set prior to the DELETE • DELETE file-name INVALID KEY imperative statements NOT INVALID KEY imperative statementsEND-DELETE

14. DYNAMIC Access - START • File can be read both sequentially and randomly • START file-name KEY IS {=, >, >=} data-name INVALID KEY imperative statements NOT INVALID KEY imperative statementsEND-START • START simply positions the file’s record pointer a READ must follow

15. DYNAMIC Access - READ • READ file-name NEXT RECORD AT END imperative statements NOT AT END imperative statementsEND-READ

16. Hashing • Converts a record key to a relative key • Remainder Algorithm • Folding • Digit Extraction • Square Value Truncation • In all cases the record key is processed through a mathematical formula to produce a relative key • Relative Key must be > 0 and <= max records

17. Remainder Algorithm • Pick a prime number divisor close to the max number of records expected. But not greater than • Divide the record key by the divisor to obtain the remainder • Add 1 to the remainder

18. Remainder Algorithm - Example • CUSTOMER-NUMBER = 5532 • File has been formatted for 90 records • Divisor = 89 • 5532 / 89 = 62 with remainder 14 • Relative Key = 14 + 1 = 15 • CUSTOMER-NUMBER = 0548 • 548 / 89 = 6 with remainder 14 • Relative Key = 14 + 1 = 15

19. How to handle Collisions • Search sequentially for next available record slot • Place record in an overflow area • Extra record space allocated over and above the expected number of records

20. Folding • Separate record key into parts • Add the parts together • Truncate high order digits • EXAMPLE • File formatted for 1000 records • SSNUMBER is record key • SSNUMBER = 024-57-4594 • 024 + 574 + 594 = 1192 • Truncate High Order • RELATIVE KEY = 192

21. File Organization Comparisons • SEQUENTIAL • Storage medium - any • ACCESS - SEQUENTIAL ONLY • Creation - SEQUENTIAL • Update - SEQUENTIAL • KEYS - None Required • Advantages • Uses storage efficiently Easy to Extend • Flexible record format Variety of medium • Disadvantages • Sequential Access Only

22. File Organization Comparisons • INDEXED • Storage medium - Disk Only • ACCESS - SEQUENTIAL RANDOM DYNAMIC • Creation - SEQUENTIAL RANDOM DYNAMIC • Update - SEQUENTIAL RANDOM DYNAMIC • KEYS - RECORD KEY ALTERNATE KEYS • Advantages • Flexible Access and updating • Disadvantages • Requires more disk space and more memory to process • May require multiple accesses to INDEX and DATA area

23. File Organization Comparisons • RELATIVE • Storage medium - Disk Only • ACCESS - SEQUENTIAL RANDOM DYNAMIC • Creation - SEQUENTIAL RANDOM • Update - SEQUENTIAL RANDOM DYNAMIC • KEYS - RELATIVE KEY may be record key • Advantages • Flexible Access and updating • Fast Access • Disadvantages • Fixed length records only Synonyms • Wasted disk space