Summarizing the Tasks

TIP

In the preceding table structure, the value 0 for Allow Nulls implies that it is mandatory for you to specify a value for the field when you add a new record. Similarly, the value 1 implies that the field is optional when you add a new record. For example, in the dtUsers table, the PasswordChanged field, which stores a Boolean value to specify whether or not the user has changed the password, is optional.

After network administrators create user IDs in the dtUsers table, business managers should specify flight details.

The dtFltDetails Table

The dtFltDetails table stores details of airline routes flown by the airline. The structure of the dtFltDetails table is given in Table 18-2.

Table 18-2 Structure of the dtFltDetails Table

410 Project 4 CREATING AN AIRLINE RESERVATION PORTAL

In the structure of the dtFltDetails table given in Table 18-2, I have added a LaunchDate field.The LaunchDate field is used to store the date on which the flight is launched. This information will be used to display details of newly launched flights on the Web site.

After flight details are added to the dtFltDetails table, line-of-business executives can make reservations on the airline. Therefore, I now move on to the table that is used for storing details of reservations, dtReservations.

The dtReservations Table

The dtReservations table is the most frequently used table of the database. The table is used to store details of passengers who have reserved a seat on the flight. The structure of the dtReservations table is given in Table 18-3.

Table 18-3 Structure of the dtReservations Table

In the dtReservations table, the TicketConfirmed and EMail fields allow null values. The TicketConfirmed field is updated when users confirm their ticket. The e-mail address, when specified by the passenger, is used for enabling the frequent flier program.

TIP

To ensure privacy of data, only customers who specify their e-mail address can query their ticket status on the online portal of SkyShark Airlines.

As a result of a large number of flights flown by the airline, there will be a large amount of data in the dtReservations table. However, if you notice, you need to store details of passengers related to those flights that have departed only for the frequent flier programs. Therefore, a network administrator should ideally move the data related to departed flights to another table, which can be used for the frequent fliers program.This type of mechanism will have the following advantages:

Archiving database tables easily. Data that is ready for archiving is automatically moved to another table. Therefore, network administrators can archive database tables easily.

Improved performance. If you use a different database for storing data pertaining to flights that have departed, queries for analyzing data will be directed to the other database and the performance of the dtReserva-

tions table, which is critical to the online portal, will improve because redundant transactions are eliminated.

Easy access to data. For generating reports, business managers need not access dynamic data in the dtReservations table. Instead, they can use the other table to retrieve only the data that is pertinent to analysis.

To implement the logic that I have explained, I have created the dtDepartedFlights table, which follows next.

The dtDepartedFlights Table

The dtDepartedFlights table has exactly the same structure as the dtReservations table. Therefore, I do not replicate it here. You can look the structure up in Table 18-3. After a flight has departed, data pertaining to passengers who have flown the flight is moved to the dtDepartedFlights table. This data is used for generating reports and for enabling the frequent flier programs.

Having examined the tables related to flight details and reservations, you can examine the table for cancellations, dtCancellations.

412 Project 4 CREATING AN AIRLINE RESERVATION PORTAL

The dtCancellations Table

The dtCancellations table stores information related to tickets that have been cancelled. This information is required only for accountability of refunded fare and reservations. Therefore, if a passenger whose ticket has been cancelled informs the airline that the ticket should not have been cancelled, the details of the executive who processed the cancellation can be traced and the reasons of the cancellation can be determined. The structure of the dtCancellations table is given in Table 18-4.

Table 18-4 Structure of the dtCancellations Table

The dtCancellations table can be archived on a timely basis to ensure that no redundant data is stored in the database. I will now discuss the next important

table, dtFltStatus.

The dtFltStatus Table

When a passenger reserves a seat on an airline, the number of seats available for reservation should reduce by one. Similarly, if a flight is overbooked, excess passengers should be placed in queue. The updated status of a flight should be available to passengers when they reserve their seat.

To ensure that an updated status of a flight is always available, I have used the dtFltStatus table. As soon as the first ticket is booked on a flight, an entry for the flight is created in the dtFltStatus table.This table is updated as reservations and cancellations are made. The structure of the dtFltStatus table is given in Table 18-5.

In the structure of the dtFltStatus table, the FltNo, StatusDate, and StatusClass

fields form a composite key. This implies that the three fields together form a unique combination that can be used to retrieve the status of a specific class of a flight on a specified date.

I will now examine the last two tables that are related to the frequent flier pro-

gram, dtPassengerDetails and dtFrequentFliers.

The dtPassengerDetails Table

To enable the frequent fliers program, the dtPassengerDetails table retrieves data

from the dtDepartedFlights table. The dtPassengerDetails table uses the e-mail

address of passengers to identify the number of times they have flown the airline and the total fare collected from them in these flights.The structure of the sengerDetails table is given in Table 18-6.

Table 18-6 Structure of the dtPassengerDetails Table

Whenever a new frequent flier program is launched, data from the dtPassengerDetails table is used to determine how many passengers the program will impact. This data is used for enabling discounts to passengers. The discounts are specified in the dtFrequentFliers table.