bdfd1m0oDatabase Administration

Mapping the Passenger File

Each LREC in the passenger file contains the fields shown in Table 31. This table also shows the number of bytes in each field.

Table 31. LREC Fields for the Passenger File

Field No. of bytes
size 2
key 1
passenger number 8
passenger name 25
passenger address n (10-50)
flight information 17
passenger facts 4
Total 107

From this table, you can see that each LREC in the passenger file contains from 57-97 bytes. (The passenger address field has a minimum of 10 bytes and a maximum of 50 bytes.)

Data requirements for the passenger file must be calculated twice. You need to know the requirements for the average number of flights (3) and for the maximum number of flights (20).

You can calculate the data requirements for the average number of flights as follows:

number + name + address + facts + (average no. of flights
x flight information) = amount of data

The calculation is:

8 + 25 + n + 4 + (3 x 17) = 88 + n bytes

The calculation for the maximum number of flights is as follows:

8 + 25 + n + 4 + (20 x 17) = 377 + n bytes

From these calculations, you can see that most passenger LRECs fit into a single block of L1 size (320 bytes). However, you need 1 overflow block for each LREC where passengers are taking 14 flights or more.

Spreading Data over Several LRECs

Some of the passenger LRECs contain too much data to fit into a single 381-byte block. Moreover, the data held in variable length LRECs is likely to vary in size.

Because the TPFDF product does not allow you to spread a single LREC over more than 1 block, you must spread the data over several LRECs. The alternative of using larger blocks for the passenger LRECs is wasteful because only a few of the LRECs need the larger size block.

Looking at the passenger file again (Table 32), you can see that the data can be split into five separate LRECs.

Table 32. Passenger File

Passenger number Passenger name Passenger address Flight Date Passenger facts
Pn1 Na1 Ad1 Fl1
Fl2
Fl3
Da1
Da1
Da2
Ft1
Pn2 Na2 Ad2 Fl1 Da1 Ft1
Pn3 Na3 Ad3 Fl1 Da1  
Pn4 Na4 Ad4 Fl1 Da1  

Figure 15 shows the five new LRECs and the number of bytes held in each LREC.

Figure 15. Spreading Data over Several LRECs


Because the data is now spread over several LRECs, the database must hold slightly more data. (Each LREC holds 3 bytes of identifying data.) However, the data is easier to manipulate in this form so that performance is improved overall.

Note:
Name and number are kept as two separate LRECs in case the name field needs to be expanded in the future.