Creating a database system for Mr Azizur Rahman, Mr Bilal Sheikh and Mr Iman Ahmed who are part of their own computer-upgrading business, called N.E.T
CONTENT TABLE
ANALYSIS 3
.1 INTRODUCTION AND BACKGROUND INFORMATION 3
.2 THE CURRENT SYSTEM & PROBLEMS WITH THE SYSTEM 3
.3 QUESTIONNAIRE 10
.4 OBJECTIVES OF THE NEW SYSTEM 10
.5 DATAFLOW DIAGRAM OF CURRENT SYSTEM 11
.6 INPUT REQUIREMENTS 12
.7 STORAGE 12
.8 PROCESS REQUIREMENTS 12
.9 OUTPUT REQUIREMENTS 13
.10 DATA DYNAMICS 13
.11 SUB-TASKS 13
.12 PERFORMANCE INDICATOR 13
.13 HARDWARE & SOFTWARE REQUIREMENTS 14
.14 SYSTEM OVERVIEW 15
.15 USER'S SKILL LEVEL 15
.16 PLAN OF PROJECT COMPLETION 15
.17 CRITICAL PATH DIAGRAM 16
2 DESIGN 18
2.1 POSSIBLE SOLUTIONS 18
2.2 FINAL CHOICE OF SOFTWARE 18
2.3 DATA FLOW DIAGRAM OF PROPOSED SYSTEM 19
2.4 NORMALISATION 20
2.5 TABLE DESIGN 22
2.6 QUERY DESIGN 24
2.7 FORM DESIGN 26
2.8 REPORT DESIGN 27
2.9 MACRO DESIGN 27
2.10 SECURITY & BACK UP 27
2.11 GANTT CHART 28
2.12 TEST STRATEGY 28
2.13 TEST PLAN 29
3 IMPLEMENTATION 33
3.1 COMMENTARY ON IMPLEMENTATION AND IMPROVEMENTS 33
3.2 IMPLEMENTATION PLAN 34
4 TESTING 71
4.1 IMPROVED TEST PLAN 71
4.2 COMMENTARY ON TESTING 76
5 EVALUATION 90
5.1 LIMITATIONS 90
5.2 FUTURE ENHANCEMENTS 90
6 USER MANUAL 91
Analysis
.1 Introduction and background information
As part of my A2 ICT project I will be creating a database system for Mr Azizur Rahman, Mr Bilal Sheikh and Mr Iman Ahmed who are part of their own computer-upgrading business, called N.E.T (New Emerging Technology) computer upgrades.
Mr Azizur Rahman will be my main source of contact; he will be supplying me with the relative information and documents. Using the information a prototype project and design will be revealed to Mr Rahman and using his feedback the final system will be developed.
N.E.T computer upgrading business is relatively new, started about a year ago; it's a semi professional business with the workers being part-time students in university and college. In a recent interview with Mr Rahman equal partner and worker for N.E.T, he explained that they provide assistance to at least 4 customers a month. He explained that because of their studying commitments they do not have the time to build their own database system.
N.E.T basically works as the middleman. Information s gathered by then on what the customer requires, then they order the appropriate components and build or fix the customers computer.
In the current process the members have to record customer details, the problems they have with their computer and what they want the computer to perform, these details are at the moment recorded on paper-forms (see next page). In addition they have to work out how much money will take to buy the appropriate components for the upgrade, plus make additional calculations on their fees. All of the cost must be added to see if it falls in the customer's budget. The customer gives them a deposit, and then they build the specified computer.
.2 The Current system & Problems with the system
To find out all the information I require to build the system I first created a questionnaire to give to the end-user to fill in.
Furthermore I interviewed the user in how exactly his system works, so he can explain it to me face to face so that it is more understandable. I took notes of these conversations.
See next page for the scanned questionnaire given to the end-user:
The current system is all recorded manually on paper. I will now explain step by step on how the current system works. First of all the members of N.E.T have organised a recording sheet on which customer details, user requirements and faults with current computer can all be recorded.
Below is the is scanned recording sheet on Customer Details:
Below is the scanned recording sheet on Current computer spec+ Faults with current PC+ Possible Solutions/User Requirements:
In order to keep track of customers and their orders all information is currently recorded manually and kept in a file. The customer can make several orders depending on how much he or she wants to update their computer (this must be with in their price limit). This order is then sent to the supplier (I.T. Computer Solutions Limited).
Their current system of recording on these sheets, shown on the previous pages has many problems these are as follows:
Time consuming: The current system takes the workers around 15 minutes to record all the customer details, requirements, problems and price. This is half due to the fact that they do not have a form already organised to record the customer details.
Human Errors: When recording customer details/requirements the technicians try to note down what information they need from them, this is usually brief and unprofessional plus the writing in the recording sheet is usually not understandable, as the individual boxes are too small to record, thus human error occurs in gathering the information e.g. spelling.
Furthermore when making the customers current computer spec, the table in which they record it does not contain hardware software RAM etc. They do not have a form with all the questions already written. As a result they miss out some of the vital questions, which leads to calling back to customer to get their full details. This results in a delay in the ordering of the new components and in effect delays the up grade of the computer. In addition the calculations made to work out the overall cost of the upgrade and to see whether or not it meets the price limit set by the customer.
Generates Excess amount of paper: The current system uses too much paper as the recording of the customer details and orders wastes unnecessary amount of paper.
Tracking Customers: The un-organisation of the file in which customer details are kept, makes tracking customers time consuming.
Data Protection: All customer information including contact number and address are kept in a file where other customer details are also kept. This file is not well protected; the file is placed in a drawer, from where anyone can access it.
Data Storage: No back up copies of the customer/order details are made all information is kept in the file.
The calculations, which are involved in their current system, may also cause error. At the moment the N.E.T staff works out the total cost of upgrade by first adding the prices of the components ordered. They then work out 35% of that price then add it on top of the original total cost (this is their labour cost). The calculations that they make are done on a separate piece of paper, usually when they are searching on the Internet for computer components. A scanned copy of one of their customer's total price is shown on the next page:
As the source document above shows the current system of calculating the customers total cost of upgrade including labour cost is very unorganised.
The calculation is done on a separate piece of paper; the only way to relate this information to the customer is by the name. This would delay the process of acquiring the data later on, as this calculation sheet could get misplaced.
In addition all the calculations looks as though they have been rushed thus the accuracy of the data is in question.
Furthermore if the customer requires more components the technician would need to edit their information, place their new component price on to the list, this would make the layout of the information untidy, as the technician would either have to fit in the new order in to their current sheet or start on a brand new sheet. This would be time consuming or the data could even be indecipherable due to the frequent changes.
Mr Rahman feels that the introduction of a computerised Recording System could tackle some or all of these problems. He is hoping for a system that can organise the customer details and order forms efficiently and mort accurately. Also as the business grows, in the future paper-based system will not work.
.3 Questionnaire
See Appendix--
.4 Objectives of the new system
In a recent interview with Mr Rahman he has requested that some objects must be met by the new system. The following are the objectives that he requested:
* The database should be able to locate any records, which are relevant quickly and efficiently as and when it is required by the end-user.
* It should be relatively easy to add, delete or make new records. For example add or delete a customer form the database. Also make simple changes in the database, such as correcting the name of a customer.
* The database should be able to show customer details in an understandable form plus the problem that the customer's current computer has, what upgrade it require. Plus the price of the upgrade.
* The system should be able to record customer details, computer specification, and fault with the customer's current computer and price plan.
* Should be able to create queries containing relevant data of customers and orders.
* Calculate the total amount, which has to be paid to meet user requirements.
* The database should be able to produce an order form that can be printed out and sent the supplier.
* Enforcing referential integrity so Access will not allow the user to enter data that does not exist in the database. For example making a User specification for a customer who has not been entered in the customer details table.
* Validation techniques should be used to improve efficiency and accuracy of system.
* Should be able to calculate total cost, budget forecast or expenditure.
.5 Dataflow diagram of current system
. Recording Customer details, faults and requirements:
2. Quotation:
.6 Input Requirements
The following are the details that should be inputted into the new system:
* Customer Details
- Customer ID
- First Name
- Second Name
- Address
- Gender
- Contact number
- Technician Initials
* Current Computer Specification
- Computer code
- Hard Disk
- RAM
- Drives
- Printer
- Monitor
- Processor
- Speakers
- Fault with current computer
- User requirements
* Component Details
- Component Code
- Component Name
- Component price
- Component description
.7 Storage
The following information will be stored by the new system:
* Customer Details
* Customers Current Computer Specification
.8 Process Requirements
The following processing will than take place after the input data has been entered and stored:
* Input Masks will be used, as it will allow a patter of all data to be entered in a specific field. For example for contact number the following input mask can be used (9999) 00090009.
* Display control will be used, as it will display all the relative information that should be entered in a specific cell. For example for technician name, the cell will have a list of all the names to choose from.
* Calculating total cost of components.
* Calculate overall cost of the upgrade including labour cost.
.9 Output Requirements
The following output requirements will then be displayed on the monitor or can be printed out if the user feels the need to:
* Report on which customers have spent the most on components.
* Report on which customers have come to N.E.T previously.
* Report on which components have been ordered/specified by customers the most.
* Report on which components are most expensive and which are least expensive.
* Report verifying all the components ordered for a customer including price.
.10 Data dynamics
The component details will be updated every 2 weeks, as new components are being developed and become available all the time. The database system will remind the user of their updating obligations.
Old Customer Details information will be deleted from the main customer details table, to a secondary customer details table, where it will be stored for future reference.
.11 Sub-Tasks
) Customer Administration
.1. Add new customer
.2. Edit an existing customer
.3. Delete an old customer
2) Component Administration
2.1. Add new component
2.2. Add price of component
2.3. Add picture of component
3) Customer specification Administration
3.1. Add customer current computer specification
3.2. Add fault with current computer
3.3. Add possible solution/user requirements of new computer
4) General Administration
4.1. Update component details
4.2. Calculate overall price of upgrade
4.3. Delete unwanted records such as previous computer specification
.12 Performance Indicator
The following performance indicators will be used to see if the system that is being created is considerably better than the old system.
The performance indicators will be divided in to qualitative and quantitative indicators. These indicators will be used to check that the new system meets all the user requirements:
Quantitative
* The user should be able to enter new customer details in less than 30 seconds.
* The user should be able to record all relevant data e.g. customer details, current computer specification, problem with current computer, possible solution, overall price of upgrade etc. All of this should take no longer than 5 minutes to record, depending on fluidity of conversation.
* It should be possible to locate any record for example 'contact number' in less than 30 seconds.
* It should be able to print out a report in less than 30 minute, to give either to the end-user as evidence or to be used as backup/filed for future reference. For example an invoice displaying cost of components, total cost of components to show to the customer.
* It should be possible to generate reports with different criteria in less than 45 seconds. Output requirements indicate the possible report criteria, which can be generated.
* The database should be able to produce an order form that can be printed out and sent the supplier.
* Should be able to enforce referential integrity so Access will not allow the user to enter data that does not exist in the database. For example making a User specification for a customer who has not been entered in the customer details table.
* All the information in the database should be consistent. For example customer name should not change in other tables. Correct information should be displayed.
Qualitative
* The database should be simple to use so avoiding complication when using the system.
* Switchboards and macros should be implemented as a graphical user interface. So to guide the user through the system.
* The system should not overload the user with too much information. Simplify the information so that it is easy to understand and to access.
* The user should be able to locate information with out any difficulties.
.13 Hardware & Software Requirements
Mr Rahman already has effective Hardware and Software, the following is the specification on what his the computer include:
* Input:
. PS/2- Style Keyboard (Dell Quiet key Keyboard)
2. PS/2- Compatible mouse (Dell 2 button mouse)
3. DVD-Rom 48x
* Output:
4. 21" Ultrascan P1130 Performance monitor
5. Dell Stereo speakers
6. 128 MB DDR ATI Radean 9700 Pro with TV - Out, DV1 output and dual monitor support.
7. Brother HL1660 printer
* Storage:
. Floppy Drive 1.44 Mb
2. CD - RW (48x CD-R, 24x CD-RW, 48x CD)
* Processing:
. Intel Pentium 4 processor 2.40 GHz
2. 128 MB RAM
3. 30 GB 5400 rpm Hard disk
4. Integrated AC97 audio Sound Card
Software available to Mr Rahman and other technicians:
> Operating System - Windows 2000
> Microsoft Office 2000
> Norton Anti Virus 2004
.14 System Overview
After looking at the problems, a computerised system seems to be the most logical choice. It would organise the data entry and speed up the process in which a customer is dealt with.
Furthermore I.T solution is appropriate as it would record customer details, record component information and price, plus is able to calculate automatically the price of the upgrade. As well as its organisation and calculation functions it is also more time efficient when compared to the manual paper-based system.
.15 User's Skill Level
Mr Rahman has a lot of experience in using computers, specifically in the hardware section. But he also has some knowledge of how Access works. The system requires input of relevant data (Names, problems with computer, component code etc.), Mr Rahman has the understanding to input these data.
Mr Rahman is a fast typer due to experience. As well as using computers at University he also has a computer at home, which he uses regularly.
.16 Plan of project completion
Sections
Time spent/hours
Deadlines
Analysis
7
Monday 22nd September 2003
Design
7
Monday 13th October 2003
Implementation
0
Monday 1st December 2003
Testing
7
User Guide
7
Monday 15th December 2003
Evaluation
6
Monday 12th January 2004
Report
6
Monday 26th January 2004
Total
50
.17 Critical Path Diagram
Task
Subtask
Comment
Interview Mr Rahman about how their current system works. Give questionnaire.
Find out what the end-user requires the new system to perform, plus find documents of their business.
2
Write Analysis section
Assess the requirements, objectives, performance indicators which should be met by the new system.
3
Design table structure
Field names, size, type, input masks and validation rules etc.
4
Design the relationships
Draw relationship diagram for relationships.
5
Sketch out input forms
Rough sketches of the layout of each of the forms.
6
Design menus
Rough sketches on the front-menu.
7
Sketch reports
Rough sketches of reports.
8
Design queries
Basic design of what fields each query will require.
9
Design Macros
Routing the system, from which function to which it will perform.
0
Create Test plan
Plan to help me go though what I need to do for testing the system.
1
Create table structure
Create basic structure first e.g. field name. Then alter field structure, type, size etc.
2
Create input forms
Create the forms.
3
Create queries
Create queries.
4
Create reports
Report based on queries.
5
Create front-end menu
Menu to go through the system.
6
System testing
Testing to find out how the system copes with the data.
7
Functional testing
Functional testing of the system.
8
End-user testing
Test the system with Mr Rahman. Check if they find the system easy to use and if it is what they require.
9
Make changes to the system if needed
Make changes if end-user requires of it, or if he finds the system ...
This is a preview of the whole essay
5
Create front-end menu
Menu to go through the system.
6
System testing
Testing to find out how the system copes with the data.
7
Functional testing
Functional testing of the system.
8
End-user testing
Test the system with Mr Rahman. Check if they find the system easy to use and if it is what they require.
9
Make changes to the system if needed
Make changes if end-user requires of it, or if he finds the system unacceptable.
20
Evaluation
Say if it meets all the performance indicators. What could have been added to make the system better.
21
Create User Guide
Create the user guide so the end-user knows how to use the system.
22
Create Technical guide
Explaining technical feature i.e. macro code.
2 Design
2.1 Possible solutions
I briefly touched on possible solutions in system overview. I will now explain the possible solutions in more details.
. Manual system: Is a paper-based solution, which consists of writing down the customer details e.g. Name. Writing down the customer's current computer specification and problem, plus what they require their new computer to perform.
Problem with this solution is that it is insecure as unauthorised personnel can access the information. In addition it is more likely for the information to have an error when recording, for example the hand writing may not be understandable, spelling errors. Plus calculations on the price of the upgrade may contain errors. Also this solution would be extremely time consuming.
2. Spreadsheet System: This would enable customer details to be held on one sheet and their problems and requirements on another sheet. The calculations on the price of components and the over price of the upgrade can be calculated. However it would be difficult to design a data entry form so that customer details and other information such as what they require, are all entered in one operation. Plus it is also much difficult to format reports in excel.
3. Bespoke Software: They are off-the-shelf packages, this may be a very good solution for some systems, however some packages are limited. Some packages does not suit the system, they do not contain the correct functions to carry out specific requirements.
4. Tailor-made: This solution is ideal for Mr Rahman, as it is well implemented by professionals and put under extensive testing so that it meets every objective. However it does have its disadvantages; this solution is too expensive, plus if there are a problem the technicians charge for the assistance, in addition the support may not be that good.
5. Database system: This solution is also ideal for the end-user, if successfully designed and implemented using Access. It contains complex functions such as queries, forms, sub-forms, reports etc. These functions are well designed to aid in the production of an effective solution. Mr Rahman's current system problems should be overcome by the use of this form of administering the proposed system.
2.2 Final Choice Of Software
Microsoft Access 2000 will be used to create the system for Mr Rahman and is colleagues. Access is the appropriate software for this task as it is ideal for what Mr Rahman requires. Access/database will store information as well as give Mr Rahman new time efficient functions which will help in their business.
A relational database will be used this has many advantages:
- It permits you to create several tables, thus allowing more control over the data with in the database.
- The data can also be linked so complex forms, queries and reports can be made.
- Using the relational database means that there is no redundant data (no unnecessary repetition of data).
- Plus data integrity and consistency can be achieved for the whole of the database system. Thus making the system of recording, processing and outputting more reliable.
The minimum specification needed to run the Database is a 486 or higher processor, 8MB if the software is running on Microsoft Windows 95 or higher, 60-167MB of Hard disk space, a VGA monitor or higher and a mouse.
Mr Rahman has a well-equipped computer as shown in the 'Hardware and Software Requirements', so running the database system will not be a problem.
2.3 Data flow diagram of proposed system
2.4 Normalisation
Normalisation is the process used to come up with the best possible design for a relational database. Normalisation allows the following to occur:
* No data is unnecessarily duplicated (i.e. the same data held on more than one table)
* Data is consistent throughout the database for example a customer's address does not change. Consistency should be an automatic consequence of not holding any duplicated data.
* The structure of each table is flexible so it allows as many or few items to be recorded.
* The table structure also allows the user to create complex queries.
Three stages must first be carried out before normalisation is complete; they are known as first, second and third normal form.
st Normal Form - No repeating attributes
Definition: A table is in first normal form if it contains no repeating attributes or groups of attributes.
For Mr Rahman's system, several customers can take the same component and each component, can be chosen by several customers. The relationship can be represented by the following standard notation and entity relationship diagram.
CUSTOMER_DETAILS (Customer ID, First name, Surname, Address 1, Address 2, Address 3, Gender, Contact Number, Current computer Spec, Problem with current computer, User requirements, Technician Name, Date of entry)
COMPONENT_DETAILS (Component Code, Component name, Component description, Component price)
Second Normal Form - Partial Key Dependency test.
Definition: A table is in second normal form (2NF) if it is in first normal form and no column that is not part of a primary key is dependant on only a portion of the primary key. This is expressed by saying that it contains no partial dependencies.
The standard notations in first normal form cant be put in second normal form because for example Customer name is dependent only on Customer ID and not on Component Details. To put the tables into second normal form I need to introduce a third relational table that acts as a link between the entities Customer Details and Component Details.
CUSTOMER_DETAILS (Customer ID, First name, Surname, Address 1, Address 2, Address 3, Gender, Contact Number, Current computer Spec, Problem with current computer, User requirements, Technician Name, Date of entry)
COMPONENTS_NEEDED (Customer ID, Component Code)
COMPONENT_DETAILS (Component Code, Component name, Component description, Component price)
Third Normal Form- Non-Key Dependency test.
Definition: A table in third normal form contains no 'non-key dependencies'. One entity one table this means that all the attributes relates to a specific entity.
There is too much information that needs to be inputted in Customer Details table. So to make the system easier to manage another table will be created just for the customer's current computer specification.
CUSTOMER_DETAILS (Customer ID, First name, Surname, Address 1, Address 2, Address 3, Gender, Contact Number, Technician Initials, Date of entry)
CURRENT_COMPUTER_SPEC (Computer Code, Customer ID, Hard Disk, RAM, Processor, Drives, Monitor, Printer, Speakers, Problem with current computer, User requirements, Customer Price limit)
COMPONENTS_NEEDED (Computer Code, Component Code)
COMPONENT_DETAILS (Component Code, Component name, Component description, Component price)
This is the optimum way of holding all of N.E.T business information, there are no attribute being duplicated. Thus this design should be effective when starting implementation of database system.
2.5 Table Design
Table Name: tblCUSTOMER_DETAILS
Attribute Name
Data Type
Length
Default Value
Description, Input Masks & Validation Comments
Customer ID
Text
5
N/A
Key field - "CUI "000 - Presence check
First Name
Text
30
N/A
>L<???????????? - Presence check
Surname
Text
30
N/A
>L<???????????? - Presence check
Date of Entry
Date/time
Short Date
Now()
Presence Check
Gender
Text
5
N/A
Lookup Function, Value list showing "Male", "Female"
Presence check
Address 1
Text
00
N/A
00\ >L<?????????????????
???????????????????????
- Presence check
Address 2
Text
00
N/A
>L<?????????????????????
???????????????????????
- Presence check
Address 3
Text
20
N/A
>L0<\ 0>LL< - Presence check
Contact Number
Text
20
N/A
\(9999") "00090009;0; - Presence check
Technician Initials
Text
0
N/A
Lookup function, Value List showing "AR";"IM";"BS"
Table Name: tblCURRENT_COMPUTER_SPEC
Attribute Name
Data Type
Length
Default Value
Description, Input Masks & Validation Comments
Computer Code
Text
5
N/A
Key field - "COCI "000 - Presence check
Customer ID
Text
5
N/A
Foreign Key - "CUI "000 - Presence check
Hard Disk
Text
50
N/A
Presence check
RAM
Text
50
N/A
Presence check
Processor
Text
50
N/A
Presence check
Drives
Text
50
N/A
Presence check
Monitor
Text
50
N/A
Presence check
Printer
Text
50
N/A
Presence check
Speaker
Text
50
N/A
>L<?????????????? - Presence check
Problem with current computer
Text
50
N/A
>L<?????????????????????
???????? - Presence check
User Requirements
Text
50
N/A
>L<?????????????????????
???????? - Presence check
Customer Price limit
Currency
Auto
0
Presence Check
Table Name: tblCOMPONENTS_NEEDED
Attribute Name
Data Type
Length
Default Value
Description, Input Masks & Validation Comments
Computer Code
Text
5
N/A
Foreign Key - "COCI "000 - Presence check
Component Code
Text
5
N/A
Foreign Key - "SCM "000 - Presence check
Quantity
Text
0
N/A
Presence Check
Table Name: tblCOMPONENT_DETAILS
Attribute Name
Data Type
Length
Default Value
Description, Input Masks & Validation Comments
Component Code
Text
5
N/A
Key Field - "SCM "000 - Presence check
Component Name
Text
25
N/A
Presence check
Component Description
Text
50
N/A
Presence check
Component Price
Currency
Auto
0
Presence check
Table Relationship in more details:
2.6 Query Design
Below I have designed the queries that I plan to include in this database. These queries are used to interrogate data so the end-user is left with specific data which he can used. These queries will be displayed in the reports.
Which Customers have spent the most on Components Query:
Query Name: qryWhich_Cutomers_Spent_Most_components
Underlying table(s): tblCUSTOMER_DETAILS
tblCOMPONENT_DETAILS
tbCOMPONENTS_NEEDED
Criteria: This query will show which customers have spent the most on components, form the most at the top, to the least at the bottom. This will be placed in the form of a report 'Price plan of customers report'. This report design can be seen in the report section.
Which components have been ordered by customers the most, Query:
Query Name: qryMost_Ordered_Component
Underlying table(s): tblCUSTOMER_DETAILS
tblCOMPONENT_DETAILS
tbCOMPONENTS_NEEDED
Criteria: This query will show which components have been ordered by customers the most. This will be placed in the form of a report 'Most Wanted Components Report'. This report design can be seen in the report section.
Which Component is the most expensive Query:
Query Name: qryMost_Expensive_Components
Underlying table(s): tblCOMPONENT_DETAILS
Criteria: This query will show which components are most expensive in the database list. It would show the information from the most to the least expensive. This will then be placed in the form of a report 'Most Expensive Components Report'. This report design can be seen in the report section.
Total spent on Components Query:
Query Name: qryCustomer_Component_Total
Underlying table(s): tblCOMPONENT_DETAILS
tbCOMPONENTS_NEEDED
Criteria: This query will show the total amount spent by a customer on components plus labour cost of the upgrade. This will then be placed in the form of a report 'Most Expensive Components Report'. Also it will be placed in a Form, both the form and the Report design can be seen in the Report and Form design section.
List only the Hard Disks Query:
Name: qryHardDisk
Underlying table(s): tblCOMPONENT_DETAILS
Criteria: This query will show only the Hard Disks available in the component details table. It would show the information from the most to the least expensive. This will then be placed in the Component Details Form.
List only the RAM's Query:
Name: qryRAM
Underlying table(s): tblCOMPONENT_DETAILS
Criteria: This query will show only the RAM's available in the component details table. It would show the information from the most to the least expensive. This will then be placed in the Component Details Form.
List only the Processors Query:
Name: qryProcessor
Underlying table(s): tblCOMPONENT_DETAILS
Criteria: This query will show only the Processors available in the component details table. It would show the information from the most to the least expensive. This will then be placed in the Component Details Form.
List only the Drives Query:
Name: qryDrives
Underlying table(s): tblCOMPONENT_DETAILS
Criteria: This query will show only the Drives available in the component details table. It would show the information from the most to the least expensive. This will then be placed in the Component Details Form.
List only the Monitors Query:
Name: qryMonitor
Underlying table(s): tblCOMPONENT_DETAILS
Criteria: This query will show only the Monitor available in the component details table. It would show the information from the most to the least expensive. This will then be placed in the Component Details Form.
List only the Graphics Card Query:
Name: qryGraphics Card
Underlying table(s): tblCOMPONENT_DETAILS
Criteria: This query will show only the Graphics Card available in the component details table. It would show the information from the most to the least expensive. This will then be placed in the Component Details Form.
List only Other Components Query:
Name: qryOthercomponents
Underlying table(s): tblCOMPONENT_DETAILS
Criteria: This query will show other components that are also in the component details table. It would show the information from the most to the least expensive. This will then be placed in the Component Details Form.
2.7 Form Design
Customer Details Form (frmCustomer_Details)
This form should allow Mr Rahman to view all relevant information about a customer plus enter information for a new customer.
It should also contain macro buttons to link this form to the Current Computer specification form, so to allow Mr Rahman to enter the customers current computer information straight after entering a new customers personal details. Look at 'frmCustomer_Details' Design on next page for more detail.
Current Computer specification (frmCurrent_Computer_spec)
This form should be linked to the customer details form. It should allow Mr Rahman to input data on the customers current computer specification. Look at 'frmCurrent_Computer_spec' Design on next page for more detail
Component Details Form (frmComponent_Details)
This form should be linked to the customer details form and the current computer specification form. It should allow Mr Rahman to view all the components in the database which are available fro the customer. In addition Mr Rahman should be able to input new information on components in this form. Look at 'frmCurrent_Computer_spec' Design on next page for more detail.
Customer Requirements Form (frmCustomer_requirements)
This form should be linked to the Customer details, Current computer Spec and Component details form. It should allow Mr Rahman to input data on the components the customer requires. Plus it should also be able to work out the overall price of the upgrade. Look at 'frmCustomert_requirements' Design on next page for more detail.
Main Menu Switchboard
This form should auto open when application is opened. Macro AutoExec should be implemented. This form will contain macro on Reports, Customer Details, component details, etc. Look at 'Main Menu Switchboard' Design on next page for more detail.
Reports Form
This form will be linked to the Main Menu from. The form should contain a menu of all the reports that are available. The individual reports will be linked by macro buttons, which will be present in this form. Look at 'frmReport' on next page for more detail.
2.8 Report Design
Look at all report designs on next page for more detail.
2.9 Macro Design
Find Customer: This macro button allows the user to find a specific record of a customer, for example customer details.
Add Customer: This macro button will allow the user to add a new customer detail into the database system.
Delete customer: This macro button will allow the user to delete customer information.
Next Record: This macro button will allow the user to go to the next record in the database.
Previous Record: This macro button will allow the user to go back to a previous record.
Current Component Specification: This allows the user to go to the customer's current computer specification from.
Main Menu: This allows the user to go back to the Main Menu form.
Customer Requirements: This allows the user to go to the Customer requirements form.
Customer Details: This macro button allows the user to go to the Customer Details form.
Component Details: This macro button will allow the user to go to the Components details form.
Reports: This macro button will allow the user to go to the reports menu form.
Exit: This macro will allow the user to close the application.
2.10 Security & Back up
The system will be password protected, so that only authorised technicians can access the system. Mr Rahman also needs to back up the information to do this the data in the system must be regularly backed up, at least once a month. Mr Rahman has a CD-RW this can be used to record the information onto a CD. A floppy disc drive cannot recode too much information so a CD-RW is ideal.
2.11 Gantt Chart
No. of Weeks
2
3
4
5
6
7
8
9
0
1
2
3
4
5
6
7
8
9
20
21
22
23
24
25
Problem definition
Background information - research
End-user requirements
Skill acquisition
Aims and objectives
System data flow
Specifications of ICT Tasks
Design
Tables/Forms/Reports/Queries
Implementation
Tables/Forms/Reports/Queries
Testing
Improvements
End-user testing
Evaluation
User manual
2.12 Test strategy
The aim of this test plan is to prove that all parts of the system works properly, and how it was originally specified.
I cannot test every combination of paths due to time restrictions; I will be testing only the main important paths of the system. I will be testing the valid and invalid data.
Normal Data: data that is correct, there is nothing wrong with it e.g. Postcode E1 3BW in the address column.
Erroneous Data: This is data, which involves typing in data which is defiantly incorrect e.g. typing '5468' for the postcode. This should not be allowed because I inputted input masks into that field so only correct figuration of postcodes can be typed in.
In Order to test the user friendliness of the system, I will give a copy of the system to Mr Rahman. Mr Rahman will use the system and give me feed back on how well he feels the system has performed in relation to his objectives and performance criteria's.
He will then explain what the parts he feels are too complicated or which parts he feels does not work properly. For these I will rectify the problem.
2.13 Test Plan
Test category
Test for
Sub Test
Predicted Outcome
Macros
Macros
Main menu Switchboard
Click on Customer details
Customer Details form should appear.
2
Click on Component Details
Component details form should appear.
3
Click on Reports
Reports Menu form should appear.
4
Click on Exit
The application should be closed automatically.
5
Reports Menu Form
Click on 'Which Customers have spent most on Components'
'Which Customers have spent most on Components' Report should appear.
6
Click on Previous Customers
Previous Customers report should appear.
7
Click on Most Popular Component
Most Popular Component report should appear.
8
Click on Most Expensive + Least expensive Component
Most Expensive + Least expensive Component, Report should appear.
9
Click on All Components Ordered by Customer
All Components Ordered by Customer, Report should appear.
0
Click on Main Menu
Main menu switchboard should appear.
1
Click on Exit
The application should be closed automatically.
2
Customer Details Form
Click on Add New Customer
New customer details can now be entered.
3
Click on Find Customer
Should be able to find a specific customers detail.
4
Click on Next
Next record should now be displayed
5
Click on Previous
Previous record should now be displayed
6
Click on Delete Customer
Should be able to delete a customer
7
Click on Current Computer Specification
Current Computer Specification Form should appear.
8
Click on Component Details
Component details from should appear.
9
Current Computer Specification Form
Current Computer Specification Form
Click on Add New Customer
New customer details can now be entered.
20
Click on Find Customer
Should be able to find a specific customers detail.
21
Click on Next
Next record should now be displayed
22
Click on Previous
Previous record should now be displayed
23
Click on Delete Customer
Should be able to delete a customer
24
Click on Component Details
Component details from should appear.
25
Click on Customer Requirements
Customer Requirements Form should appear.
26
Click on Customer Details
Customer details form should appear.
27
Component Details Form
Click on Customer Requirements
Customer Requirements Form should appear.
Click on Customer Details
Customer details form should appear.
28
Click on Current Computer Spec
Current Computer Spec form should appear.
29
Customer Requirements Form
Click on Customer Details
Customer details form should appear.
30
Click on Component Details
Component details from should appear.
31
Click on Current Computer Spec
Current Computer Spec form should appear.
32
Queries
Which Customers have spent the most on Components, query
__
Should show a list of which customers have spent the most on Components.
33
Which customers have come to N.E.T previously, query
__
Should show a list of Which customers have come to N.E.T previously
34
Which components have been ordered by customer the most, query
__
Should show Which components have been ordered by customer the most
35
Which Component is the most Expensive + least Expensive, query
__
Should show Which Component is the most Expensive + least Expensive
36
List All Hard Disks, query
__
Should list All Hard Disks
37
List All RAM's, query
__
Should list all the RAM's
38
List All processors, query
__
Should list all processors
39
List All Drives, query
__
Should List all drives
40
List All Monitors, query
__
Should list all monitors
41
List All Printers, query
__
Should list all Printers
42
List All Speaker, query
__
Should list all speakers
43
List All Other Components, query
__
Should list all other components
44
Total Cost of all Components spent
__
Should display all the components chosen by customer. Their price and total cost, including labour cost.
45
Input Mask
Input Mask
Customer Details table
Test all input mask formulas given in Customer details table design
Entering data should become quicker and more accurate.
46
Current Computer Spec table
Test all input mask formulas given in Current Computer Spec table design
Entering data should become quicker and more accurate.
47
Components Needed table
Test all input mask formulas given in Components Needed table design
Entering data should become quicker and more accurate.
48
Components Details table
Test all input mask formulas given in Components Details table design
Entering data should become quicker and more accurate.
49
Validation Rules
All Forms
Presence Check
The system should not allow you to skip to another record until this piece of information is recorded e.g. Customer ID
50
Reports
All Reports
Click on Print Report Function button
The chosen report should be printed.
51
Security Password
For the whole of the system
Test if the password function appears, and whether it works
When staring up the database password screen should appear - should only accept the correct password and reject anything incorrect.
52
For editing the database
Test if the password function appears, and whether it works
When editing the database another password screen should appear - should only accept the correct password and reject anything incorrect.
53
Data Consistency
All forms
Data consistency
If the information changes in any of the forms, it should automatically change in the table in which the data is being recorded.
54
Data Integrity
Customer requirements Form
Add incorrect Component Code or incorrect Customer ID
If incorrect ID or code is entered other information should not appear automatically- and an error message should appear telling the end-user that that record does not exist.
3 Implementation
3.1 Commentary on Implementation and improvements
After planning the system I showed the end-user the proposed system, containing all the form designs, query designs and report designs. The interlinking forms and reports impressed Mr Rahman. But he thought that the presentation of the forms were not that good. He explained that it should contain more colour and it should be more informative.
In addition he pointed out a vital error in the design, he spotted that components could not be added or deleted. I explained to him that to add or delete he would have to go to the Component list tabs in 'Enter Customer Requirements for upgrade' form. He then explained that this would be quite confusing to him, so he suggested another macro should be entered somewhere so that he could easily enter or delete components. He also added that the view of the form should be like that of a table, he explained that he did not want to press a button every single time he wanted to go to the next, he wanted all of the components to be visible on just one sheet. His referral was taken into consideration.
He also pointed out that there were no delete buttons for the customer Details, I explained to him that delete is not really that necessary as you would need them for future reference and my deleting you cannot get the information back. Mr Rahman was not convinced and said that it must contain a delete button so that he can delete customers if the database becomes too full.
He also explained that for the delete button a warning window should appear so that customer detail is not accidentally deleted.
Furthermore he explained that the reports weren't that informative or that professional, he explained that he wanted to use some of the reports as invoices, thus it should contain the customers address and total price etc.
These improvements are pointed out in the implementation plan by the following symbol:
Refer to that improvement by going to that particular Task.
3.2 Implementation plan
To show step by step how Mr Rahmans system was made, I will divide each section into Tasks, for example Task 1, Task 2 etc. This would order the way in which I made the system and also make it more understandable.
. Create a table to store Customer details.
2. Create a table to store Component details.
3. Create a table to store Current computer specification.
4. Create a table to store Components Needed.
5. Set up a relationship between the tables.
6. Set up a query to display only the Drives from the component list.
7. Set up a query to display only the Graphics Cards from the component list.
8. Set up a query to display only the Processors from the component list.
9. Set up a query to display only the RAM's from the component list.
0. Set up a query to display only the Hard Disks from the component list.
1. Set up a query to display only the Monitors from the component list.
2. Set up a query to display only the Motherboards from the component list.
3. Set up a query to display only Other Components from the component list.
4. Set up a query to work out the Total Cost of Components Chosen by the customer.
5. Set up a query which asks for Computer Code and displays all the components he/she has chosen.
6. Create a form to enter or search Customer Details.
7. Create a form to enter or search Current Computer Specification of customer.
8. Create a form to enter customer Requirements for upgrade.
9. Create a form for Lost ID's.
20. Create a Report to show all components present in the Database.
21. Create a Report to show all components ordered by customers.
22. Create a Report to show Customer Requirements and total cost of upgrade.
23. Create a switchboard containing various buttons which will be connected to all the different functions of the system, allowing easy navigation through the system for the end-user.
24. Create macro buttons in the forms so they can access other forms or reports.
25. Add Splash screen and define a start-up procedure for the database.
Task 1: Creating a Table to store Customer Details
This task involves creating the customer details table. Choosing the primary key, working out the Data type, placing input masks. Basically placing all the information that I included in my design section for this table in to the design view.
The above figure shows that the primary Key is Customer ID.
Input masks were placed in the Customer ID so 'CUI' appears before every number, plus it also allowed only three numbers to be inputted after the code. This makes entering the data easier. In addition input masks were used in entering the contact number so that it contains the correct amount of numbers. Input masks were also placed in the first name, surname so that the first letter of each name is capital. Input masks were also put in Address 1, so that the user can first input numbers (door number) then the name, the first letter of the name will be in capitals. Input masks have also been placed in the Address 2, where the first letter will always be in capitals. In Address 3 the input mask has also been placed so to allow the user to input the correct postcode.
Furthermore I used Lookup Wizard for the Gender and the Technicians Initials. The figure below shows how the Look up function works in the system.
I also entered a formula for Date of Entry. I inputted Now() in the Default Value section, I also chose the format to be short date. This function shows today's date in the field. The figure below shows how Date of Entry works.
Task 2: Create a table to store Component Details
This task involves creating the Component Details table. Choosing the primary key, working out the Data type, placing input masks. Basically placing all the information that I included in my design section for this table in to the design view.
As you can see from the figure above the primary key is Component Code.
Input masks were placed in the Component Code so 'SCM' appears before every number, plus it also allowed only three numbers to be inputted after the code.
For Component price, currency has been placed as a Data type. Examples of the code and the price are shown in the figure below.
Task 3: Create a table to Store Current Computer Specification
This task involves creating the Current Computer Specification table. Choosing the primary key, working out the Data type, placing input masks. Basically placing all the information that I included in my design section for this table in to the design view.
As you can see from the figure above the primary key is Computer Code.
Input masks were placed in the Computer Code so 'COCI' appears before every number, plus it also allowed only three numbers to be inputted after the code.
For Customer price Limit, currency has been placed as a Data type. Examples of the code and the price are shown in the figure below.
Task 4: Create a Table to store Components Needed
This task involves creating the components needed table. The specification of this table is shown in the design section. As you can see form the design view of the table below, it does not contain any primary Key.
This table contains the computer code and the component code. Both have their unique code, as shown in tasks 3 and 4. This table is the link table inking all the vital information on what items a customer has taken. Figure below shows the table layout.
Task 5: Set up a Relationship between the tables
The Print screen below shows the relationship of the N.E.T database system.
Task 6: Set up a query to display only the Drives from the component List
This task involves extracting only the Drives form the component list. The figure below shows the query in design view.
Underneath component name, in the criteria section "Drives" has been typed in, this will be recognized by the query, thus show all the components which are the Drives.
The print screen below shows all of the Drives from the component list. Save it as qryDrives.
Task 7: Set up a query to display only the Graphics Cards from the component List
This task involves extracting only the Graphics cards form the component list. This was done by entering "Graphics Card" underneath component name, in the criteria section and then pressing the 'Run' button and then save it as qryGraphics_Card.
The extracted information after pressing run is shown in the figure below.
Task 8: Set up a query to display only the Processors from the component List
This task involves extracting only the Processors form the component list. This was done by entering "Processor" underneath component name, in the criteria section and then pressing the 'Run' button and then save it as qryProcessor.
The extracted information after pressing run is shown in the figure below.
Task 9: Set up a query to display only the RAM's from the component List
This task involves extracting only the RAM's form the component list. This was done by entering 'RAM' underneath component name, in the criteria section and then pressing the 'Run' button then save it as qryRAM.
The extracted information after pressing run is shown in the figure below.
Task 10: Set up a query to display only the Hard Disks from the component List
This task involves extracting only the Hard Disks form the component list. This was done by entering 'Hard Disk' underneath component name, in the criteria section and then pressing the 'Run' button and then save it as qryHard_Disk.
The extracted information after pressing run is shown in the figure below.
Task 11: Set up a query to display only the Monitors from the component List
This task involves extracting only the Monitors form the component list. This was done by entering 'Monitor' underneath component name, in the criteria section and then pressing the 'Run' button and then save it as qryMonitor.
The extracted information after pressing run is shown in the figure below.
Task 12: Set up a query to display only the Motherboards from the component List
This task involves extracting only the Motherboards form the component list. This was done by entering 'Motherboard' underneath component name, in the criteria section and then pressing the 'Run' button and then save it as qryMotherboard.
Task 13: Set up a query to display only Other Components from the component List
This task involves extracting only Other Components which have not been extracted separately, form the component list. This was done by entering the other Components, "Fan" Or "Keyboard" Or "Mouse" Or "Case", underneath component name in the criteria section and then pressing the 'Run' button and then save it as qryOther Components.
The extracted information after pressing run is shown in the figure below.
Task 14: Set up a query to work out the Total Cost of Components Chosen by customer
This task involves creating a query which will work out the total cost of the component by multiplying the quantity with the component price. The print screen below shows how this was done.
On a new column on the design view of the query the following code was typed in, 'Total Cost: [Quantity]*[Component Price]'. This formula allows the query to view component price times the quantity.
After pressing the Run button the resulting query table is shown in the print screen below.
Task 15: Set up a query which asks for Computer Code and displays all the components he/she has chosen.
Open qryTotal_cost_of_component_chosen and in the design view of the query enter the following as shown by the print screen below.
Type in [Please Enter Computer Code] in the criteria's section, underneath Computer Code column. Click on the Run button, the following print screen shows what then occurs.
This function allows the user to select only specific information, thus by entering computer code of a customer only that specific customers components and price will appear. As shown by the print screen below.
Save the query as qryEnterComputerCode_totalCostofComponentchosen.
Task 16: Create a Form to Enter or search Customer Details
First of all go to the form section on the database window, then click on the New button as show in the print screen below.
After doing this click on the button labelled Test Box, then drag out the box onto the form as shown by the print screen below.
Double click on the Customer Details subform then in the in the window which appears type in the following information as shown by the print screen below.
Purpose of doing this is to allow the user to allow the customer to choose specific customers form the database, just by entering the customer ID. By doing this it will eliminate the use of macro buttons such as Add, Next, Previous and Search. It would simplify the form for use.
Save the form then no back to the Database window then click 'New' to create another subform. This time select the following:
Then click OK. Look at print screen below.
Go to design view of the form, then place a text box on the bottom of the form, as shown by the print screen below.
Double click on the text box to view the properties window. Then in the Name section type in Countof, then on the control source type in the following code '=Count([First Name])'. This formula will allow this form to count how many customers are present in the database.
The print screen below shows what to enter.
Close the Countof subform then open the previous form. Then drag the Countof subform onto the design view of the previous form. As shown by the print screen below.
Save the form as frmCustomer_details.
Then double click on the Countof subform to view the properties window, then in the format section choose under Visibility 'No'. The print screen below shows this.
Then in this form insert another text Box, double click on the box to view its properties then enter the Name as Count and the Control Source as '=[frmCountof].Form!Countof'. The print screen below shows this.
Save the form and then tidy up the form so that it looks presentable and easy to manage and understand.
In the properties window which then appears, go to the Format section click No on the sections shown in the print screen below.
Task 17: Create a Form to Enter or search Current Computer specification of Customer
First of all click on the New button on the Database window to view the Form Options.
Save the form as frmCurrent_computer_spec_subform, then click on the 'Create Form in Design View' option and drag the subform into it. The following print screen shows how it should look.
Then place a text Box on to the form and double click it to display its properties. The following print screen shows this.
Reopen frmCustomer_details_subform and delete all the text Boxes, except for First Name, Surname, Customer ID and Technicians Initials. Then go to file and select Save As, then save it as frmCustomer_subform.
Print screen below shows this.
Then in the design view of the previous form drag frmCustomer_subform into it. As shown by the print screen below.
Double click on the current comp spec subform and in the properties window which then appears type in the following as shown by the print screen below.
Do the same for the frmCustomer_subform, then save the form as frmCurrent_compu_spec.
Then tidy up the form so that it looks presentable and easy to understand. As shown by the print screens below.
This form can be used either to view information or enter information, like the Customer details form, this form is also linked by the Customer ID, thus it does not need a search button or a Next or Previous button.
Also all of the unnesessary icons are removed form this form, like the Customer Details form.
Task 18: Create a Form to Enter Customer requirements for upgrade
First of all create all of the subforms required for this form, there will be in total 9 subforms in this form.
A subform for each of the queries except for task 15 must be made. The print screen below shows how one of the subforms were made.
After creating all of the subforms, click on the 'Create Form in design view' option and then insert 'Tab' on to the form, as shown by the print screen below.
Drag each of the component queries and place then in separate Tab pages, so one query per page. The print screen below shows this.
Place also frmTotal_cost_of_component_chosen Subform on a new page and insert another page labelled 'Total Cost of Upgrade'.
Drag frmCustomer_subform into this form, but not in the Tab pages, place the subform outside, then save and close the form.
Open frmCurrent_comp_spec_subform and delete all information text boxes, except for the Computer code, Customer ID, Problems with current computer and User Requirements.
Close this form then reopen frm_customer_requirements form and drag the frmProblem_with_current_comp_subform into it.
Make sure that the spelling is correct and that all of the subform contains a Customer ID of their own. This is important as to identify the link. In addition hide all other Customer ID except for the Customer ID text Box, thus to make more space and avoid confusion.
On the frmTotal_cost_of_component_chosen_Subform place another Text box right at the bottom as shown by the print screen below.
Open the next Tab page 'Total Cost of Upgrade', then insert three more text boxes into that pages, Name and control source of the text boxes are shown below.
Save and close the form. Reopen frmCurrent_comp_spec_subform, this time delete all information text boxes except for the Price Limit. Save the form as frmCustomer_price_limitsubform.
Reopen frm_Customer_requirements and drag frmCustomer_price_limitsubform into the Tab page labelled Total Cost of Upgrade.
Tidy up the form so that it looks presentable, below is the print screen on how it works.
Task 19: Create a Form for Lost Customer ID's
Create a subform for customer details as shown by the print screen below.
By doing this it would link the text box with the subform so when the end-user enters the surname of a customer all the information of that customer will appear including his/hers Customer ID, if more than one person has the same surname, first name will be checked.
Task 20: Create a Report to show all components present in the database
Below is the print screen of the tidied up report showing component code, name, description and price.
Task 21: Create a Report to show all components ordered by customers
This form was carried out following the same procedure as the components list report. However instead of selecting tblCOMPONENT_DETAILS, qryTotal_cost_of_components_chosen was chosen, this would show all the customers computer code who have chosen an item form the list plus what they have chosen.
Below is the print screen showing the finished report. Saved as qryAll_Chosen_components.
Task 22: Create a Report to show Customer requirements and total cost of upgrade
This form was carried out following the same procedure as the one above, however different queries were selected. To create this report qryCUSTOMER_DETAILS was selected and qryEnterComputerCode_totalCostofComponentchosen.
This report would be the main report which the user would give to the customer as a reference, thus it needs to specific in showing only the chosen customers information query named qryEnterComputerCode_totalCostofComponentchosen is the query which asks for the computer code of the customer thus recognising and selecting only their data. Look at Task 15 for more information.
As this is the main report which will also be used as an Invoice, it needs to work out the grand total of the upgrade including labour cost.
To create more space for this information another section must be made. Print screen below shows how this was carried out.
In the section which then appears on the report enter the grand total of the components, labour cost and grand total of up grade. The formulas for these are exactly the same as the formulas used in frm_customer_requiremensts form. Print screen of one of the formulas are shown below.
Below is the tidied up, finished report showing customer details, as well as what components they have chosen and total and grand total of their upgrade. To make it more professional N.E.T Company logo has been placed at the top of the Invoice.
Task 23: Create a Switchboard containing various buttons which will be connected to all the different functions of the system, allowing easy navigation through the system for the end-user.
The switchboard was created to aid the end-user to navigate through the system, the print screens below shows what it was created.
Close the Edit Switchboard page to reveal the previous window, 'Switchboard Manager', select 'Problem Solutions Switchboard' then click Edit.
The following was then carried out:
Close the Edit Switchboard page to reveal the previous window, 'Switchboard Manager', select 'Reports Switchboard' then click Edit.
The following was then carried out:
Task 24: Create macro buttons in the forms so they can access other forms or reports
Macro buttons will be created to open forms and close forms the print screens below shows how they were implemented into the system.
After the macro button has been placed on the form, double click on the button to view its properties as shown by the print screen below.
Carry out the same procedure for all of the macros on all of the Main forms. The purpose of this is so that when one form is opened the previous form will be closed automatically. This will simplify use of the system by the end-user.
Additional macros have also been made as shown by the print screens below:
The Name 'Finish' placed in the Object Name row is another form, which has been created. In the form macro buttons were placed using the Command Button function.
The user must be able to access the Finish form via the Main switchboard. Below is the print screen on how it was linked to the switchboard:
Further more another macro has been made to Delete Customer Details, this will be a special macro which is linked to a MsgBox which contains the following message: 'It is advised not to delete Customer detail in case of future reference. Are you sure you want to Continue?'
This is to inform the end-user of the importance and the danger of deleting a record, I do not need to type more information as the database automatically informs the user that the record cannot be recovered.
The print screen below shows how I designed the macro:
Another macro was also made to aid in the end users navigation of the system. This macro would allow the end-user to go straight to the Component details table where the user can add or delete a component. This macro will be placed in the switchboard so that it is easily accessible. The print screen of the macro is shown below:
Switchboard manager was opened then in the Open Form switchboard a new button was entered. The following print screen shows this:
Task 25: Add Splash screen and define a start-up procedure for the database.
After the switchboard was completed a start up procedure had to be made in which it allows the switchboard to automatically appear when the database system has been opened. Furthermore it does not show the Database window, just the switchboard. The following print screen shows how this was carried out.
First a splash screen was created in a form and saved as Splashscreen, then a macro was created, saved as Splash, the print screen below shows this:
Then is the properties of the 'Splashscreen' the following was entered:
To make a start-up procedure the following the carried out:
Only 'Display Status Bar' and 'Allow Full Menus' was ticked, all the other option were not tick so to act as a security procedure. The toolbars, which allows the design of the system will not appear , thus preventing unauthorised modification of data.
4 Testing
4.1 Improved Test Plan
The finished system does not relate completely to the original design, thus the previous test plan did not contain the new features, so in turn a new test plan has been developed to test the new features, to see if they are in working order.
In this new improved test plan, queries will not be tested separately as all of the queries are in someway linked to the forms. Thus tests will be carried out only on the forms, so if there were to be an error, it would show that the query was malfunctioning, so in turn the query would be fixed.
Test
Test for
Sub Test or Test data
Predicted Outcome
. Main menu Switchboard
Macro
Click on Open Forms
Form Menu should appear.
Macro
Click on Open Reports
Reports Menu should appear.
Macro
Click on Problem solutions.
Problem Solutions Menu should appear.
Macro
Click on Exit System
Exit System form should appear.
2. Open Forms Menu
Macro
Click on 'Enter or Search Customer Details'
'Enter or Search Customer Details' form should appear.
Macro
Click on 'Enter or Search current computer specification'
Enter or Search current computer specification' form should appear.
Macro
Click on 'Enter Customer requirements for upgrade'
'Enter Customer requirements for upgrade' form should appear.
Macro
Click on 'Add or Delete Component'
'Component Details' table should appear.
Macro
Click on 'Back to main Switchboard'
Main Menu Switchboard should appear.
3. Open Reports Menu
Macro
Click on 'Components List Report'
'Components List Report' should appear.
Macro
Click on 'All Components ordered by Customer'
'All Components ordered by Customer' should appear.
Macro
Click on 'Back to Main Switchboard'
Main Menu Switchboard should appear.
4. Problem
Solutions Menu
Macro
Click on Find Customer ID
Find Customer ID form should appear.
Macro
Click on 'Back to Main Switchboard'
Main Menu Switchboard should appear.
5. Exit System
Macro
Click 'Yes'
The application should be closed down automatically. .
Macro
Click 'No'
Exit System form should close.
6. 'Problem Solutions' Form
Test Data
Enter 'Hoque' where it asks for surname.
All the customers with the surname 'Hoque' should appear.
7. 'Enter or Search Customer Details'
Form
Macro
Click on 'Enter Current Computer Specification'
'Enter Currents Computer Specification' form should appear.
Macro
Click on Main Menu
Main Menu switchboard should appear.
Macro and MsgBox
Go to record 028 then click on the delete button
That particular record should delete, and with it an Information window should appear stating that it is advised that the data is not deleted for future reference.
Also another warning window,
which states that the deleted record cannot be retrieved.
Test Data
Enter 001 where it asks for customer ID
All relevant information about that customer should appear.
Input Mask
Enter 32 where it asks for customer ID
The entered number should automatically have the code 'CUI' present in front of it.
Also it should only allow three digits to be entered.
By doing this, the code would become identifiable.
Count of Customers text box
Add new Customer to the list.
The text box should display the total number of customers present in the database, including the new entry.
8. 'Enter or Search Current Computer Specification' Form
Macro
Click on Customer Requirements
Customer Requirements form should appear.
Macro
Click on back to Customer Details
Should go back to customer details form.
Macro
Click on Main Menu
Main Menu switchboard should appear.
Linking
Enter 001 where it asks for customer ID
Customer's name and technician initials should appear.
Linking with current computer specification
Enter a Customers current Computer Specification
When the customer ID is inputted the name of customer and that customer's current computer specification should appear.
Input Mask
Enter 001 where it asks for computer code
The entered number should automatically have the code 'COCI' present in front of it.
Also it should only allow three digits to be entered.
By doing this, the code would become identifiable.
Test data
Enter 300 in the 'Customer Price limit' text box.
The number should be automatically converted to currency.
9. 'Enter Customer Requirements for Upgrade' form
Macro
Click on Main Menu
Main Menu switchboard should appear.
Macro
Click on 'Back to Customers Current Computer Specification'
Should go back to Customers Current Computer Specification
Macro
Click on 'Back to Customer Details'
Should go back to Customer Details form
Test Data
Enter 001 where it asks for customer ID
All relevant information about that customer should appear.
Tab pages
Click on all of the tab pages.
All the components should be placed in the correct tab pages, in relation to the title of the page. So for the Drives page all drives must be present in that page.
Input Mask
Enter 37 where it asks for Component code.
The entered number should automatically have the code 'SCM' present in front of it.
Also it should only allow three digits to be entered.
By doing this, the code would become identifiable
Tab page test data
Enter the following in the 'Customer Component list' tab:
Computer Code: 001
Component Code: 005
Quantity: 2
When component code is entered the Name, description and Price of the component should appear. When quantity of the component is set as 2, the price should double and shown on the Total cost.
Test related text boxes
Enter more components in the 'Customer Component list' tab page
On the next tab page labelled 'Total Cost of upgrade', all the totals should be added to give the 'Component Cost Grand total', which contain the total cost of the components ordered by the Customer. 'Labour Cost' text box should display 35% of the components total cost. The Grand total of upgrade should display the price of the labour cost added to the Component cost giving the overall cost of the upgrade.
Macro
Click on 'Preview' button
A window should appear asking for 'Computer Code'
Test Data
Enter 001 in the 'Preview button' window.
All components ordered by that customer should appear in a report. It should also display the correct total for the upgrade.
Macro
Click on 'Print' button
A window should appear asking for 'Computer Code'
Test Data
Enter 001 in the 'Print button' window.
Report should printing containing all of the components ordered by customer and total cost. The information displayed should be correct.
0. Add or Delete Component
(Component Details Table)
Test Data
Click on the row with the code 'SCM 071' then click on the delete button on the keyboard.
That particular component should be deleted from the list, and a warning window should appear stating that the component cannot be retrieved after deletion. In addition this particular component should now not be present in the list in the 'Enter Customer Requirements for Upgrade' form Tab.
Look up Combo box
Select component name field, and from the list select Monitor.
All component types should appear in a list when component name is selected. When monitor is selected form the list it should appear on the field.
Test Data
Click on the bottom empty row of the list the enter the following: Component Code: SCM 072
Component Name: Monitor
Component Description: 17" TFT with SPEAKERS Crème
Component Price: £299.00
The data should be automatically entered into the 'Enter Customer Requirements for Upgrade' form Tab.
1. All Forms
Validation Rules/ Enforcing referential integrity
Presence Check
The system should not allow you to skip to another record until this piece of information is recorded e.g. Customer ID or Computer Code
2. For the whole of the system
Security Password
Test if the password function appears, and whether it works. Password: Shah
When staring up the database password screen should appear and should only accept the correct password 'Shah' and reject anything incorrect.
3. Start up procedure
Security
Open database System
Start up procedure should be implemented so it does not show any of the design or editing toolbars and the database window, thus not allowing alterations to the Tables, queries and forms.
Furthermore when the database is opened it should automatically open the Splash screen, which in turn should automatically open the Main switchboard.
4. All forms
Data Consistency
Data consistency
If the information changed in any of the forms, it should automatically change the subforms relating to that form.
5. Data Integrity
-
Add incorrect Customer ID iqwecivye
This data should not be allowed to enter due to the input mask placed for the customer ID.
6. Time
Check
-
Should show correct time
7. 'Component List' Report
Reports
Open report
All the details on components and other relevant data should fit on to the page when printing and should be legible.
8. 'All Components chosen List' Report
Report
Open Report.
All the details on components and other relevant data should fit on to the page when printing and should be legible.
9. 'Customer total upgrade ' Invoice Report.
Report
Open Report
All the details on components, Customer and total price etc should fit on to the page when printing and should be legible.
20. Splash screen
Macro
Open the database system
The splash screen should open immediately, then after 3 second or so the main menu switchboard should appear.
4.2 Commentary on Testing
Testing the system vigorously will show me in depth whether the system does or does not perform in relation to the performance indicators set by the end-user. I will follow the improved test plan to check if the predicted outcomes occur, if they do not I will try to correct the error.
Later on in this testing section, to will get even more of a perspective of what people think of the system, it will be first given to my ICT teacher Mr Lye to use, so to test if he can use it effectively and its ease of use. Furthermore it will be given to Mr Rahman, who will in turn tell me if the system performs fully to his expectation, or whether it still requires some modification.
Test 1 Main menu switchboard
Test for the correct operation of the navigation buttons on the Main Switchboard. The macro buttons work as expected on the switchboard this is shown below:
Test 2 Open Forms switchboard
Test for the correct operation of the navigation buttons on the Open Forms Menu Switchboard. The macro buttons work as expected on the switchboard this is shown below:
Test 3 Open reports switchboard
Test for the correct operation of the navigation buttons on the Open Reports Menu Switchboard. The macro buttons work as expected on the switchboard this is shown below:
Test 4 Problem Solutions Menu
Test for the correct operation of the navigation buttons on the Problem Solutions Menu. The macro buttons work as expected on the switchboard this is shown below:
Test 5 Exit System
Test for the correct operation of the Exit System form which is accessed via the main switchboard. Pressing the 'Yes' button should automatically close down the system, pressing the 'No' button should only close down the Exit system form.
The macro buttons work as expected.
Test 6 'Problem Solutions form' Form
Test for the correct operation of the Problem solutions form. Thus test data will be carried out to see if by entering 'Hoque' where it asks for surname, the table shows a list of all the customers, present in the database with that surname. Plus all other additional details about those customers. The test data works as expected and is shown by the print screen below.
Test 7 'Enter or Search Customer Details' Form
Test for the correct operation of the 'Enter or Search Customer Details' Form. This test will determine whether all of the functions present in this form works correctly. Functions include the input mask in customer ID, the navigation macros, linking of the subform to the Customer ID, the Delete macro with the MsgBox, and the count of customers present in the database text box. The results of these tests are shown in the print screens on the next page.
Test 8 'Enter or Search Current Computer Specification' Form
Test for the correct operation of the 'Enter or Search Current Computer Specification' Form. This test will determine whether all of the functions present in this form works correctly. Functions include the input mask in customer ID and computer Code, the navigation macros, linking of the subforms to the Customer ID, and the Customer price limit set to currency.
Test 9 'Enter Customer Requirements for Upgrade' form
Test for the correct operation of the 'Enter Customer Requirements for Upgrade' Form. This test will determine whether all of the functions present in this form works correctly. Functions include the input mask in customer ID, computer Code, Component Code and the navigation macros, linking of the subforms to the Customer ID, Correct information being present in the tab pages and totals, preview and print buttons, plus the related text boxes giving the correct grand total etc.
When component code was entered name description and price appears automatically, thus works as expected. When quantity was set as 2 the component price was doubled to give the total cost. Thus again works as expected.
This form is a subform which was made by the use of a query named 'qryTotal_cost_of_component_chosen'. As the functions work in this form the query must also be working correctly.
Test 10 Add or Delete Component (Component Details Table)
Test for the correct operation of the component details table to which the main menu switchboard is linked. This test will determine whether all of the functions present in this table works correctly. Functions include the input mask in Component Code, being able to add or delete components form the list, also the new component added or component deleted should automatically update the 'Enter Customer requirements for Upgrade' form.
To test addition of new components to the list the following was inputted into the empty row at the bottom of the list:
Component Code: SCM 072
Component Name: Monitor
Component Description: 17" TFT with SPEAKERS Crème
Component Price: £299.00
The new component, which is added, can now be viewed in the 'Enter Customer Requirements for upgrade' form as shown in the print screen below. Thus this function works as expected.
Test 11 All forms - Validation rules/ Enforcing referential integrity
This test involves checking all forms to see if the validation rules work or not.
As the previous print screens already displayed that the validation of the code are in working order (input Mask), further print screens will not be needed to show that they work as predicted.
Validation rules were set in the 'Enter or search customer details' form, these were mainly input Masks. First Name, Surname contained input mask so that the first letter is always in capitals. This function works as expected. In addition to this address 1 has an input Mask which allows two numbers to be entered first for the door number then text to be entered, with the first letter of the text being in capitals. Address 2 also has the input mask which allows the first letter in the name to be in capitals automatically. An address has an input Mask which allows post code to be entered, it is set in a way so that incorrect configuration of postcode cannot be entered (For all these input masks look at design section). All of these input masks works as expected. Furthermore the date validation also works as it displays the correct date.
This test is also to check that customer ID is always entered, before going on to another record. The results of this test is shown below:
Test 12 for the whole of the system - Security Password
Test to see that the password protection works. As shown by the print screen below it does work as expected. When the database system is opened the following window appears, which asks for the password, which in this case is 'shah'. After clicking O.K. it allows the user to enter the system.
Test 13 Start up Procedure - Security
Test to see when the data base system is opened, the standard tools in the program like design view have not appear, also the database window. After opening the system these tools have not appeared, thus this test is successful.
Test 14 All forms - Data consistency
Test to see if information is changed in any of the forms, it should automatically change the subforms relating to that form. This function works as expected. The print screens below shows this:
Test 15 Data Integrity
Test to if incorrect Customer ID is entered, whether the system accepts the customer ID. After completing the test it was found that the it did not except the incorrect code due to the input mask which was placed in the tables. The print screen below shows this.
Test 16 Time
Test to see if the time on the main switchboard is correct. Compare the actual time with that of the switchboard. The test was successful the switchboard showed the correct time.
Test 17 'Component List' Report
Test to see if the component list report shows the correct information and that all information fits on to the page when the report is printed out. This test is successful as all information in the list relates correctly to that in the table, plus it fits onto the page when printing. The prints screen below shows this:
Test 18 'All Components chosen List' Report
Test to see if the all components chosen list report shows the correct information and that all information fits on to the page when the report is printed out. This test is successful as all information in the list relates correctly to that in the tables and forms, the customer ID is correct , plus it fits onto the page when printing. The prints screen below shows this:
Test 19 'Customer total upgrade ' Invoice Report
Test to see if the 'Customer total upgrade ' Invoice Report shows the correct information and that all information fits on to the page when the report is printed out. This test is successful as all information like the total, grand total and labour cost are correctly, the customer ID is correct and the address and name of the customer, plus it fits onto the page when printing. The prints screen below shows this:
Test 20 Splash screen
Test to see the correct operation of the splash screen. When the data base system is opened the splash screen should appear and after three seconds the main menu switchboard should appear. The following occurred when testing this function:
When the data base system was opened the splash screen appeared as expected but when the main menu switchboard appear it displayed the menu in design view. As shown by the splash screen below
5 Evaluation
5.1 Limitations
5.2 Future Enhancements
6 User Manual
See next page ---
This database system has been specifically designed for the use of N.E.T computer upgrade organisation. The following user manual is accompanied by a 31/2 floppy that contains the system. Follow the user manual to aid in the understanding of and use of the system.
Note: When installing the system it is best viewed in Access 2000
* Insert disk into the A: drive
* Double click on 'My Computer' then double click on '31/2 Floppy'
* On the window which then appears select the file labelled 'NET database System'
* Right click on the file then select 'Send to' and 'My document'
These steps are shown on the print screen below:
Note: It is strongly advised that the floppy disk is kept in a safe place and out of the reach of children. You will need this if you wish to reinstall the system.
* Open 'My Documents' then double click on the file 'NET database System'
o If this does not work open the Access application software then find the file by choosing 'Open an existing file' then clicking OK. Try to find the file form your documents the click 'Open'.
* Access should automatically open and a window should appear asking for a password to be entered.
* Enter 'Shah' in the window.
The print screen below shows what should appear on your screen:
* After entering the password click OK then the system should allow you to enter the N.E.T database system.
Note: Information on changing the password will be given later on in the manual
An opening screen should appear followed shortly by the Main Menu.
Each button on the Main menu leads to a different set of option:
o Open Form - Leads to the menu of all forms present in the database
o Open Reports - Leads to the menu of all reports present in the database
o Problem solutions - Leads to the menu with possible problem solutions
o Exit system - Leads to the form confirming if you wish to Exit the system
The Form menu contains the following macros:
o Enter or Search Customer Details - Leads to the customer details form
o Enter or Search Current Computer Specification - Leads to the current computer spec form
o Enter Customer Requirements for Upgrade - Leads to the customer requirements form.
o Add or Delete Component - Leads to the add or delete form
o Back to Main Menu - Leads back to the Main menu
Enter or Search Customer Details
Click on this button when you wish to enter or search for a customer.
When selected the following form will appear.
> How to enter a new customer
* First choose a Customer ID for the new customer then enter it where it asks.
* Now enter the customer detail into the rows in relation to the headings of the rows.
Note: Some of the rows allows only specific data to be entered, either a certain length or type, this is done so to minimise errors.
The Report menu contains the following macros:
o Component List Report - Leads to the Component List Report
o All Components Ordered by Customers - Leads to the All Components Ordered by Customers report.
o Back to Main Menu - Leads back to the Main menu
The Problem Solutions menu contains the following macros:
o Find Customer ID - Leads to the find customer ID form
o Back to Main Menu - Leads back to the Main menu
The Exit System menu contains the following macros:
o Yes - Closes down the application automatically
o No - Closes down the Exit system form and returns to the Main Menu.
Database Project Analysis
Shelim Chowdhury