bdfd1m0g | Database Administration |
The duplication of some data in the tables has considerably improved the performance of the database. However, there are still some areas where poor access is slowing down the retrieval of data. You will need to improve these access paths before the database can achieve the performance level required of a real-time system.
The following list shows four common requirements that the database must be able to meet quickly:
The revised tables, as shown in Figure 8, do not yet provide easy access paths for these requirements.
The following pages explain each of the previously outlined requirements and a method of improving access times. See Figure 8 when working through these requirements.
Once you have input a passenger name or number, you need to be able to access the passenger table directly. At present, there is no direct access from either of these inputs to the passenger table.
However, the addition of pointers would improve access times significantly. Because pointers provide a means of linking one table or file with another, you can use them to achieve direct access to tables. In the TPFDF product, you can use the DBIDX and DBDIX macros to maintain pointers between files. Files containing pointers are index files.
Table 17 and Table 18 directly access the passenger table.
Table 17. Passenger Name Table
Passenger name | Pointer to passenger table |
---|---|
Na1 | Pointer1 |
Na2 | Pointer2 |
Na3 | Pointer3 |
Na4 | Pointer4 |
Table 18. Passenger Number Table
Passenger number | Pointer to passenger table |
---|---|
Pn1 | Pointer1 |
Pn2 | Pointer2 |
Pn3 | Pointer3 |
Pn4 | Pointer4 |
Figure 9 shows the two pointer tables (passenger number and passenger name) pointing to the passenger table.
Figure 9. Adding Pointer Tables to Improve Access to the Passenger Table
Flight information is held in the flight table and the seat table (see Figure 8). Between them, these two tables contain all the information you need to know about a flight.
However, at present there is no direct way of accessing the seat table from the flight table. You can overcome this difficulty if you include a pointer in the flight table.
Figure 10 shows how the pointer has created a direct access path from the flight table to the seat table.
Figure 10. Adding a Pointer to Improve Access between the Flight and Seat Tables
Now that the flight table and the seat table are linked by the pointer, you can remove the resulting data duplication (date and flight). Because the TPFDF product reads the flight table before reading the seat table, you should remove the duplicated data from the seat table.
There is still no direct access from the seat table to the passenger table (see Figure 10).
An added pointer in the seat table provides the access path you need.
Figure 11 shows how the added pointer in the seat table creates a direct access path to the passenger table. (Note that the duplicated attributes, date and flight, have been removed from the seat table, as discussed in Accessing Flight Information.)
Figure 11. Adding a Pointer to Improve Access between the Seat and Passenger Tables
There is still no direct access from the flight table to the aircraft table (see Figure 11). You can overcome this difficulty by including an additional pointer in the flight table.
Figure 12 shows how the second pointer in the flight table creates a direct access path to the aircraft table.
Figure 12. Adding a Pointer to Improve Access between the Flight and Aircraft Tables
The four original tables (see Figure 1) have now been optimized to be used in a real-time database. Two new tables have been added to improve data access by using pointers. These six logical tables are now ready to be mapped to physical TPFDF files.
Figure 13 shows the database tables arranged hierarchically. The tables are shown in their relative sizes. Some attributes (for example, passenger name) are longer than others (for example, date).
The two pointer tables (passenger name and passenger number) that are at the top left of the diagram contain no detail data. Mapping Tables to TPFDF Files shows how the tables are mapped to TPFDF index files.