CSEC

BERBICE HIGH SCHOOL

INFORMATION TECHNOLOGY

GENERAL PROFICIENCY

SCHOOL BASED ASSESSMENT

MAY 2022
 1

Description of the Project

WideGuy Tours and Travel, located in downtown Guyana, offers tour packages to various destinations
around the world. The company has scheduled four tours for the summer (July – September) holiday
season. This year the agency has organized tours to the Caribbean and Europe. The agency advertises
these tour packages in various newspapers and travel magazines throughout the Caribbean. Travelers are
invited to book in advance. Incentives are offered for parties of four or more persons and for early
booking.

In the past, WideGuy Tours and Travel used a manual filing and accounting system. The demands of
modern business and the advent of technology however, have forced the company to automate their
services. In order to accomplish this, they have employed you to design and implement an automated
system incorporating the use of computers and utilizing suitable productivity tools software.
 Spreadsheet

WideGuy Tours and Travel offers tours to four destinations for the July/August holiday season – the Caribbean and Europe.
Persons must submit their first and last name when making a booking for their tour of choice. Persons may book to travel First
Class or Economy class. The cost for First Class is higher than the cost for Economy. The number of persons in the travel party
must also be indicated when making reservations. While some persons may travel alone, most make bookings for their entire
families. Yet others make bookings on behalf of organizations, groups, etc. An incentive discount is offered for groups of four
or more persons.
NORTHWEST TOUR

Travelers must make an initial payment of 15% of the cost of the tour, per person. The cost of the tours (per person) is as
follows: Economy precedes First Class: Caribbean Tour - $19,500, $22,500, European Tour - $43,000, $47,600. WideGuy
Tours and Travel reserves the right to change the tour fares and % of initial down payment.

You are required to:

1. (a) Design and populate a spreadsheet which accepts and computes financial data FOR EACH TOUR SEPARATELY.
The spreadsheet should allow for entry of the first and last name of the person making the booking, the class
preferred, the number in group, the amount due, the down payment, the discount, and the final payment. NOTE: 15
persons per a tour.
The amount due is calculated on the number of persons in the group, and the cost of the tour consistent with the
class of travel chosen. All persons in a group MUST travel in the same class, either economy or first class.

The down payment is calculated as 15% of the cost of the tour per person. Note that some bookings are for first class
travel while others are for economy class travel.

(b) A discount of 8% of the amount due is given only if the number of persons in a group is four or more. Between ten
and fifteen persons make bookings for each tour. A few of these persons book for a party of just two (possibly
 themselves and their spouses). Many others however make bookings for parties (groups) of three to a maximum of
six persons.

(c) Since only two payments are allowed, calculate the final payment for each person who made a booking.
(d) In an appropriate row, calculate the total number of persons booked on each tour.
(e) Format all cells appropriately consistent with the data they contain.
All column heading should be center aligned and bolded. Note that the cost of each tour and the percentage down
payment are subject to change.
Save your spreadsheet as TravelAccounts1

2. You have only just realized that you forgot to list the date each booking was made. Since an incentive is given for
early booking, you must now include this data.
(a) Insert a column after the ‘Numb. in Party’ column which allows for entry of the Booking Date. Enter booking
dates for each person who made a booking for each tour.
(b) Insert another column after the ‘Discount’ column. Name this new column ‘Additional Discount’.
An additional discount of 6% is given to all persons who made their booking before June 1 st, 2021. Bookings
made after that date attract no additional discount.

(c) Insert a column which lists the Total Payment made. The total payment is calculated on the amount due, the
final payment and the various discounts. Adjust the Total Payment for each person accordingly.

(d) In the appropriate row, determine the gross amount of money collected for each tour.
Insert a row to accept booking by another person. This person’s data must be inserted after the second name
entered in the Caribbean Tour.
The fourth person in the Kaieteur tour has canceled their booking. Delete this person (row) from the
spreadsheet.

Save your spreadsheet as TravelAccounts2

3. With great regret, WideGuy Tours and Travel must inform travelers that, due to the international increase in fuel
cost, the cost of their tours have been increased as follows: Economy precedes First Class: Caribbean Tour - $11,
500, $15,260; European Tour - $38,000, $42,800.

(a) Make the necessary adjustment to your spreadsheet to reflect the effect of these price changes.
(b) Sort the data for each tour in ascending order by total payment MADE
(c) Use an advanced sorting feature to find persons who is in a group who booked the Economy Class.
(d) Create a table which lists the tours and the gross payments collected for each tour . Using this table, create an
appropriate chart which compares the amount collected for each tour. Data labels should show category names and
values. Name this chart gross payments collected.

(e) For any ONE tour, generate an appropriate chart which includes the names of the person booking, and compares the
numbers in the group with the total payments made by each person. Name this chart Chart2.
Save your spreadsheet as TravelAccounts3.

