Access Routes in Personnel System Example

Consider the Department, Section and Employee tables which are part of the Personnel System Application database.

For the Department table, access routes to both the Employee and Section tables are created. The access routes tell LANSA how the Department table will access the other tables:

• Department will access the Employee table by using an index over PSLMST which is keyed by DEPTMENT. When Department accesses Employee, it will expect to find many records since a Department can have many Employees.

• Department will access the Section table by using the physical table with the DEPTMENT column as the key. When Department accesses Section, it will expect to find more than one record since a Department can have many Sections.

For the Section table, a relationship back to Department and a relationship to Employee are needed.

• Section will access the Department table by using the physical table with the DEPTMENT column as the key. When Section accesses Department, it will expect to find just one record since a Section belongs to just one Department

• Section will access the Employee table by using an index over PSLMST which is keyed by DEPTMENT and SECTION. When Section accesses Employee, it will expect to find many records since a Section can have many Employees.

For the Employee table, a relationship back to both the Department and Section tables are needed

• Employee will access the Department table by using the physical table with DEPTMENT column as the key. When Employee accesses Department, it will expect to find just one record since a Employee works in just one Department at a time.

• Employee will access the Section table by using the physical table with SECTION column as the key. When Employee accesses Section, it will expect to find just one record since a Employee works in just one Section at a time.