Subject: Cruise Ship Booking and Accounting Database System

Question: A database must be constructed for the following Cruise Ship Booking and Accounting System:
Accommodation in different categories of staterooms (or cabins) .

There may be zero, one, or many passengers (up to 2 adults and 2 children) in a given stateroom but a
passenger cannot occupy more than one stateroom at a time

the crew provide various amenities, some free, others at a charge, during their stay.

Passengers do not pay for their food, but there is a surcharge for each meal when they dine at the
prestigious Todd English restaurant or at the First Mate's pub.

You should not charge guests for shows, lectures or movies but these should be recorded with the
amenities so that the bulletin can publish what entertainment is available on a given day.

Entertainment is a subtype of Amenity where the day of cruise and time is recorded.

There is no booking for entertainment (first come, first served) but certain amenities must be booked
for e.g. a beauty therapy appointment - and others e.g. drinks on deck are not booked for.

Attached to each room-category is a certain charge for the entire cruise and a certain gratuity rate
per day of the cruise .

Activities or Amenities include shore excursions, most drinks (fruit drinks, tea, coffee,
hot chocolate free), phone calls, internet, beauty therapy, a variety of bars, health spa, chiropractic treatment and others.

Each different amenity has a code, a description and a charge attached (if applicable); and an attendant who
administers or manages the amenity (treatment).

Attendants are paid a basic salary and then may get commission according to their activity participation or
per treatment.

Tips are not necessarily recorded, because they may be cash.

You should allow for two subtypes of dining places: dining rooms and restaurants.

All dining places have attributes Maitre'd and capacity and daily menus.

Your database does not have to include menus or general dining staff on duty details.

Restaurants can be King's Court open restaurants or other restaurants.

King's Court restaurants have as an attribute Buffet_Status,

other restaurants have an attribute surcharge and Open_status.

Some restaurants are only open when the weather is suitable.

Note that a given restaurant will change it's buffet status and open status regularly.

The passengers are automatically assigned to one of 3 dining rooms, depending on their status
(which level their stateroom is found). But they might decide to miss a meal, or dine at one of
the other restaurants, where they may have to book.

You must keep a record of which dining room the passenger is assigned to or compute it automatically

when the passenger embarks (and at other times when necessary)

The King's Court area has 4 restaurants:
Lotus Carvery
La Piazza Chef's Grill
One of the top three is always buffet and the other two are subject to dinner bookings.

This is . on an informal rotation basis -
e.g. Monday buffet is Lotus
     Tuesday buffet is Carvery
     Wednesday buffet is La Piazza etc..

You always have to book at Chef's Grill.

The database must keep a record of which restaurant is buffet for each day of the cruise.

The 3 dining rooms are
1. Britannica,
2. Princess Grill
3. Queen's Grill.

Passengers are assigned a table number at their allocated dining room

There are five restaurants where booking for the day's evening meal is allowed :-
1. Todd English
2. First Mate's Pub
3. King's Court
4. King's Court
5. Chef's Grill

The database must keep a record of all current bookings and assign a table number to a given guest
(by stateroom number).

The passengers can also book for shore excursions when the ship visits a port.

A passenger can book for several shore excursions on a cruise and each excursion can accommodate many,
but a limited number of, passengers.

Each day of the cruise is assigned a dress code.
Dress codes which apply for after 18h00 are
1) Formal ,
2) Semi-formal,
3) Smart Casual and
4) Casual.

The database must keep a record of each day of the cruise and the dress code for that day, as well as whether the clocks must be turned back or forward one hour at midnight
if applicable when travelling east-west or west-east.

The database must also keep a record of birthdays and anniversaries for passengers where applicable

Your database must have sufficient information to print the daily bulletin and make announcements on the Cruise Director morning talk show (on the stateroom TV): giving
1. dress code,
2. status of restaurants,
3. port of call if applicable,
4. entertainment offered,
5. notices of special days for passengers,

You must keep sufficient information in your database to enable a passenger to book for an evening meal at a
restaurant or an excursion, and an invoice to be created to charge the passengers for their cruise,
including all amenities.

You may assume a guest may occupy more than one stateroom during their stay, i.e. they may move cabins or be upgraded.

B. Barry says the Things of Interest are :-
1. Bookings
2. Cruises
3. Customers
4. Payments
5. Others to be determined

C. The Business Rules are as follows :-
1. To be determined.



 © DataBase Answers Ltd. 2011