Part 1: Access Database 50 Marks 1) Create a database and save it to ISR104NAA_Test2_DB_Your Last Name Your First Name on your USB jump drive and save every five minutes to ensure minimal loss if there is a system crash 2 Marks 2) In this database, please create the following tables and fields: Setup the tables based on the information below: Flight Table AircraftTable Passenger Table Field Field Field Field Name Type Length Field Name Type Length Field Name Type Length Passport Flight ID Number Aircraft ID Number Number Number Flight Origin Text 30 Make Text 20 Flight ID Number Flight Destination Text 30 Model Text 20 Last Name Text 30 Flight Time Decimal 2 Dec Airline Text 30 First Name Text 30 Pilot Number Number Maint Date Date Date of Birth Date Special Pilot Name Date Notes Text 120 Accomodations Y/N Aircraft ID Number Accom Notes Text 120 In the Passenger Table, in Accom. Notes field se up a drop down list as follows: Celiac, Diabetic, Fear of Flying, Vegan Diet, Heart Condition In the Aircraft Table, in Airline field, setup a drop down list as follows: Air Canada, Air North, Air Ontario, Cal Airways, Air Eastern, Brasil Airways In the Flight Table, in the Flight Time field, if hours are >4, then display the message "Check with Passenger Safety Steward, regarding long flight precautions". 18 Marks 3) Create input forms for each table. Enter the title of the Flight Table and change to Flight Information on the form; the title of the Aircraft Table to Aircraft Information on the form; and, the title of the Passenger Table to Passenger Details on the form. Change the title font size to 16 and the colour to Dark Green. Change the background to Tan. Change the labels for the field names to Aqua. 6 Marks 4) Create relationships between the Flight Table and the Passenger Table and between the Aircraft Table and the Flight Table. 4 Marks 5) See the file supplied in Blackboard for the data to enter into all of the table fields. 4 Marks 6) Do queries on the following: a On all Passenger Table fields. (Select Passengers who need Accomodations) B.Flight by Aircraft: Flight ID, Elight Destination. Flight Time.Aircraft.ID, Airline, Spesial Notes (Sort by Flight Time >=2) 8 Marks 7) Create reports on the following: a. Flight by Passenger: Flight.JR. Flight Time Pilot.Name. Passport Number Last Name. First. Name (Sort ascending by Passport. Number: total] the Flight Time) b. Aircraft by Flight: Make, Model, Airline, Flight D. Elight. Origin Elight Destination (Sort Descending by Airline, then ascending by Make). 8 Marks 3) Create input forms for each table. Enter the title of the Flight Table and change to Flight Information on the form; the title of the aircraftTable to Aircraft Information on the form; and, the title of the Passenger Table to Passenger Details on the form. Change the title font size to 16 and the colour to Dark Green. Change the background to Tan. Change the labels for the field names to Aqua. 6 Marks 4) Create relationships between the Flight Table and the Passenger Table and between the Aircraft Table and the Flight Table. 4 Marks 5) See the file supplied in Blackboard for the data to enter into all of the table fields. 4 Marks Do queries on the following: a On all Passenger Table fields. (Select Passengers who need Accomodations) kFlight by Aircraft: Flight ID, Elight. Destination. Elight.Time. Aircraft.IRAirline, Spesial. Notes (Sort by Flight Time >=2) 8 Marks 7) Create reports on the following: a. Flight by Passenger: Flight.In. Elight Time Pilat.Name. Passport. Number Last Name. First. Name (Sort ascending by Passport. Number: total]the Flight. Time) b. Aircraft by Flight: Make, Model, Airline, Flight D. Elight.Qrigin, Elight. Destination (Sort Descending by Airline, then ascending by Make). 8 Marks
