bdfd1m0f | Database Administration |
In this section, whether the existing design performs adequately in a realistic setting is assessed. The design is changed wherever necessary to ensure a good real-time performance level.
The following list shows six common requirements for an airline reservation system:
The four tables in Figure 1 show that all this information can be extracted from them. However, if you consider the tables carefully, you can see that some of these operations would involve substantial I/O processing. For example, to display all flights booked for a passenger, you would need to read through the entire seat table for each flight. Because of this, it is more sensible to reintroduce some duplication of data in the tables. This data must be carefully selected. There should be no arbitrary data duplication.
In the following pages, each of the common requirements for an airline reservation system is analyzed.
Before booking a seat, check whether there is a seat of the required class available on the flight specified. To check seat availability using these tables:
This read process is shown in Figure 2. You can see the seat table and the aircraft table will probably have to be read many times, which would involve a significant amount of I/O processing. Performance would be much improved if some changes were made to these two tables.
Figure 2. Read Process for Checking Seat Availability Before Optimization
Figure 3 shows how the tables have been changed.
In the aircraft table, the seat number (Se) attribute has been changed to seat range (Sr). The table is now more compact because you do not have to store every seat on a separate row.
The class (Cl) attribute has been duplicated in the seat table. Availability (Av), in the flight table, is a new attribute.
Figure 3. Altering the Tables to Improve Availability Checking
After improving these tables, the revised seat table (shown in Table 13) contains all the data needed to check seat
availability. Every flight is recorded there, and you can see at once
whether a seat has been booked or not.
Table 13. Seat Table (Revised)
Date | Flight number | Seat number | Class | Passenger number |
Da1 | Fl1 | Se1 | Cl2 | Pn1 |
Da1 | Fl1 | Se2 | Cl2 | Pn2 |
Da1 | Fl1 | Se3 | Cl2 | Pn3 |
Da1 | Fl1 | Se4 | Cl3 | Pn4 |
Now that you have checked seat availability on a particular flight, you can make a booking for the passenger as follows:
To display all the passengers booked on a particular flight:
Figure 4 shows this read process.
Figure 4. Read Process for Displaying Passengers Booked on a Flight
You can eliminate reading the passenger table if the passenger name attribute is duplicated in the seat table. Figure 5 shows this duplication.
Figure 5. Duplicating Names to Display Passengers Booked on a Flight
After this duplication, the new seat table (Table 14) contains all the data needed to display passengers booked
on a flight. You do not need to refer to any other table.
Table 14. Seat Table (Updated)
Date | Flight number | Seat number | Class | Passenger number | Passenger name |
Da1 | Fl1 | Se1 | Cl2 | Pn1 | Na1 |
Da1 | Fl1 | Se2 | Cl2 | Pn2 | Na2 |
Da1 | Fl1 | Se3 | Cl2 | Pn3 | Na3 |
Da1 | Fl1 | Se4 | Cl3 | Pn4 | Pn4 |
To display all the flights booked for a particular passenger, read the seat table (Table 14) for each flight, checking for a match between each flight and your passenger.
You can avoid this I/O intensive search of the seat table if you add flight and date attributes to the passenger table.
Figure 6 shows how date (Da) and flight (Fl) have been duplicated in the passenger table.
Figure 6. Duplicating Flights and Dates to Improve Flight Display
The revised passenger table (Table 15) shows all the flights booked for each passenger. You
do not need to refer to any other table.
Table 15. Passenger Table (Revised)
Passenger number | Passenger name | Passenger address | Flight | Date | Passenger facts |
Pn1 | Na1 | Ad1 |
Fl1 Fl2 Fl3 | Da1 | Ft1 |
Pn2 | Na2 | Ad2 | Fl1 | Da1 | Ft2 |
Pn3 | Na3 | Ad3 | Fl2 | Da1 | |
Pn4 | Na4 | Ad4 | Fl1 | Da1 |
The revised aircraft configuration shows how many seats each aircraft holds in each class. In the original aircraft table (see Figure 1), every seat in every aircraft is listed in a separate row.
Now that the seat number attribute has been changed to seat
range, configurations for different aircraft in the same table are shown
in the revised aircraft table (Table 16). You can now quickly display the number of seats in
each class for each aircraft.
Table 16. Aircraft Table (Revised)
Aircraft type | Seat range | Seat class |
A1 | Se1-Se12 | Cl2 |
A1 | Se13-Se120 | Cl3 |
A2 | Se1-Se4 | Cl1 |
A2 | Se13-Se23 | Cl2 |
A2 | Se28-Se70 | Cl2 |
A3 | Se1-Se23 | Cl2 |
A3 | Se26-Se40 | Cl2 |
To cancel a passenger's booking:
Figure 7 shows the process for canceling passenger bookings.
Figure 7. Read Process for Canceling Passenger Bookings
Looking at the process outlined in Figure 7, you can see that it would require substantial I/O processing to read through these three tables to check every flight for every day against a particular passenger number. The date and flight data duplicated in the revised passenger table (see Figure 6) has, in fact, increased updating times because you must now update the passenger table as well as the flight and seat tables.
However, the performance benefits gained for the previous five queries outweigh the losses incurred in this query. Overall, the optimization has improved the performance of the database.