In this assignment you will be working extensively with Microsoft Access 2000 in order to practice database management skills. In this assignment, you will be working with the management of a fictitious airline named LondonAir to create a database to help them manage their airline’s flights out of London. Read the entire assignment before you begin.
Create a new folder named Assign5 on your floppy diskette to use in organizing all of your files for this assignment. Your employers have provided you with two files to get you started. One is a binary Access database file named LondonAir.mdb which contains information on the airports serviced by the airline and the types of planes owned by the airline. The other is a text file named Flights.txt that contains information on the first batch of flights the database you are building will need to contain. Using anonymous FTP, get both of these files from the /pub/cs401h path on the computer named cs.unh.edu and store them in your Assign5 folder.
Your first task is to design and implement a table that will contain information about the pilots employed by the airline. The information needed for each pilot is their name, mailing address, phone number, and a five character pilot ID number consisting of letters and/or digits. Each pilot record should also indicate the total number of flight hours the pilot has logged, the hourly bonus they get paid while actually flying a plane, and whether or not they are certified in emergency water landings. You will need to come up with a workable design for this table. Be sure to take into account the lecture material on table design and the various requirements stated elsewhere in this assignment description. Once you have designed this table, implement it within the LondonAir database as a table object named Pilots.
When implementing the Pilots table, it is your responsibility to have Access enforce the following requirements:
- Pilot IDs and zip codes cannot exceed 5 characters
- State names cannot exceed 2 characters (you can assume that all of the pilots have US addresses)
- Phone numbers must be entered in the format (###) ###-#### with a maximum length to match
- All pilots must have the minimum 500 hours total flight time required to be hired
- All pilots must get at least $10.00 as an hourly bonus
- The pilot ID must be established as the primary key for the table
- The table must be indexed by the phone number field
- The table must also be indexed by the pilot’s full name (last name followed by first name)
Once the table design is implemented, it is time to enter the pilot data. Start out by entering five pilots into the table using the table’s datasheet view. Then create a form named PilotEntry that contains all of the fields in the Pilots table, one field per line of the form, and use that form to enter another ten pilots into the table. You can just make up reasonable information for the fifteen pilots you enter into this table.
When all fifteen pilots have been entered, create a report named PilotList that displays all of the information about each pilot, showing one pilot per line. Each line should start with the pilot’s ID, then the name, then the phone number, then the address, then the flight hours, then the bonus rate, then the water landing certification. This report should be sorted alphabetically by last name (with ties decided based upon first name). The printed report should be titled “LondonAir Pilot Roster” in the report header, and your name should appear in the lower left hand corner of the page in the report footer. Clean up this report as necessary to make sure all information is visible and that the entire report fits on a single page in landscape orientation. Print a copy of this report.
Display the Pilots table in datasheet view, adjust the field widths so that they fit their contents, sort the table in descending order based on the flight hours field, and print a copy of the table in datasheet view using landscape orientation. If possible, adjust the left and right margins to get the entire table on a single page.
Create a query named PilotTraining that will show only the pilot ID, name, phone number, flight hours, and bonus rate (in that order) for those pilots who are not certified in emergency water landings. Then generate a report named PilotTraining based upon this query that includes the same information found in the query. Give the report the title “Pilots in Need of Additional Training” and make sure your name appears in the lower left hand corner of the page. Print a copy of this report.
Examine the contents of the Flights.txt file to familiarize yourself with the best name and data type to use for each field. Delete any information that you would not want to appear in the table, and save your changes to the file. Then import the contents of the Flights.txt file into a new table named Flights, choosing an appropriate field to act as this new table’s primary key. Your new table should contain 38 records and 3 fields. Add a new field named Pilot (limited to a length of 5 characters) to the Flights table, and assign a pilot from your Pilots table to each flight by entering that pilot’s ID in the new Pilot field. Note that you will need to assign a single pilot to several flights, since you only have 15 pilots and there are 38 flights.
With your new table displayed in datasheet view, define a filter that will show all flights to Auckland (airport code: AKL) and print the table with the filter applied. Then define a filter that will show all flights to destinations other than Auckland and print the table with this filter applied.
Study the contents of the four tables that now exist in your database and identify the common data on which they can be related. Using the Flights table as the primary table, establish global relationships between all four tables and save those relationships as part of the database. You will not be able to do the remaining steps until you have successfully established these relationships.
Create a query named FlightList that will list (for each flight in the Flights table) the flight number, the name of the airport and country the flight is destined for, the name of the plane on which the flight is scheduled to fly, and the name of the pilot assigned to fly that flight. Then define a report named FlightList that will list this same information in a tabular format sorted alphabetically by country of destination (with flights to the same country alphabetized by airport name). Give the report the title “List of Scheduled Flights” and make sure your name appears in the lower left hand corner of the page. Clean up the report design as necessary to arrange the columns and their labels, then print a copy of this report.
Add a new number field named FlightTime to the Flights table. Make this field’s Field Size “Single,” its Format “Fixed,” and set it to display 2 Decimal Places. Now create an update query named CalculateTimes that will fill in this field for each flight by dividing the distance the flight will travel (which is in the Distance field of the Airports table) by the speed at which the plane travels (which is in the Speed field of the Planes table). Run this query.
Now add a new currency field named TotalBonus to the Flights table, and create an update query named CalculateBonuses that will fill in this field for each flight by multiplying the flight time by the pilot’s hourly bonus. Run this query, and then print a copy of the Flights table in datasheet view showing the results of both this query and the CalculateTimes query.
Create a summary query named CostAnalysis that will display a single record for each pilot. For each pilot, display the pilot’s name and ID, the total number of seats (including first, business, and coach classes shown as three separate totals and then again as one combined total) available on that pilot’s flights, and the bonus cost per seat (which you can calculate by dividing the total bonus paid to the pilot by the total number of seats). Create a report named CostAnalysis based upon this query that will present all of this information in a tabular format sorted in descending order by the bonus cost per seat. Give the report the title “Bonus Cost Analysis” and make sure your name appears in the lower left hand corner of the page. Clean up the report design as necessary to arrange the columns and their labels. Be sure that the bonus cost per seat displayed as a currency amount. Print a copy of this report.
Along with your diskette, submit the following printouts, stapled together in the following order and labeled with the following numbers:
- the PilotList report
- the Pilots table in datasheet view sorted by flight hours
- the PilotTraining report
- the Flights table in datasheet view filtered to show only flights to Auckland
- the Flights table in datasheet view filtered to show all flights to destinations other than Auckland
- the FlightList report
- the Flights table in datasheet view showing the filled in FlightTime and TotalBonus fields
- the CostAnalysis report