could you assist me please?

CSEC INFORMATION TECHNOLOGY School Based Assessment ( 202 2 ) You are a member of the management team of Superior Cars 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 word - processing, web page design, spreadsheet, database management as well as a programming software. You are required to design and implement a computer - based solution to per for m the tasks listed below: 1. Generate a le tter to be sent to various individuals and companies specially inviting them to participate in the Car Show . Create a fillable form for individuals and companies to register to participate in the car show . 2. Design a website to advertise the Car Show. 3. Generate a budget showing the rental fees expected to be incurred by the company. This is intended to be used to perform various kinds of analysis to review th e effect that the Car Show had on the company’s finances. 4. Maintain all records of cars and their owners. Perform queries to obtain relevant information and generate a report. 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 usin g a trace table. Please note that any assumptions made must be stated and submit ted with assignments . WORD PROCESSING Fillable Form using Microsoft Word 1. Open a new document and save it as ‘ Car Show Fillable Form’. 2. Insert a Page Border around the entire page (optional) 3. S et the layout for page size as Legal (8 ½ * 14) 3. C hoose double spacing (2.0) as the line spacing option. 4. Inse rt an image associated with cars in the header at the left hand side. This will be used as the logo for your company and the same logo should be used in the letterhead for the mail merge and in the Home page in the web page component . 5. Insert in the header “Superior Cars” . Use different formatting features for them – bold, underline, font size, font colour or font style 6. Type ‘Car Show Information Form’ at the top of the form. Change the font size, font style, font colour and center and bold this text. 7. Insert a footer with an email address and any other contact information you would like to inclu de. Use fictional information for this . Use Controls in the Developer Menu to add control elements to each of these labels: - Name of Individual/Company (Rich Text Content Control) - Phone Number (Rich Text Content Control) - Email Address (Rich Text Content C ontrol) - Model of Car (Drop - Down List Content Control with the three (3 ) car models - Owner of Car (Check Box Content Control ) - Date of Registration (Date Picker Content Control) Fillable Form using Google Forms 1. Sign up for a gmail account if you do not already have one. You need a gmail address to use Google Forms. 2. Open your web browser. In your search engine type “Google Forms” OR in the URL Address bar type “forms.google.com” and sign in if necessary with your gmail account. 3. Open a new blank form. Name the form ‘Car Show Fillable Form’. 4. Insert an image associated with cars in the form header. This will be used as the logo for your company and the same logo should be used in the letterhead for the mail merge and in the Home page in the web page component. 5. Insert the form title “Superior Cars” 6. Customize your form by changing the form colours from background and themes. 7. Put as the form description ‘Car Show Information Form’. 8. Use Form Controls to add control elements to each of these labels: - Name of Individual/Company - Short A nswer - Phone Number - Short Answer - Email Address - Short Answer - Model of Car - Drop - down (for the three (3) car models). - Owner of Car - Multiple Choice (to indicate if owner) - Date of Registration - Date 9. Add a custom message to be displayed when the user submits the form. An example of this could be “Thank You. Response Submitted” Mail Merge Some individuals will be sent a special invitation to take part in the Car Show. Information on t hese individuals will be kept in one of the database files. Guidelines for the letter as follows: a) Create a letterhead for the company. Th e letterhead should contain the company’s logo (use the same logo as in the fillable form). It should also contain company’s name, address, telephone number(s), fax number and e - mail address. - Bold and increase the font size to an appropriate size for Supe rior Cars. - Italicise the email address in the c ontact information in the letterhead. - Use different font styles and sizes wherever is appropriate in your design of the letterhead b) Format the letter as follows: i) Insert the date in the format (e g. October 20 th , 2020) ii) Set margins to “Narrow” (0.5” left, right, top and bottom) ii) Set to 1.5 line spacing option for in the text in the body of the letter iii) Use two (2) columns for the body of the letter (optional) . iv) Include a table in the second column showing the Cars and the Rental . Bold the headings in your table . Use Design in the Table Tools and choose a style for the table. v) U se the image on the next page as a sample for the letter. c) Prepare the letter to be sent to the at least ten (10) individuals (the information is to be taken from the OWNER S file in the database . Use mail merge codes where appropriate. The heading should be left justified and the body of the letter fully justif ied. d) E nsure that your documents have been spell - checked and is suitable for sending to the v arious individuals . e) Save the primary doc ument as PrimaryDocument , the data source as DataSource (indicate that it is the OWNERS Table from the database) , t he mer ged letters as Mergeddocument . WEB PAGE Design a website to advertise the Car Show. 1. Go to the website www.wix.com . Create an account if you not already have one. 2. Search for a “car ” related template to use or use a blank template and select an appropriate layout design . 3. Keep two (2) web pages , one for “Home” and one for “Contact”. Delete all others. 4. On the Home page - E dit the main title to be the business name ’ Superior Cars ’ . - I nsert an appropriate logo/ picture of your choice at the top of the “Home” webpage ( preferably it should be the same logo/picture used in your letterhead for the mail merge ) . - Provide a brief description on Superior Cars and information on the Car Show such as the date and time of the Car Show, the different models of the cars that will be displa yed (insert pictures of some), the venue of Car Show, other attractions (pictures to represent these can be inserte d), link to web page with contact information for people interested in obtaining further information. - Use different font colors /sizes/styles in at least three (3) positions on this page. - Provide a link to the fillable form created so that interested person s can register online (optional). 5. On the Contact page, i nsert the following the contact information which should contain - The name of the company - An address for the company - A phone contact for the company - The text “ Email us at : ” and a link to an email addr ess ( email does not have to be valid ) . 6. Insert a link on the “Contact Page” to go to Superior Parts website . This is a company that Superior Cars own s that sells car parts . 7. Remove any other irrelevant information on both pages or replace it with informat ion that relates to the Car Show . SPREADSHEET 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 three (3) different models of Toyota cars and each model attract s a different rental fee . The table below shows the rental fee paid for each car model . Model Rental Fee Yaris $1 , 000.00 Corolla $1 , 5 00.00 C amry $2 , 0 00.00 a) Enter the information from the table in Sheet 1 beginning from A1 . The Rental Fe e should be formatted with Currency and Comma format and to two (2) decimal places. (eg. $1,000.00) . Create two range names Model and RentalFee to be utilised later. Rename this sheet CARDATA . b) Add a new sheet to the spreadsheet . Beginning in A2 , enter in separate cell a headings for the owner ’s name (First name and Last name) , the car model of car to b e rented , the rental fee per car , the number of cars rente d and the rental fee being paid to the owner. Assume that every owner rents out only one model of vehicle but can rent out any number of that model of vehicle. i) Increase the font size of the headings to 14 . ii) Bold and center all of the headings. Wrap the cells that contain headings. iii) Enter ten (10) random first names (First Name) and last names (Last Name ) for owners . iv) Enter the number of cars being provided for rental by each owner . c) Generate the rental fee for the model of vehicle by using an IF function and the informat ion from the table in the CARDATA sheet . Format the cells for using Currency and Comma format and to two (2) decimal places. (eg. $1,000.00) . d) Calculate the total rental fee to be paid by multiplying the number of cars by the rental fee . Format this figure using the Currency and Comma format and to two (2) decimal places. (eg. $5,000.00) . e) In cell D1 in the CARDATA sheet, type the label “Incentive Rate” and in cell B1, type the value for 10%. f) If more than three (3) cars are being rented out, the owner is given a ten percent (10%) incentive on his total. Add a new column for incentives and using the IF function and the value for the incentive from the CARDATA sheet , calculate the incentives owners. The formula for the first owner must reference the value in cell B1 when calculating the owner’s incentive using absolute addressing, then the formula can be replicat ed to the other owners. Rename this worksheet, RentalInfo. g) Add a new column for the new rental fee and calculate the new rental (Rental fee + Incentive) for each owner. h) Highlight the cell range with the values for the New Total Rental Fee and name this range NewTotalRentalFee. i) Use the Max function with the named range “New Total Rental Fee” to d etermine the largest rental fee to be paid to an owner. This must be displaye d on the worksheet. j) Use the Min function with the named range “New Total Rental Fee” to determine the smallest rental fee to be paid to an owner. This must be displayed on the worksheet. k) Rename this sheet, Rentalinfo. l) Copy and paste the information from the sheet Rentalinfo into another sheet in the same range . Ensure that the formatting is maintained after copying the information from one sheet into another. Rename the sheet Sortedinfo. m) Sort the information in this Sortedinfo s heet by Car Model in asc ending order. n) In A2 , t ype Rentalinfo sorted by Model of Vehicle . Bold the text. Merge and center this text in A 2 across to I2 . o) Copy the data from the SortedInfo worksheet into a new worksheet in the same cell range. Use a simple f ilter to obtain all the owners wh o are renting out more than three (3 ) vehicles. In A1, replace the heading with “Filtered data showing owners who are renting ou t more than 3 vehicles”. Merge and ce nter the heading. Rename this sheet Simple Filter. p) Copy the data from the SortedInfo worksheet into a new worksheet in the same cell range Use an advanced f ilter to extract all the owners wh o are renting out more than three (3 ) Toyota Camry car s . In A1, replace the heading with “Filtered data showing owners who are renting out more than 3 Toyota Camry Cars ”. Merge and center the heading. Rename this sheet Adv Filter. q) Create a Pivot table from the RentalInfo worksheet to show the Total Rental Pai d for the three (3) categories of cars. Place this pivot table on a new sheet and rename the sheet “Pivot Table”. r) Insert a row at the top of the worksheet and type the title ‘Category and Rental Fee Pivot Table. Merge and center the heading across all th e columns in the piv ot table. Use an appropriate fon t style and size for the title and wrap text if the complete title cannot be displayed in the merged cells. s) Using an appropriate type of graph or chart, create a graph or chart comparing the total r ental fee paid for each model of car. Place the graph or chart on a new sheet and label appropriately. It must however, have a title and either an axes label or data label. Name this graph or chart Total Rental Fee by Model . DATABASE MANAGEMENT Records are kept for all cars and their owner. There are three (3) models of cars rented by the company for this Car Show. They are Yaris, Corolla and Camry . The rent paid for the use of each type of car is dependent on its model. All rental fees and an indication of whether or not the rental fee has been paid are stored in a separate rates table. The company maintains the following information for each car owner: Name, Address, Telephone number, Date of Birth, Gender. Each owner is also assigned a unique identification number which is used to access his/her data. For each person, the company maintains a record of the number of cars rented by the company, the license number of each car . A table called “MODELS ” is created to store the three (3 ) entries below. Add a field to show the rental fee charged by the owner with information that is consistent with the spreadsheet. You will need to: a) Build a database to sat isfy the manager’s requirements . T he tables should be properly indexed. Create three tables MODELS , OWNER S and CAR S . Use t he database structure for the MODELS, OWNER S and CARS given . MODELS Field Name Field Type Description CarCode Text The Code assign to the model of car CarModel Text The model of the car RentalFee Number (Currency Format The rental fee charged by the owner of the car Car Code Car Model Rental Fee TYA Yaris $1,000.00 TCO Corolla $1,500.00 TCA Camry $2,000.00 O WNERS 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 Address Text The address of the owner PhoneNo Text The telephone contact of the owner CarID Text The license plate number of the car Noof Cars Number The number of cars rented to the company RentalPaid Logical A yes/no field to indicate whether the rental fee has been paid CARS CarID Text The license plate number of the car Carcode Text The code assigned to the model of the car OwnerID Number The unique number assigned to a car owner Insurance Co Text The insurance company the car is insured with b) Populate the OWNERS table with information of at least ten (10) car owners with at least three (3) of them renting out more than one vehicle. c) Add at least fifteen (15) records to the table CAR S . (An owner may have more than one vehicle) d) Create a one to many relationship bet ween tables. e) Create the Rental sub - form using fields OwnerID, Lastname , Firstname from the OWNERS Table and CarModel and RentalFee from the MODELS table. This can be done using the Form Wizard. f ) Test your data by running the following queries : i ) A simple query to li st the name s , phone numbers and email addresses of all car owners who the company has not been paid for rent al of their car(s). Name this query SIMPLEQUERY OWING. Sort this information in alphabetical order by last name and then by first name. i i) A complex query to l ist the number of vehicles rented from each car owner who rented out Camry vehicles . Name this query COMPLEXQUERY CAMRY . iii ) A query with a calculated field to u pd ate the rental fees by adding a 10% incentive to encourage more car owners to rent out their car ( s ) . A new field should be added for the updated rental. Name this query CALCULATEDNEWRENTAL. f) Generate a report which includes the name of the Car Owner, the model of the car to be displayed together with the vehicle number(s) and the rental fee charged for each car. Show also the total rental charged by each model of car . This report should be group ed by the model of the car , and sorted by Car Owner in alpha betical order by Last Name, then by First Name. T Title the report Car Rental by Model or any other appropriate title. Name this report CARINFO. PROBLEM - SOLVING 1. Develop a pseudocode algorithm to determine the total amount of Rental Fee paid by the Auto Company. The algorithm should accept the Model vehicles listed in the Car Model Table in the Spreadsheet S ection. It is not known beforehand how many cars will be rented but to indicate the end of the data, the word “End” is entered for the model of the car . i) Prompt the user to i nput the model of the car rented. Assume that it is one listed in the table. i i) Count the number of cars of each model rented. iii) Use the Rental Fee Table below taken from the spreadsheet section to : a) determine the rental fee for each model of vehicle , b) calcu late and print the total rental (without the incentive) for Camry vehicles rented. Model Rental Fee Yaris $1,000.00 Corolla $1,500.00 Camry $2,000.00 2. Using data consistent with your spreadsheet, design and execut e a trace table that accepts each car model and does a manual trace to determine accuracy of the Rental of the Camry vehicles . The table should have at least three (3) iterations a nd should end when MODEL = “End ”. PRO GRAM IMPLEMENTATION 3. Using PASCAL or any other programming language , i) W rite source code for the algorithm developed in (1). ii) I nclude documentation at the top of your program with the author of the program (a fictitious name may be used), date program was created and statement of the problem (what the program is designed to do) c) Add any appropriate inline comments d) Save at least o ne (1) s creen shots to show that your program has successfully executed. Please note that all parts of this programming component of the SBA is to saved as one PDF file to included in folder to zip when submitting. 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. Befo re determining the rental fee from the table, i t validates the data entered. The algorithm also keep s 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 Name Variable Type Variable Description X Integer Counter variable used in a For loop Noofcars Integer Counter for the number o f c ars inputted Rental Real Used to store the rental of the vehicle TotalRental Real Stores the total rental fees of the approved cars Model String Stores the Car Model entered by the user Begin Initialisation of Variables {initialisation of variables.} Bod y of Algorithm : Sequence of steps End