bdfd1m0iDatabase Administration

Before You Begin

The tables used for mapping in this chapter are those developed in Organizing a Database and Optimizing the Database Design. They are as follows:

Data Requirements

Table 20 shows the data requirements for the tables that are to be stored on the database. The requirements shown here are examples only. They are the result of the initial data analysis carried out before the start of the design process.

Table 20. Data Requirements

Data Amount
Flights each day 100
Days stored 366
Passengers on each flight (min=50, max=300) 150 (average)
Flights for each passenger (max=20) 3 (average)
Flight classes 3
Aircraft types 50
Note:
Because leap years must be accommodated, 366 days can be stored.

Data Field Lengths

Before mapping the tables to TPFDF files, you must estimate the amount of data for each file. To do this, assign a length to each data field (attribute).

Many data fields (for example, the flight number or the destination code) have a fixed length. However, you cannot always be precise when assigning a length to a data field. For example, you cannot be sure how long a passenger's name will be.

Because of this, always allow for future expansion when you are assigning data field lengths. Where possible, use variable length LRECs, and set the variable length portion of the LREC in the DSECT to zero. This makes it easier to expand the field length later.

Table 21 shows the length of each data field from the tables developed in the previous chapters.

Table 21. Data Field Lengths

Table Data field Length in bytes
Aircraft aircraft type
seat range
seat class
4
8
1
Passenger name passenger name
pointer
25
5
Passenger number passenger number
pointer
8
5
Flight date
time
flight number
start
destination
aircraft type
availability
pointer1
pointer2
2
2
7 (airline=3, flight=4)
3
3
4
6
5
Seat seat number
seat class
passenger number
passenger name
pointer
4
1
8
25
5
Passenger passenger number
passenger name
passenger address
flight information (flight, date,
time, start, destination)
passenger facts
8
25
50
17
   
4
Note:
In the flight file, the date field has now been divided into two fields, date and time. This is done to provide a clearer display for the reservation agent.

Figure 14 shows the number of LRECs required for each of these six files.

Figure 14. Number of LRECs Required for Each File


Passenger LRECs

Before mapping the tables to TPFDF files, you must calculate the number of passenger LRECs required overall. From the Data Requirements table (Table 20), you can see that the following amounts are held:

Calculate the number of required passenger LRECs as follows: (flights each day x days stored x passengers on each flight) ÷. flights for each passenger

The calculation is:

(100 x 366 x 150) ÷. 3 = 1.825 million

The calculation shows that the database must be able to accommodate 1.825 million passenger LRECs.

Calculating the Number of Subfiles Needed

The following points will help you determine how many subfiles you need to accommodate the data in your file:

Block Size

The TPF system offers four different block sizes. Table 22 shows the amount of user data allowed for each block (assuming you are using optional trailers):

Table 22. TPF Block Sizes

Block type Bytes of user data
L0 127
L1 319
L2 993
L4 4033

The Airline Control System (ALCS) offers eight different block sizes as detailed in Table 23.

Table 23. ALCS Block Sizes

Block type Bytes of user data
L0 127 (max.)
L1 319 (max.)
L2 993 (max.)
L3 4000 (min.)
L4 4033 (max.)
L5 32K (max.)
L6 32K (max.)
L7 32K (max.)
L8 32K (max.)

Though TPF block sizes are fixed, ALCS block sizes show the maximum number of bytes for each size. In practice, ALCS blocks can be any size you choose. For easier data transfer between TPF and ALCS, blocks L1, L2, and L4 are the same size in both systems.

Note:
Block size affects performance. A large block size can reduce the amount of overflow blocks required but may waste DASD space.

Chaining

When deciding on the number of subfiles you need, you must also consider the number of overflow blocks (chains). A large number of blocks slows down data retrieval, hindering the performance of the database.

In general, you should have no more than 3 blocks in a subfile. However, if the file is not likely to be accessed frequently, you may be able to use more blocks.

Note:
For files with a large number of data blocks, B+Tree indexing will speed data retrieval and the performance of the database.

Overflow Blocks

Overflow blocks do not need be the same size as the prime block. Because of this, you can save DASD space by having small overflow blocks when the data overflow is slight.

Database Design Hints and Tips discusses how to define the size of overflow blocks.