CALABAR HIGH SCHOOL

CSEC INFORMATION TECHNOLOGY
(General Proficiency)

School Based Assessment

SPREADSHEET

-------------------------------------------------

Description of Project
It takes 90 seconds or less for a car thief to take your vehicle. It is no longer enough to just roll-up your windows and lock your doors. Although few stolen vehicles are recovered within 48 -72 hours, the damage has already been done.
The Spartahouse City Council in collaboration with the Spartahouse Police Department is encouraging residents to protect their vehicles. This is due to the fact that Incidents of Grand Theft Auto in the city have spiked by over 35% over the last year. There is work to be done to educate citizens on how to protect their vehicles to reduce this particular crime even more. The council has been approached by the National Motor vehicle Theft Reduction Organization NMTRO and has planned to launch a campaign entitled “PROTECT YOUR VEHICLE". The program is specifically targeted at encouraging owners of high risk vehicles to install immobilizers and other antitheft devices.
NMTRO works with other stakeholders to develop and implement countermeasures that will impede the activities of organized criminals.
In the city and surrounding suburbs, any make or model of car is subject to theft. Toyota and Nissan models made between 2005 and 2012 and Honda products are the most vulnerable for being stolen.
The City has hired a team of specialist to analyze the car stealing situation in the city. They need to have an idea of the magnitude of the car stealing situation in order to provide preventative measures. In the past the City council distributed free immobilizers to owners of high risk vehicles after receiving a grant from the NMTRO. A repeat of this venture is being considered.
The insurance companies are also interested in these results since the recent trends is causing them to consider raising the premiums and introducing penalties based on the number of car owners who fail to install antitheft devices on their vehicles. They also require a program based on the study to assist their business in helping with the data collection and the necessary calculations with reference to premiums and payments.
At the end of this analysis, a report is to be presented to the Mayor and other relevant parties including NMTRO and the insurance companies.
A pamphlet on Tips to Avoid Auto Theft will be made available to the public as a part of the Operation Bounce Back Project. Candidates are required to utilized Word processing, Database, Spreadsheet and Programming packages to efficiently carry out the required activities.

SPREADSHEET
TASK A
You are required to:
1. Create a workbook based on the headings below: Statistical Analysis | FirstName | LastName | Gender | Year Manufactured | Model | Category | Insurance Company | Type Of Coverage | Anti Theft Device Installed | Value | Sasha | Moore | Female | 2000 | Accord | LCR-70 | BCC | TPO | FALSE | J$612,000.00 | Jermaine | Black | Male | 2005 | Charade | LCR-71 | Nationwide | FC | TRUE | J$2,617,881.00 | * The categories are as follows: * LCR-70 Motor vehicle theft, cars * LCR-71 Motor vehicle theft, SUV\van * LCR-72 Motor vehicle theft, truck or bus * LCR-73 Motor vehicle theft, other vehicles

* The type of coverage’s are as follows: * Third party only (TPO) or Fully Comprehensive (FC)

2. Insert columns and add the labels “Age of Vehicle” to the right of Year Manufactured” and “Premium" to the right of "Value" columns. Modify the spreadsheet as necessary using "M" for "Male" and "YES" for "Antitheft Device Installed"

Use the spreadsheet functions and/or formulae to perform the following calculations. a) The age of each vehicle based on the year it was manufactured. Hide the column labeled “Year Manufactured”. b) Total ALL appropriate columns and ensure that your worksheet is efficient. Also use formatting features to allow clarity of your worksheet (example bold, decimal places $-and % signs). c) Enter the regular premium for each vehicle in the worksheet based on the insurance company table below.

Insurance Company | Premium | STATEFARM | 1950.00 | BCC | 2700.00 | IWCI | 1675.00 | NATIONWIDE | 3500.00 |

FirstName | LastName | Gender | YearManufactured | Model | Category | Insurance Company | Type Of Coverage | Anti Theft Device Installed | Value | Tyrese | Witter | Male | 2000 | Eclipse | LCR-71 | State Farm | TPO | FALSE | J$1,210,000.00 |

3. Insert before the record number 8 the following data

4. Sort the data in the worksheet by Type of Coverage (Primary field) and Insurance Company (secondary field).

Present the data using bordered line style and centre and bold all column headings. Titles should be displayed evenly across the data provided.

Save as Task A
Print this worksheet. Include printouts showing ALL formulae.
.
TASK B 1. The insurance companies calculate the compensation packages for stolen vehicles based on the age of the vehicle and the insurance coverage. Incorporate the following information in your worksheet. If the vehicles were not insured FC, then the owner would receive 0% payment. Use additional columns as necessary.

Age of vehicle | Payment by Insurance Company | 1 year or less | 50% | 2-4 years | 40% | 5-7 years | 30% | 8-10 years | 20% | Over 10 year | 10% |

2. In another area of the worksheet, generate the following information from the table: * List of New cars (less than two years) with No antitheft devices installed * List of Female drivers * List of Vehicle comprehensively insured

