Document Sample
					                                  INFORMATION TECHNOLOGY
                                School Based Assessment (2009)

You are a member of the management team of a Auto Company that has recently opened. As part of
an advertisement campaign, the team has decided to organize a Car Show. The company has at its
disposal a Word Processing package, a Spreadsheet package and a Database package and
Programming Software.

You are required to design and implement a computer-based solution to perform the tasks listed

   1. Generate a flyer advertising the Car Show and a letter to be sent to various individuals and
      companies specially inviting them to participate in the Car Show.

   2. Generate a budget showing the revenue and expenses expected to be incurred which should
      be able to be used to perform various kinds of analysis to review the effect that the Car Show
      had on the company’s finances.

   3. Maintain all records of cars and their owners.

   4. Produce a Post-analysis report of the Car Show budget which includes a comparison of the
      pre- and post funding estimates.

   5. Develop a programming solution that produces the number of cars in each category and the
      total rental cost incurred. This should be tested with data from your spreadsheet using a trace

Please note that any assumptions made must be stated and submitted with your printouts.

       1   CPDD/SMS/2009
                                WORD PROCESSING (use A B or C)

A. The management of a recently opened Auto Company plans to launch a flyer to advertise their
   upcoming Car Show to be held on 3rd August, 2009 at Gulf City, La Romain. Their main objective
   in hosting this Car Show is that it will improve sales and promote the company. The flyer should
   be must fit on a letter size sheet of paper. Use a right column format to add an additional column
   to the page to give information on the Pageant and any contact information. The following
   information must be displayed:
    Name of the Company
    Date/Time of the Show
    Different models of the cars that will be displayed
    Venue of Car Show
    Models of cars to be displayed (Eg. Nissan, Honda, Chevrolet, BMW, Mitsubishi, Mercedes
       Benz, Peugeot, Mazda, Subaru etc.)
    Other Attractions
    Contact information for people interested in obtaining further information

B. Some companies and individuals will be given a special invitation to take part in the Car Show.
   Information on these will be kept in one of the database files. Guidelines for the letter as follows:

   a) Create a letterhead for the company. The letterhead should contain the company’s name,
     address, telephone number(s), fax number and e-mail address. Italicise/bold the contact
     information in the letterhead.

   b) Format the letter as follows:
      i)   Allow 1.5” for top and bottom margins. 1” for left and right margins.
      ii)  Indent the first line of each paragraph by 0.5”
      iii) Insert a blank line between paragraphs.

   c)    Prepare the letter to be sent to the at least ten different companies and individuals (the
        information to be taken from the OWNER file in the database. Use mail merge codes where
        appropriate. The heading should be left justified and the body of the letter fully justified. The
        letter should be in the format required (see next page):

        2   CPDD/SMS/2009
     <Title> <First Name> <Last Name>
     <Address Line 1>
     <Address Line 2>

     Dear <Title> <Last Name>:

     Body of letter

     The Body should include the Car Model and Rental Fee tables as information for the client.

     Yours respectfully,

     your name

     d) Use different font styles and sizes wherever is appropriate in your design of the letter.

     e) Inform the recipient of the letter of the date and time of the Car Show.

     f) Ensure that your documents have been spell-checked and is suitable for sending to the
        various individuals and companies.

     g) Print the main document, the secondary document, the first and the last merged letters.

C.   After the Car Show, you need to send a thank you letter to all the organizations and
     individuals participated in the Show.

      3   CPDD/SMS/2009

The spreadsheet package will be used to create a model which will allow the accountant of the Auto
Company to create, manipulate and monitor adherence to budgets.
There are different categories of cars and each category attracts a different rental fee depending on
the category that the car has been assigned. The category that the different models of cars have
been assigned are given in the table below.

                          Car Model                 Category
                   Chevrolet                           C
                   Honda Civic                         B
                   Nissan Primera                      B
                   Nissan B-14                         C
                   Subaru                              B
                   Mitsubishi Lancer                   C
                   Mazda                               B
                   Mercedes                            A
                   Peugeot                             B
                   BMW                                 A

The table below shows the rental fee paid for each category of car.

                           Category                Rental Fee
                              A                    $1000.00
                              B                     $600.00
                              C                     $400.00

Prepare a spreadsheet to show the type of car, the category, the rental fee, the no. of cars rented of
each type and the rental fee paid for all vehicles of each type. Include totals for the number of cars
and the total rental fee. The relevant information is transferred to the spreadsheet showing the
income and expenditure.

       4   CPDD/SMS/2009
