ST.

HUGH’S HIGH SCHOOL FOR

GIRLS

CSEC INFORMATION TECHNOLOGY

SCHOOL BASED ASSESSMENT

(General Proficiency)

Year 2017-2018

SPREADSHEET

DATABASE MANAGEMENT

WORD PROCESSING

PROBLEM SOLVING AND PROGRAM DESIGN

Student Name: ________________________________________

Student ID number: ________________________________________

Teacher: ________________________________________

1
 Description of Project

Hotel Sea Side is a well established all inclusive hotel on the north coast with a capacity
of two hundred (200) rooms. Recently the management has decided to launch a new
promotion to attract more guests to their hotel. They have decided to offer to cooperate
entities group packages for stays during their off season period.

They have decided to target a number of entities around the island by sending them
promotional and informational material making them aware of their new offerings.

With the expected upsurge in guest arrivals, the management team has also come to the
realization that a more efficient system of tracking new and current guests is going to be
necessary. They need to be able to easily enter, edit, retrieve and maintain the records of
all their guests. There is also a need now to better track the current and projected capacity
of the hotel.

The management team will also be requiring daily, weekly and monthly revenue reports
to access the success of this new proposal. Therefore the staff must be able to produce
reports on monthly income and expenditure and also give projected figures based on
expected arrivals,

They have contracted you to provide them with a comprehensive and user-friendly
system. You are expected to use the necessary word-processing, spreadsheet, database
management and programming tools to satisfy their requirements

2
Spreadsheet
The management team of Hotel Sea Side maintains financial records of guests using a
spreadsheet. In an effort to improve occupancy the hotel will be offering varying discount
packages based on the table below.

Package Description Discount

Bronze Single 2%
Silver Couple 3%
Gold Families/Groups of 3 to 9 persons 5%
Platinum Groups of 10 or more. 10%

Note: Early bird discount is 15%, if a guest has been awarded the early bird package, no
other discount SHOULD be applied. The promotional period spans October 1 2017 to
October 31 2017. If reservation is made on or before September 15 2017 then the early
discount bird discount is applied.

You are required to:

Task A

1. Using the template below design a worksheet (s) which accepts reservation
information for individuals from FOUR (4) companies (Sagicor Bank,
National Commercial Bank, Bank of Nova Scotia and Guardian Life) . Each
group should consist of no less ten (10) and no more than fifteen (15) persons.
One company can utilize several packages; the spreadsheet should calculate the
cost per person for each package.

Note: ensure that all packages are utilized and enter the length of stay for
each. Assume that all individuals within the SAME group stay for the
same duration.

3
Last First Package duration early Final
Date of Package status Cost Discount
Name Name Discount of stay bird Cost
reservation
Lacy-
Rose Ann 9/16/2012 Platinum resident 0.1 5 $77,500 N $7,750 $69,750

(i) Insert Last Name, First Name, Date of Reservation, Duration of stay, Package
and Status for each resident. ALL other fields should be populated using the
relevant formulae.

(ii) The status of the guest is either resident or non-resident. The promotional
rate for resident is Jamaican (Ja) $15, 500 and US$ 200 per night for non-
resident at an exchange rate of $89 that is subjected to change.

(iii) Determine the package discount for each package. Use a VLOOKUP or IF
statement.

(iv) Calculate cost : If individual status is resident then the cost is

resident rate * duration of stay. If individual status is non resident then the
cost is non resident rate * duration of stay* exchange rate.

(v) Insert a formula to compute the early bird status.

(vi) Calculate final cost after discounts have been applied.

(vii) Count the number of reservation made for each group.

(viii) Save spreadsheet as reservation 1

4
TASK B

(I) The exchange rate has been increased to $90.01

(II) Due to the overwhelming response to the promotion early bird discount has
been increased to 18.5%. However the first group has been overlooked for this
discount increase.

(III) Insert two (2) additional reservations to the spreadsheet for group three (NCB).

(IV) Sort the data in ascending order using last name as primary key and status as
secondary key.

(V) Save spreadsheet as reservation 2

TASK C

(I) Sort in descending order on final cost.

(II) In an appropriate section of each spreadsheet, extract the data for all guests
who received an early bird discount.

