2020-2021

CAMPION COLLEGE

CXC CSEC INFORMATION

TECHNOLOGY
(General Proficiency)

Paper 03 – School Based Assessment

Practical Assignments
Database Management
Spreadsheet
Word-Processing
Web Based Design
Problem-Solving and Programming

Copyright ©2020 Campion College

ALL RIGHTS RESEERVED
 MAY/JUNE 2021

Description of Project
Coronaviruses are a large family of viruses that are known to cause illness ranging from the common cold to
more severe diseases such as Middle East Respiratory Syndrome (MERS) and Severe Acute Respiratory
Syndrome (SARS). A novel coronavirus (CoVid-19) is a new strain of coronavirus that has not been previously
identified in humans.

Most people infected with the COVID-19 virus will experience mild to moderate respiratory illness and
recover without requiring special treatment. Older people, and those with underlying medical problems
(comorbidities) like cardiovascular disease, diabetes, chronic respiratory disease, and cancer are more likely
to develop serious illness.

Jamaica reported its first case of COVID-19 on March 10, 2020 and by September 7, 2020 Jamaica was
officially declared Corona free. The Ministry of Health and Wellness has employed a CoVid Response Team
(CovRT) to track the spread of the Coronavirus in Jamaica and educate citizens on how to protect themselves.

As part of the wrap up analysis, the CoVid Response Team needs to analyze the data over the 7 month period
and provide the Prime Minister, MOH and insurance companies with a detailed report of how the pandemic
affected Jamaica.

A Website on Tips to Avoid the Coronavirus will be made available to the public as part of the operation No-
Rona Project.

1|Page
Copyright ©2020 Campion College, All rights reserved
 MAY/JUNE 2021

Database – 20 Marks
The CoVid Response Team has received the latest information from the various Public Hospitals for the last 5
months on the Patients who have been suspected of having the virus. The data is so far collated in hand
written tables with the following structures:

Patient Information

Column Heading Description

First Name Patient Name

Last Name

DOB Date of Birth

Gender Male or Female

Address Street Address of

Patient
Town

Parish

Telephone # Contact number for

patient
TRN Tax Registration
Number in the format
XX-234-5678
Next of Kin Contact name for
closest family member

Medical Record

2|Page
Copyright ©2020 Campion College, All rights reserved
 MAY/JUNE 2021

3|Page
Copyright ©2020 Campion College, All rights reserved
 MAY/JUNE 2021

Column Heading Description

PID Hospital Assigned

Patient ID
Age at Admission Ages range from 0.5 to
98 years.
TRN TRN of the patient
Admitting Hospital AMH, UWHI, VJH, STH,
SVH
Symptoms Symptoms of the
patient when they
presented at the
hospital. (Cough,
Fever, Fever and
cough, Shortness of
breath, etc)
CoVid Positive Yes or No
Leaving Status How did the patient
leave the hospital? -
Deceased, Recovered,
Discharged
Medical Bill The cost of all health
services.
Pre-existing Old age, cardiovascular
Conditions disease, diabetes,
chronic respiratory
disease, cancer, other,
none.
Notes
It is possible for a patient to have more than one medical record.
All Patients who test negative must be discharged. Less than 20% of your patience must test negative.

Task A – Tables – 7 Marks

1. Create a database with the name PatientBD_YourName to store the required information.
2. Create and enter records in a table called PatientInfo_tbl. The table should contain 40 – 50 records.
3. Create and enter relevant data in another table called MedicalRecords_tbl.
4. Create a table called Insurance_tbl. Export the TRN numbers of the patients and assign a
corresponding Insurance company, insurance type (private or government) and coverage percentage
to each patient. The private insurance companies are Sagigor – covers 80%, Mediplus– covers 75%,
ParishFarm– covers 50%, and NationWidth– covers 47%. There is also GovCare for civil servants, who
cannot be privately insured that covers 90% of all expenses.
5. Appropriate data type, field size and description must be assigned to each field.
6. Create appropriate primary keys for all tables.

4|Page
Copyright ©2020 Campion College, All rights reserved
 MAY/JUNE 2021

