Definition-nature of the problem solved Essay Example

Definition Definition-nature of the problem solved Essay Definition-nature of the problem solved Essay Willcare is an independent car mechanic business. The business located near the owners home where there are a number garages available. It is a small service business promoted by the owner and has been running around 20 years. The owner may employ some workers for a specific job, but usually works alone on a variety of jobs. A separate outlet provides supplies when they are needed. * The main aim of the business is to provide an alternative for customers who dont like the treatment from there local dealerships. The owner feels that people would prefer a face-to-face meeting with him to discuss their problem and says he prefers talking to clients personally rather than through a system because it helps create trust. His business also aims to give an equal amount of customer satisfaction to all those using the business. Also, to make sure that the companies recognition grows, so that more people are aware of what it has to offer. * Most vehicle related services are available. These include repairs and modifications, as well as a phone service for queries or complaints. The business will offer to keep vehicles for a short period of time but only if necessary as it means insurance and liability does not have to be dealt with. Most jobs are done in under a day and they usually receive no more than 5 customers a day. * The majority of the business material e.g. dealing with finances, orders and bills is done by the owners wife and is still completely based around paper filing and written receipts. Current Method Used * All the paperwork of the business is still file based without an effective computer system. Lists, receipts and orders are produced and sent through conventional methods such as postage and delivery. * Customer information, their history with the company and their previous cars, requests and other information is kept in an alphabetic filing system. This method is fairly effective at the moment however the company is gaining recognition and eventually there will be to little resources to keep the current system working effectively. * Finding required information will involve looking through the filing system for a customer and then searching their records for any needed information. This can be much more time consuming for customers who have used the company often as there may a large amount of files to search through. Also the most recent receipts are kept close at hand in case a customer is unsatisfied and needs to prove on their return that they are legitimate. * Lists of information and data, such as clients cars are also kept in a brief date book so that the owner can at a glance see what is expected in the next few days. This helps him inform other customers of the probability of their car being seen in the near future based on the amount of work needed and also the space left available. The owner told me that he estimates space but it never poses too much of a problem because of not keeping individual vehicles longer than a day. A final list is also kept of the other companies he has bought off and how much they charge for various items. * Invoices and receipts are still produced using trace through paper copies * Calculating payments is usually done before the work based on an estimate of the time required and the resources needed. It still remains an estimate however and is worked out with a calculator. All other costs are worked out using a calculator also, e.g. suppliers pricing. * With about 3 to 5 customers a day tasks such as calculating are done while the work is taking place so there is no delay for the customer when they come to pick up the car. Usually though the owner likes to talk to the customers to discuss how the service went and get their opinion on various matters to do with the car. If, however a large number of customers are present at a similar time, which can often be the case because they may want to pick up their car after work, then there can be a delay. * It appears that tasks are performed fairly efficiently as long as it is a simplistic job. However, because different areas of the business seem to be stored separately e.g. the supplier receipts and data and those of the customer that this can often lead to confusion with payment calculations. * Lacking from the current system, according to the owner is the ability to search for the customers information while they are on the phone. He quite often has to put customers on hold while he searches for their information or for an answer to a query he doesnt know of hand. He also feels that if the business continues to grow at its current rate he would be lacking the space to keep all of the records and would have to discard any before a certain date. This could take a fair amount of time and needs so he needs to find an alternative method. I also feel the system is lacking a high enough amount of security. Currently all records are just kept in a locked filing system with no copies present to compensate for critical damages. A more secure system requiring restricted entry would be preferable in the future. * As a result of the current system being so basic there is little room for any critical errors as long as the business remains at a steady rate. It is only when increased customers bring an increase in the amount of data that the system will be prone to mistake. This is the main fault with the current system. With all processes being hand written and calculated there is a much larger margin for error than modern technology has. Any incorrect calculations will result in time consuming back tracking. Other faults are the estimation of time and space. It would be much easier to confidently inform customers of when there car can be seen, without wasting their time because of a lack of space or time. Data Flow of Current System Inefficiencies and Problems As mentioned earlier under current method used there were a number of inefficiencies present. The most important of these are listed below 1. Calculations are slow and prone to mistake. 2. Large amounts of paper-based data are susceptible to being confused or lost as a result of over crowding information. 3. Security is of a low standard and there are no copies of data being made as backups. 4. There is no fast access to customer information that may be needed for quick consultation, in situations like a phone conversation. 5. Any mistakes in calculations or mix up in customer data take a long time to correct due to lots of paperwork in a rough system. Origins and Forms of Data Data Origin Form Customer Details Signed Form Customer Records Sheet/ Receipts Problem Description Signed Form/Telephone Call Customer Records Sheet/ Receipts Car to be serviced Telephone Call/Signed Form Customer Records Sheet/ Noted Sheets/ Receipts Date/Time confirmation Job Diary Customer Records Sheet/ Noted Sheets/ Receipts Cost Estimate Predicted by owner prior to service, confirmed after. Customer Records Sheet/ Receipts Initial Problem Definition A slow customer service as a result of paper based filing systems for storing data. These cannot be accessed quickly with phone-based customers and may lead to frustration. Each customer has a single folder for their data and it is not ordered in any other way than newest entries being put at the front. Faster access to required information could be achieved under a different system. Only an estimated price and time can be calculated before a service takes place. A new system that could calculate exactly would help improve customer trust and efficiency. Mistakes are a higher probability using the current system of calculators, as they are prone to human mistake. A different system incorporating checks would improve reliability. Security is not of a good enough standard for large amounts of customer information. It is only secured in a locked draw with no insurance policy. The system requires a lot of time to create any backups. Investigation and Analysis Methods of Fact Finding Research into various methods of finding more about the current system have shown me that a one on one interview with the company owner would be the most beneficial. Three other techniques taken into consideration were a questionnaire, observation and analysis of current data regarding the company and its system. These were not preferred for the following reasons 1. Questionnaire- For a smaller organisation with fewer customers this would prove less effective. The data could not be considered as completely reliable based on the small number of participants. I also think they are less likely to know about the company information techniques. 2. Observation- With the business receiving two to three jobs a day the owner will be spending most of the day doing the actual work. It would be difficult to know when to visit to be able to see the other side of the business. Also, some customers may be opposed to there payments not being confidential. It may be possibly to get a small glimpse at the workplace following my interview. 3. Existing Data Analysis- The only data kept by the company is that regarding the individual customers and these are kept confidential as it is personal data. The interview I will prepare for is with the main manager because he will have the information most useful to me. He started the business and has been in full control ever since. Interviewing someone lower down in the business, such as a part time employee or perhaps a customer would most likely only provide me with limited knowledge. Following the interview I will try and question an employee to see how they feel about implementing a new system and how it would help them. It is important preparation takes place before an interview of this sort to make sure the correct information is gathered. Therefore here is a basic outline of the information I will hope to gather. What happens to the data the customer provides? How long are forms kept, is finding them on later occasions easy? What aspects do you feel need improving relating to the customer? What type of information do you need to get that sometimes isnt available? Are you in favour of new system displays e.g. logos, colour schemes? What paperwork would you like in a new system? Would you like an upgraded appearance for the new system logo, images and colours? Would you like shortcuts for various office jobs, would they speed up the processes? Would you or your employees need training to use a new computerised system? Is there anything else you would like the system to do? Interview Notes This interview took place on the 8th October at 4.30pm, at the owners house with himself and his wife present. I informed him of what my report would be based around and how I predicted a computer-based approach would likely be the most effective way to take the business. He agreed and said money was the main issue when it came to these improvements. Q: What happens to the data the customer gives you? The data is stored along with the customers previous records. Its a basic folder which we order by the keeping the most recent jobs at the front. Any new customers have a new folder created and are placed in alphabetical order with the other folders. If the data is something I might need to hand I record it in a journal I keep with me in the garage. Q: How long do you keep the forms for each customer and does the folder system make them harder to find? At the moment I still keep everything for each customer because often I may need to look back over previous jobs to remind me of what the customers situation is in terms of their vehicle and there history with the company. Also while the business is still small there is no issue with overcrowding of information. It doesnt really take long to find the info because its all organised properly and efficiently but it still takes time to leave a customer on hold while I sort out the data they want. Q: This problem relates to the customers, is there anything else you think the customers would like to see the business change. I think part of why this business has been as good as it has is because of the straightforward basic approach we use. Customers who dont want to talk to a mechanic through about five other people can just have face-to-face meetings to discuss the problem and make the decisions around that. Its a trust thing, I dont think they mind the paper-based approach but they would probably prefer it to be a bit more professional overall, and a computer system would probably accomplish this. Q: What type of information do you need to get relating to jobs that sometimes isnt available? If you mean things like how a certain car works and how to deal with certain problems then I usually just rely on instinct. Sometimes I consult the cars handbook but usually the problem isnt too serious to need that. Q: If a new computer based system was introduced would you like a specific display screen, featuring perhaps the company logo or a specific colour scheme? Yes, if thats possible itll just make it look a bit more professional but the company has no unique colour scheme so you would probably have to make one. Thats fine; Ill give you a rough copy to check over before I use it properly Q: What paperwork would you like to keep in a new system? Well the information the customer receives, things like receipts, bills and other notes and stuff, theyll need to be paper because most of my customers dont have access to computers. They prefer the simplistic approach so I dont want to change my approach of keeping the customer happy with little involvement. Q: Would you like shortcuts for various office jobs, would they speed up the processes? Anything thats simple enough for me to use effectively and also speeds up the process for customers would be great but it would have to be pretty simple for me to control it. Q: Would you or your employees need training to use a new computerised system? Some of the people I hire are pretty accustomed to computer systems but for me and especially my wife we would probably need a short introduction into what does what. Q: Is there anything else you would like the system to do? Well Im open to suggestions really but I wouldnt know what else would be possible. If you have other ideas just run them by me and probably approve them for this new system. 4.6 Current Data Structures Low-level DFD Booking a Job This is a low level DFD highlighting the processes required when booking a job. The owner has to consider both whether he can complete the job and also what it will cost the customer to pay for the job and any resources. The pricing list and current job notes are stored on paper as a checklist to be calculated. This process takes time so therefore numerous customers have to be called back before they know the full details about a job. All the paper-based information regarding each customer contains no specific reference that it is related to the company. Each piece is usually headed with the customers name and what the sheet will be referring to. A great deal of input into the system comes from customer word of mouth. Often times the owner will arrange a one to one meeting with a customer to discuss exactly what they want done. The interview notes are recorded and discussed with the customer afterwards to make sure they have gathered all the necessary information. 4.7 Requirements Specification Output requirements Requirement Evidence The system must provide a legitimate receipt to be given to customers to check and sign Interview with manager The system must provide a printed page containing an outline of job details to be kept with the owner during a job. Interview with manager The system must include a specific colour scheme in keeping with the business to appear on all printed documents Interview with Manager The company logo must appear on all printed documents in order to help classify information. Interview with manager The system must provide a report that lists customers who have requested a job in the last 7 days. Interview with manager The system must produce printouts regarding the requirements for a job to be given to the customer so they are fully aware of what they are paying for and also as an insurance policy. Interview with manager Input Requirements Requirement Evidence The company logo must be automatically present on every document before anything is written Interview with Manager System must be easy to operate for novice users Interview with Manager The owner must be able to secure the customer details with a login and password known only to him. Interview with Manager The system must provide quick access to customer data through a fast and efficient search engine. Interview with Manager The system must be able to link customer data to their current and past involvements with the business, through a relational database. Interview with manager Hardware and Software Requirements Requirement Evidence The system must be linked to a printer that is capable of printing out reports and receipts. Interview with Manager System must run on operating system Windows XP Interview with Manager All queries should return their results within 5 seconds on a computer with a fairly powerful modem. Interview with Manager The system must be operational through a responsive mouse as well as through the keyboard. Interview with manager The system must have various linked shortcuts to travel to different locations in the system faster. Interview with manager The system must be compatible with other storage devices in order to create backups of customer data. Interview with manager 4.8 Agreement to Requirements A copy of a letter I sent to the owner questioning whether these requirements were acceptable can be found in the Appendix. The letter contains requests for the previously listed system requirements and is signed by the owner as conformation of there agreement. 4.9 Alternate Solutions Analysis of the problems faced by this business has given me ideas for various solutions. Although a computer based approach seems a faster easier solution to the problem, other solutions must be examined to make sure the most efficient approach is used. Solution 1- with the owner feeling that the current system is still fairly efficient it maybe most effective to make subtle changes to this original system so as not to alienate employees unfamiliar with more complex methods. Remaining with the paper based system saves the need for large investments in newer software and hardware as well as the inconvenience of setting the system up. The installation process could take a fair amount of time and disrupt customer relations, which are such a high principle to the companys success. The solution would involve updating the string techniques for customer data. For example more secure folders and a quicker access system to find customer data. Each customer would be given a job identification number based on their personal data and the time when their job was ordered. This way the manager can prioritise the filing system to have the most recent jobs and best customers in a more accessible manor. Another approach to improve the current system would be to include partial computerisation. This would be a system where a search device would contain records of the locations of customer information. By using the search tool it will direct the manager to exactly where the documents they require are stored. This could be a very effective technique for when the businesss popularity starts to grow because it will mean a lot more storage places will be being utilised and customer documentation will become harder to find. If the business does start to grow this way however, the storage spaces may become limited for the amount of information it needs to keep secured and min good condition. Solution 2- with using a computer system seeming the most effective choice different solutions become available using various different programs and approaches. A possible solution would be to use a spreadsheet document and incorporate user forms. This approach has its advantages because by creating a form system they can be customized greatly to incorporate the company information and make them as simple and accessible to the customer as possible. This is very important for this business because the system must not appear confusing to customers who have chosen because they want a simple solution they cannot get elsewhere. Getting the customer more involved with the businesss processes may appeal to some customers more than others. If the customer feels they are making an input to the business and is familiar with the processes they may become more supportive and confident with the new system. One of the biggest advantages of this approach to the manager is the spreadsheets ability to perform complex calculations quickly. This is a great improvement on the conventional method of a calculator and paper workings. The downside to this is that it would require a fair amount of time-spent training before the manager could understand the formulas needed for what may become a complex system with more customers joining it all the time. Although the system could be set up by someone else leaving a simple input system, the manager still requires knowledge of the system in case any mistakes are made with the system during inputs and they need to correct it without bringing someone else in to fix it. It is important that the system does not disrupt the managers relationship with his customers. The biggest disadvantage wit this system however is that it would become very crowded and difficult to understand the more customers join the system. This is because spreadsheets do not work with a one to many relationship. Any customer who requested multiple jobs would have to have their name and their details entered into the system multiple times rather than having the jobs kept under the same name. This leads to a great deal of memory being used up by the system. With a very large amount of data the system may become slower because of the number of details being processed all at once. Solution 3- with a spreadsheet having a number of more complex inputs such as formulas and action buttons a more practical approach would be a database system. The Relational Database system I would use has a one to many relationship so it would allow each customer with their individual identification to have access to all their previous job details. The system will work by having multiple tables linked through a primary key. This primary key will be the customer ID. This ID will be present in tables containing the customers information, the job details and the pricing system. The customer table will contain their name, contact details and personal information. The job table will contain descriptions of the most recent job performed for that customer and a summary of all their previous jobs. The price table will be a list of each customers bills for their jobs. Using the ID to look a customer up the prices of each job will appear broken down into various segments so it is clear to them how a total was reached. In order to produce receipts quickly the system will also include a mail merge system linking the database to a word processor. Then through a button click the user can replicate the billing information and print it of for the customer. This method saves the time previously spent writing out receipt details such as the company name, data and job details. This information can now be saved in a word document and produced quickly in large numbers. 4.10 Limitations of Chosen Solution From the three solutions described above I have chosen to use Solution 3-a database system. This is because it provides the most efficient use of a computer and allows a customer to have various jobs under their name without overcrowding the system. The database can be set up by me and modified to fit the companies needs addressed in my one to one interview. For example the company name and a new logo will be present on the input forms as well as a constant colour scheme throughout the system. I chose it over an advancement to the current method because it is much to difficult to anticipate whether there would be enough room to store personal data if there was a sudden increase. Using the database means all the information can be kept together in a much more secure environment. The solution I have chosen is in itself not perfect. Here are some of the limitations facing the current system. * A fairly high level of instruction will be needed before the system can be implemented. With the amount of important data on the system it is vital that the manager and all his employees are fully aware of the security measures that have to be in place. * While a small amount of customisation is possible, a database application contains far less than a spreadsheet or word application making it harder to accurately represent the company logo and colour schemes. * For the database to work without any slowdown or technical problems it will need a fairly modern computer set up incorporating a high-speed modem and a large memory capacity. These specifications up the price of a computer and for a smaller business such as WillCare this larger amount of money is harder to come by without risking loosing company profits. The investment would be a risk because if business started to decline the company would be struggling to cover expenses. * The database application is unable to print what the business requires straight from the tables e.c.t. Instead a word processor must be incorporated for a proper receipt to be produced. Design Nature of the Solution 5.1.1 Objectives Input Objectives Customers personal data must have secure access through a login and password The database must be easy to use for a novice user. The system allows the user to input details of the customer on a single screen The system must be operational through a standard mouse and keyboard Process Objectives The system must recognise customer details through a unique member ID. It must calculate a job price based on a price list. It must store each customers previous job descriptions It must be possible to sort customer records in various orders such as alphabetical or by their most recent jobs. A means of backing up of all the files is provided. A simple method of recovering corrupted files has been produced. Output Objectives The display screen must present the company logo clearly The colour scheme chosen by the owner must remain constant throughout all output screens Printouts of receipts must be produced through a mail merge with the word processor When a code for an ordered item is entered, details of the item, including current stock levels is displayed on the screen Query results must be produced within 5 seconds of them being used. 5.1.2 System Structure This is a brief outline of the how the new implemented system will work. The structure will be kept as basic as possible to benefit the inexperienced workforce. A top down structure- Hierarchical Diagram Design of Forms All of the following forms and explanations are based on an outline of the system created in a spreadsheet document. By using this method I will be able to show the system to the owner of Willcare and get some feedback on what he thinks works well and what improvements he would like. All forms within the database will use this same colour scheme, as it is a professional look that will also help customers and employees recognise that they are still in the system. I felt that because the probable users will be inexperienced it would be useful to include a brief description of what functions the buttons will have in the database. The font specifications are Arial, size 10 with the table definition Menu using Berlin Sans at font size 18. The Willcare name is presented in a word art technique modified in its colour scheme and size. The contrasting colours help it show up on the system and are another way to reassure the user they are still correctly using the system. This is the customer form where the user will be able to add a new customer, search for an existing one or delete an old member. All the buttons are clearly labelled to explain their functions and I have maintained the same style of headings and logo used on the main menu. To make searching through current customers easier I have incorporated navigational arrows for quicker access and easier browsing. When considering the users abilities with computers I also decided to include a button to return to the Main Menu on each form. This will be useful because it will allow quicker access when the user wants to use a different part of the system. Each customers details will be filled in the shown boxes and various pieces of the information will be validated to make sure it is correct within the laws of the system. The most important aspect of this page will be the member ID. Each customer will be given a unique number that will be used to look him or her up through the system. The owner may feel, however that giving all his current customers a number for the system would alienate them and waste company time. Therefore I may only use member IDs for new customers and use a different look up method for existing customers e.g. their D.O.B. For the customers D.O.B entry I will use a combo box method so data input can be sped up. This form also includes a link to Add a Job. This button will allow the user, once they have entered the details to go straight to the job form so they can choose what they want done and find out the estimated price. The post code is an important way to identify customers legitimately and will contain a validation making sure it is accurately filled in. During my interview with the company owner I learned he would require the system to produce an output in the form of a receipt. To quickly produce the receipt using a mail merge technique the produce a receipt button will create the receipt for the job currently recognised in the form entry. If this is not the required job for the customer, the user can search for a different one using the view customer job button. The maintenance menu includes a link to job prices and a supplier list. These are options that are likely to be scarcely used. This influenced my decision to include them in a maintenance menu. If the user becomes confused with the system they can check this important information and hopefully save customer time. The suppliers list does not belong in parts of the system the customer views as it will confuse them and overcrowd parts of the system. A mechanics business has a very wide variety of jobs that may be required. It is therefore important that the user can add a description of a new job as well as have quick access to common ones. When a new job is entered a full description can be added or just a shorthand. The shorthand is necessary for it to be stored under the common jobs. The price remains an estimate because all jobs are different and customers may have required more or less work depending on their situation and car. The date of order and the expected completion date will both use combo boxes and the date of order will be immediately validated to display the current date automatically. The add to outstanding list will be covered under reports design as is the create receipt button. As the business grows they are likely to have numerous suppliers therefore I felt different search criteria would help speed up the process. One or both of the criteria can be entered and then searched for within the database. A supplier list will contain all the suppliers and products the company have used in the past. This is where the database will search based on the criteria. This is an example of one of the tables data will be processed from. Each Job will have a unique ID to help searching through the database faster and more efficient. Design of Reports With a growing number of customers I felt a report covering the outstanding jobs for the owner would be very useful. I decided to return to the original white background so the businesses paperwork looks professional and is always as clear as possible. The logo remains as it helps to identify the company documents against those from customers and suppliers. Including the ID means the report will be created through a single button click in the database. Keeping these reports at hand will save backtracking to the computer system to see what jobs need to be done. The button to add information to this report is located on the job form detailed earlier. The button will add the currently displayed job onto this report. The user can then decide to print the report with any amount of jobs on it. This report keeps the same fundamentals of the previous one, those being the plain background and company logo. This report will contain a list of all the customers currently on the system database. This will be useful for the manager because it will help him remember customer information while on the job. It can act as a quick reference for customer contact details so that they can be kept informed of developments in the business. This is a copy of a company receipt. It will be created in the same manner as a report but will contain a query system so that prices can be worked out without the manager needing to do anything. The font has remained constant; however I have now included bold print for the totals and to highlight problems. This will make identifying key information easier for both customer and manager. In the style of many receipts I have now included a contact number for any problems or questions. Pressing the create receipt button found on the Job form will create this receipt. The receipt must include all the details of the transaction including the individual jobs and there prices so that it is clear to the customers where the total is obtained from. This will also make any mistakes easier to identify. Design of Mail Merged Letters A standard letter required by the user is to cover jobs that are going to be overdue. The letter will be created in a word document through a mail merge button present in the database. The letter once produced will be printed and sent to customers to inform them of the delay and the expected date for the job to be finished by. This shows the customer that they are not being forgotten and the business values their custom. By pressing the mail merge button a word document will automatically open and the outline of the letter be filled with the necessary information. The information will be entered through the use of a code on the word document. You can find an attached plan of the mail merge layout in the appendix. Design of Data Capture Forms As this is a mechanic business there is no need for any data capture forms because they cover the details entry for members and employees. A data capture form is usually sent to these people to be filled in. The information gathered is then input onto the computer system. All information of this sort is obtained straight from the customer or over the phone so this technique is an unnecessary approach. Relationships ERD- Entity Relationship Diagram TRD- Table Relationship Diagram Table Designs Customer (Customer ID, First Name, Surname, Date of Birth, Address Line1, Address Line2, Telephone Number) Request (Customer ID, Job ID, First Name, Surname, Job Title) Job (Job ID, Job Title, Estimated Price, Date of Order, Expected Completion Date) Order (Job ID, Part Name, Price, Supplier Name) Part (Part Name, Supplier Name) Query Designs Generic Buttons Within the database there are various buttons that will be considered generic. This means they have a single function that remains constant throughout the whole database. The Main Menu button presented in the corner of every form will return the user to the main menu no matter where they are in the database. The Search buttons found on numerous occasions in the database will always perform the same function of searching the database for the data present on the page. As I am designing this system for a user with very limited knowledge I have included a small explanation of what various buttons will do before they are pressed. For example Produce a Receipt is written next to a button labelled Go. This helps reassure the user that they are doing what they want. Start action: The Customer option button is selected. The Customer Form Screen is displayed Start action: The Report option button is selected The Report Form Screen is displayed Start action: The Maintenance option button is selected The maintenance menu is displayed Start action: Customer details are entered into the customer entry form and the store customer button is pressed The customers details are stored in the corresponding database table- Customer_TBL Start action: A customer is selected on the customer forms and the edit details button is pressed. The form will become active and allow the details to be changed by the user Start action: A customers details are present in the customer form and the delete customer button is pressed A message box will appear asking for confirmation of the deletion. The Yes option is chosen from this message The customers details are deleted Start action: The Add a Job is pressed on the customer entry form The customer form remains open and the Job entry form appears. Start action: The button is pressed The previous record in the system is displayed Start action: The button is pressed The next record in the system is displayed Start action: The find button is pressed while a member ID is displayed on the form. The system will search for the customer details and display those corresponding to the ID If no member ID is present the user will be prompted to enter one by a pop up message. Start action: The Add to outstanding list button is pressed after a jobs details are entered. The current job will be added to a report list that can be printed of and taken away to help remember what jobs need doing when the user is away from the system. Start action: The create receipt button is pressed A receipt will be produced using a mail merge based on the currently displayed information. The receipt will appear in a word document and include company details Data Dictionary Field Name Description of Contents Data Type Length Sample Values/Range/Set Type of Validation Expected Customer ID A number classifying each customer so they can be quickly identified in the database Long Integer 4 7 No more than four characters to be entered. No duplicates. First Name Customers First Name Text 15 Roy Required. Surname Customers Surname Text 15 Knightly Required. D.O.B Customers Date of Birth Date/Time 10 07/11/1975 Required, DD/MM/YYYY, Must be Todays date Address Line 1 Customers immediate local address Text 30 26 Hemlington Road, Walmley None Address Line 2 Further customer address details Text 30 Sutton Coldfield, West Midlands None Telephone Number Customer contact number Numerical 11 0121 345 6556 Required, Maximum 11 numbers. Must be structured in traditional way e.g. 0121 333 2233 Post Code 6 character code identifying customer address Text 8 B76 1JH Required Field name Customer_TBL Location Booking Database Purpose To store customer details Size of Individual Record: 123bytes Maximum Records Stored: 60 Total Size Required for Data: 7380 Bytes Overheads (10%) 738 Bytes Total Size Required: 8416 Bytes Field name Request_TBL Location Booking Database Purpose To show what jobs customers have requested Field Name Description of Contents Data Type Length Sample Values/Range/Set Type of Validation Expected Customer ID A number classifying each customer so they can be quickly identified in the database Long Integer 4 7 No more than four characters to be entered. No duplicates. Job ID A number classifying a job identity so it can be linked to the customer ID Long Integer 4 15 No more than four characters. Must not exceed the number of available jobs. Price Estimated price for requested job(s) Numerical 6 à ¯Ã‚ ¿Ã‚ ½15.00 A maximum and minimum value. Decimal must be situated correctly. Size of Individual Record: 14bytes Maximum Records Stored: 300 Total Size Required for Data: 4200 Bytes Overheads (10%) 420 Bytes Total Size Required: 4400 Bytes Size of Individual Record: 55bytes Maximum Records Stored: 80 Total Size Required for Data: 4400 Bytes Overheads (10%) 440 Bytes Total Size Required: 4840 Bytes Field Name Job_TBL Location Booking Database Purpose To record jobs the company can complete for customers and to identify job descriptions through their Job ID Field Name Description of Contents Data Type Length Sample Values/Range/Set Type of Validation Expected Job ID A number classifying a job identity so it can be linked to the customer ID Long Integer 4 15 No more than four characters. Must not exceed the number of available jobs. Job Name The title of a job Text 20 Wheel Change None Job Description A brief description of jobs that is unclear from their name. Text 25 Replacement of side mirrors due to damage None Price Estimated price for requested job(s) Numerical 6 à ¯Ã‚ ¿Ã‚ ½15.00 A maximum and minimum value. Decimal must be situated correctly. Field Name Order_TBL Location Booking Database Purpose To show the jobs that suppliers are providing parts for. Field Name Description of Contents Data Type Length Sample Values/Range/Set Type of Validation Expected Job ID A number classifying a job identity so it can be linked to the customer ID Long Integer 4 15 No more than four characters. Must not exceed the number of available jobs. Supplier ID Unique number to distinguish between suppliers. Long Integer 4 14 No duplicates. No number higher than those of listed suppliers. Part Name Name of part to be purchased Text 20 Bonnet None Price Estimated price for requested parts(s) Numerical 6 à ¯Ã‚ ¿Ã‚ ½15.00 A maximum and minimum value. Decimal must be situated correctly. Size of Individual Record: 34bytes Maximum Records Stored: 300 Total Size Required for Data: 10200 Bytes Overheads (10%) 1020 Bytes Total Size Required: 11220 Bytes Field Name Supplier_TBL Location Booking Database Purpose To hold details of parts owned by suppliers. Field Name Description of Contents Data Type Length Sample Values/Range/Set Type of Validation Expected Supplier ID Unique number to distinguish between suppliers. Long Integer 4 14 No duplicates. No number higher than those of listed suppliers. Supplier Name Name of supplier Text 20 Kwik Fit Required Price Estimated price for requested parts(s) Numerical 6 à ¯Ã‚ ¿Ã‚ ½15.00 A maximum and minimum value. Decimal must be situated correctly. Part Name Name of part to be purchased Text 20 Bonnet None Size of Individual Record: 50bytes Maximum Records Stored: 50 Total Size Required for Data: 2500 Bytes Overheads (10%) 250 Bytes Total Size Required: 2750 Bytes System Security The system will only ever need to be used by the one manager so with a single password access method and a lock system tool the system should be as secure as it needs to be. I will advise the manager to have a fairly long password that includes both text and numbers to make guessing the password very difficult. He is also advised to change the password on a regular basis to protect the system further. Levels of security are not a big issue for Willcare because with a very small workforce everyone sends information straight to the manager or his wife to be dealt with. It is very unlikely these employees would need to use the system and on the off chance they can be shown the password for a short time before it is changed. This method also saves the lower employees from having to learn about the system, and the system can be designed exactly as the manager wants without worrying about other employees use of the system. Data that needs to be changed e.g. a suppliers part pricing or new customer details such as an address change are to be dealt with by the manager because he must approve the changes before they take place. He can therefore keep the system to his individual specifications. Object Name Access rights / View User Customer_TBL Read only Manager Job_TBL Read only Manager Request_TBL Read only Manager Order_TBL Read only Manager Part_TBL Read only Manager Object Name Access rights / View User Customer Form Read/write Manager Reports Form Read/write Manager Maintenance Form Read/write Manager Job Form Read/write Manager Supplier Form Read/write Manager Job Prices Form Read/write Manager Data Security This security is very important because the system is protecting personal customer data. If this were to be released by accident or due to lack of security the manager would face various consequences that would effectively ruin his business. Backups will be made and updated at the end of every day. The system will be stored on memory sticks and in early cases floppy disks. Backups will be made of all areas of the database that contain personal data. Therefore it is best to save the database as a complete file. This will be fine while the business remains small but as the database grows separate backups may be needed due to the size of the file. One backup will be stored with the manager at all times while a second will be kept at a separate location away from the system. It will be the managers responsibility to backup the system daily. In the event of data loss in the system the data will be backed up on a new computer system to avoid data corruption. It will remain the managers responsibility but he may decide to employ a more experienced technician to make sure all data is restored properly. Intended Benefits 1. A faster mode of access to customer data to help with negotiations over the phone. Provides a guide to the manager to help him recognise customers, their history with the company and location. Previously paper based records had to be found within a large number of files and were unclear when compared to a computer presentation. With the system in place the manager can enter the customers name and search for all the information they have on file about them. 2. A more secure way to store and protect customers personal data. Previously the data was only kept in a locked cabinet which was still susceptible to be breakage. There was no way to backup this data without a huge loss of company time and resources. The new system will allow data to be locked with the use of a password that can be updated on regular occasions. Backups will be easy to make and the customer data protected to the highest degree possible. 3. Faster and more accurate calculations come as a benefit of a computer system saving the manager the job of manually working out bills for customers and suppliers. This improves customer service and gives the business a more professional look. With a query system the calculations occur without any work involved and are constantly correct. Limits and scope The method I chose for the company is a vast improvement but still has its own limitations due to the fact that the size of a database is constantly growing and with the system the company is incorporating it may cause slight slowdown. Limitations may only become apparent as the business grows because more and more customers mean more data and a strain on the system memory. Another problem is that the system can only provide a certain amount of help to those unfamiliar with computer systems. A basic training is still needed to familiarise the new user(s) with the fundamentals of the system. There are various functions that would have been of great use if the system had used a different type of program. For example a spreadsheet-based system would have provided easier parameters for formulas such as working out a customers final job price. It would also have been easier to design various menu screens and visual aids for the user due to the compatibility of the program for designing from scratch. The requested company logo is likely to be limited to something pre designed by the computer and as such is likely to be of a slightly lower quality. This is because there is only a certain amount of pre made designs on the computer system. It would be feasible to use a scanner to include a paper-based design however this would require more equipment, a skilled artist and a longer timeframe. Due to the fact that the user does not have the required equipment it would seem a waste of finances to invest in one for a single task. Therefore the logo will have to use computer graphics and be modified as much as possible to fit the users demands. Table Name Estimate of total number of records Size Customer 123 7380 Request 14 4200 Job 55 4300 Order 34 10200 Part 50 2500 Total Record Size 28580 This table shows that when the business begins to grow substantially and the customer count reaches that shown of around a hundred then the total size of all records will reach a very high memory, in this case 28580 bytes. If the system begins to struggle with an overload of information an upgrade is likely to be required. However from gathering information about the business through my interview and gaining an overall impression of its set-up and position I feel the chosen system will work efficiently and without problem for a fairly large amount of time. Time Line Weeks Tasks 1 2 3 4 5 6 7 8 9 10 Create Tables in database Create Relationships Create Queries Develop forms Develop reports Manager Test Period System Review Create User Guide Create Technical reference Guide Software Development Input, Output and processing printouts Forms Theses are what will be visible to the user and the customer when the system is implemented. It is therefore important that I take into account the requests the user made during the interview regarding the new system. This is the main menu that will act as a base from which the other forms can be accessed. The user will be able to return to this menu at any time through a main menu button. As this is screen most likely to be seen by the customers and user I have distinguished it with a different colour scheme from that of the other forms. I made sure the colours used were meeting the specifications of the user, in that they are clear and complimentary. I have also taken into account the computer knowledge of the likely users and maintained the design I had early chosen of a brief explanation of what each button will allow the user to do. This will help the user become comfortable with the system and maintain their understanding of how it works. The owner also requested that there be the company name present on all slides and I made it sure it is clear and remains a constant design throughout all the forms. For example when a new customer is joining the user can choose the customer option and open the customer form for the customers themselves to fill in. The customer form shown here is slightly different from my original design in that it is no longer a completely blue background. I felt this design had a more professional look while still maintaining the clearness apparent on the original design. The company logo however remains the same as that from the main menu and this helps the customer realise they are still in the correct place and are safe to enter their personal details. The customer ID will automatically move onto the next number when a new customer is being entered. The Add Customer button will take the user to the next available empty form and allow them to enter new data. All of the buttons are command buttons. They allow different functions relating to forms, reports and others. The arrows will take the user either back or forward by one through the records. This fast access will allow quick searches for customers whos ID is close to that currently displayed. The Delete Customer button will clear the form of the current information it is displaying. This in turn will delete this customer from the Customer_TBL. The Store Customer will save the currently displayed customer to the system allowing the user to add another customer knowing the previous customers records are safe. To help the user and also to make the system more accessible I have included the Add a Job button. This will allow the user once they have entered the customers details to immediately fill in a job that might be being requested for the first time. It is important to do this because otherwise the job will not be present in the system and therefore cannot be linked to that customer. It saves the user the time of returning to the main menu and re-opening the Job form. If the user does need to return to the main menu for any reason they can do so by using the main menu button which will remain constant in the same position on all forms here after. All of the buttons from my original design have been recreated except for the Edit Customer button. This has been removed because the option to change the data is available to the user automatically. This is the Job Form where the user can enter details of new jobs as well as deleting uncommon ones. This form also links the customers ID to the job they want performed, therefore the Print a Receipt button can now use both these pieces of information to print a receipt I have created in a report. The report will be shown later. The arrow buttons move forward and back through the job records and the Main Menu button returns the user to the initial starting screen. This is the Part form, which allows the user to store information regarding parts they are supplied with. The same options are available with the button commands allowing them to add or delete a record as well as moving between records. The part ID is automatic like the other IDs in the database because it is an auto number. When a new record is added it automatically moves onto the next number. The Main Menu button retains its job from previous forms. This is the supplier form. It allows the user to enter and store all of their current suppliers and keep track with what parts they provide by having a Parts ID linked to its supplier. Keeping the add and delete options for these form records as well as the Main Menu button keeps the user confident whilst operating the system. The same look and design of buttons ensure there is no confusion as to functions. This is also why I chose text to label the majority of my buttons. Queries Data Structures Tables This is the Customer_TBL. It records the information listed under the field name column. Customer ID is the primary key because it is the most unique data available. It will allow records to be found easily through a search when the database is complete. The auto number data type means the number will automatically appear in a sequential order when another customer is being added. The date of birth field is classified as Date/Time under data type because this makes it easier to add the data in this field. Under the validation rule field you can see the validation that will prevent dates being entered after the current date. The validation text that will appear when an incorrect date is entered is shown here also. The text is user friendly and makes it clear what they will have to change. It prevents incorrect data being entered in the database that will jeopardise the system. Telephone Number data type is number as this makes sure no text can be accidentally entered into the database. Post Code remains a text data type because it incorporates both numerical and text entries. Details of these fields and their validations can be seen in the data dictionary on the following page. For the postcode field I have included an input mask shown on the previous screen shot. The code I have entered will only allow data to be entered when it fits in with the structure required. The code allows any number or letter to be used as long as it is positioned correctly. This is a vital validation because a postcode is a very unique piece of data and may well be used to find or identify a customer within the database. Field Name Description of Contents Data Type Type of Validation Expected Customer ID A number classifying each customer so they can be quickly identified in the database Long Integer No more than four characters to be entered. No duplicates. First Name Customers First Name Text Required. Surname Customers Surname Text Required. D.O.B Customers Date of Birth Date/Time Required, DD/MM/YYYY, Must be Todays date Address Line 1 Customers immediate local address Text None Address Line 2 Further customer address details Text None Telephone Number Customer contact number Numerical Required, Maximum 11 numbers. Must be structured in traditional way e.g. 0121 333 2233 Post Code 6 character code identifying customer address Text Required This is the Job_TBL. It contains information about all jobs the company has previously undertaken. Any new jobs that are requested and the company feels they can carry out can be added to the list through the Add a job button on the corresponding form. Job ID is now the primary key and is the data type Auto number. The date of order and expected completion date fields both have validations preventing them from carrying inaccurate data. The date of order must be either the same as or before the current date and the expected completion date must be after the date of order date. Customer ID is now classified as a number because when a customer requests a job their specific number will be entered here by the user, classifying what jobs they want completed on the system. Field Name Description of Contents Type of Validation Expected Job ID A number classifying a job identity so it can be linked to the customer ID No more than four characters. Must not exceed the number of available jobs. Job Name The title of a job None Job Description A brief description of jobs that is unclear from their name. None The Job Parts_TBL is a link table in the database. It is present because there was a many to many relationship between the Job_TBL and the Parts_TBL. This table allows the data from both of these tables to be stored together, with a one to many relationship leaving the table in both directions. This will be shown later in the Relationship Diagram. Here you can see both the Job ID and Part ID present both classified as numbers under the data type. The JobPartsID field now acts as an autonumber for this table so that the two pieces of data can be classified together. This will allow the production of receipts and other printouts to be faster and more accurate. Part_TBL This table follows the same patterns as the previous two in that the ID, in this case Part is the primary key and an auto number. The price field uses the data type currency. This will automatically fill the cell with a pound sign when data is entered. It will help in calculations within the database and provide quicker usage for the user when filling in forms. Supplier ID is present as well so that the user can see which supplier supplies them with a specific part. Supplier_TBL My final table contains information about the companys suppliers. The table contains a part name and part ID field that will link to the parts table. The data types remain similar to those in the previous tables to keep a constant style throughout the system. It helps to keep the system less complicated for the user and helps problems become apparent quickly. Field Name Description of Contents Type of Validation Expected Supplier ID Unique number to distinguish between suppliers. No duplicates. No number higher than those of listed suppliers. Supplier Name Name of supplier Required Part ID Number to classify a certain part No duplicates. Relationship Diagram Here are the five tables described above presented in their relationship format. There are only one to many relationships present because the Job Parts table has acted as a link table between the Job and Parts tables which were previously a many to many relationship.