Infotech100.weebly.com

  • Docx File 30.97KByte



CXC CSEC INFORMATION TECHNOLOGYSCHOOL BASED ASSESSMENT(General Proficiency)Paper 03 – School Based Assessment?PRACTICAL ASSIGNMENTSWORD-PROCESSINGSPREADSHEETDATABASE MANAGEMENTPROBLEM-SOLVING AND PROGRAM DESIGN&IMPLEMENTATION??Project Description?The?Ticket Collection and Payments Department of the Revenue Collections Agency of Jamaica (RCAJ) has a manual database which is used to store information on motorists; track/ monitor the issuing??of tickets to unlawful motorists and also the collection of monies for such traffic offences. This type of system results in delinquent motorists being often overlooked causing a backlog of unpaid tickets. No stringent measure is in place to record traffic violations and update the ticketing system in a timely manner; therefore, tickets are unpaid and monies owed to the Government are being lost.Due to these inefficiencies??you are hired by the Government as an Information Technology Specialist. The Government wants relevant details of tickets issued to be recorded and monitored electronically.After a ticket is issued, the motorist being charged is given a 21 day period (including weekends) within which he or she is required to pay the amount documented on the ticket. The system is to keep track of the days in order to print a summon letter for the delinquent motorist, inviting him or her to a hearing in court. Four copies of the letter are to be created for the following purposes/ departments:The Driver’s CopyThe Control Center CopyThe Revenue Collections Office CopyThe Court’s Office Copy?A pamphlet is to be made available to motorists who visit the Revenue Collections Agency of Jamaica (RCAJ) office to inform them of the different traffic laws, offences and penalties. An explanation of the Point System is to be included in the pamphlet.?Candidates are required to utilize Word Processing, Database, Spreadsheet and Programming software packages to efficiently carry out the required activities.Word Processing Design a letterhead for your documents which should be placed in the header. Using suitable feature of your word processing application create a document that will be mailed to the police department who will served on the person to attend the next court date. Ensure that you incorporate data from your database.The document should be justified and the left margin set at 1.5”Design a brochure that will be used to promote good driving habits amongst drivers and enlighten them about offences and points allocations. Ensure to include payment outlets and graphics that depict either good or bad driving habits. PRINT a copy of the Letterhead, Data Source, Main/Primary Document, the 1st and 3rd Letters, and the Brochure.SpreadsheetThe Ticket Collection and Payments Department of the Revenue Collections Agency of Jamaica (RCAJ) has mandated that you design a spreadsheet that will accept relevant data on offenders, tickets and vehicles. Each offender is given points on a scale of 1-14. In case of an offence the cost per point is $1050. NB. The list of offenders should range between 10 and 15.Each offender is given a 21 day period to pay a ticket; if fees are late then their payment attracts an interest of 20%. And after 45 days they are summoned to attend court.You are required to create three tables: OFFENDERS, TICKETS AND OFFENCES.The OFFENDERS table include: First Name, Last Name, Street, Town, Parish, Gender, Payment Deadline. The TICKET table include: Drivers License Number, Last Name, First Name, Issue Date and Payment Deadline.TASK AThe OFFENCES table include: Type of offence, cost of ticket and points Value. Offences possible are listed below:SeatbeltReckless drivingObstructionExpired fitness/registrationDefective vehicleRacingSuspended licencesFleeing accident sceneIllegal parkingThe cost of tickets are calculated as: points* cost per points(cost per point is $1050)In an appropriate row in the offenders table count the number of offenders Ensure that your worksheet is efficient. Use necessary formatting features to allow clarity and professionalism(example- bold, decimal place, % $, and comma)Using appropriate formula, calculate the payment deadline. The deadline is 21 days after the ticket is issuedSave as Traffic01TASK BModify the spreadsheet as follows:In the OFFENDERS table, insert columns to show offence committed, ticket cost, and points added. Then use appropriate formula/function to insert ticket cost and points addedA new traffic offence was added(DISOBEYING A NO U TURN SIGN)with a point value of 7. In an appropriate part of the spreadsheet insert the relevant dataSort the ticket data by last name and issue dateSave as Traffic02TASK CModify the spreadsheet as follows:In the offenders table Insert a column called “STATUS” to show that atleast 65% of offenders have paid their ticketsIn the offenders table insert a column that indicates whether or not an offender goes to court. This column should state “COURT” or “NO COURT”. An offender attend courts if he has unpaid ticket.Insert a column to display court date for offender who will be attending court. Court date is calculated as: If Court is YES then add 45 to payment date otherwise they are exemptedIf an offender has an unpaid ticket then a 20% interest is added to cost of ticket; calculate interest and also total paid.In an appropriate part of your spreadsheet filter all offenders with points greater than 10 and total to be paid greater than $8000Generate a Pie chart to compare the total money to be collected by the tax office and the total money owing from unpaid tickets. Insert appropriate titles and data label. Generate a chart to show ALL offenders whose License should be suspended (offenders with points greater than 10 and total to be paid greater than $8000)Insert appropriate titles and data label.Save as Traffic03PRINT a copy of all worksheets in Traffic01 and Traffic02 and the offenders’ worksheet in Traffic03, the Charts and the formulae sheets.Database ManagementTask ATickets issued are to be monitored in order to efficiently and effectively target delinquent motorists who owe the Government. Create a table called OWNERTABLE containing the fields: TRN/ Driver’s License Number, First Name, Last Name, Address (Street, Town, and Parish) Driver’s License Expiry Date, Gender and License plate number. Use appropriate data types and field sizes for each field Create a table called TICKET TABLE containing the fields: Ticket Number, License plate, Issue Date, Payment Deadline, Offence ID, Status and Amount Due. Use appropriate data types and field sizes for each field.Create a table called OFFENCE TABLE containing the fields: Offence ID, Offence, Cost/ Charge, and Point Value. Use appropriate data types and field sizes for each field.Task BYour database should respond to the following queries:Sort the owners table by Parish and save it as Sorted Owners. Make copies of all other tables.List the names of all offenders sorted by last name. Display the TRN/Driver’s License Number, First Name, Last Name, Driver’s License Expiry Date and Gender. Save the query as Qry_Motorist.Find the total number of offences per parish. Save the query as Qry_Parish_Count. Increase the charges of the offenders who are requested to attend court by 25%. Save the query as Qry_New_Charge.List all the offenders who have seatbelt or racing offences. Save the query as Qry_Sbelt_racing.List all offenders who have unpaid tickets. Display First name, last name, addresses, issued date, liscense plate, payment deadline, points, offence and Amount due. Save the query as Summons_List(i) Find total for all paid offenders. Save the query as Qry_SumofPaid. (ii) Find total for all unpaid offenders. Save the query as Qry_SumofUnpaid.Delete records of persons who have paid. Save the query as Qry_DeletePaid.Create a report using the fields: TRN/Driver’s License Number, First Name, Last Name, Parish, Offence ID, Cost /Charge. Group by Parish. Sort in ascending order by Last Name. For each grouping level the report must indicate the total for Cost/Charge.Add the following title in two lines on the report“Revenue Collections Agency of Jamaica”“Parish Offenders for (The year of your records)”. Write your candidate number in the top right hand corner of the Page Header. PRINT a copy of the Table Structures, the Tables, Queries, the Queries Structures and Report.Problem Solving and Program ImplementationThe Ministry of National Security has instructed that all offences with a point greater than 8 should be deemed too serious an offence for an offender to be simply slapped on the wrist with a ticket. It was mandated that they should be immediately arrested and charged a fine of fifty (50) times the amount on the ticket.As the programmer you are required to:Create a pseudocode algorithm that will accept the name of the motorists, the points for the ticket and its cost. Determine if the points awarded is less than 8 then the penalty remains the same. If the points are greater, indicate that they should be immediately arrested and a fine of fifty (50) times the cost of the ticket be charged. Display the names of the motorists, and the amount they are to pay.Design and execute a trace table that accepts data from the Sorted Owners table in the database.(Select the parish with the highest number of offenders.) The input fields are the name of the motorists, the points for the ticket and its cost. The table should trace whether motorists will be immediately arrested and charged the new fine or they should pay the amount originally recorded on the ticket. Using the programming language PASCAL, write the program code for the pseudocode solution you produced in question (1).PRINT a copy of the Pseudocode, Test data, Trace Table and Pascal code. ................
................

Online Preview   Download