7. A drop down list should be used where applicable to facilitate simplicity in data entry. E.g. admitting
hospital, leaving status etc.
8. You are required to change the field definition of Gender. Change the field size to one (1) so that the
letter ‘M’ is entered for male and ‘F’ is entered for female. A validation rule and validation text
should be set in the event the user tries to enter anything other than M or F. (A copy of the tables
must be made before changes are made)
9. Sort the Data in the Patient Info Table by LAST NAME in ASCENDING order.
10. Perform the necessary association linkages among tables.

Task B – Queries and Forms – 7 marks

1. List the names of all the male patients. Display their Name, full address and Next of kin. Name the
Query MalePatients_qry.
2. Information is needed for patients under 60 years of age at admission who have recovered from the
virus. Display their full name, gender, symptoms, pre-existing conditions and insurance company.
Name the Query RecoveredLt60_qry.
3. How many persons have tested negative? Name the Query Negative_qry.
4. Determine the amount the insurance pays and the amount the patient has to pay. Display their
Name, Gender, Parish, TRN, medical bill, insurance company, insurance coverage, amount paid by
insurance, and amount paid by patient. Name the Query CoPay_qry.
5. Create a form to enter patient’s data with their insurance information as a sub form. Name the Form
PatientData_frm and ensure the form has the heading Patient Data Form. Also include instructions
for filling out the form in the header. Add two (2) navigational buttons to the form.

Task C – Report – 6 marks

1. Create a report of the patients including their Names, Gender, Parish, amount paid by insurance, and
amount paid by patient. Group the report by Insurance Company. Sort on Last Name in ascending
order. Calculate the total paid by the patients as well as the total paid by the insurance companies
and also calculate the grand total for both. Give the report the title “CoVid19 Patients” with a
subheading “March to September 2020”. Name the report PatientSummary

5|Page
Copyright ©2020 Campion College, All rights reserved
 MAY/JUNE 2021

Spreadsheet – 20 Marks
Task A – 10marks

1. Create a workbook named PatientFinances_YourName– Export the category of information below from the Database into one worksheet. Name the
worksheet Patients.

First Last Age at Admitting CoVid Leaving Medical Insurance

TRN Gender Address Town Parish Type Coverage%
Name Name Admission Hospital Positive? Status Bill Company

2. Insert a column and add the label “Date of Testing” and insert the date of testing for each patient noting the date of the first patient and the date of the
end of the pandemic. Model the real data set on the MOH website.
3. Format the worksheet by inserting a heading evenly across the data provided that reads “CoVid-19 Patient Data 2020”. Ensure the font size is larger than all
other headings. Apply 3 other formatting features of your choice.
4. Sort the worksheet by gender then by age, both in descending order.
5. Insert a column called Category beside Age to determine which category each patient falls under. See table below for categorization criteria.
6. Use spreadsheet functions and/formula to perform the following calculations
a. Insert a column called Discount, next to Medical Bill. The discount on all Hospital Fees given to each patient is detailed in the following table: (Use
relevant referencing feature)

Category Age Range Discount

Children 12 and under 50%
Teen Between 12 and 18 exclusive 25%
Adult Between 18 and 65 inclusive 10%
Senior Over 65 30%

2|Page
Copyright ©2020 Campion College, All rights reserved
 b. Calculate the Balance of all patients after the discount.
c. Add two columns, Amount paid by Insurance and Amount paid by Patient after the Balance field.
Calculate the insurance payment based on the coverage percentage and the amount the patient should
pay.
d. Present the data using bordered line style and center and bold all column headings.

Task B – 6 marks
1. On a new sheet named Summary, in the same workbook create a summary table which will contain the
following:
a. Total number of patients
b. Percentage of men
c. Percentage of women
d. Average Insurance Payment made
e. Total money paid by patients
2. In another worksheet, in the same workbook, use complex criterion to generate all the Patients who tested
Positive. Name this worksheet Positive.
3. Copy all the patient data from Task A in a new worksheet called AMH, in the same workbook, and Filter all the
patients whose admitting hospital was AMH.

Task C – 4 marks
1. In the Patients worksheet, create Pivot Table of the total number of positive cases by parish.
2. Create relevant chart, in a separate worksheet called Status, to compare the leaving status of the patients.
3. Create a Line Graph, in a separate worksheet called Confirmed, showing the number of confirmed cases by month.

