Tod’s Tyres

Tod’s Tyres is a small business that supplies new car tyres to company car fleets and car hire firms.I have been asked to create a computer application, either programmed or using a database, to replace the current manual record-keeping system.Definition of data requirementsWhen a car is brought to Tod, the following information is recorded on the card “CARDTBL”: (invoice number), (car registration number), (company name), (type of tyre), (date), (car mileage), (number of new tyres fitted), (comment) and (total price).The next time the company books in a recorded car for new tyres, Tod checks the cars file to see what type of tyre that car has fitted; he then ensures hat he has the required tyres in stock: “PRICELISTTBL” (type of tyres), (manufacturers code) and (customer’s price ex VAT).Tod gets his tyres from one supplier only. This supplier sends him an up-to-date supplier list once a month. Some companies are allowed a 5% discount before VAT is added.Tod keeps company details: “COMPANYDETAILSTBL” (company name), (contact name), (street address), (town/city), (post code), (telephone number) and (discount offered).CARDTBL:Field nameData typeValidationInvoice numberNumberCar registration numberTextInput maskCompany nameText25 characters limitType of tyreTextInput maskDateDate/timeCar mileageNumberNumber of new tyres fittedNumberValue between 1 and 5CommentText50 characters limitTotal priceCurrencyPRICELISTTBL:Field nameData typeValidationType of tyresTextInput maskManufacturer’s codeTextInput maskCustomer’s price (ex VAT)CurrencyCOMPANYDETAILSTBL:Field nameData typeValidationCompany nameText25 characters limitContact nameText25 characters limitStreet addressText50 characters limitTown/cityText25 characters limitPost codeTextInput maskTelephone numberText11 characters limitDiscount offeredPercentageEither 0% or 5%User interface design including output, forms and reportsIn this particular exercise, I have created each form and report by using wizards, supplied through the software used.Form design of “cardtbl”…Report design of “invoice”…Method of data entry, including validationI intend to enter data through the forms created from the tables and that validation will take place on each appropriate field.Data will be entered through means of input devices such as a mouse and keyboard; a keyboard is a computer input device derived from the typewriter, where keys pressed communicate discrete commands to the computer. Although most often used to input the letters of the alphabet, most keyboards contain special character keys programmed by the user or the software to perform specialized tasks. Keyboards are quick, simple and a cheap method of entering data into the system and so is very efficient. Again the mouse is also advantageous and enforces quick data entry and is normally less error prone.Record structure, file organisation and processingThe database is split up into multiple sections, starting off with the three major tables and then forms, and finally branching off at the report (invoice) and the mail merged letter.Evidence of the tables and validation usedCard table…Validation used for “cardtbl”…Other fields, which have been validated in the card table, are company name and comment. For company name, the maximum field size enabled here is 25 and 50 for the comment field. Number of new tyres fitted has also been validated which restricts users to enter a value greater than 5, if a greater value is entered, then an error message is produced.Company details table…Validation used for “companydetailstbl”…Other fields, which have been validated in the company details table, are company name, contact name, street address, town/city, telephone number and discount offered.The maximum field size for company name is 25; 25 for contact name, 50 for street address, 50 for town/city, 11 for telephone number and for discount offered, validation involved here only allows the values 0 or 0.05 (validation rule).Price list table…Validation used for pricelisttbl…Other fields, which have been validated in the price list table, are type of tyres.The maximum field size for type of tyres is 9.Calculations/formulas used in accessForFor example, to calculate total price, firstly go to properties on the total price text box in the design of the report. Then click control source and enter the formula “=[Price before discount]+[Customer’s price (incl VAT)]”.This means that whatever value is in the price before discount text box is added to the value in customer’s price (incl VAT).Below is a screenshot indicating the points described…The price before discount and price of VAT are both done in the exact method as total price.To calculate the price before discount, the formula “=[number of new tyres fitted] * [customer’s price ex VAT]” is entered in the expression builder of the design view of the report. The value produced is the result of multiplying the value in number of tyres fitted by the value in customer’s price ex VAT.To calculate the price of VAT, the formula “=[discount offered] * [customer’s price ex VAT]” is entered in the expression builder of the design view of the report.Query used to determine those whose cars have not been fitted with new tyres for over 12 monthsI have named this particular query overdue and it basically calculates which customer has not had any tyres fitted for over a year since their last fitting.The formula used here is entered under the criteria for the field date.”<Date()-365″Design of mail-merged document – a letter is sent to people who have not had their tyres checked on their vehicle for a period of over twelve months…Create and print form letters1. On the Tools menu, point to Letters and Mailings, and then click Mail Merge Wizard.2. Under Select document type, click Letters.The active document becomes the main document.3. Click Next: Starting document.4. Click Use the current document.You can then either type the letter in the document window, or wait until the wizard prompts you to do so in a later step.5. Under Select recipients, click Use an existing list.6. Click Browse.7. In the Select Data Source dialog box, locate and click the data source you want.By default, Microsoft Word opens the My Data Sources folder.8. Click Open.9. If you have not already done so, in the main document, type the text that you want to appear in every form letter.10. Insert merge fields where you want to merge names, addresses, and other data from the data source.System designEntity relationship diagram…Security and integrity of dataData integrity refers to the correctness of the data; the data held in a computer system may become incorrect, corrupt or of poor quality.For example:* Errors on input* Errors in operating procedure* Program errors* Viruses* Transmission errorsTo protect against input and operating procedure errors…* Data entry must be limited to authorised personnel only.* All output should be inspected for reasonableness and any inconsistencies investigated.Security of dataComputer-based information systems are vulnerable to crime and abuse, natural disaster and human error. To overcome this problem, I have password protected my database. The password is “tod2004”.Implementation/TestingTest planTest NoTestPurpose of testExpected outcome1To check the system only allows Car Registration numbers of the post-2001 formatThe system would only allow car registration numbers of post 2001 format2To check invoice is produced with all specified informationInvoice produced should have all specified information3To check standard reminder letter is correctly producedStandard reminder letter is correctly produced4To demonstrate that navigation between forms works effectivelyNavigation between forms work effectively5Type correct passwordCheck securityScreen will change to allow access to system6Type incorrect passwordCheck securityMessage box “invalid password”7Leave password blankCheck securityMessage box8Test formulaCheck discount is subtracted correctlyTotal adjusts accordingly9Test formulaCheck VAT is added correctlyTotal adjusts accordingly10Test formulaCheck query “overdue” calculates customers who have not had new tyres replaced over 12 monthsDisplays those who had their last fitting over 12 months11Test validationCheck number of tyres fitted does not exceed 5Message box12Test validationCheck if input mask used for post code is correctMessage box13Test validationCheck discount only allows either 0% or 5%Message box14Test validationCheck field size for company name is 25Message box15Test validationCheck field size for street address is 50Message boxTestingTest No.TestPurpose of testExpected outcomeActual outcomeCorrective action1To check the system only allows Car Registration numbers of the post-2001 formatThe system would only allow car registration numbers of post 2001 formatThe system only allows car registration numbers of post 2001 formatN/A2To check invoice is produced with all specified informationInvoice produced should have all specified informationInvoice has all specified informationN/A3To check standard reminder letter is correctly producedStandard reminder letter should be correctly producedStandard reminder letter is correctly producedN/A4To demonstrate that navigation between forms works effectivelyNavigation between forms should work effectivelyNavigation between forms work effectivelyN/A5Type correct passwordCheck securityScreen will change to allow access to systemScreen changed to allow access to systemN/A6Type incorrect passwordCheck securityMessage box “invalid password”Message box “invalid password”N/A7Leave password blankCheck securityMessage boxMessage box “invalid password”N/A8Test formulaCheck discount is subtracted correctlyTotal adjusts accordinglyTotal adjusted accordinglyN/A9Test formulaCheck VAT is added correctlyTotal adjusts accordinglyTotal adjusted accordinglyN/A10Test formulaCheck query “overdue” calculates customers who have not had new tyres replaced over 12 monthsDisplays those who had their last fitting over 12 monthsThose who had their last fitting over 12 months is displayedN/A11Test validationCheck number of tyres fitted does not exceed 5Message boxMessage boxN/A12Test validationCheck if input mask used for post code is correctMessage boxMessage boxN/A13Test validationCheck discount only allows either 0% or 5%Message boxMessage boxN/A14Test validationCheck field size for company name is 25Message boxNo message box appears and invalid data is not accepted.Add validation text, to make error box appear15Test validationCheck field size for street address is 50Message boxNo message box appears and invalid data is not accepted.Add validation text, to make error box appearEvidenceTest 1 – To check the system only allows Car Registration numbers of the post-2001 format.Test 2 – To check invoice is produced with all specified information.Test 3 – To check standard reminder letter is correctly produced.Test 5 – check security (Type correct password).Test 6 – check security (Type incorrect password).Test 7 – check security (Leave password blank).Test 8 – test formula (Check discount is subtracted correctly).Test 9 – test formula (Check VAT is added correctly).Test 10 – test formula (Check query “overdue” calculates customers who have not had new tyres replaced over 12 months).Test 11 – test validation (Check number of tyres fitted does not exceed 5).Test 12 – test validation (Check if input mask used for post code is correct).Test 13 – test validation (Check discount only allows either 0% or 5%).Test 14 – test validation (Check field size for company name is 25).Corrective action:Tod’s Tyres – The Practical Exercise 2004

Leave a Reply

Your email address will not be published. Required fields are marked *

x

Hi!
I'm Heidi!

Would you like to get a custom essay? How about receiving a customized one?

Check it out