bdfd1m0i | Database Administration |
The tables used for mapping in this chapter are those developed in Organizing a Database and Optimizing the Database Design. They are as follows:
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.
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 |
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 | 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 |
Figure 14 shows the number of LRECs required for each of these six files.
Figure 14. Number of LRECs Required for Each File
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.
The following points will help you determine how many subfiles you need to accommodate the data in your file:
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):
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.
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.
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.
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.