Overview
This lecture reviews an Excel flight data file and explains the structure and key variables in preparation for building a database in Microsoft Access.
Excel Flight Data Structure
- The data is stored in an Excel file with 151 rows; 1 label row, and 150 data rows.
- Each row represents a passenger's ticket for a specific flight.
- Key columns include ticket number, record locator, frequent flyer number, name, address, and contact number.
- Flight-specific details include flight number, departure date and time, ticket price, aircraft type, seat, and seat type.
- Seat types are coded as EP (economy plus), E (economy), and B (business).
- Flight route information uses three-letter codes for both origin and destination airports.
- Crew details for each flight include captain and first officer names.
- The 'miles earned' column shows the frequent flyer miles a passenger earns for that flight, not the physical distance between airports.
- 'Cumulative miles' reflects the passenger's frequent flyer account balance before the current flight.
Considerations for Database Design
- Ticket number is unique for each passenger, while record locator may repeat for group bookings.
- Deciding on a primary key in the reservation table requires attention to ticket number versus record locator.
- Not all passengers have a frequent flyer number; the field may be empty.
Planned Database Tables
- The data will be parsed into separate tables: frequent flyer, passenger, reservation/manifest, flight, captain, and first officer.
- The personnel (crew) will be stored in two separate tables for captain and first officer, instead of one.
Key Terms & Definitions
- Ticket Number — Unique ID for each passenger's ticket.
- Record Locator — Unique reservation code, can cover several passengers.
- Frequent Flyer Number — Account number for loyalty program members; may be blank.
- Seat Type — Class of seat (EP: economy plus, E: economy, B: business).
- Miles Earned — Frequent flyer miles awarded for the flight, not actual flight distance.
- Cumulative Miles — Total frequent flyer miles in account before the flight.
Action Items / Next Steps
- Parse the Excel flat file variables into the proposed database tables in Microsoft Access.