. Save as Task B
Print this worksheet. Include printouts showing ALL formulae.

TASK C

1. On a new sheet in the same work book, create a summary table which will contain the following:

Summary information for first quarter of 2014 | Number of vehicles stolen | | Number of cars less than 4 years old | | Average insurance payment made by insurance company | | Percentage of Female owners whose vehicles were stolen | | Maximum Vehicle cost | | Number of Vehicles Comprehensively insured | | % of owners who installed antitheft mechanisms | |

2. Create a pie chart to compare male and female victims. The chart should have a suitable heading and percentage for the data displayed.

3. Create a bar chart to compare the different categories of vehicles stolen over the 4 month period.

Save as Task C
Print this worksheet. Include printouts showing ALL formulae.

* Specific Guidelines for the Spreadsheet

* Enter records so that there is no less than 30 records and no more than 40 records. * Make sure you print ALL your formulas. * Print your gridlines or set borders for all your tables. * When printing your formulas, make sure you print the column and row labels as well. * Make sure you design your spreadsheet so as to make full use of the cell referencing capabilities. Your formulas must be very efficient. * All pages must be numbered. * All pages should include a header with your name and email address and a footer with your teacher’s name and school.

DATABASE
The analysts have received the statistics for the first quarter of 2014. The tables which contain the data have the following structures, import all necessary information.

INCIDENT Field Name | Type | Description | Incident Number | Auto Number | An automatic number assigned when a motor vehicle theft is reported | VIN | Text | Registration Number of Vehicle | Category | Text | LCR-70, LCR-71, LCR-72, LCR-73 | Date | Date\Time | Date of Incident | Time of day | Date\Time | Time Vehicle was Reported stolen | Occasion | Text | Holiday, Midweek, Weekend | Location | Text | Home, Car park, Other | Address | Text | Address of Incident |

VEHICLE Field Name | Type | Description | VIN | Text | Registration Number of Vehicle | Year | Number | Year Vehicle was manufactured | Vehicle Make | Text | Manufacturer of vehicle | Model | Text | Model of Vehicle | Vehicle Type | Text | Sedan\SUV\Truck etc | Transmission | Text | Manual\Standard | Engine Number | Text | Engine Number | Colour | Text | Colour of vehicle | Owner Number | Text | The TRN of owner | Proprietor Type | Text | Private, Company, Government | Anti Theft Installed | Logical | Yes or No | Value | Currency | The Value of vehicle at last assessment |

OWNER Field Name | Type | Description | IDNUMBER | Text | This reference to the owner number in the VEHICLE table | First Name | Text | | Last Name | Text | | Address | Text | | Gender | Text | Male\Female |

Notes: 1. Records are to be added for the first quarter of 2014. Data for each incident should be entered in the table INCIDENT and the corresponding data entered in the VEHICLE and OWNER tables. 2. It is possible for an owner or company to have more than one vehicle stolen for the duration. TASK A
You Will Need

1. Enter Records in the tables so that the INCIDENT table contains no more than 30 records.

2. Enter relevant data in the remaining tables so that they correspond to the records in the INCIDENT table.

3. Utilize formatting features e.g. list boxes and input mask and select the appropriate primary key for each table.

4. Create a table called INSURANCE. Export the registration numbers of the vehicles and import the corresponding insurance company and premium for each vehicle. Include a field that indicates the type of coverage i.e. Third party only (TPO) or Fully Comprehensive (FC). All Government vehicle and vehicles that are less ONE year old MUST be fully comprehensively insured.

5. Create a table called STAKEHOLDERS. This table will contain mailing information for all the companies\individuals that will be updated on the findings. These include:

(i) The Superintendent of the Spartahouse Police Department (ii) The Chief Technical Officer of NMTRO (iii) The Mayor of the Spartahouse City Council (iv) The Insurance Association of Spartahouse.

6. Perform the necessary association linkage between tables (relationship diagram).

You are required to submit: a) a printout of the description of each table structure (design view) b) a printout of the relationships (relationship diagram) c) a printout of the data contained in each table (datasheet view)

TASK B a. List the names of all victims sorted by last name. Display the make, model and type of vehicle owned, the insurance company insured with and the value of the vehicle. VICTIMS

b. Find the total number of motor vehicles stolen for each occasion. OCCASION

c. The owners of each vehicle that are comprehensively insured are required to pay 0.5% of the value of the vehicle as a part of their Annual fee to NMTRO. List the owner, vehicle number, Vehicle manufacturer, value and the amount contributed. NMTROCON

d. What is the vehicle with the highest value stolen? Display the name of owner, type of vehicle, location and occasion. MOSTVAL

e. How many Government Vehicles and Company owned Vehicles were stolen over the quarter? NOTPRIVATE

f. List all Owners that had some form of antitheft mechanism installed and their vehicles got stolen at home. Antitheft_StolenHome

g. An error was made in the incident table. All data on Incident 2 should be deleted. INCIDENTDELETE

