MIS$303$ Introduction to Business Information Systems

Access in$-$class Exercise I

Page $1$ of $2$

Charter Database

Joe$$s SkyTour Inc. is a helicopter tour and sightseeing company in Maui, Hawaii. The company owns helicopters that

are used to fly tourists over the Maui Island for sightseeing. The company has created a database to track the helicopters

it owns, including each helicopter$$s unique ID number, name, model, seating capacity, average cost per hour used, and

the date it was last maintained. The database also maintains information about each pilot it employs and assigns to fly

trips, such as unique pilot ID$,$ Social Security number, first name, last name, street, city, state, zipcode $($first $5$ digits$),$

phone number, and date of birth. The company offers different types of tours $($with different tour durations and cover

different sites$)$ for customers to book. Information regarding these tour types, including the unique tour type ID$,$ tour

type name, expected tour hours, and ticket price, is also saved in the database. In addition, all information about each

trip, including unique trip ID$,$ the ID of the helicopter used, the ID of the pilot taking charge of the trip, the ID of the

tour type for the trip, the date the trip takes place, the starting time of trip, and the number of passengers on the trip, is

also kept in the database.

Each trip takes only one helicopter, but a helicopter may be used for multiple trips $($on different dates$/$times$)$ or may have

not been used for any trip yet. Each particular trip has only one pilot assigned to it$,$ but a pilot may be assigned to different

trips $($on different dates$/$times$)$ or may have not been assigned to any trip yet. Each trip belongs to one particular tour

type, but a specific tour type may be chosen for different trips or may have not been chosen by any trip.

Charter Database Implementation Steps:

T$1.$Create a blank Access database

T$2.$Create the tables in Access

T$3.$Establish the relationships in Access

T$4.$Enter data into the tables using the data provided

T$4\mathrm{.}1.$ Using Datasheet View for Helicopter and Pilot table.

T$\mathrm{.}4\mathrm{.}1\mathrm{.}1.$ What if the AutoNumber ID is non$-$continuous? How to solve it$?$

T$4\mathrm{.}2.$ Using Form for TourType table.

T$4\mathrm{.}3.$ Import data from an external worksheet to Trip table.

MIS$303$ Introduction to Business Information Systems

Access in$-$class Exercise I

Page $2$ of $2$

Data for Entry into the Database

Helicopter

HelicopterID HelicopterName HelicopterModel SeatingCap HourlyCost DateLastMaint

$1$ E$-$Star Airbus EC$1306$ $$300\mathrm{.}0005/05/2021$

$2$ R$-$Star Robinson R$443$ $$180\mathrm{.}0008/12/2021$

$3$ A$-$Star Airbus AS$3506$ $$320\mathrm{.}0007/15/2021$

$4$ B$-$Star Bell $4076$ $$290\mathrm{.}0007/23/2021$

TourType

TourTypeID TourTypeName ExpectedHours TicketPrice

L Complete Maui $1\mathrm{.}50$ $$300\mathrm{.}00$

M West Maui and Molokai $1$ $$230\mathrm{.}00$

S West Maui Mountains $0\mathrm{.}50$ $$160\mathrm{.}00$

Pilot

PilotID SSN FirstName LastName Street City State ZipCode Phone DateofBirth

P$001133-46-5889$ Your First

Name

Your Last

Name $182$ Nohea Kai Dr$.$ Lahaina HI $96761(808)155-3509$

P$002436-55-1346$ Linda Jones $102$ W Kaahumanu

Avenue Kahului HI $96732(808)777-350810/13/1965$

Trip: imported from the Trip Excel worksheet.