THE ALEXANDRA SCHOOL
SECONDARY EDUCATION CERTIFICATE
EXAMINATION
INFORMATION TECHNOLOGY
Paper 03 – General Proficiency
PRACTICAL
SCHOOL-BASED ASSESSMENT
WORD PROCESSING
SPREADSHEET
DATABASE
PROGRAMMING
PLEASE KEEP YOUR
DEADLINES.
HAPPY WORKING!
PLEASE NOTE
All assignments must be submitted for correction on the date due.
Spreadsheet must be submitted on or before 20th November 2011
Word processing must be submitted on or before 4th December 2011
Database must be submitted on or before 18th December 2011
Programming – to be announced
These assignments can be submitted on a flash drive or by email to your teacher no later
than midnight of the date given above. If you do not have Internet access, the assignment
should be submitted on the Friday before the due date.
1
The citizens of Trinbarjam, a country in the English-speaking Caribbean, are unable to purchase
housing for their families, mainly due to the extremely high cost of real estate and building
materials. Middle and low income earners are severely affected. The government, through its
agency, the Housing Construction Corporation, has intervened however, and has embarked on
an extensive house building exercise in three selected parts of the country.
Citizens were invited to apply for the chance to own one of these homes. You are asked to use
word-processing, spreadsheet and database management applications to design and
implement computer-based solution to ensure that:
the public is well informed of the government’s new housing initiative
an efficient record of applicants’ information is maintained
applicants’ financial income and expense commitments are accurately calculated and
assessed
suitably qualified and approved applicants are duly informed.
Housing Construction is a state agency mandated to provide affordable housing for middle and
low income earners. The Housing Construction Corporation invites applications from nationals
who are employed and who can demonstrate that they would be able to meet their mortgage
payments each month. These include the applicant’s salary and monthly salary deductions.
Details of the applicants’ monthly expenses on groceries, utilities, transportation, etc. must be
submitted.
SPREADSHEET
Homes are being built to accommodate approved applicants in three communities: Clarendon
Court, Sangre Grande Villas and Providence Gardens. Applicants who are middle and low
income earners must meet a net monthly income of $9500 in order to qualify for consideration
in each of these communities. This net income may be met by the applicant himself/herself or
in conjunction with his/her spouse. Each applicant may not have a spouse.
You are required to:
Using the table below as a guide, design a spreadsheet that contains a list of fifteen (15)
applicants for the housing communities.
First Last Applicant’s Community PAYE Other Spouse’s PAYE Spouse’s Gross Net Status
Name Name Salary Applied for Deductions Salary Deductions Income Income
Include the following table on a new sheet
Monthly (Salary) Deductions
PAYE $5000 If salary is $5000 or less no
tax is deducted.
If salary is more than
8% $5000, 8% is deducted
from the excess amount.
Health Surcharge $155
Union Dues 1% 1% of Salary
Pension 15% 15% of Salary
This table shows how the salary deductions are calculated.
TASKS
Task A
Insert an appropriate logo on each sheet.
Perform the following operations on sheet 1:
a) Calculate the salary deductions, gross income and net income.
b) If the net income meets the qualifying income for housing community applied for as
specified by the Housing Construction Corporation, the applicant’s status would be
‘Qualified’, else ‘Not qualified’ should be recorded. Determine whether the application
qualifies for consideration.
c) Rename this sheet Task A1.
d) Copy all the records from Task A1 to sheet 2.
Use Sheet 2 to complete the following tasks:
e) The spouses of Applicant #4 and Applicant #17 have lost their jobs. Adjust their salary
to suit.
f) Five rows below the list of applicants, write functions to count the number of persons
who qualify, as well as, the persons who do not qualify.
g) Sort the list of applicants by status and then by last name in ascending order.
h) Rename this sheet Task A2.
Task B
a) On a separate sheet, information on each applicant’s monthly expenses must be
calculated. Provide realistic estimates of monthly expenses on groceries, utilities
(water, telephone and electricity), transportation and miscellaneous expenses. A
column stating the applicant’s number of dependents (children) must be included.
Miscellaneous expense is calculated as (number of dependents * $75.00).
b) You must enter expense information for each applicant (each applicant may not have all
expenses mentioned). Insert a column to calculate each applicant’s total monthly
expenses.
c) Five rows below the list write functions to determine the maximum and minimum Total
Monthly Expense.
d) Rename this sheet Task B.
Task C
Copy the data from sheet Task A2 to a new sheet and perform the following operations:
a) PAYE has been increased by 0.5% and Health Surcharge has increased to $175. (Please
note, these changes should not affect the data on sheet Task A2)
b) Insert a column called Total Monthly Expenses between Net Income and Status
column. Place each applicant’s Total Monthly Expense in the corresponding cells.
c) Insert a column called Balance between Total Monthly Expenses and Status. Balance is
calculated as Net Income – Total Monthly Expenses.
d) Add another column that states whether the applicant has been approved or not for
housing allocation. An application is approved if the Balance is greater than 95% of the
Gross Income.
e) Applicant #12 is no longer interested in purchasing a house. Remove this applicant from
the list.
f) Extract the applicants whose net income is greater than $10000.
g) Create a suitable chart that compares each applicant’s expenses. Give an appropriate
name to this chart and place it on a new sheet.
Save and Print Formulae sheets. (Please make sure that cells are referenced to previous
worksheets appropriately)
Requirements
Your spreadsheet should:
1) Include absolute and relative addressing.
2) Use appropriate functions to perform calculations.
3) Use cell referencing. (When performing calculations, cell referencing should be used,
e.g. =A4*4+2 (Incorrect). Rather, you should place the values 4 and 2 somewhere in the
spreadsheet and reference them. E.g. =A4*A20+A21, where 4 and 2 are located in cells
A20 and A21 respectively).
4) Use Range Names where appropriate.
5) Use formatting features where appropriate to enhance the look of the spreadsheet.
Word Processing (A)
You are required to create a three-fold brochure on legal size paper. The brochure should
contain information about the housing solutions and an application form. It should also inform
prospective clients of the documentation needed when applying (for example, job letter). Use
mail merge features to send this document to the 1st person in each community from the
spreadsheet.
Requirements
You word processing document should include:
Formatting features: Bold/Underline/Italics/Borders & Shading/Bullets &
Numbering/Varying font types and font sizes (Any two)
Appropriate margins
Header/Footer or Endnotes (Any one)
Table(s) - borders
Graphics
Double spacing
Company logo (copied from spreadsheet)
Alignment – (justify, center)
The following images show the suggested the layout for the brochure.
Front View
Supporting Company
> > >
Documents Information
>
>
>
Back View
Housing
Solutions
Application Form
DATABASE MANAGEMENT
Information must be maintained on all applicants for housing units. Using the information from
the data source, you are required to design and populate a database with applicant data.
Tables
1) Create a table called Applicant Info. The table should contain the following field names:
a. Applicant ID
b. Title
c. First name
d. Last name
e. Sex
f. Address1
g. Address2
h. Parish
i. Date of birth
j. Marital status
k. Number of dependants
l. Tel. Number.
m. Housing community – (i.e. the applicant’s selected choice of community)
2) Applicant ID is five characters in the format AB107 where the first two characters are
the first and last initials of the names.
3) Financial data on each applicant is also required. Create a table called Financial
Information. The data for this table should be taken from the spreadsheet. The table
should include the following fields:
a. Applicant ID – (use the IDs from the Applicant Information table),
b. Net income
c. Total monthly expenses
d. Approval status
You are required to:
i) Design and create database tables to meet the required criteria.
ii) Appropriate data types/field width are to be used when designing the structure
of each table.
General Tasks
You are required to:
Copy the Applicant Info table to a new table called Applicant Information.
Use the new table to perform the following tasks:
Remove the marital status field.
Delete one record from the Sangre Grande Villas of your choice.
Sort the records by housing community and then by last name.
Queries
You are required to:
a) List the last name, address, phone number, net income and number of dependents of all
applicants who listed Clarendon Court as their choice of housing allocation. Name this
query Clarendon Applicants.
b) List name, applicant ID, net income and total monthly expenses of all female applicants
whose total monthly expenses exceed $450.00. Name this query Expenses.
c) Count the number of persons who applied for homes in Sangre Grande Villas. Name this
query SangreGrandeCount.
d) List the name and date of all applicants who are between the ages of twenty and thirty,
inclusive. Name this query TwentytoThirty.
e) List the title, names, address, telephone number and housing community of all
applicants whose application has been approved for housing unit allocation. Name this
query Approved.
f) Perform a query to add a further ten (10) percent to the salary of Providence Gardens’
applicants. Name this query ProvidenceGardens.
Report
Using the Approved query, create a report. This report should be sorted by community, then by
last name. The report must show each applicant’s title, name, applicantID, address and housing
community. The report should be grouped by community and for each grouping level the
report must indicate the applicants’ average net income. The logo should also be included in
report title area. The following is the report title and should appear on two (2) lines:
HOUSING CONSTRUCTION CORPORATION
Approved Applicants