Estimated Expenses for this Car Show are given in the table below.

                           Expenses                   Amount
                   Rental of Place           $5000.00
                   DJ                        $800.00
                   Allowance for Damages     $1000.00
                   Printing of Tickets       $1.50 per ticket
                   Refreshments              $1000.00
                   Police/Security           $1000.00
                   Rental of Cars            Dependent on category
                   Door Prize                $500.00
                   Other Expenses            $1500.00

Traditionally the company hosts one Car Show annually to advertise the Company. Expected income
from the event is calculated using the estimated figure from the sale of tickets and from the sale of
refreshments. There is also a Pageant Show for which tickets are on Sale at the door that patrons
can attend during the Car Show. Assume that the tickets printed for the Car Show are 100 times the
total number of cars rented and that the tickets printed for the Pageant Show are half of the total
printed for the Car Show. All tickets cost the same amount to print. The price of a ticket for the Car
Show is $20.00 and the Pageant is $10.00. If someone buys a ticket for both, then a discounted
price of $25.00 is charged. The total income estimated is derived from the following:-
    -      75% of the tickets for the Car Show is sold.
    -      60% of the tickets for the Pageant is sold
    -      25% of the number of people who bought tickets for the Car Show bought tickets for the
    -      Sale of refreshments was three times the cost of it.

You are required to:

1. Create one or more spreadsheets which will enable the accounting personnel of the Auto
     Company to easily create a budget showing the expenses and estimated income. Your
     spreadsheet should use appropriate labels and the appropriate information should be easy to

   a) Incorporate the following facilities in your model:
         i)     The ability to insert additional income and expenses as required, without the need
                for re-calculation.
         ii)    The ability to display the required statistics.

       5   CPDD/SMS/2009
   b) Include a section in your worksheet to show a summary of the estimated, actual and
      difference in the two of each source of income. After the Car Show, the following was
   -      the total of ticket sales for the Car Show was 10% more than what was estimated.
   -      5% less of the estimated number of people bought tickets for both the Car Show and the
   -      the sum of $550.00 was not included in the Sale of Refreshments
   -      the total sum of money collected for the Pageant was $150.00 less than the estimated
   c) ) Include the profit (income – expenses) at the bottom of your spreadsheet.
   Save your work as Car01.

2. a) Test your model by
          i) adding a minimum another source of income
          ii) adding an expense item
          iii) sort the cars according to category in ascending order
          iv) changing the rate used to decide how many tickets to print.
   Save your worksheet as Car02.

3. a) Using an appropriate format, create a graph (or chart) showing the percentage contribution
      for each item (tickets, refreshments etc.) to the actual income of the Auto Company. Your
      graph should be appropriately labeled. Name this graph (or chart) Income.

   b) Using an appropriate format, create a graph (or chart) comparing the estimated profit with the
      actual profit from each item of revenue. Your graph (or chart) should be appropriately
      labeled. Name this graph (or chart) Profit.

   Print the spreadsheet.

       6   CPDD/SMS/2009
                                      DATABASE MANAGEMENT

Records are kept for all cars and their owner. There are seven types of cars that are rented by the
company for this Car Show. They are Honda, Nissan, Mercedes, BMW, Mitsubishi, Mazda, Peugeot.
The rent paid for the use of each type of car is dependent on its type. All rental fees and an
indication of whether or not the rental fee has been paid are stored in the rent table. The company
maintains the following information for each car owner: Title, Name, Address, Telephone number,
Gender, e-mail, no of cars and rent paid. Each owner is also assigned a unique identification number
which is used to access his/her data. For each car, the company maintains a record of the license
number of each car including car code, chassis no, color, insurance company as well as if the car is
automatic and fully powered.

A table called “RENT” is created to store the ten (10) entries below. Add a field to show the rental
fee charged by the owner with information that is consistent with the spreadsheet.

                           Car Code         Car Model
                              HC        Honda Civic
                             NISP       Nissan Primera
                            NISSB       Nissan B-14
                            MITSL       Mitsubishi Lancer
                             CHEV       Chevrolet
                              SUB       Subaru
                             MAZ        Mazda
                             PEUG       Peugeot
                             MER        Mercedes
                             BMW        BMW

You will need to:
   a) Build a database to satisfy the manager’s requirements. The tables should be properly
        indexed. Create three tables RENT, OWNERS and CARS. Use the database structure for the
        OWNER and CARS that are given (see next page)

       7   CPDD/SMS/2009

           Field Name       Field Type      Description
           OwnerID          Autonumber      An automatic number assigned when a car
                                            owner allows his car to be rented.
           LastName         Text            The surname of the owner of the car
           FirstName        Text            The first name of the owner of the car
           Title            Text            The title of the person (eg. Mr. Mrs. Miss etc.)
           E_Mail           Text            The E_mail of the person
           Gender           LookUp wizard   The gender of the owner, Male/Female.
           Address          Text            The address of the owner
           PhoneNo          Text            The telephone contact of the owner
           NoofCars         Number          The number of cars rented to the company
           RentalPaid       Logical         A yes/no field to indicate whether the rental
                                            fee has been paid