h. ALL insurance companies value each vehicle at 2% more than the actual assessment value. Perform the necessary update. ASSESSUPDATE

You are required to submit: (a) a print out of the query results (b) a printout of the SQL statements/views (c) a printout of the design view

TASK C

Create and print the following reports:

h. Group by Occasion then by Location, the names of owners that had their car stolen. Also include the type of vehicle stolen and the colour. Give the report the heading “Grand Theft Auto” in line one and “First Quarter 2014” in the line below. Ensure that the Owner’s names are sorted in alphabetical order.

i. Create a report using the fields Insurance Company, Date of Theft, Owner’s name, category and value. Group the report by Insurance Company then by Date. Include relevant summary data and provide total insured cost. Give this report the heading “Grand Theft Grouped by Insurance Company”. Sort the report by insurance company.

Word Processing
After analysis of the data using a DBMS and a Spreadsheet, the team is now ready to present a report based on the statistics for the quarter.

You are required to design and print.

Task A ONE REPORT

Write a report based on the queries, charts, reports and insurance statistics derived from the database and spread sheet. Your report should have the following format: 1. Cover Page 2. Table of contents 3. Introduction stating the purpose of the investigation. (Be realistic in your introduction) 4. Tools used to obtain your statistics. (state programs and version) 5. 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 or report that you created in the database section of your SBA. An example is as follows

The investigation revealed that: 1. 65% of vehicles that were stolen were government owned
<Produce proof of statistics e.g. charts or table.> 2. 20% of victims were females.
<Produce proof of statistics e.g. charts or table.> 3. ...

The investigation revealed that: 4. 65% of vehicles that were stolen were government owned
<Produce proof of statistics e.g. charts or table.> 5. 20% of victims were females.
<Produce proof of statistics e.g. charts or table.> 6. ...

6. Conclusion and recommendations. (Be realistic) 7. Appendix. Any additional information relevant to the SBA.
NOTE… The document should be well formatted by using the following features: * Bold\italics * 1.5 line spacing * varying font & font size * good use of bullets and numbering * Alignment

Task B ONE PAMPHLET
As a part of the “Operation Bounce Back” you are required to design a pamphlet that will be made available to the public. The aim of the pamphlet is to educate the public in the fight against motor vehicle theft. The Pamphlet should be entitled “PROTECT YOUR VEHICLE”
The pamphlet should include but not limited to the following information: * Some statistics (taken from your database and\or Spreadsheet) * Tips on how to avoid auto theft * Information on free and discounted antitheft devices that will be made available. (e.g. discounted immobilizers of free steering lock)
You are required to consider the following: (i) The Pamphlet should be tri-fold on letter size paper (ii) Include appropriate graphics that will enhance the presentation (iii) The document should be well formatted by using the following features: * Bold\italics * 1.5 line spacing * varying font & font size * good use of bullets and numbering * Alignment * Borders and Shading

Task C ONE COVER LETTER i Create a letter head for your company

ii Write a cover letter stating that the report and a brochure are enclosed and that a review meeting will be held on April 1, 2015. Include a table for an agenda for the review meeting and any other relevant details in the cover letter. Be sure to use your name as the team leader for the analysts.

iii Using the database table STAKEHOLDER, as the data source, use the necessary features to perform a merge, with the letter as the primary document.

Print the First and last letters after the merge.

Problem Solving and Programming

1. Insurance companies are interested in software that will better assist their customers based on the study carried out by the NMTRO. A trial run of the software should be implemented where up to 30 customers may be attended to per day.

You are required to write a pseudocode or draw a flowchart that will accept customer’s information (First Name, Last Name, Gender, Model of car, Category of car, Insurance Company, TOC, whether or not an Antitheft device has been installed, Premium and value of car). Calculate the compensation payment for each customer based on calculation criteria given in Task B of the spreadsheet section.

At the end you are to: a. Calculate the number of vehicles insured Fully Comprehensive b. Find the highest vehicle cost c. Calculate the percentage of owners who installed antitheft mechanism d. Prompt the user for a customer’s name and search for and display the customer’s information including the compensation payment to be made.

2. Design and implement a trace table that accepts data for 6 members (same data as the spreadsheet). The table should track the Value of car, TOC, Premium, antitheft installed and insurance payment. The total premium of all customers should be calculated.

Program Implementation

3. Using the programming language Pascal, write a program code to implement the algorithm in one (1) above, use the features available to you example: a. The use of font and background color. b. The use of arrays and control structures (eg. If, For, While and Repeat Until) where necessary. c. Your program should have the appropriate data types and variable naming. d. The program must be user friendly.

You are required to submit: a) the flowchart or pseudocode b) the trace table c) the program code (students may use notepad or the Pascal compiler program) * the pascal code should be personalized to each individual student with the following i. student full name ii. pool iii. subject iv. reason for the program
(The above must be written in the code either as part of the program or as comments) d) screen shots of the main screen for your actual program running