Enchantment Lakes Corporation's Traveler DatabaseBackground: Line of Business: Enchantment Lakes Corporation provides a variety of corporate travel services. These include booking services for individual travelers, management of corporate meetings, and management of travel expences. This Project: The company wants to consolidate information about reservation and invoicing into a Traveler database, in order to provide consolidated reports. Reservation information must be collected from three different computirized reservation systems (CRSs). Invoice information is stored in an accounting system that is named Travel Data System(TDS), which runs on a Unix-based minicomputer. Information must be collected from each CRS and from TDS, checked for completeness and accuracy, and consolidated in the Traveler database. Current Business Process: VP Sales : We need to provide reports that succinctly summarize important data. Data accuracy and completeness are of utmost importance. Chief Information Officer (CIO): When a traveler makes a reservation, a travel agent enters details into the CRS. A reservation typically includes two flights, outbound and return, and each flight includes at least one segment When a traveler changes reservations, a travel agent updates the reservation in the CRS, issues a new ticket. Many customers want specific information stored with each reservation. For example, a consulting firm might want a project number associated with each flight segment. A CRS provides complete access to data about individual reservations, but does not support queries across reservations. For example, we cannot query a CRS to get total spending by customer for flights from New York to London in the past three months. Customer Travel Director: Often somebody will book a trip and receive the tickets, but cancel the trip at the last minute. The tickets need to be refunded or exchanged, but they just end up in a file folder somewhere. Current Environment: Chief Information Officer (CIO): The CRSs do not maintain historical data. 24 hours after a traveler's last flight,, all information about the reservation is discarded. Each CRS uses a different coding scheme to identify airlines, airports, hotels, and car rental agencies. Database Administrator: The company has implemented an enterprise database that runs on Microsoft SQL Server. All company applications use the same tables for common data customers, airlines, hotels, car rental agencies, airport locations, etc. Director of Application Development: Travel agents are rather inconsistent about entering reservation data. They often omit important items, or put terms into a comment field in the CRS. Because the data is in TDS originates from entries that were made in the CRS, the TDS data will reflect those same inaccuracies. Proposed System: Director of Application Development: We need a system that will: - Capture data from each CRS and from TDS. - Transform the data to a standard data model. - Validate the data for completeness and accuracy. - Load the validated data into a Microsoft SQL Server database. - Match invoices to reservations. - Generate reports. For each CRS, we will build a data collector application that will extract reservation data from the CRS and load it into a traveler-centered staging database. We will build another data collector application that will extract invoice data from TDS and load it into the same staging database. A validation engine will read data from the staging database and check for accuracy and completeness. The validation engine will also translate CRS-specific codes for airlines, airports, hotels and car rental agencies to database keys from our Traveler database. Transactions and validations will be based on rules that are read from a rules database. Validation rules vary by customer and change frequently. We will need a rule editor. The validation engine will pass invalid data to an exception handler. The exception handler will route invalid CRS data back to travel agent for correction at the host CRS. The exception handler will also route invalid TDS data to an accounting manager for correction in TDS. The validation engine will pass validated data to a data loader. The data loader will merge validated data into the Traveler database, matching invoices to reservations. Only validated data will be loaded into the Traveler database. Reports will be generated from data in the Traveler database. |