The database structure for the table CARS is given below.

           CarID            Text            The license plate number of the car
           Carcode          Text            The code assigned to the vehicle
           OwnerID          Number          The unique number assigned to a car owner
           Insurance Co     Text            The insurance company the car is insured with
           ChassisNo        Text            The chassis number of the car
           Automatic        Logical         A Yes/No field to indicate whether the car has
                                            an automatic transmission or not
           Fully            Logical         A Yes/No field to indicate whether the car has
           Powered                          fully powered or not
           Colour           Text            The colour of the car

   b) Populate your database files with information of at least ten (10) car owners with at least
      three (3) of them renting more than one vehicle.

   c) Add at least fifteen (15) records to the table CAR.
   d) Delete the field “Fully powered from the cars table.

   e) Test your data by running the following queries

       i) List the Car Id, Car code, ownerid, and chassis no of all vehicles insured for by a named insurance
      company. Save this query as insurance.

       8    CPDD/SMS/2009
   ii)     List the name, phone numbers of all car owners who the company has not been paid for
          rent of their car(s) and who rented more than two (2) vehicles. Name this query OWING.
          Sort this information in alphabetical order by last name and then by first name.
   iii)   List the number of vehicles rented from each car owner. Name this query TOTAL
   iv)       List the total rental fees that the company has to pay to the various car owners. Name
             this query OUTSTANDINGFEES.
   v)        Display the Car Id, Car code and color of all cars owned by female drivers that are not
             automatic cars. Name this query Female manual.
   vi)    Update the rental fees by decreasing it by 10% to encourage more car owners to rent
          their car(s).

f) Generate a report to show the total rental fee paid for each model of car. The report should
   include Car Model, Last Name, First Name and Telephone number of the car owners. In
   addition, it should be grouped by Car Model and sorted in alphabetical order by last name, and
   then by first name. Name this report CAR RENTALS.

    9     CPDD/SMS/2009

     1. Develop an algorithm (preferably written in pseudocode) that determines the total amount of
        Rental Fee paid by thel Auto Company. The algorithm should accept the Model and category
        of the ten (10) types of vehicles listed in the Car Model Table from the Spreadsheet Section
        and store it in two arrays, CARMODEL and CATEGORY respectively. The model, Model of a
        number of cars is then entered for approval. It is not known beforehand how many cars will
        be rented but to indicate the end of the data, the word “End” is entered.

     i)    Perform a validation check to ensure that the model of the car inputted is one listed in the
          table. If it is not found, then an error message stating, “Model not found” should be printed.

     ii) Determine and print the number of cars inputted and the number of approved ones. A car is
         considered to be approved if its model is found in the table.

     iii) Use the Rental Fee Table below taken from the spreadsheet section to determine the rental
          fee for each model of vehicle and to calculate and print the total rental cost.

                                Category         Rental Fee
                                   A             $1000.00
                                   B              $600.00
                                   C              $400.00

2.   Using data consistent with your spreadsheet, design and execute a trace table that accepts the
     car model, MODEL, and traces the Category of the car and Rental Fee. The table should have at
     least ten iterations and should end when MODEL = “None”. The number of cars together with
     the number of valid cars should be counted. In addition, the total rental fee should be


3.   Using the programming language, PASCAL, write source code for the algorithm developed in (1).

          10   CPDD/SMS/2009
INPUT                                  PROCESSING                   OUTPUT

Model                  1. Read/get ten (10) car models and          NoofCars - No of Cars
Cat                       category of vehicles                                 requesting approval
                       2. Store in arrays CARMODEL and              Noapproved - No. of Cars
                          CATEGORY respectively                                    approved
                       3. Read the models of a number of            TotalRental – Total Rental Fee
                          vehicles                                                 of Approved Cars
                       4. Validate data (check to see if it
                          exists in array)
                       5. Calculate the number of vehicles
                          requesting approval
                       6. Calculate the number of approved
                       7. Find the rental fee for the individual
                       8. Calculate the total rental fee
                       9. Print the number of vehicles
                          requesting registration, the number of
                          vehicles approved and the total rental
                          fee paid by the company

                                           Algorithmic structure

