Information Technology School Based Assessment

(Grenada)

2022-2023

Theme: Covid-19 Pandemic

Description

The novel coronavirus has severely impacted Grenada and its dependencies. From March 2020 to

June 2022 a total of 18,786 persons tested positive for the virus. As a Data Analyst, the Ministry

of Health has hired you to provide up to date data, through the use of productivity tools such as

spreadsheet, database, web design, word processing as well as program solving and program

implementation features for new confirmed cases in the communities, the covid-19 variants present

on island and the different types of vaccinations that have been available. These vaccines include,

Moderna, Pfizer, AstraZeneca and Johnson & Johnson.

NB: Modifications to the description are acceptable.

Spreadsheet

The Ministry of Health has asked you to design a Spreadsheet that will be used to analyze the

statistical data on the number of Covid-19 infections as well as the number of vaccinated and

unvaccinated persons among them for the period 2020-2022 based on parish.

Task A:

1. Create and populate the spreadsheet with information based on the sample worksheet

below:
Parish Percentage No. Of Percentage Cases Cases
Of Cases Covid Vaccinated Unvaccinated
(%) Cases Vaccinated (%)

100% 18,786

a) Insert the parishes (all including Carriacou) and percentage of cases as well as the

percentage of vaccinated among them.

b) Percentage of vaccinated must range from 15 to 45%.

2. Using the appropriate formulas:

a. Calculate the number of Covid cases for each parish.

b. Calculate the number of vaccinated persons among the cases in each parish.

c. Calculate the number of unvaccinated persons in each parish.

d. Calculate the total number of covid cases that were vaccinated and unvaccinated.

2
 e. Sort the table to rank the parish with the most unvaccinated residents to the parish

with the least.

3. Using formatting tools:

a. Bold and center the column headings.

b. Text wrap the column headings.

c. Change the font style to Times New Roman.

d. Change the font size of the entire table to 12 points.

4. Rename the worksheet Covid Parish.

Instruction: Save the workbook as Covid Data1 <Student Name>.

Task B:

Data was collected from a group of persons, who traveled on various flights that landed in Grenada,

within the first quarter of the year in 2022. A covid-19 test was done on arrival at the airport, at

the cost of $150. Everyone who tested positive had to be quarantined in a government facility until

they got a negative test result. The cost of accommodation was $250 per day. Persons with positive

tests spent from 5 to 14 days at the facility.

1. Create a new worksheet, using the sample below:

3
 First Last Region of Vaccine Test Days in Total Total
DOB Parish Status Accommodati
Name Name Origin Name Result Quarantine Cost
on Cost

John Doe 12/3/2018 St. Europe Vaccinated Pfizer Positive 6 $1500 $1650

Andrew

Mary White 3/5/2001 St. Patrick North Unvaccinated None Negative 0 $0 $150

America

a) A person’s status can be vaccinated or unvaccinated. Use the appropriate formula

to determine status.

b) Region of origin can be any of the following: South America, North America,

Europe, Caribbean, Africa or Asia.

c) A minimum of 3 and maximum of 5 persons must arrive from each region.

d) The worksheet must contain no less than 25 persons.

2. Count the number of persons who tested positive.

3. Calculate the total cost of accommodation per person and the total cost overall.

4. Modify the total accommodation costs to show 2 decimal places.

5. Use an advanced filter to show only the person(s) testing negative for Covid-19 and were

unvaccinated.

6. Create a pie chart to show the number of persons testing negative, using the advanced filter.

4
 7. Create an appropriate chart to show the total cost paid by individuals arriving from each

region. Name the chart Total Cost.

8. Rename the worksheet Covid Travel.

Instruction: Save the workbook as Covid Data2 <Student Name>.

Task C:

The Ministry of Health has requested you provide a summary table, reflecting the number of

positive Covid-19 cases, that came into Grenada from North America and Europe.

Number of Positive Cases Region of Origin

1. Using the data from the Covid Travel worksheet, complete the table above.

2. Rename the worksheet, Covid Summary.

3. Create a pivot table in a new worksheet, comparing the regions of the positive cases that

came into Grenada against the negative cases.

4. Rename the worksheet Covid Comparison.

Instruction: Save the workbook as Covid Data2 Update <Student Name>.

5
Database

The Ministry of Health desires to have the collected information of all persons, coming in on flights

to Grenada, in a database. As the Data Analyst, you have been asked to create a database system

with four (4) tables to capture the relevant data. The data in the spreadsheet section of the SBA

should be incorporated into the database. The following are the names and descriptions of the

tables:

1. The Passenger table should include PassengerID, First Name, Last Name, DOB, Village,
Parish, Country, Phone Number, Covid Results, VaccineID and FlightNum.

o PassengerID should include the passenger’s initial and a three-digit random

o Country represents the country of arrival. At least one country must be selected
from each region in the spreadsheet.

o A minimum of three passengers must arrive from each country.

2. The Flights table should include FightNum, Airline, Date landed and Arrival Time.

o FlightNum must include the first two letters of the Airline name and a random

two-digit number.

o Date landed must be within the first quarter of 2022.

3. The Vaccines table should include VaccineID and Vaccine Name

o VaccineID is the first three letters of the vaccine name.

4. The Quarantine table should include QuarantineID, PassengerID, Quarantine Days and
Accommodation Cost.

6
 o QuarantineID should be an autonumber, generated for each passenger who had to
