- Must support floppy disk backups
- Must be a commonly used format (e.g. must be able to be read on most machines, etc.)
Feasibility study
Although the computerised process would save time in the long term, there is the short-term cost of buying and setting up the computer equipment. However, I think this would save the company time and money in the middle/ long term. To keep the firm efficient whilst it expands, computerising the administration system would free up staff time, making the workforce bigger than it needs to be. This computerised system means the firm will not need to employ a secretary or any other manual filing system, until the database grows too large to manage. This is all part of the process of mechanisation, and can’t be helped. If the firm expands too much without checks being made on efficiency, a diseconomy of scale may well result. This would increase running costs, and decrease profits over time.
In all, I think the middle- and long-term benefits far outweigh the short-term costs. There is the possible problem of Tod having to personally fill in all the records himself, since he is the only one with the knowledge of who gets the 5% discount or not.
The system would result in a much better ‘reminder’ system, so that more customers will potentially come back to the firm. The only cost would be the cost of the computer equipment, and the cost in time and money of maintenance in the future. The system runs basic hardware and common software, so technicians able to deal with the system would be common.
Why use a database?
Databases are very practical methods of data storage, because they provide a very simple, uncluttered GUI for a novice user, but can prove very powerful tools for searching and the like for the advanced user. This is why I decided not to use Excel; the interface is comparatively complex in relation, and may confuse the first-time user.
Also, databases allow for many more types of record to be shown – there is the option to publish the results in an individual record card, which Excel cannot do.
Excel is not totally useless for this project; Tod might be able to make use of its graph-making and accounting features, especially as the company grows and needs to be more in control of its productivity and expenses.
Hardware Specification Required
At the moment, we are going for a very cheap, reliable system, because of the low system requirements of the programs involved. Following the recent leaps in processor speed, and the advent of new technology like the Pentium 4 series, you can purchase new Pentium 3/ duron processors at very low prices. Because of rapid increases in new CPU speeds, we can now set up a very competent system very cheaply. RAM has also become cheaper, which is just as well, because we’re going to need 128 Meg in each machine in order to maintain a good level of stability, and safely clear the minimum requirements of Windows 98. A 128 Meg RAM chip costs about £20. We won’t bother with DDR Ram; SDR is cheaper and has no noticeable downgrade in performance, as well as the added cost of getting a compatible motherboard, since the technology is relatively recent. I would recommend buying the parts from a registered computer fair, rather than a large- scale retailer, because larger shops are only interested in £700 home systems and upwards. The older parts used in these systems enable a complete computer to be built for under £180.
CPU: 450-mhz p3 – Less than £25 if used.
Hard Disk: 10-gig ATA 133 – Surpassed, but cheap and plenty of room for OS and database.
RAM: 128 MEG, SDR.
Motherboard: Old ABIT models have legendary reliability. About £50 used.
Case: Minitower case. They’re cheap (ish) and save space on the desk. About £15.
Graphics card: Cheap, low-end versions cost about £10, or some motherboards carry one on-board.
For this sort of application, we really have no choice but to use a database/ spreadsheet program, because they have a better capacity to index and sort data by name, price etc. more easily than having each record on paper, for instance, or even entering the information in a word-processing application. For example, it’s a lot easier to add a record in-between two others, because a Database program like Access will automatically update other fields, like the “Autonumber” field, for example. It’s also a lot easier to move the records down in the first place, because the database program has many spreadsheet features. The screenshot here shows the option clearly visible. However, performing the same operation in word can send records all over the page.
Entity Relationship diagram
This illustrates the relationships the solution will need to take into account. The relationships should ideally be linked in real time, so that changing the record in one table will automatically update the fields in another table.
Database Notation:
ACCOUNTS (Customer account ID, No. of cars name, company address ,company postcode, 5% discount?)
CARS (Customer account ID, Car ID, Model name, Reg number, Last tyres date)
CURRENT JOBS (Customer account ID, Car ID, Date in, reg number)
TYRE STOCKS(Tyre ID, Number left in stock)
Data dictionary
Car Database
Example…
Accounts Database
Example…
Current jobs Database
Example…
Tyre stocks Database
Reports
Invoice report
‘Cars due in’ report
Queries
Validation rules
To ensure the smooth running of the database, it is important to ensure that errors are found and corrected by the user. To ensure these errors are detected, the following measures have been implemented.
Format checks – all dates input into the system must be in the format
DD/MM/YYYY. For instance, a date like 02/06/2002 will be accepted, but anything not in this format or a letter will create the following error message…
The ‘short date’ criteria means data is only accepted in numerical
A similar set of rules apply for the car registration numbers. They must be in the ‘new’ style European format, or this error box will appear:
Range check – A date from before 1950 is very unlikely to be valid. Since Tod’s tyres hasn’t been around very long, a rule preventing dates from before 1953 being put in is useful.
Query Design
Firstly, we need a query to highlight cars which are due in for new tyres, i.e. those which the ‘last tyres date’ was over a year ago.
To do this, we can set up the following query.
Standard letter query
Fields displayed = Car ID, Customer address, customer postcode, date due in
Criteria for ‘Date due in’ – date() + 365
This query will produce a list of the cars which are due in for new tyres; this list can then be used to set up a mail merger, so that the customer’s address, postcode and the registration of the car will be inserted into the letter. An example of this letter is attached at the back of the report.
An example of this could be given by a car with a ‘last tyres date’ of 02/05/2001. This would be picked up by the query, and entered into the mail merge letter with the owner’s address and postcode.
Dataflow diagram
Level one diagram
Why use a database?
Databases are very practical methods of data storage, because they provide a very simple, uncluttered GUI for a novice user, but can prove very powerful tools for searching and the like for the advanced user. This is why I decided not to use Excel; the interface is comparatively complex in relation, and may confuse the first-time user.
Also, databases allow for many more types of record to be shown – there is the option to publish the results in an individual record card, which Excel cannot do.
Test plan
Testing report
- On opening the database, the user is asked for a password before the database will open. If the password specified is incorrect, the database will refuse access:
- Extending an entry in the ‘accounts’ field can display data for the cars owned by a particular account holder:
- Updating the ‘last tyres’ date in the table above has updated the data in the original table, so that one table can be used to change or update another:
-
Searching for ‘Cross’, the name of one of the customers, brings up the file for Mr. Cross’ account.
-
The mail merge correctly inserts Elliot Cross’ Name, Address and Postcode successfully into the table
-
The mail merge also inserts a final paragraph, dependant on whether the account holder is entitled to a 5% discount.
- An invalid car registration number is picked up by the validation rule
- The validation rule stops the user from inputting a very suspectable value, a date before 1953 for a new car
- Using the switchboard to increase tyre stocks (e.g. following a delivery of fresh tyres) automatically updates the ‘tyre stocks’ table.
The table is updated successfully, which suggests the switchboard has allowed for real-time editing of the database
- Because of the following validation rules in the ‘tyres used’ field:
Putting in a value greater than 5 or lower than 1 produces the following error message:
Evaluation
The system has left Tod with a powerful database of stock and account details. This has completely computerised the system, making it more efficient and productive. This has meant addresses are accessible at the touch of a button, saving time. The added increase in staff morale has made the firm more efficient, as happier workers are more productive. This increased efficiency will save the firm money in the short run. This means extra profit for the firm.
However, improvements could still be made in the future, including these points:
- Connecting the system to the internet, allowing Tod to simply email reminders to interested clients. This would make the entire system more reliable and efficient, since labour costs are reduced by eliminating the need for a clerk to put the letters in envelopes, etc. It also reduces the possibility for human error, as long as the details are correctly input in the first place.
- Upgrading the computer to make the system more stable. More RAM in future upgrades may prove particularly useful. Also, over time the company may wish to transfer the details into a bespoke system. This would maximise processing power, since RAM is being dedicated only to the basic functions of the database, and not unnecessary functions such as a windows shell. Also, it would reduce the possibility of time being wasted by employees on unrelated applications, like games on the computer.