Here is a list of the items that I have to design:
- Design an order-form for the customers with 6 different products. This is necessary, as it will act as the data capture form for the data to put in the database.
- A database will have to be set up, the fields and names will have to be made. They will have to be validated and verified. The database will also produce reports for the manager.
- Invoices will have to be designed and will be sent to all customers by mail merge.
- Letters for overdue payments will have to be made, also with mail merge.
My aims for this coursework are as follows:
- To complete the 4 above tasks (database, order-forms, invoices and letters).
- To produce these systems concisely, neatly and accurately with good presentation.
- To produce these systems in the course of 2 weeks.
- To keep within the budget of £25,000 for software and hardware for the computers in the network.
- To publish the program to another company, adjusting my system to their requirements.
Design of Solution
The flow chart shows how the computer system will work. It begins where the data is sent by mail/ phone/ fax or email. The data is then keyed in by one of the staff as he/she is in contact with the customer. This stage must go first in order to obtain the data. As the data is keyed in, it is first validated. If there are any corrections to be made, i.e. data was keyed in incorrectly; it will be repeated by referring to the errors on the screen. After each piece of data is validated, it is verified by repeating the data to the customer as they confirm the order. This validation and verification should be carried out immediately after the data is keyed in, as it will need to be accurate, as it will be saved onto hard disk. After the data has been inputted, it immediately needs to be saved in case the computer shuts down, i.e. by a power cut.
Then invoices to the customers will be sent which will function by mail merge. This is the first document that needs to be sent, as all the customers must receive an invoice.
The next step the staff would do, would be to perform a search for overdue payments and then these records will be saved to another file. This database file will then be linked to a mail merge program to print the overdue reminders. The search needs to be performed firstly in order to make a separate database for overdue payments that will be saved. Once it is saved, it will be mail merged with the letter of overdue reminder.
The main database will then have to be sorted by customer number in a logical way. This will sort the database, which will then be transferred into a ‘manager report layout’ design, which will be for the manager. This database in a new format will be printed for the manager. The manager report won’t have been produced as many times as the invoices, so this is the last stage.
Implementation of solution
i). Resources
The hardware that I used is as follows:
24 of these computers would have to be purchased.
The software that I used is as follows:
25 of each of these software packages would have to be purchased.
Budget: £25,000
Price of 1 computer £649.99 x 25 = £16249,75
Price of MS Office £179.99 x 25 = £4499.75
Price of Clarisworks 3.0 £179.99 x 25 = £749.75
Price of Windows ‘98 OS £79.99 x 25 = £1999.75
£1426.75 £23499
Price of software + hardware for one computer £1426.75
Price of software + hardware for 25 computers £23499
£25,000 - £23,499 = £1501
Money left over form expenditure £1501
The hardware and the software were economical enough to keep within the £25,000 budget. £1501 was left over which was adequate enough for the company. The managers and directors were happy and satisfied that I hadn’t spent more than the limit.
The computers that I purchased were relatively cheap and at the same time they were quite fast. There was enough money left over from the expenditure of the hardware to purchase quality hardware.
The computers have a processor speed of 166mhz, which is quite fast. I is an average processor in the market, but not obviously as fast as the 450mhz ones. However I don’t think that 450mhz chips were suitable as firstly they were quite expensive, and secondly, they don’t make too much difference to the speed of the computers. The Dell Computer Company was also associated with quality, so it seemed good to purchase Dell computers. The 32mb RAM meant that graphics and large amounts of data could be handled. Also, when designing the order form and invoice, graphics and presentation could easily be processed. The 3.7GB Hard Disk meant that there would be sufficient space for the database, which would be very large. Multimedia features were also available, such as the 2mb Graphics Card, meaning graphics and presentations could be made. The CD-ROM drives and the 3½ floppy disk drives meant that work was portable; I could transfer saved work from one computer and then take it to another computer. The modem also meant that the Internet was available to download information or graphics and also it would allow orders from any customers via e-mail.
There was enough money left to purchase Windows 98 for each computer that would act as the operating system for each computer. It wasn’t too expensive - £79.99 and it did provide quality. I also felt that it was needed, as many new software titles are released quite often. This system would last for a long time, so the latest software would be required. The word processor (MS Word in the MS Office ‘98 package) was one of the best around and I was familiar to it. However I also purchased Clarisworks v3.0, which was cheap, and also I was familiar to the database, (Microsoft Access on MS Office was very hard to use). My familiarity of Microsoft Word and Clarisworks was my main reason for choosing them. There was no problem in converting the database from ClarisWorks into a readable format for MS Word (for mail merge) as the database was saved in a dbf format (*.dbf) which was readable by Microsoft Word.
ii) Data Collection, data capture and input
My data capture form can be found in the appendix. My problem requires me to obtain the data from the customers which will be done by the order form (or data capture form) of ‘MailSport’.
My data capture form had to be colourful, vivid and eye-catching in order for the products to be advertised and ultimately purchased. Therefore many colours were used, as well as gradient effects in order to make the order form effective. Presentation of the products took up to two thirds of the page so that the products could be advertised.
The order form was a cut-out section at the bottom of the page. The questions were spilt into 2 categories; personal and order details. The personal details included name, address, county, postcode, date and telephone number. The order details consisted of the code (of the product), the description (of the product), the colour, the size, the quantity and the price. Enough customer information was obtained so that they could be contacted in case of shortage of stock or any problems.
The data was keyed into the computer, as it was the most accurate form of input. There were other options for inputting the data, such as Optical Character Recognition (OCR) via the scanner. However, I didn’t choose this method as some orders would be on the phone and also the number of errors is quite high. The operator would obtain the details from the customer on the phone and repeat all the details at the end, which would act as verification.
iii) Data Verification and Validation
Verification
Verification, the manual operation to check that data is correct was performed by cross checking. After receiving the customer details, the operator would repeat all the details to the customer and he/she would confirm the details. That would be the case if the order were by phone. If the order were by mail, email or fax, the customer would be phoned a working day after receiving the order. I could have performed proof-reading or double inputting, but I felt that these methods would be inaccurate. This is because to obtain the most accurate data, the customer has to be contacted – you have to directly speak to the customer. Also, these other methods are expensive.
Validation
Validation, the check to make sure that data is realistic, was performed in ClarisWorks database and Microsoft Word. Many checks were performed in order for the data to be as accurate as possible.
A character type check was used for the fields, such as customer number. Table look-ups were used in my database such as in size and colour. In ClarisWorks, these were known as value-list. The spell check validation was used for the documentation such as in the invoices and letters. Presence checks wee used for most fields, as most of the data was required. Range checks were used for quantity, as to be realistic; someone wouldn’t purchase more than 100 T-shirts.
Data and Program Structures
These are the fields in my database:
My database had many validation checks in order to make it as accurate as possible. The nest paragraph deals with the table.
The customer number was the key field; the datum was unique for each customer. (It was made unique by a serialised number.) This was needed so that the records could be sorted. Most of the fields had to have a presence check (‘must not be empty’) as the data or information was necessary to process customer orders. The only fields without the presence checks were the calculation fields and also the order 2 detail fields. They did not have to have a presence check as not all customers would have more than one order. The county field had to have a table look up of all the counties in England – the mail order offer was only available in England. Most fields had a text type field as most of them contained text. Even the telephone numbers were text type fields. This was because when you attempted to type in 0181… it would print 181… for a number field, so a text type field was used. Value lists were used for colour 1, colour 2, size 1, size 2, price of order 1 (single) and price of order 2 (single), as they could only be from the value list. A range check was used for date of order, that it should be more than 1/1/98 and less than 31/12/02. The company started to trade from 1st January ‘8 and will stop trading on 31st December ’02. Many calculations were also used within the database. The price of each order was worked out by multiplying quantity by the price of single item. Also the total charges were worked out by adding the two price orders together and then adding £2.95 for the postage and packaging. Calculations were also used for date of latest payment and balance. Range checks were used for the quantity, as to be realistic, someone wouldn’t order more than 100 T-shirts.
Manager Reports
Two kinds of manager reports were made:
-
A search would be made for all the customers that haven’t received an invoice. This would be performed by searching for orders made after the most recent Monday. (The invoices were all processed on Monday, and so the invoices are made each Monday.) For example, say if invoices were last processed on 7/11/98. Today is 14/1198 so the search would be performed today. The search would look for all orders received since last Monday.
The user would input the most recent Monday. This searched database would then be saved into a different database name with an extension *.dbf. The database would be saved as ‘invoice.dbf’. Subsequent files would overwrite this file to save hard disk space.
-
The other manager report would be for the weekly overdue account system. This would be performed by firstly putting the database into the manager report layout, then performing a search. This search would be for overdue payments when the balance is more than 0 AND today’s date being greater than the date of latest payment. The search was then sorted in descending order. This seemed logical, as the customer’s with the greatest overdue accounts should be bought to the manager’s attention first. The database, in the manager report layout would then be printed for the manager. This database would then be saved as overdue.dbf. Subsequent files may overwrite this file to save on hard disk space.
Invoices
Invoices would be made for the customers who haven’t received it. The invoice.dbf database would then be mail merged with a Word Document containing the invoice layout. Microsoft Word is able to read *.dbf files, that’s why the database was saved into this format. After the database is mail merged with the document, they will be printed and sent to the customers.
Overdue Letters
Similarly, an overdue letter would be mail merged with the overdue.dbf database that would send the letters to any customer who hasn’t paid their bills.
Output Format
Manager Reports
The manager reports were made for the manager so that he could find overdue payments. The fields were split into 4 groups: personal details, order 1 details, order 2 details and finance details. All the details were needed, as they would have to be put on the overdue letter, the name, address etc. The order details were also required, as they would have to be bought to the customer’s attention. Refer to the manager reports in the appendix.
Invoices
This contained most of the information from the fields of the database. The invoice functioned by mail merge. The invoice contained customer number, dates, quantity, item description, charges, credits and balance. The invoice was colourful and vivid to have an effective look. It contained the company logo and many other presentations to make it colourful and eye-catching. The gradient effects made the invoice look elegant and professional. The company logo and name was put on as well as how to contact the company for any enquiries. The Girobank payslip was at the bottom, which was professionally made on Microsoft Word. Also, on the back of the invoice, enquiries and help were available on methods of payment, also with a credit card payment at the bottom. I think the invoice was made very realistically and professionally. Refer to the invoice in the appendix.
Overdue Letter
An overdue letter was also made. It had the company logo on, the address and contact methods on it. It again looked very professional, with gradient effects to make it look effective. It did deliver the message clearly, ordering the customer to pay up. The text did patronise the customer to hurry their payment. The letter did deliver the point and gave enough information for the customer to respond and also understand it. Refer to the overdue letter in the appendix.
Order Form
The order form was made with great deals of expertise presentation, colour and pictures. This was necessary in order to advertise the products. Two-thirds of the page was taken up by presentation of the products to persuade potential customers to purchase the goods. The order form at the bottom was small so tick boxes couldn’t be used. Alternatively, a table was made that made sure that the user would write down the information correctly and mistakes would not fall through. Refer to the order form in the appendix.
Testing of Solution
For my testing of solution, I am going to test the validation in my database. Such examples are character type checks, presence checks and range checks. I am going to test this as in order to make sure data is accurate, the validation must be checked. Also, I am going to test the documentation by giving both documentation (user and technical) to 3 people. 3 people will access the technical documentation and another 3 will access the user documentation. They will access how good they are and how easy to understand they are.
User Documentation
The first person said that the documentation was very helpful and well presented. The second person said that the documentation didn’t include everything, such as how to change the field type. The third person said that the user documentation was a bit too brief, more explanation of why things happen must be required.
Technical Documentation
The first person said that the technical documentation was very well presented and concise. The second person said that a few more screen shots were required, otherwise it was good. The third person said that it was very good and it goes into quite some depth.
Documentation of Solution
Evaluation of Solution
In the ‘Investigation & analysis of problem and specification solution’, I stated my aims. To conclude my coursework, I will access how well I did in them, by referring back to them.
I initially had to do various graphics and database handling, i.e. communicating and handling information, and here are my aims and my evaluation of them:
- The order form that acted as the data capture form was done very well, in my opinion. It was well presented and enough information could be collected to build a database. The questions were unambiguous and closed (i.e. not open-ended) so they could be answered quickly and analysed in a database. This meant that a good order form was designed.
- The database was set-up where information of the customers was held. The database was designed and accurate data was obtained through validation and verification checks. Reports were generated for the manager, which were helpful to him and he was very impressed and satisfied. Therefore, I can conclude that I fulfilled my aims for the database and reports well
- Invoices were made and were sent to the customers who had recently ordered something. The invoices were vivid, colourful and also contained all the necessary information of the customer. The invoices were made very well, so I have fulfilled my aims for the criteria.
- The letters of overdue payments were successfully made and they functioned by mail merge. The wording of the letter was good, with no spelling or grammatical errors. The letter did patronise the customer into paying the overdue bills. I did fulfil my aims for creating this letter.
I fulfilled the company objectives. However, I did also set some personal aims:
- I completed the 4 items that I had to make for the company. I fulfilled this aim.
- The items were concisely structured as well as neatly presented and accurate. I fulfilled this aim.
- I tried to keep within my limit of a 2-week period. Eventually, I did keep within this constraint. I did fulfil this aim.
- I aimed to spend no more than the £25,000 budget. I did so, leaving £1501. The company was pleased that I hadn’t used more resources. So I did fulfil my aims for this criteria.
-
However, the one thing that I failed to do, was to publish this system to another company, adjusting it to their needs, provided that they were a mail order company. I found that most of my time was spent on working on the MailSport products, while another company called Sohsys had shown some interest. I had no choice but to reject this offer due to the time constraint. I therefore failed on this criterion.
On the whole, my project was a success. If I could do this project again, I would use my time more willingly and more qualitatively. I find that for the first few days of my project, I barely do any work, but then all this has to be changed when I have to work very hard through the end of the time period. So in future, I will use my time more efficiently and concisely.
On the whole, my project was a success.
By Sohaib Nazir