Header      : CarRental {Algorithm’s name or title}
Declaration : {.e. A statement of purpose} This algorithm takes a table inputted with information
    on Cars and the Rental Category assigned to them and uses it to determine the rental fee of a
    car model inputted. Before determining the rental fee from the table, it validates the data
    entered. The algorithm also keeps a count of the total number of cars entered and those that are
    approved. For those that are approved, the algorithm calculates and displays the total rental fee.

    {declaration of variables.}

                       Variable Type                          Variable Description
         X                   Integer      Counter variable used in a For loop
     Noofcars                Integer      Stores the number of cars inputted
    Noapproved               Integer      Stores the number of approved cars
      Rental                   Real       Used to store the rental of the vehicle
    TotalRental                Real       Stores the total rental fees of the approved cars
        Cat                   Char        Stores the Category entered by the user
        11   CPDD/SMS/2009
        Model                String       Stores the Car Model entered by the user
       Carmodel         Array (1 to 10)   Stores the ten (10) car models listed in the spreadsheet section
                           of String
       Category         Array (1 to 10)   Stores the respective categories of the ten (10) car models
                            of Char

  {initialisation of variables.}
       Set Noofcars to 0
       Set Noapproved to 0
       Set Approved to 0
       Set TotalRental to 0
       For X = 1 to 10 do
           CarModel (X) = ‘ ‘
       For X = 1 to 10 do
          Category (X) = ‘ ‘

Body            : Sequence of steps

  Main Program
           Print (‘Please enter the model of a car’)
           Input Model
           While (Model <> ‘End’) do
                    Approved = 0
                    Print (‘ Please enter the model of another car’)
                    Input Model
    End                                                Terminator : END {An end statement}

  Module Welcome
          Print ( ‘Author : Sharon Moonan-Seepaul’ )
          Print ( ‘Date : May 31, 2009)

       12    CPDD/SMS/2009
          Print ( ‘This program takes a table inputted with information on Cars and the Rental
                   Category assigned to them and uses it to determine the rental fee of a car
                   model inputted. Before determining the rental fee from the table, it validates
                   the data entered. The algorithm also keeps a count of the total number of cars
                   entered and those that are approved. For those that are approved, the
                   algorithm calculates and displays the total rental fee’)

Module InitialiseVariables
        Set Noofcars to 0
        Set Noapproved to 0
        Set Approved to 0
        Set TotalRental to 0
        For X = 1 to 10 do
                 CarModel (X) = ‘ ‘
                  Category (X) = ‘ ‘

Module StoreCarData
        For X = 1 to 10 do
            Print (‘Please enter the model of a car’)
            Read (Model)
            Set CarModel (X) to Model
            Print (‘Please enter the category of the car’)
            Read ( Cat)
            Set Category (X) to Cat

Module DetermineApproval
        Set NoofCars to NoofCars + 1
        For X = 1 to 10 do
           If CarModel (X) = Model then
               NoApproved = NoApproved + 1
               Approved = 1
               Cat = Category (X)
               X = 10
  13   CPDD/SMS/2009
                  If (Approved = 0) then
                      Print (‘Model not found’)

   Module DetermineRental
            If (Cat = ‘A’ ) then
            Rental = 1000.00
               If (Cat = ‘B’ ) then
                  Rental = 600.00
                    If (Cat = ‘C’ ) then
                       Rental = 400.00
                         Print ( ‘Invalid Category’ )

   Module CalculateRental
           If (Approved = 1) then
              Set TotalRental to TotalRental + Rental

Module PrintInfo
     Print (' The number of cars entered is', NoofCars);
     Print ('The number of cars approved is', NoApproved);
     Print (' The total rental fee is', TotalRental);

      14    CPDD/SMS/2009
(See Page 29 CXC 30/G/SYLL 08)

Cover   Sheet
       Centre Number
       Student Number
       Territory

Table of Contents (not necessarily in this order)

    Word Processing
     - Document 1 - Mail Merge
         the primary or main document
         the data source or secondary document
         samples of the merged document
     - Document 2
         Eg. Fax, Report, Simple Brochures, Flyers, Newspaper Articles

    Spreadsheet
     - Worksheet (s)Three major tasks of no more than three requirements
         Worksheet (s) showing formulae used
     - Chart 1
     - Chart 2

    Database
     - Table 1
     - Table 2
     - Table 3
     - Relationships and tables design
     - Query 1
     - Query 2
     - Query 3 (with calculation)
     - Query 4 (with calculation)
     - Report (with grouping and sorting involving TWO or THREE tables)

    Problem-Solving and Program Implementation Component
     - Introduction (Optional)
     - Problem Definition
          Variable Listing
          Algorithm Listing
          Flowchart
          Trace Table
     - Program Listing (after compilation)
     - Test Data/ Text File
     - Sample Run

        15   CPDD/SMS/2009

Shared By: