Qualitative objectives
- A password should be used to log on to the system.
- The labels used in the items of each menu must give an idea to the user as to what process that item carries out in the database.
- It should look professional.
- The reports and forms produced should be eye catching as well as presenting the information in a clear way.
- The main menu should appear friendly and not too daunting.
Software and Hardware
To complete this project I will need advanced software and hardware. This is a list of software and hardware available at the college and at Edes where the spreadsheet will be used. I will be using MS Access and MS Word to complete this project.
Hardware at College
- Keyboard
- 233 MHz-processor
- 64MB
- 9GB hard disk
- Scanner
- Mouse
- Floppy and CD-Rom drives
- Laser jet printer
Hardware at Edes
- Keyboard
- 128MB
- 26GB hard disk
- Laser Printer
- Floppy, DVD and CD-Rom drives
- Mouse
- 800MHz
Software
The software I will use needs to be capable of storing, retrieving and amending data so that I can save files, change them if any new employees join or customers book a job and call it back up to perform queries. I have access to; MS word, MS Access and MS Excel. I am going to use MS Access as I can create a good database that is clear to show information and easy to use.
I will be using windows 2000 to run MS Access. MS Access is part of MS Office. Access is one of the most popular and powerful database packages for standalone and networked PC’s, in use all over the world in hundreds of thousands of different organisations.
The main capabilities of Microsoft Access 2000 are:
- Easy to make and use tables
- Lookups like LOOKUP, VLOOKUP and H LOOKUP
- Customized macros, which take you to other cell on different sheets.
- Cell protection to protect the cells.
- Queries to find information in the database.
- Reports and charts
Using a relational database means there will be no redundant data as there is no repeating information due to the normalisation process.
Alternatives for the system
Instead of using MS Access to make spreadsheets I could have used other programs like:
MS Word. Word is a word processing package that can be used for writing letters and then grammar and spell check. I would not use this for this project because it takes a long time setting up tables in word and calculations must be done manually. This package would not be worth using to make a database, as it would be able to handle the large amount of information. Also, it will not allow me to create relationships between tables, which means I could not run queries to find specific information.
MS Excel. This is a spreadsheet package that it is used for plotting information into graphs and charts. This would be good because I could total the amount of money earned from each job on a graph. The spreadsheet would be able to run quickly while holding large amounts of information I still could not create queries and forms, which are need for my system.
MS PowerPoint. This is a presentation package, which is used in meetings to show people information in an easy to read style. I would not use it for this project as you cannot create databases and also once information has been entered it cannot be easily edited.
Comparison of alternative solutions
This project could probably be implemented using software applications other than Access like a spreadsheet package. One solution might even be to streamline/improve a paper-based system.
Many users will decline large-scale DBMS due to cost or complexity. Different developers have different needs. Those on a Linux platform, or a Java platform, may consider very different alternative from the typical Windows-based developer.
An alternative software package would be MSDE. This is a free version of an SQL Server that is designed for use on smaller workgroups, and the licensing for it at one time precluded use on web servers. I could have used this instead of Access, as it is a way to step up to a more scalable architecture. It brings you what is in effect a real instance of SQL Server. All it appears to lack is Enterprise manager.
Justification for Chosen Solution
I have chosen Access, as the employees at Edes Removals are confident and competent to use Access.
I believe the use of MS Access is needed as is already installed on all of the Windows systems.
Using Access will allow the user to collect data in relatively simple tables, keeping organizational tasks simple.
Diary
Users IT skills
The end users have a range of computer skills. Some have advanced computer skills as they are university graduates of ICT and also have been trained to use SQL systems, which is very advanced. However, some of the older employees are not confident when it comes to using a computer. This means the spreadsheet must be made user friendly so all the employees can use it.
In order to achieve this I will use forms and make a good looking database so it will be easy to use.
I will also provide a user guide to ensure specific procedures are followed when problems or uncertainties occur while using the system.
Legal Implications
As my system includes data of a personal nature it will need to comply with the Data Protection Act 1998. The Data Protection Act places clear demands upon those holding personal data in terms of the security that must be applied to protect it. It is necessary to apply a wide range of security measures to meet these terms. I will ensure I abide by the data protection act laws by placing a password on the system so data cannot be deleted, making tables read only so they cannot be altered and design a handbook for employees so they know how to abide by the laws.
Final Choice
I intend to solve the current problems by creating a database in MS Access 2000. I will use Access as I will be able to produce forms and queries and I can sort information efficiently, unlike MS Excel and MS Word. By using Access I can meet the objectives I set for the system. I can meet the quantitative objectives by making a relational database, this means it will be a quick and easy task of finding information. The hardware that I am going to use is a, 15 inch monitor, keyboard, 233 MHz-processor, 64MB ram, 9GB hard disk, scanner, mouse, floppy and CD Rom drives and a laser jet printer. I am going to use this because it is available at home, college and at Edes. This means I can work on the project at all of these places.
In the database there will be the option of using queries to find what customers are in need of the service that Edes supplies and what men are available.
I will also use forms and reports to make job sheets for the men. This will make the job of the Transport Managers at Edes much easier because they will not have to manually produce everything. As customers book a job their name, contact details, date of job and what will be involved will be recorded. On the morning of the jobs worksheets will be printed off and given to the men. They will show the men what job they are on, where to go and what needs to be done.
The computer system at Edes is linked over a network which means all employees at Edes will have access to the database.
Data Flow for New System
DESIGN
I have decided to design the system myself and then use MS Access to make the system because this is cheaper and easier to use than an SQL System. I can make it just how the user wants it, with straightforward and easy to use features. I think this would be the best idea, as the user does not want a very detailed system. I chose this package because it has a number of very useful features that will allow me to implement a good system. These features include:
- The ability to create relational tables and use a customised main menu screen to access and update them, meaning the user wont often need to actually look at the tables, but work through the main screen which will be a simple GUI.
- The package allows the user to use professional reports, which allow data to be shown in different ways and make it more accessible.
- The user can use well designed and user friendly forms to enter data into tables, which means there are less chances that the user will make mistakes.
- By using passwords and making certain areas of the tables read-only, you can prevent the user making mistakes and deleting tables.
- Macros are small, user created programs, which automatically do things for the user, from going to another page to creating a new table.
- The ability to link to other MS Office programs like Word.
User Friendliness
I must try and make the system as user friendly as possible so it is easy to use and employees will be more motivated to use the new system. I can test the user friendliness in the testing stage by recording the length of time it takes to create a job sheet. From this I can see if the users are capable of using the system. I will make the system user friendly by using colours that are easy on the eye and easy to use navigation buttons that are clearly labelled.
Table Design
Customer Table Design
Job Sheet Table Design
Job Detail Table Design
Employee Table Design
Company Position Table Design
The above tables show the fieldnames I will input into the tables.
Using these tables I have been able to make relationships between the data.
1 1
1
1
Above is the relationship between the tables in the system. It shows how a table is related to another with the use of 1 and symbols. This shows that a customer can have a lot of work being done at once with many different men of the jobs.
The diagram below shows how the table will be linked together.
Entities
An entity is a thing of interest to an organisation about which data is to be held. The entities in my project are:
COMPANY POSITION (company position ID, company position)
CUSTOMERS (customer ID, customer name, address line 1,2,3, post code, contact name, contact number)
EMPLOYEE (employee ID, name, address line 1,2,3, post code, telephone number, mobile number, hourly rate, company position)
JOB DETAIL (job number, employee name, hours)
JOB SHEET (date, time on site, customer, job description)
Testing
For this section I will explain how I am going to test my prototype. For this testing I will design a test plan that will show how I will test data.
Testing Objectives
The objectives of testing are to prove that:
- All parts of the system work correctly no matter what data is input.
- All parts of the system as originally specified are present.
Test Plan
I will be testing my system in many different ways. I will test objects as they are created, this is called module testing e.g. testing macros after they are recorded. I will test each menu and command button to ensure they work under different circumstances. I will test incorrect and extreme data. I will do system testing by running a whole sequence of events and making sure it all runs smoothly. Finally I will do end user testing to ensure the system meets the end users requirement as specified in the specification.
The following tests are a sample of the system testing which will include at least one for each test strategy mentioned above.
For user testing the user will test the system. This may expose things that the user requested that are not in the table, or things that don’t work as he expected. The user will test the system using the tests I use.
For module testing I will thoroughly test objects as they are created. I will test the important parts of objects like the extreme cases e.g. deletion of an object. I will also check for valid and invalid data.
Once the module testing has finished I begin system testing. I will test my system with a realistic volume of data. I will test calculations, command buttons that perform updating and commands that open a form with data already in it.
Query Design
Table Design
Related to:
Inputs, Outputs, and Processes
This section will show the procedure that will enable me to specify the logical relationships in my database. I must analyze how a given component should ideally function. I will do this by showing how the processes that will be performed use a set of specified inputs to produce desired results or goals i.e. outputs.
My system will involve lots of inputs when my database is being set up. This will include employee details, customer details, and job details etc. There will be ongoing inputs as customers will request more work and new employees will need to be added. The transport managers at Edes are responsible for hiring new employees so they should enter the employee’s details when they have been hired. The higher managers at Edes are responsible for dealing with the customers so they should be responsible for entering customer details. When data is added into a form the tables of information are automatically updated. The data that has been entered will be verified and validated to check it is correct.
Once the data has been entered it needs to be processed. I aim to make several processes that will filter and sort the data into easy to read and specific information for the end user. The processes will be from running queries and the outputted into reports. In the database I will include queries that will search for employees available to work. It will then sort alphabetically and it will be validated so no mistakes are made.
This computer system contains many outputs from forms and reports that are printed and also displayed on screen. The system contains no sound outputs, as the end user has not requested it.
The outputs will include reports that will show what customer has ordered Ede’s services and what the job involves. This will allow the end user to decide on how many men will be need to complete a job. Once this is done a quote can then be sent to the customer with estimate figures of how much the job will cost. There will be reports of employee’s names and phone numbers so they can be easily contacted when needed. There will be job sheet forms that can be printed off and given to the foreman of a job. These job sheets will show what men are on the job, what needs to be done and where they have to go.
Normalisation
Normalisation is the process of refining the structure of a database to minimise data redundancy. When a database has been normalised, it is said to be in normal form. There are three normal forms:
A database is in first normal form if there are no repeated fields. That means that there must only be one field for each item of data that I want to store.
A database is said to be in second normal form if it is already in first normal form, and if it has no fields that aren't dependent on the whole of the key.
A database is in third normal form if it is already in second normal form and if it has no non-key dependencies. A non-key dependency means that there are no fields that are dependent on other fields that are not part of the key.
Form Designs
A form is an easy way for my end user to enter information. Forms are a good way of entering data as they present it in a user-friendly appearance. The fields can be chosen so specific data can be entered. Validation can be performed on fields e.g. fields with dates must have the correct amount of figures (20/09/2002). Forms can have sub forms so more detailed information can be made. I have drawn up rough designs of the forms that can be used in my system. These designs will help me in the implementation stage. I have kept the design of the forms similar and consistent so the user will not be confused when looking at different forms.
Employees Detail Form
This form will show all the employees and their personal details. All new employees can be recorded through this form.
Customer Detail Form
This is a simple form that allows the user to check the customer details and also add new customers.
Job Sheet Form
Job Detail Form (job sheet sub form)
This is a simple form that will be linked with the job sheet.
Menu Design
To make this project user friendly I need to make the navigation through the system clear and simple. The majority of employees at the firm have basic ICT skills and limited knowledge of computers. I want the system to be easy to find forms and reports, add new information and print the needed information. I have designed a main menu, which will create links to the forms, reports and queries in the system. To do this I will use the switchboard feature as it allows me to quickly and easily create a menu system for the whole database.
Main Menu
The background colour of the main menu will be army style green to match the company’s colours.
Form Menu
Report Menu
Reports
The main output for my project will be from reports. I will discuss the use of reports with the end user, as I believe reports are a better out put then forms. Reports are made to be printed on A4 whereas forms are a different shape and do not print clearly due to coloured designs.
Edes Removals UK Ltd
DATABASE SYSTEM DESIGN APPROVAL SHEET
Bob Piggott,
If the design meets the requirements and the objectives set then please sign below in order for me to continue to the implementation stage of the project.
Signed
Table design:
FIELD NAME DATA TYPE DESCRIPTION/VALUE
Table Entities e.g. tblEmplyoee(employeeID, first name, address1, address2, address3, telephone number, post code,
Menu Design
Entity relationship diagram
Report Design
Form design
Query design
Implementation of the menu system
BIBLIOGRAPHY