quarantined.

o Only passengers testing positive for Covid should be included in this table.

Queries:

1. Create a simple query to show the First Name, Last Name, Address, Country of all persons

who had positive covid test results. Save this query as Positivecases.

2. Create a simple query to show the First Name, Last Name, Country, FlightNum & Vaccine

Name of all persons in the parish of St. David, who arrived in Grenada between January 1,

2022 and February 15, 2022. Sort this query by Last Name in ascending order. Save this

query as StDavid.

3. Create a query that counts the total number of persons receiving the different vaccines.
Save as Totalvac.

4. Create a query that calculates the actual date the passengers, who tested positive, should

be released from quarantine, based on the date of arrival and the number of days spent in

quarantine. Save as Quarantinedays.

Form:

Using the Covid Travel worksheet from the Covid Data2 <Student Name> workbook, create a
form to display the type of vaccines taken by the vaccinated passengers, who arrived in Grenada.

1. The main form should include the PassengerID and FlightNum .

2. The sub-form should include the VaccineID and VaccineName.

Save this form as VaccineForm.

7
Report:

Generate a report to show a summary of persons who quarantined upon arrival to Grenada and the

vaccines they opted for. The report should list First Name, Last Name, Address, Country, Covid

Results, Vaccine Name, FlightNum, Quarantine Days and Accommodation Cost. Group by

Vaccine Name and sort by Last Name in descending order. The report should also display the total

number of days spent in quarantine, the total accommodation cost and the grand total of both

quarantine days and accommodation costs. The report should have the title:

“GRENADA’S COVID19 SUMMARY

FOR THE PERIOD 2020 - 2022”.

Save as SummaryReport.

Instruction: Save the database as Covid Cases <Student Name>.

Word Processing

There have been a number of new covid-19 cases in Grenada. In an effort to limit community

spread, the Ministry of Health needs your help to inform the residents of the need to get tested if

they are experiencing any covid symptoms and to get vaccinated.

Task A

1. Design a letter, using the appropriate mail merge features to inform residents who have

traveled into the country recently that they may have been exposed to Covid-19 on their

flight and that they should get tested if they begin to experience any symptoms. The letter

must include:

a. Passengers’ First Name, Last Name, Village, Parish and Flight Number.

b. Line spacing of 1.5

8
 c. Font style Times New Roman

d. Font size 12 point.

Instructions:

- Save the main document as Covid Letter <Student Name>

- Save the data source as Data Source <Student Name>

- Save the merged document as Merged letters <Student Name>

Task B

1. Create an informative brochure called; All You Need to Know about Covid-19, informing

the general public of covid-19 signs and symptoms; where to get tested and receive

vaccination as well as Covid Guidelines or Protocols. The brochure must:

a. Include bolding and underlining of appropriate headings.

b. Graphics inserted on the cover page or within the brochure.

c. Have a font size of 20 points for headings.

d. Have line spacing of 1.5

e. Be fully justified

f. Have the appropriate margins and layouts to be easily folded

Instruction: Save the Brochure as Covid Brochure<Student Name>.

Task C

1. Prepare a report on the number of vaccinated persons, who arrived in Grenada, during the

first quarter of 2022. The report must include:

a. Table of Contents with suitable headings

b. Report generated by the database showing the Covid-19 summary in Grenada.

9
 c. The pie chart from the spreadsheet, showing the percentage of unvaccinated and

vaccinated persons who came in from each region.

d. Numbered pages on the right footer of each page

e. Font size of 12

f. Line spacing of 2

Instruction: Save the Report as Covid Report <Student Name>.

Web Design

The Ministry of Health has asked you to create a four-page website that will inform the general

public of the protocols for entering Grenada; guidelines/steps to follow when in quarantine and

isolation and tips on personal care for persons who contracted Covid-19.

The website can be created using a word processor, www.wix.com, www.squarespace.com or

www.web.com, among any other free online website creators.

The website must include:

1. Four pages (Home, protocols, guidelines, tips)

2. A designed logo for the Ministry of Health

3. Overview of the Covid-19 situation in Grenada

4. Page listing protocols for entering Grenada

5. Page containing guidelines for quarantine and isolation

6. Page providing tips on preventing Covid-19 spread

7. Pictures that depict Covid-19 in relation to each page

8. Informative video on Covid-19 for any of the pages

9. Link to all the created web pages

10. Link to the World Health Organization (WHO) website

10
Instruction: You are to publish the website and create a word processor document to add the link

for the website. Save the document as Covid Website <Student Name>.

Problem-Solving and Program Design

Algorithm:

Create an algorithm using pseudocode or flowchart to accept the names of our seven parishes

(Carriacou included), number of vaccinated persons, number of unvaccinated persons and positive

cases. The algorithm should calculate and display the total number of vaccinated persons, total

unvaccinated persons as well as the total positive cases on the island.

Additionally, you are required to calculate and display the parish with the highest number of

positive cases. Save the Algorithm as Algorithmcovid19 <Student Name>.

Trace Table:

You are required to design a trace table, to accept test data and trace the algorithm above. Save

the Trace Table as TTcovid19 <Student Name>.

Programming:

You are required to write a program that would implement the algorithm above, using a suitable

programming language. Create a word processor document with the program code and screenshots

of the program runs and results. Save the word document as Programcovid19 <Student Name>.

11