bdfd1m0fDatabase Administration

Assessing the Normalized Tables

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.

Note:
When developing your design, you must also consider frequency of operation. A 10% performance gain in an operation performed several times per second is better overall than a 50% gain in a daily operation.

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.

Checking Seat Availability

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:

  1. Read the flight table to find the aircraft type (At).
  2. Read the aircraft table to find a seat number (Se) in the required class.
  3. Read the passenger number field (Pn) in the seat table to find whether that seat has already been booked.
  4. If that seat number is already booked, read the aircraft table again to find the next seat in the required class.
  5. Repeat steps 3 and 4 until an available seat is found, or until all seats in the required class are found to be booked.

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

Booking a Passenger on a Flight

Note:
When working through the following example, refer to Figure 3.

Now that you have checked seat availability on a particular flight, you can make a booking for the passenger as follows:

  1. Add the new details to the passenger table.
  2. Add the new details to the seat table.
  3. Update availability information in the flight table.

Displaying Passengers Booked on a Flight

To display all the passengers booked on a particular flight:

  1. Read the seat table to find every passenger number for a particular flight.
  2. Read the passenger table to find the corresponding passenger name for each passenger number.

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

Displaying All Flights Booked for a Passenger

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  

Displaying an Aircraft Configuration

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

Canceling Passenger Bookings

To cancel a passenger's booking:

  1. Read the seat table to search for the passenger number and name. Delete these if found.
  2. After deleting the number and name, continue reading the seat table to find any more occurrences of that number and name. If found, delete these as well.
  3. When no more occurrences are found, delete the appropriate details from the passenger table.
  4. Finally, delete the appropriate details from the flight table.

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.