Save TravelAccounts1, TravelAccounts2, TravelAccounts3, Chart1 and Chart2.

 Database Management

Accurate information must be maintained on all persons who travel on any of the tours. Using information from your
spreadsheet, you are required to design and populate a database with data pertaining to EACH person making a booking as well
as the persons in each group. The number of persons in each group must be consistent with the Number in Group column in your
spreadsheet.

You should create three tables in your database. Required Personal Information on travelers making bookings (reservations)
should include, but is not restricted to bookings. Information should include booking number, title, FName, LName, gender,
address (city), telephone number, booking date, Tour, passport number, total payment made and number in group . Each person
making a booking should be assigned a booking number.

Each of these persons in the group should be associated the person making the booking. Data on these travelers should include
just their name, passport number, date of birth and sex.

Another table should be created listing the following data, which states the countries to be visited on each tour.

Tour
Destination Country1 Country2 Country3
Caribbean Bahamas Jamaica St. Martin
European England France Italy

TASK 1
You are required to:
1. Design and create database tables to meet the required criteria.
(A) PERSONAL INFORMATION – persons made the booking
Create a table with the following heading booking number, title, FName, LName, gender, address (city),
telephone number, booking date, Tour, passport number, total payment made and number in group.
(B) TOUR
Create a table with the following heading: Tour Destination, Country1, Country2, Country3,
(C) COST
Create a table with the following headings: Tour Destination, Economy, Cost
2. Appropriate data types / field names/ widths are to be used when designing the structure of each table.
3. Save all tables.
TASK 2
Your database should respond to the following queries.

1. List the passport number, first name, last name, sex, address and tour of any person with a specific booking number.
Name this query MainBooking.

2. List the passport number, booking date, class and tour of all persons who made their booking (reservation) on or
before May 1st 2015. Name this query EarlyBooking.

Use query summary features to calculate:

3. The total payments for all the tours

4. Perform a query to deduct a ten percent discount on total payments made by persons who made bookings on the
Caribbean tour. Name this query FurtherDiscount. Save the table showing names, tour, booking date, total
payment and adjusted payment of these travelers.

5. Prepare a second report which lists information on travelers who made bookings for the tours to Kaieteur or Europe.
The report must be grouped by tour destination and sorted by booking date, then by last name. The report should
also list the name, passport number, sex, number of persons in group and total payment. For each grouping level the
report must indicate the average total payment. A grand total of payments should be indicated at the end of the
report. Give a meaningful title to this report, which should be on two lines. The title of the report must be centre
aligned. Save this report.
 Word Processing

1. WideGuy Tours and Travel places advertisements of their tours in selected newspapers and travel magazines throughout
the Caribbean. You are asked to design an attractive and informative advertisement which would be used in the print
media. The ad should be designed on letter size paper (8 ½” x 11”) using suitable features of a word processing
application. The left margin must be set to 1”. All other margins should be set to .5”

2. The tour itinerary and other pertinent information on each of the persons who made a booking to travel on the Kaieteur
or European tours must be sent by internal memo to Ms. Janice Browne, Tour Manager at WideGuy Tours and Travel.
The memos are sent from you, the Technology Solutions Specialist.

Each memo must list the name, address and passport number of the persons who made reservation on the above-
mentioned tours. The memo should also list the Class of travel. If the booking was made for first class travel, then
‘Class – FC’ should be stated. ‘Class – EC’ should be stated if economy class option was chosen. There should
therefore be a memo (file) for each person who made a booking on behalf of themselves or their group.

You are required to send these memos to Ms. Browne, outlining details and other pertinent information for the selected
tours. The paragraph should end with the statement: “Please find a listing of travelers booked on Kaieteur or European
tours.”

At this point you should insert the second report from the database.

Using the mail merge feature of your word processing program, and data from your database, insert the relevant data
into the first paragraph of the memos to Ms. Browne. Save memos with information on the first two and last two
persons only.
 SECTION D – PROBLEM-SOLVING AND PROGRAM DESIGN

PROGRAM IMPLEMENTATION
Problem-solving

You are required to:

1. Develop an algorithm in narrative and Pseudocode or flowchart that will accept the names
of 7 persons booked to go on the tour, it must input the names of the persons, the name of
the tour, and the class preferred . The algorithm MUST calculate the 25% down payment,
5% additional discount for booking before 1 May, 2021, 8% discount if the group has four
or more persons, the total payment made and the amount due. It should also display the
name, the name of persons in a tour, the total discount received, the total payment made
and the final payment due.

2. Design and execute a trace table with the column headings ‘Name’, ‘Amount due”, “Total
Discount”, “Total payments made and ‘Final Payment due’. Using the trace table, display
the highest discount and the persons with least final payments due.

Write a program to implement the algorithm in Task 1 of the Problem Solving, using a learned
programming language