ffi&ffiffi Molly Mackenzie Boat
ffi%ffiffi
Marina
lntegration Gase Difficulty Rating: ***”**
GASE BAGKGROUND
Marvin and Dena Mackenzie own and operate the Molly Mackenzie Marina. Currently, data
about the marina’s daily operatlng”activities are manually captured and processed. When
necessary, reports are created’using a word processing package. As the marina continues
to grow, Mr. and Mrs. Mackenzie recognize the necessity of using information technology to
capture details about the marina’s dajly operations, as well as facilitate decision-making
activities. Mr. and Mrs. Mackenzie need a database to track information about customer
reservations, customer payments, and the marina’s rental properties. Mr. and Mrs.
Mackenzie hire you to build the database. Building the database requires you to develop
forms and subforms, reports, queries, and a switchboard. The forms will uiilize buttons to
facilitate data navigation. Once the database is developed, Mr. Mackenzie will export
selected data from the marina database to a spreadsheet for further analysis. Mr.
Mackenzie will use the Subtotals function, PivotTable, Solver, and charting tools to analyze
the data.
74
GASE 12: Molly Mackenzie Boat Marina
GASE SCENARIO
The Molly Mackenzie Marina and its surrounding property are located on Lake Merewether
in the Midwest. Lake Merewether is a popular tourist spot, attracting visitors year round.
The lake’s notoriety is due to its beautiful surroundings, camping facilities, excellent fishing,
and ability to handle large boats. Lake visitors are primarily local, but many visitors come
from across the nation. Lake Merewether is situated between two large cities, serving as a
weekend getaway for many of its visitors.
Mr. Mackenzie uses a manual-based information system to manage the marina’s rental
property. However, he realizes that automating the marina’s daily operations will make his
management activities more efficient. Mr. and Mrs. Mackenzie spend numerous hours
keeping the marina’s paperwork up to date. The marina’s popularity continues to grow, and
Mr. Mackenzie is quickly realizing the necessity of using information technology to ease the
marina’s papeMork burden. ln an effort to run the office in a more efficient manner, Mr.
Mackenzie hires you to design a marina database and worksheet for him. Since you have
worked as a parttirne marina employee, you are familiar with the marina’s daily operations
and are eager to help.
The Molly Mackenzie Marina rents cabins, a variety of watercraft, and boat slips to its
customers. When a customer requesis a watercraft or a cabin, a price quote is given. The
price quote is based on a daily rate. lf a customer wants to rent a jet ski for a half day, then
the daily rate is adjusted. Likewise, if a customer rents a cabin or boat slip for more than a
day, Mr. Mackenzie will discount the daily rate, making a longer stay more attractive.
Cabins can be rented for a weekend, week, or month at a reduced rate. Table 1 shows the
discount codes and their associated percentage discounts. lf a customer rents a jet ski for a
half day, Mr. Mackenzie will charge the person 65 percent of the daily rate. lf a person rents
a boat slip for 180 days, Mr. Mackenzie will reduce the daily rate by 15 percent.
Table 1 : Rate Codes and Discounts
Rate Code Rate Description Percentage
D Daily 100.00%
HD Half Day 65.00%
SD Special Discount 25.O0%
SM -Six Months 85.00%
Y 12 Months 80.00%
Weekend 90.00%
When a property is reserved, the customer places a deposit on the rental property. The
deposit is applied to the rental fees for the property or is forfeited if the customer cancels the
reservation. Mr. Mackenzie is flexible on the deposit amount. Mr. Mackenzie requires a
minimum deposit from frequent customers, but does charge a hefty deposit for new
customers. When a customer picks up the watercraft or checks into a cabin, he is required
to pay the entire balance for the property, minus any deposit. lf an advanced reservation
was not made, the customer pays the entire balance when he rents the property.
75
CASE 12: Molly Mackenzie Boat Marina
When a customer requests a particular property, Mr. Mackenzie or an employee checks to
see if the property is available for the dates requested by the customer. lf the requested
dates are available, reservation data are captured on a reservation form. Based on the
length of stay, Mr. Mackenzie deiermines which rate is applicable. For a typical reservation,
customer contact information (name, phone number, and mailing address), reservation
dates, property information (rental property number, description and rate), and payment
amount are recorded. When a reservation is made, the customer is given a reservation
number. The customer uses the reservation number if he needs to cancel the reservation or
when he checks in. When the customer returns the watercraft or checks out of the cabin, he
stops by the office and is given an invoice showing his rental transactions.
Storage Specifications
To build ihe marina database, you decide Customer, Rate, Property, Payment, and
Reservation tables are necessary. The Customer table stores basic customer information,
such as customer:, identification number (ClD), last name, first name, street address, city,
state, zip, and phone number. As the customer identification number is unique for each
customer, lt serves as the primary key for the Customer table. Table 2 shows the Customer
table structure.
The Rate table stores information about the rate codes, rate descriptions, and discount
percentages. As the rate code is unique for each rate code, the Rate Code field serves as
the primary key. The rate description provides additional information about the rate code,
such as daily, weekend, six-month, special discount, half price, or twelve-month. Where
applicable, the discount percentage is multiplied by the daily rate to provide a reduced rate
for the customer for a given rental property. Table 3 shows the Rate table structure.
The Property table stores information about each of the marina’s rental properties. The
property identification number (PlD) is unique for each property. Therefore, the PID serves
as the primary key. The Property table also includes rental price and description fields. The
rental price field stores the daily rental price for the property. The description field further
clarifies the type of property. Table 4 shows the Property table structure.
The Payment table stores information about each payment made by a marina customer.
The Payment table includes PaymentlD, customer identification number (ClD),
PaymentDate, PaymentAmounl, and Comment fields. The PaymentlD field stores a unique
value for each payment record; therefore, it serves as the primary key for the Payment table.
The CID field associates a giv6n payment record with the customer making the payment.
The PaymentDate stores the date the payment was made. The PaymentAmount field
stores the amount paid. Table 5 shows the Payment table structure.
The Reservation table stores information about each of the marina’s rental property
reservations- The Reservation table’s fields include ReslD, customer identification number
(CID), ResDate, BookingDate, EndDate, PickupTime, RateOode, and property identification
number (PlD). The ReslD field contains a unique value for each reservation and serves as
the table’s primary key. The CID value associates a given customer with the reservation.
The ResDate field indicates the starting date of the reservaiion. The BookingDate indicates
when the reservation was made. The EndDate indicates the last day of the reservation.
The PickUpTime freld indicates the time that the rental property will be picked up. The Rate
76
GASE 12: Molly Mackenzie Boat Marina
Code field identifies the applicable rate code for the reservation. The PID field identifies the
property associated with this reseryation. Table 6 shows the Reservation table structure.
As part of the design process, relationships among tables are necessary. Where applicable,
you should enforce referential integrity.
Table 2: Customer Table Structure
Field Name Data Type Field Description Field Size Comments
ctD AutoNumber
L.Jniquely idertifles the
customer. Serues as the
primary key.
Long lnteger ls required.
Last Name Text Stores the custome/s last s0 ls required.
Filst Name Text Stores the customeis first 25 ls required.
Street Address Text Stores the customeis street
address. 50
Ciiy Text Stores the name of the
customer’s city.
25
State Text Slores the name of the
customer’s staie. 2
zip Text Slores the customeis zip
code. Use an inpul mask. 10
Phone Text Slores the customefs phone
number. Use an input mask. 10
Table 3: Rate Table Structure
Field Name Data Type Field Description Field Size Comments
Rale Code Text
Stores a uniqLre identilcatlon
nllmber for each rate code
Serves as the p rnary key.
10 Is requjred.
Rate
Description Text Stores the descripijon for the
rate code. 25 ls required.
Percentage Number
Stores the discount
percentage. Muitiply thls
arnount times the daily rate to
get the adjLrsted price. Use
percent format. Display 2
diglls to the rlght ofthe
declmal seoarator.
Single ls required.
77
GASE {2: Molly Mackenzie Boat Marina
Table 5: Payment Table Structure
Table 4: Property Table Structure
Field Name Data Type Field Description Field Size Comments
PID ‘Text
Uniquely identifies the
prqperty. Setues as the
primary key.
10 ls reqLrired.
Renial Price Number ldeniifies the daily rental rate. Long lnteger ls required.
Description Texi Describes the rental property. 50
Field Name Data Type Field Description Field Size Comments
PaymentlD AutoNumber Stores a unique value.
Serves as primary key. Long lnteger ls required.
ctD Number
Stores the customer’s
identification number.
Consider using the Lookup
Wizatd.
Long lnteger ls required.
PaymentDate Date/Time Siores the date that the
payment was made.
PaymentAmount Currency Stores the amount paid. ls required.
Comment Texl Stores a comment about the
transaction. 50
Table 6: Reservation Table Structure
Field Name Data Type Field Description Field Size Comments
ReslD AutoNumber
Uniquely identifies each
reseNation. Serves as the
primary key.
Long lnteger ls required.
clD NlJmbea Uniquely ideniifies the
customer. Long lnteger ls required.
ResDate Date/Time
ldentifies the start date of the
reservation for the rental
property.
ls required.
CASE 12: Molly Mackenzie Boat Marina
Field Name Data Type Field Description Field Size Commenis
BookingDaie Date/Time ldentifies the date the
reservation was made ls required.
EndDate Date/Time ldentifies the finalday of the
reservation ls required.
PickUpTime Date/Time
ldenlifies the time the
property will be picked up or
the check in time.
RateCode Text
ldentifies the applied rate
code. Use the Lookup 50 ls required.
PID Text ldentifies the rental property.
Use the Lookup Wizard. 10 ls required.
lnput Specifications
From your conversations with Mr. Mackenzie, you realize that the database must capture
and store information about the marina’s customers, rental property, rental reservatjons, and
rental payments. capturing this information requires you to build several forms and
subforms. For instance, a customer form with reservation and payment history subforms is
necessary. A sketch of the customer form is provided below.
The customer form captures and displays the customer’s name, address, and phone
number. Additionally, the customer form shows the customer identification number, amount
owed by the customer, the amount he has paid, and the balance due. The amount owed
field shows the total amount owed for the customer’s current reservations. Likewise, the
Amount Paid field shows the total paid toward the current reservations, and the Remaining
Balance field indicates how much is still due.
when using the customer form, Mr. Mackenzie would also like to view or enter information
about the customer’s curren! reservatjons and payments. you decide to use a tab control to
organize each customer’s reservation and payment information, as well as make the
information easily accessible lo Mr..Mackenzie as he views a customer,s record.
on the reservation subform, Mr. Mackenzie wants to see, at a minimum, the reservation
dates, booking date, property identification number, propedy description, rate code, discount
percentage, daily rental rate, discounted daily rental rate, total usage cost, and comments.
when using the Reservation subform, Mr. Mackenzie wants to select the rental property
number from a list of his rental properties and have the rental property’s descripiion and
rental fee displayed. on the payment subform, Mr. Mackenzie wants to see the payment
date, payment amount, and comments.
79
Figure 2: Customer Form
MM
a-,”r”.*”r–
Customer First Name: Prid on Account:
Customer Last Name: Remaining Balance:
Phone Number:
Street Address:
City: Stater Zipl
MM
Raservation Tab Payment Tab
CASE {2: Molly Mackenzie Boat Marina
Although Mr. Mackenzie encourages you to be creative with your form design, he also
requests that the forms have a consistent, professional appearance, be easy to use, and
show the business name and logo.. Additionally, each form must include buttons that allow
Mr. Mackenzie to add, delete, find, and print records. To facilitate data entry, Mr. Mackenzie
would like to use AutoLookup, combo boxes, and control tips for fields where these features
can (or should) be used. Where possible, you should use validation rules, default values,
input masks, and proper formatting.
When the Molly Mackenzie Marina database is first opened, Mr. Mackenzie wants a
switchboard to automatically display. The switchboard allows Mr. Mackenzie to access his
forms, reports, and queries from a common location. When Mr. Mackenzie finishes with the
database, he wants the option of exiting the application from the main menu. Additionally,
he would like the option of periodically backing up the database.
I nformation Specifications
Mr. Mackenzie requires a Daily Rental Property Reservation Report, a Rental Availability
Report, and a customer invoice. He asks you to design and build these reports for him.
Each evening a Daily Rental Property Reservation Report is printed. This report shows
which properties are reseryed for the next day. This report sorts the properties by rental
property category, then by rental property number within each category. Employees use
this reservation report to make sure that the reserved watercraft and cabins are in working
ord er.
80
CASE l2: Molly Mackenzie Boat Marina
Often customers will ask when a rental property is available. Mr. Mackenzie requires the
ability to locate a rental property’s record and check its availability- The Rental Property
Availability Report should be organized by rental category, showing which rental properties
are available for rent the next day and the rental property should be sorted withrn each
category.
Mr. Mackenzie asks you to prepare a customer invoice. The invoice will serve as a receipt
for the customer’s rental transactions.
Mr. Mackenzie wants answers to the following questions. Prepare queries to retrieve the
information for Mr. Mackenzie. lf you choose, you may generate reports based on your
queries.
1. For cabin rental customers, what is the average length of stay?
2. How frequenily are watercrafts rented? On average, what is the length of rental time?
3. Which boat slip rental option is the most popular? What is the rental revenue by rate
code?
4. How much revenue did Mr. Mackenzie receive on fishing boat rental fees last week?
Last month? Jet skis? (Use the last week in July for the weekly analysis.)
5. On average, how much does a customer spend on each visit?
Mr. Mackenzie wants to export data from the marina database to Microsoft Excel for further
analysis. He would like answers to the following questions. Using Microsoft Excel, provide
answers to his questions.
6. For the month of June, what are the average sales for each product category and
product within each category? For the month of J une, what are the total salesforeach
product category and product within each category?
7. lf customers rent a cabin from the marina, are they also likely to rent a watercraft from
us? What is the percentage?
8. For the first seven days of June, prepare a bar chart comparing rental revenues by
category by day.
9. For the month of June, prepaie a pie chart comparing the revenue by property category.
10. Mr. Mackenzie has $75,000 to spend on new jet skis. He is evaluating one-person, twoperson, and four-person jet skis. The one-person jet ski costs $8,700; the two-person jet
ski costs $1 1 ,000, and the four-person jet ski costs$15,000. lf Mr. Mackenzie’s main
goal is to maximize daily jet ski rental revenue, how many jet skis should he purchase?
Which types of jet skis should he purchase? Use Solver to help Mr. Mackenzie figure
out how many one-person, two-person, and four-person jet skis he should buy. (You are
free to make assumptions; however, these assumptions must be clearly stated in your
analysis.)
81
GASE ‘t2: Molly Mackenzie Boat Marina
lmplementation Goncerns
ln order to build the Molly Mackenzie Marina database, you will construct several queries;
design forms and subforms; design reports; construct tables; and establish relationships
among tables. You are encouraged to be creative; however, your database should function
properly and have a professional appearance.
To satisfy ihe spreadshe’et requirements, you will export selected data from the marina
database into a worksheet. To perform the required analysis, you will use several
spreadsheet features, including AutoFilter, PivotTable, Solver, Subtotal, and Microsoft
Query.
As mentioned above, you are free to work with the design of the forms, reports, and
worksheets. You are also free to make additional assumptions about this case. However,
the assumptions should not violate any of the requirements and should be approved by your
professor. To satisfy your assumptions, you may need to create additional forms or collect
information that has not been previously specified.
Test Your Design
After creating your database and workbook, you should test your design. Perform the
following steps.
1. Enter the following information into the marina database.
Randall Petra rented a jetski dnd cabin forJuly 7,2006 -July 10,2006. He made his
reservations on May 1, 2006. He paid a 20 percent deposit and was given a weekend
discountforthe cabin. He was not given a discount for his jet ski rental. (You selectthe
jet ski and the cabin. Make any other necessary assumptions)-
Pauline Bishop rented two cabins for July 7, 2006 – July 9, 2006. She made her
reservations on May 12, 2006. She paid a 30 percent deposit and was given a weekend
discount forthe cabins. (You select the cabins. Make any other necessary
assumptions).
Frances Thomas rented a ski boat for July 4, 2006 – July 9, 2006. She made her
reservations on June 25,2006. She paid a 30 percent deposit and was not given a
discount on the ski boat rental. (You select the ski boat. Make any other necessary
assumptions).
2. On average, how far in advance do customers book their rentals?
3. Using Microsoft Excel’s Subtotal function, identify the total discount in dollars given by
rate code category.
4. On average, what is the daily, weekiy, and monthly revenue by rental property category?
I
a2
GASE 121 Molly Mackenzie Boat Marina
ln order to satisfactorily complete this case, you should build the database and workbook as
described in the case scenario and then prepare both written and oral presentations.
Unless otherwise specified, submit the following deliverables to your professor.
1 . A written report discussing any assumptions you have made about the case and the key
elements of the case. Additionally, what features did you add to make the database and
workbook more functional? User friendly? (Please note that these assumptions cannot
violate any of the requirements specified above and must be approved by your
professor,)
2. A printout of each worksheet.
4.
6.
{ A Orintout of the worksheet’s formulas.
An etectronic, working copy of your database that meets the criteria mentioned in the
case scenario and specifications sections.
An electronic, working copy of your workbook that meets the criteria mentioned in the
case scenario and specifications sections.
Results for each question posed above. (A memo to your instructor discussing these
results should also be provided.)
As mentioned above, you should prepare an oral presentation. (Your instructor will
establish the time allocated to your presentation.) You should use a presentation
package and discuss the key features of your database and workbook Also, discuss
how the database and workbook are beneficial for Mr. Mackenzie. What additional
information should be included in the database and workbook to make it more useful?
83