(III) In a new worksheet show the total cost for each company

(IV) Create pie chart that compares the total cost for each company. Give the chart
a suitable name and label appropriately.

(V) Create an appropriate chart which compares final cost of guests for Guardian
Life. Give the chart a suitable name and label appropriately.

(VI) Save the spreadsheet as reservation 3.

5
Database Management

Task A

Seaside Villa also recommends that you maintain a table which holds the visitors
information, company data and data pertaining to the financial data of the villa.

You are required to:

i. Design and create database tables to meet the required criteria.

ii. Select appropriate data types/ fields widths when designing the structure of each
table.

1. The required visitors information are Visitor_Id, first name, last name, sex, status,
next of kin and telephone number, date of reservation, company Id, package id and
number of days save this table as Visitors table.
2. The Company table should include the following fields, company Id, company
name, address, telephone, contact person, email and group size.

Comp_Id Comp_Name Address1 Address2 Tel_no Contact_person Email Groupsize

BNS

NCB

Guardian Life
Sagigor

3. The Payment table should include Payment Id, company Id, Visitor Id, Discount,
Early bird, payment, final cost.
4. The Package Table should include PackageId, Package Description and Package
Discount.
5. Sort the data in Visitors table by last name and then by first name.

6
 6. NCB has added another guest to their booking add another record to the
appropriate table/s.
7. Change the field name Contact_Person in the company table to
Company_Representative.

Task B

Your database should respond to the following queries.

a. List all the companies that made reservations at the hotel, name this query
qry_Company.
b. List all the guests that are from Guardian Life company; the query should also
include the type of package, status, date of reservation and the final cost.
Name this query qry_Guardian.
c. Find the total number of guest, name this query, qry_total_guest
d. List all the guests that are early bird and are platinum guests, this should
display: company, visitor’s name, package, early bird and the final cost of
package, name this query, qry_EarlyPlatinum.
e. The manager has decided to give an additional 5% discount of the final cost to
all visitors who spend more than five (5) days at the hotel. Perform a query that
will include the discounted portion; the query should also include: type of
package and company name. Name this query Calculated_qry.

Task C

Create a report showing all the visitors of the hotel. The report should include visitor’s
name, telephone number, company name, date of reservation, type of package and final
cost. The report should be grouped by company and sorted in ascending order by last
name, then by first name. The report should calculate and show the average and total
payment. Give the report a suitable title using two lines.

7
Word Processing

1. Using a suitable feature of your word processing application, create a document

that should be mailed the companies to inform them of a winter promotion. The
second paragraph should inform the visitor(s) about the promotion, it should
include the sentence ‘We are inviting you and your staff to come and make use of
our reasonable packages ’. The third paragraph should make mention of the
promotional season as well as the ‘early bird’ package. Make mention of the
brochure that is attach with the promotional information. Include a letterhead.

1b. You are required to print letters addressed to the first two and the last company.

2. Each applicant must complete and submit a booking form to the front desk of
Hotel Sea Side which could have been downloaded from their website or picked
up from the front desk of the hotel. You are required to design the form. This
form must allow for the entry of relevant data on the visitor/visitors, including his
or her choice of package.

The form must be designed on letter sized paper and must have a 0.7” margin on
both sides. A letter head must be placed at the top of the letter. A suitable footer
must be insert and should be center align.

3. As a means to inform the applicants of the many services and attractions available
at the hotel, you are to design a brochure intended to inform the public about the

8
 attraction and services available for there comfort. The brochure should be design
on a letter size paper using suitable features of a Word Processing application.
Using formatting features

NB. You have a choice of using either two or three fold design.

Problem-Solving

1. Develop an algorithm to keep track of the status of all rooms, the number of
available rooms and assign a guest to the first available room. Based on the
information given, the program should neatly display the guest’s name, derive
their room number, calculate the cost of accommodation and any discounts that
may apply and the final cost. The program should also after every iteration display
the total number of registered guests and the number of available rooms.

2. Design and execute trace table that accepts data for reservation. The table should
accept cost, discount and total payment for each member of a group. The number
of available rooms should also be counted. The table should have at least 10
iterations.

Program Implementation

3. Using the programming language Pascal, write program code to implement the
algorithm in (1).