Notes-Each chart must have appropriate labeling of chart title, chart axes, and data labels including percentages.
 MAY/JUNE 2021

Word Processing – 20 Marks

You are required to create the following documents:

Task A – The Fillable Form – 5 marks

Create a form that patients will fill out in order to collect the relevant data that the CoVid Response Team and hospital will
need e.g. Name, Address, Telephone Number, TRN, Allergies, Next of Kin, Insurance, Date of Birth, and Gender etc.

Employ the appropriate use of any three of the following: Option boxes, Date picker, check boxes, dropdown list, and
command buttons. The form must be user friendly and attractively designed. Save the form as
Patient_Information_YourName.

Task B – The Report – 15 marks

Write a report based on the queries, charts, reports and patient statistics derived from the database and spreadsheet. Your
report should have the following format:

1. Cover Page
2. Table of contents (Must be auto generated in MS Word, must have at least two levels of headings and must have
suitable headings)
3. Introduction stating the purpose of the investigation and the members of the CoVid Response Team of which you are
to be listed as Chief Investigative Officer. (Be realistic in your introduction)
4. The result of the investigation. This should be in point form and consist of no less than 5 points. Be sure to include
results of the queries and charts that you created in the database and spreadsheet section of your SBA. An example is
as follows:

The investigation revealed that:

 65% of patients were senior citizens

<Produce proof of statistics e.g. charts or table.>

 20% of patients were females.

<Produce proof of statistics e.g. charts or table.>…

5. Insert a table showing the different Insurance Companies and their percentage coverage.
6. The Fillable Form from task A.
7. Conclusion and recommendations with your signature affixed at the end. (Be realistic).
8. Add relevant information in the header and footer on each page of the report. Ensure your full name is displayed in
the footer.
9. Save the report as CoVid19Report2020_YourName.

1|Page
Copyright ©2020 Campion College, All rights reserved
 MAY/JUNE 2021

NOTE… The document should be well formatted by using the following features:

 Bold, italics or underline

 double line spacing in the Introduction, single line spacing everywhere else
 Varying font & font size
 Page numbering
 Grammar/ spell check
 Set the Margins to 0.75 all around.

Web Based Design – 10 marks

Create a website with no more than 3 linked pages.

1. Home Page – this page should provide at least 3 paragraphs of information about the corona virus, symptoms and
must include images.
2. Tips – this page should contain tips on how to protect yourself from the coronavirus and must include images.
3. Contact – This page should contain contact information, direction and an email address link for the head of the CoVid
response team, you.

Note

Each Page must have appropriate graphics and relevant information. The layout and design of the page must be suitable to a
government organization. The information on the web pages must not be plagiarized, must be factual and properly cited.

2|Page
Copyright ©2020 Campion College, All rights reserved
 MAY/JUNE 2021

Problem-Solving and Programming – 15 marks

Task A – The pseudocode
You are required to write a pseudocode that will accept at least 40 patient information: Name, Gender, TRN, Age, Medical Bill,
CoVid Status (positive or negative), Insurance type. Calculate for each patient, what the insurance pays and what the patient
pays based on the criteria given in the database section. Calculate a discount on what the patient pays based on the criteria
given in the spreadsheet section.

At the end you are to:

1. Display the number of Positive Patients.

2. Display the percentage Positive Patients.
3. Prompt the user for the patients TRN, search for and display the patient’s information.

Task B – The Tracetable

Design and implement a trace table that accepts data for 10 patients (same data as the spreadsheet). The table should Track
the patient name, parish, insurance information, balance to be paid and patient category. The trace table should also account
for when a user enters erroneous data.

Task C – Program Implementation

Using the programming language Pascal, write a program code to implement the algorithm in Task A above.

3|Page
Copyright ©2020 Campion College, All rights reserved
 MAY/JUNE 2021

Resources

https://www.who.int/health-topics/coronavirus#tab=tab_1

https://www.moh.gov.jm/updates/coronavirus/

https://www.moh.gov.jm/edu-resources/learn-about-the-coronaviruses/

https://www.who.int/csr/don/4-february-2019-dengue-jamaica/en/

4|Page
Copyright ©2020 Campion College, All rights reserved