Home

Summary - VLE - University of Leeds

1. sss JOD EB oie Se Or IEEE sconce y ONES E ANE Private Sub Balance Click SA DURER USE etx Balance Amount Owed Amount Outstanding Orrbosb BOGUS aes reels ro neste On Click oc sees eee es Event Procedure Amount_Paid On DBLCKK ioci rere End Sub S NIORT eee eee eoe oe eee eoe eee k k k k k k k ok ok k ok ok ok On Mouse Move sa isses OnMouse Up Figure 5 10 Access Icon Properties option Figure 5 11 VBA function Balance 65 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix H Implementation Appendix H Features in Microsoft Access 2000 The functions of each tool in Microsoft Access 2000 Tables Tables are the most important tools in Microsoft Access Its function is to store all the data associated with tables Entities identified in the E R diagram as shown in Figure 4 1 are transformed into tables Queries The function that queries perform is to retrieve and manipulate specified data Five queries have been created in this database development to extract specific records Forms Forms play a key role in most Access applications All data entry and data review take place in forms 27 p148 It also allows the developer to generate a more professional and easier approach of data input and appearance Forms have been used throughout the database development as the user data entry interfaces Reports The report function is used to print infor
2. Client and Business owns and the relationship between them is many to many A client may own one or more businesses and a business may be owned by more than one client The E R Diagram and the explanations of the relationships between the entities of proposed database are set out below Bookkeeping Payment requires requires Service involves Figure 4 1 E R Diagram of the Proposed Database Eom Client Owns Business A client owns one or more businesses and a business may be owned by one or more clients Client Requires Tax Each Tax provided for the client has its own Tax_sheet_Id A client requires one or more Tax but a Tax is required by one client Business Requires Bookkeeping 1 Each bookkeeping provided for the business has its own bookkeeping ledger Id A business requires one or more bookkeeping but a bookkeeping is required by one business Business Requires VAT 1 M Each VAT provided for the business has its own VAT_ledger_Id A business requires one or more VAT but a VAT is required by one business Business Requires Each PAYE provided for the business has its own PAYE sheet Id A business requires one or more PAYE but a PAYE is required by one business Business i Each clients business has one or more sales in P amp Co but a sales is from one business since each sales invoice has its own unique id Payment A sales that is invoiced has one or more payme
3. KMC then explained to JP that she would need to analyse the above details and problems in order to produce the user requirements what should be included in the system for him to approve and sign before the design of database JP agreed and KMC has arranged another meeting with JP for the approval of user requirements 49 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix D Minutes of Meeting Appendix D Correspondence with the User Minutes of Meeting with Jonathan Pitayanukul Date 4 November 2002 Present Ka Man Chan KMC Jonathan Pitayanukul JP P amp Co Chartered Accountant KMC brought the user requirements for JP approval before she builds the client and sales record management system KMC explained to JP that since the time given for this project is very limited it would not be able to implement the networking of the database JP agreed on the user requirements and said that if KMC does not has enough time to finish she can leave the networking part of the database because there will be a computer technician coming in from Taiwan in March 2003 and he will be able to follow up for the networking of the database After the agreement on the above user requirements JP added that the database should be restricted to authorised users only Also he would like to have a simple and ease of use user interface that can operate in real time to enable the administrator
4. Phase 4 This stage will be to deal with further enhancements if there is sufficient time Phase 5 This is the fifth stage of the waterfall model It will require an evaluation of the finished product Phase 6 Writing up the Project Report is involved in this phase This is carried out simultaneously with other phases 12 Client and Sales Record Management System for P amp Co Chartered Accountant Requirements and Analysis Chapter 3 REQUIREMENTS AND ANALYSIS 3 1 Introduction This chapter is the first stage of the Waterfall model which is analysis and specification To deliver the right system as required by the users the development of a system must be user oriented Thus it is vital to involve users in the development of the system so as to identify the full requirements An overview of the background to the problem of P amp Co is given in Section 1 4 and 1 5 Section 3 2 will provide a more detailed analysis of the current system procedures and associated problems The flow diagram of the activities performed in P amp Co and the research on the selection of the most suitable software tool for implementation will also be included in this chapter 3 2 Current System Procedures In order to understand the current paper based system procedures in P amp Co and identify the requirements for the new system two meetings were arranged with Jonathan Pitayanukul the owner of P amp Co He is now working in
5. below and they are in order of priority e Enter the field names attributes as shown in Section 4 2 1 were entered in each table The lists of attributes were agreed by the client that P amp Co itself used e Select an appropriate data type a suitable data type was selected for each field for instance data type Currency was appointed to the field Balance e Provide a brief description a brief description was given for each field to show the users the kind of data a field should store 28 Client and Sales Record Management System for P amp Co Chartered Accountant Implementation e Set field properties Properties such as combo box and input mask were used to indicate to the users the data format when inputting a record e Assign primary key Primary key was assigned in the relevant tables The primary key for each table is given in Section 4 2 1 It is simple to assign a primary key in Assess the field corresponding to the primary key should be highlighted and the Primary Key tool should be selected Creating tables is a relatively easy task but the main point is to ensure that each attribute has the correct data type and the primary keys are correctly labelled because if the keys are not implemented correctly the relationships and integrity clauses will not be validated and corrected The table in Figure 5 1 shows the design view of the Sales table All the other tables were listed in
6. There are some enhancements that the client would like to have on the database which are e Produce an up to date printout of monthly VAT report e Design the architecture of the networking of the database but this is not important since there will be a IT technician coming from Taiwan to work in P amp Co in May 2003 and he would be able to carry on the unfinished work These will only be implemented provided there is sufficient time 16 Client and Sales Record Management System for P amp Co Chartered Accountant Requirements and Analysis 3 4 2 Non functional Requirements Non functional requirements of the system are those not directly linked to the functions or data that the system supports The system will need to e Be restricted to authorised users only e Provide a simple easy to use data entry interface which will operate in real time to enable the client to enter details whilst on the phone 3 5 Selection of Implementation Tools This stage of analysis is to select the most appropriate method of implementing the solution It has been decided that creating a database is the most suitable solution for this development because the development will involve a large amount of related data 7 p4 So as to select the most suitable DBMS to be implemented in this development the four most popular DBMS used by the accounting industries have been chosen to be compared before making a decision They are Oracle Microsoft SQ
7. User Acceptance Testing User acceptance testing involves real users to gather their opinion of the system This allows the user to try out the system before the system is launched and gain feedback from them After the function testing and validation testing of the database by the author the database is still necessary to be tested by the users to ensure it is acceptable to them It would be ideal for the users to test the system iteratively throughout the development process but due to the time limitations this was unfeasible Thus the user acceptance testing was carried out after the database had been completed The testing was conducted based on the database requirements outlined in Section 3 3 1 and the concept of usability The author chose to emphasise on the level of usability so that the design of this system can be further improved if necessary Two users had been invited to test the database details of the test plans results and comments were gathered in Appendix K Page 66 73 The overall results shows that the staff were very pleased with the functions that the database provided and the system appeared to fulfil its role in providing a user friendly interface The database was easy to use the use of colour and level of navigation were very satisfactory except for some comments on the database design For example staff suggested adding a field Status in the VAT Report so that they can see whether a client is active
8. expected Form Clients Sales possible and allow the client sales ledger to be printed Ledger 74 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix K Testing Client Sales Preview and Preview all reports in print layout and allow the report to be printed As Summary Print a Report expected Report VAT Preview and Preview all reports in print layout and allow the report to be printed As Report Print a Report expected Main Menu Open each When the View button is clicked the relevant form should open As Form form expected Close Main While the Quit button is clicked the main menu should close and As Menu bring the user back to the Windows option expected View VAT Once the View VAT Report Menu button is pressed in Section As Report Sub VAT the relevant VAT sub menu should open expected Menu View Client Once the View Client Sales Summary Report Menu button is As Sales pressed in Section Sales the relevant Client Sales Summary sub expected Summary menu should open Report Sub Menu Comments e The navigation is easy to follow with the Main Menu to guide me through the database e Less time is needed to find a client record than before which means we can concentrate on our work such as finishing the clients bookkeeping and VAT earlier to provide a better customer service e We no longer need to worry about
9. Also maintenance of the database will be maintained by the technician in P amp Co who is only familiar with Access In terms of ease of use and functionality of Access Access provides tools which can be mastered by even the most inexperienced database users Querying forms and report creation are fairly simple tasks especially when using the main toolbars wizards and graphical interfaces provided with Microsoft Access 71 Therefore Access will be more suitable for the end users given the computer knowledge they have Login password of the database is also available in Access to restrict admission It is suggested that if the number of concurrent users is more than ten Microsoft SQL Server is more appropriate 75 and if the company is in high production data entry environment Microsoft Access is definitely not a good choice 77 As stated by the client the maximum number of concurrent users will be eight and the maximum transactions data entry per day will not exceed twenty It has also been said by the client that there is no plan in expanding the company in the future two years and thus the concurrent users and transactions rate per day will not vary much in the coming years Finally Access has been investigated to ensure that it has enough memory capabilities to support this database development It has been discovered that the maximum database size is 1Gb 70 As the data entered in the database will be a small volume it
10. Normalisation is used to put the relational schema through a series of tests to ensure that it complies with a particular Normal Form 7 p483 Relational database theory tells us that if relations are in normal form then there will be minimum data redundancy and minimum chance for things to go wrong 22 p7 1 Data redundancies occur when data is repeated more than once in a table this will result in inconsistent data where updates are incompletely performed and make updates more expensive Normalisation also minimises the insertion deletion and update anomalies Data redundancy and data anomalies can have a considerable influence on the performance of a database and therefore it is important to get it right in the design process 23 Client and Sales Record Management System for P amp Co Chartered Accountant Database Design Before the process of Normalisation can begin the notion of Functional Dependencies must first be outlined A functional dependency is the concept of one attribute in a relation being functionally dependent on another or on a set of others 22 p7 3 The functional dependencies of the P amp Co database are the following the attribute in bold is the primary key and italic is the foreign key Table Functional Dependencies Client Client Id 9 Title First Name Surname Home Tel Mobile Home Add Linel Home Add Line2 Postcode Email DOB National Ins No Business Busines
11. amp Co Chartered Accountant Background Research Chapter 2 BACKGROUND RESEARCH 2 1 Introduction This chapter is to outline the methodology the author has chosen for this project as well as the schedule of the project 2 2 APPROACH AND PROJECT PLAN In order to ensure this project is successful and would be completed on time employing a suitable methodology is one of the most critical factors needed to be considered The methodology acts as a guideline to the procedures of the project and will help to indicate how the system should be produced A methodology described in 2 1 1 has been chosen and an updated project schedule shown in Appendix C has been created which identifies the main phases of the project 2 2 1 Methodology There are many different types of methodologies related to project management but because of the word limit of this project only two popular methodologies the SSADM and the Waterfall model have been chosen to compare in order to ascertain the methodology most appropriate for this project SSADM SSADM Structured Systems Analysis and Design Methodology is a methodology a system of ways of doing things particularly suitable and orderly procedures used in the analysis and design stages of systems development Tt is said to be a data driven methodology because of its history and emphasis on data modelling and the database but in its later versions has become more balanced with
12. e Print Client Sales Summary Report Administrator Accountant 57 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix E Requirement Analysis Appendix E Use Case Diagram Payment Record Part of the Database Payment Receipt rescue MM Member of Staff a w M TEN CD Qa Details Edit Find Payment Details Administrator Accountant Cc Print Payment Receipt 58 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix E Requirement Analysis Appendix E Use Case Diagram Client Sales Ledger Part of the Database A Find Client Sales and Payment a a Record Details T Member of Staff FA g Cas 2 Print Client Sales Ledger Ea N a Administrator Accountant 59 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix E Requirement Analysis Appendix E Use Case Description Use Case Specification Add Client Details Revision History 03 Dec 02 Use Case Description was completed after the Use Case Diagram Ka Man Chan were drawn and agreed by the owner in P amp Co Use Case Name Add Client Details Summary Primary Actor The Member of Staff in P amp Co receives an information sheet from a potential client checks the details and inputs the client into the database Member of Staff Trigger Assumptions Exception Paths O
13. 00 21 00 0 00 0 00 28 04 2003 141 00 Record i 4 il 11 ot rs of 1 Figure 3 4 Client Sales Ledger Form Record 4 IT 1 gt our of 2 3 5 Quit and Back to Main Menu e The user can go back to the main menu by clicking this button 84 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix L User Manual 4 VAT When the VAT option is chosen from the Main Menu the user is directed to the screen shown in Figure 4 1 below ioj xl P amp CO VAT Details CHARTERED ACCOUNTANTS ux amez ECL Bus s Business Name Big Chef Sole Trader x 01132 589665 Chinese Takeaway VAT Ledger Ild VAT Reg No VAT Period Date Received Date Finished Re 785079852 Feb May Aug Nov 15 04 2003 25 04 2003 Kirst Record I4 4 2 Inj or2 ms end wl Record aaf 4 T gt our of 2 Figure 4 1 VAT Details Screen 4 1 Add VAT Record Add New VAT Record can be added by clicking this button Record in the VAT Details form the function button operates as the same as the Client Details Form However when details are input it is only the field Client Id Business_Id and the VAT subform as shown in Figure 4 2 that needs to be entered the rest of the business details will come up automatically since they are linked to the business table VAT Ledger Id VAT Reg No VAT Period Date Received Date Finished Re 785078852
14. Co the Leeds branch staff will then ring up the Manchester branch to get the required information which may cause low workload when there are many requests from clients e There are no standard sales invoices and payment receipts in the current system which means that the staff will need to produce invoices every time they charge clients or receive payment from clients 3 3 Flow Diagram A summarised flow of activities performed in P amp Co Chartered Accountant is shown below in Figure 3 1 This diagram will assist the design and implementation phases in the latter chapter Client becomes a member of P amp Co Chartered Accountant y Record Client Details y Record Business Details y Client will require Tax service for himself herself while clients business will either require Bookkeeping VAT or PAYE service Record different service details required by client or its business P amp Co administrator or accountant will record their sales and produce invoice to client or its business In the invoice service description will be outlined v Once the client receives the invoice he she will make payments y Record clients payment details Figure 3 1 Flow of activities in P amp Co Chartered Accountant 3 4 Requirement Analysis As the current system procedures and problems are known the requirements will be analysed using the Use Case Model The Use Case Model is comprised of Use Case Diagrams and Us
15. Co Chartered Accountant to a computerised system 7 To gather user requirements from the owner in P amp Co in short to obtain a clear view of the problem on the existing paper based system 8 To meet the user requirements and to recommend a new system by introducing database to store client and sales records 9 To design and implement a database holding information about P amp Co customers and their accounts details 10 To evaluate the system 1 3 Understanding the business background P amp Co Chartered Accountant P amp Co Chartered Accountant is a medium sized accountancy firm located in London Leeds and Manchester There are five employees working in London three employees working in Manchester and two employees working in Leeds The account service that P amp Co provides includes VAT returns Bookkeeping PAYE Self Assessment Tax Return and general administration service such as writing letters for clients detailed description of these services can be found in Chapter 3 Requirements and Analysis Inside the three P amp Co branches computers are only used for simple administration e g typing letters Since the current system is entirely paper based the working environment is full of papers files and filing cabinets Clients details are stored in files in different filing cabinets according to their business identification number There are about two hundred individual clients files at each branch and
16. Co for the client Client id is the foreign key in this table as a cross reference to client Bookkeeping BKKeeping Ledger Id Business Id Date Received Date Completed Record By Service Charge The primary key in this table is BKKeeping Ledger Id a unique identifier to determine different bookkeeping services completed by P amp Co for the clients business Business id is the foreign key in this table as a cross reference to clients business VAT VAT Ledger Id Business Id VAT Reg No VAT Period Date Received Date Finished Received By VAT Service Charge The primary key in this table is VAT Ledger Id a unique identifier to determine different VAT services completed by P amp Co for the clients business Business id is the foreign key in this table as a cross reference to clients business PAYE Sheet Id Business Id PAYE Ref PAYE Office Date Finished PAYE Service Charge The primary key in this table is PAYE Sheet Id a unique identifier to determine different PAYE service completed by P amp Co for the clients business Business id is the foreign key in this table as a cross reference to clients business Invoice No Business Id Date Subtotal VAT Total The primary key in this table is Invoice No a unique identifier to determine different Invoices issued by P amp Co either to the client or its business Business id is the foreign key in this table as a cross reference to clients business P
17. Contents TABLE OF CONTENTS SUMMARY ACKNOWLEDGEMENT CHAPTER 1 INTRODUCTION 1 1 Introduction 1 2 Minimum Requirements 1 3 Understanding the Business Background 1 4 Problems 1 5 Feasibility Study 1 6 Scope of the Project CHAPTER 2 BACKGROUND RESEARCH 2 1 2 2 Introduction Approach and Project Plan 2 2 1 Methodology 2 2 2 Schedule and Project Management CHAPTER 3 REQUIREMENTS AND ANALYSIS 3 1 3 2 33 34 3 5 Introduction Current System Procedures Flow Diagram Requirement Analysis 3 4 Functional Requirements 3 4 2 Non functional Requirements Selection of Implementation Tools CHAPTER 4 DATABASE DESIGN 4 1 4 2 Introduction Entity Relationship Modelling Page ii EW N e FF m QV a a A 10 10 11 12 12 14 14 Table of Contents Client and Sales Record Management System for P amp Co Chartered Accountant 4 3 44 4 5 4 2 1 Mapping the E R Model into Relational Tables Integrity Constraints 4 3 1 Entity Integrity 4 3 2 Referential Integrity Normalisation User Interface Design 4 5 1 User Interface Architecture 4 5 2 Forms Design CHAPTER 5 IMPLEMENTATION 5 1 3 2 5 3 5 4 Introduction Microsoft Access Table Implementation User Interface Implementation CHAPTER 6 TESTING INSTALLATION AND EVALUATION 6 1 Introduction 6 2 System Testing 6 2 1 Function Testing 6 2 2 Validation Testing 6 3 User Acceptance Testing 6 4 Installation of the Software and
18. General Lookup Field Size Long Integer Format Decimal Places Auto Input Mask Caption The field description is optional It helps you describe the field and is also displayed in the Default value Validation Rule Validation Text Required Yes Indexed Yes No Duplicates status bar when you select this Field on a Form Press F1 for help on descriptions Service Table Design 69 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix I Design View iBix FieldName DataType 1 1 Desrpin g Tax Sheet Id Number The unique number used to identify the tax sheet dClient Id Text The unique identifier of the business Tax Ref Number The reference number related to clients self assessment tax return Tax Office Text The office that deals with clients self assessment tax return Date Finished Date Time The date the self assessment tax return is completed for client Tax Service Charge Text The amount that P amp Co charged the client when self assessment service is provided E E iE E x Field Properties General Lookup Format Input Mask 00 00 0000 0 _ Caption Default Value Validation Rule The field description is optional It helps you describe the Validation Text Field and is also displayed in the status bar when you select Required No this field on a Form Press F1 for help on descriptions Indexed No Tax
19. Id 9 Business Name Type Of Business Date Business Start Business Tel Business Add Linel Business Add Line2 Postcode Status A C YE Client Business Client Id Business Id Both of the attributes are primary keys Bookkeeping BKKeeping Ledger Id Business Id Date Received Date Completed Received By Service Charge VAT VAT Ledger Id Business Id VAT Reg No VAT Period Date Received Date Finished Received By VAT Service Charge PAYE PAYE Sheet Id Business Id PAYE Ref PAYE Office Date Finished PAYE Service Charge Tax Tax Sheet Id Client Id Tax Ref Tax Office Date Finished Tax Service Charge Sales Invoice No y Business Id Date Subtotal VAT Total Payment Payment Id Payment Date Received By Amount Owed Amount Outstanding Amount Paid Balance Sales Payment Payment Id Invoice No p Both of the attributes are primary keys Service Service Id Description Service Charge Sales Service Invoice No Service_Id 9 Sheet No As the functional dependencies of the database have been defined the processes of normalisation can now commence First Normal Form 1NF First Normal Form states A table is in First normal form 1NF if and only if all columns contain only atomic values that is there are no repeating groups columns within a row 23 p99 The database is in 1NF because all the attributes
20. Maintenance 6 5 Users Training 6 6 Evaluation CHAPTER 7 EVALUATION 7 1 Introduction 4 2 Project Evaluation 7 3 Further Enhancements 7 4 Project Conclusion 16 18 18 18 18 20 20 21 23 23 23 24 29 29 29 29 30 30 30 31 33 33 34 35 Client and Sales Record Management System for P amp Co Chartered Accountant Table of Contents REFERENCES APPENDICES Appendix A Personal Reflection Appendix B Project Schedule Outdated Appendix C Project Schedule Revised Appendix D Minutes of Meeting Appendix E Requirement Analysis Appendix F User Requirements Appendix G User Interface Form Appendix H Implementation Appendix I Design View Appendix J Database Report Appendix K Testing Appendix L User Manual 36 39 39 40 41 42 46 58 59 61 62 66 68 74 Client and Sales Record Management System for P amp Co Charterec Accountant Introduction Chapter 1 INTRODUCTION 1 1 Introduction This chapter examines the minimum requirements of this project and outlines the business background and associated problems of P amp Co Chartered Accountant The scope of the project will also be clarified in due course 1 2 Minimum Requirements After suggestion from the project assessor the minimum requirements were revised since the previous version was not made clear The minimum requirements were agreed to be 6 To replace the paper based system in P amp
21. System for P amp Co Chartered Accountant Background Research Project management is extremely important to a project like this which has a very limited time scale and has to be completed on time In order to create a framework for managing the project a project schedule is constructed and represented as a Gantt chart in Appendix B Page 40 illustrating the duration of different activities Following the discussion with the project assessor a more realistic and up to date schedule has been realised Therefore the revised version is being represented in Appendix C Page 41 Six main phases of the project have been identified and they are as follows Phase 1 This is the first stage of the waterfall model It will involve carrying out some initial research studying relevant books and web sites to obtain the basic knowledge about underlying technologies in order to do the rest of the project It will also include gathering requirements from the owners of the problem and analysing them Phase 2 and Phase 3 These are derived by the waterfall model stage two three and four naturally the design coding and testing Phase 2 will consist of implementing a functional dependency analysis normalisation and design on the database Implementing the new database and producing standard clients sales invoice and payment receipt will also be incorporated in this phase Designing implementing and testing the data entry interface will make up phase 3
22. Table Design EH YAT Table inl x E Field Name DaaTypp A ELbaT Ledger Id Number The unique summary sheet number when a VAT is completed Business Id Text The unique identifier of the business VAT Reg No Number The VAT Registration number of the clients business vAT Period Text To define the Four quarter of the VAT period Date Received Date Time The date the client brings in the VAT For P amp Co Chartered Accountant to work on Date Finished Date Time The date the clients VAT is completed by P amp Co Received By Text The person who received the clients VAT VAT Service Charge Text The amount that P amp Co charged the client when VAT service is provided zl Field Properties General Lookup Field Size Long Integer Format Decimal Places Auto Input Mask Caption 4 field name can be up to 64 characters long including Default Value spaces Press F1 for help on field names Validation Rule Validation Text Required Yes Indexed Yes No Duplicates VAT Table Design Note The above associated with the Sales Table Design included in the Report are the nine main table designs in the P amp Co Database 70 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix J Database Report Appendix J Database Report Client Sales Summary Report P amp CO CHARTERED ACCOUNTANTS First Name Date 15 06 2003 Surname Invoice No B
23. Therefore it will not suitable to use towards the latter stage of the project However Waterfall model 5 p 5 considers all the stages it considers the process from requirements analysis through specification design coding testing to documentation and maintenance and upgrades In addition iteration is allowed should any modifications to the work already carried out be required Thus the waterfall model allows the project to be complete in a logical order and permits stages to be revisited if new requirements discovered or there are any changes need to be made This is a form of Rapid Application Development following the waterfall model as an outline framework Also since it already has been agreed in Section 1 6 that the project is feasible and should be continued to develop into further stages there is no need to carry out another feasibility study as stated in SSADM Moreover The emphasis of the waterfall model is on moving down the model the iteration through steps is limited Therefore the process of the project would benefit from it because through testing is completed on each section as the project proceeds the risk of having to rework tasks that were previously thought of as completed will be minimised 2 2 2 Schedule and Project Management Project Management ensures that a project is completed on time within budget and with the required functionality and quality 3 p205 11 Client and Sales Record Management
24. and design 3 Edition Prentice Hall Australia 17 Barry Eaglestone 1991 Relational Databases Stanley Thornes Publishers Ltd 18 Chapter 3 Entity Relationship Model Susan Gauch with some slides by David L Spooner Rensselaer Polytechnic Institute URL http www ittc ku edu sgauch 647 s00 notes Ch3 sld003 htm 22 February 2003 19 Mott P and Roberts S 1999 2000 DB11 Introduction to Databases Module Notes and Coursework School of Computing University of Leeds 20 Databases Glossary URL http databases about com library glossary bldef primarykey htm 3 March 2003 21 Databases Glossary URL http databases about com library glossary bldef foreignkey htm 3 March 2003 22 Roberts SR 2001 Database Principles and Practice Module Course Notes University of Leeds 23 Teorey TJ 1999 Database Modelling and Design Morgan Kaufmann Publishers 42 Client and Sales Record Management System for P amp Co Chartered Accountant Reference 24 Kirakowski Jurek 1988 Human Computer Interaction from voltage to knowledge Chartwell Bratt 25 Jenny P Yvonne R Helen S David B Simon H and Tom C 1994 Human Computer Interaction Addison Wesley 26 Faulkner Christine 1998 The Essence of Human Computer Interaction Prentice Hall 27 Susann Novalis and Dana Jones 2001 Access 2002 VBA Handbook Sybex 28 Arthur M Langer 1997 Analysis and Design of Informati
25. are no such tailored packages that can be found on the market On the other hand there are packages that can partially meet the P amp Co sales record management system requirements The most suitable accounting package is called Sage 2 which costs almost 2000 but it does not meet the full user requirements of the client record management system Also the software package is over the budget and there may be problems amending it that can cost even more Therefore the author of this report has been asked to produce a system that would solve the problem whilst reducing the cost The current system procedures and user requirements will be discussed fully in Chapter 3 1 5 Feasibility Study Feasibility study is used widely in system development projects because it provides a sense of direction and will save time and money by identifying the feasibility of the project In order to ensure the project is valuable and feasible a brief feasibility study is examined in three aspects technical financial and social e Technical Feasibility The implementation will involve a cheap and easy to use relational database using Microsoft Access and the staff have a basic knowledge in using Access The design and internal structure of the database is relatively straightforward and can be implemented therefore it is technically feasible e Financial Feasibility The project is considerably low cost because P amp Co has Microsoft Access 2000 in
26. as who received it on a piece 13 Client and Sales Record Management System for P amp Co Chartered Accountant Requirements and Analysis of paper as a record The staff may occasionally forget to do this or lose the paper which cannot keep track of the record In the worst case the bookkeeping may be left incomplete until the client rings up and checks the process VAT Details Clients who make more than or equal to 55 000 a year in their businesses need a VAT service every three months A paper is prepared every month by the staff to record which client needs to complete the VAT and when the client has brought the VAT taking sheets in but sometimes the paper prepared may be out of date The staff must make sure all the clients have brought the VAT in by the fifteenth of every month for the accountant to work on since there will be a penalty from HMCE if the payment is not posted in by the end of the month Again the staff may sometimes forget to or lose the paper which cause problems e g penalty As mentioned in Section 1 4 and 1 5 works are now transferred across three branches when the client telephones to check the process of the VAT workings the staff will have to ring the responsible branch for the process and then reply back to the client This process is very time consuming which will delay the job on which the staff is working PAYE Details PAYE means pay as you earn the employees and employers have to pay
27. clients missing files since their details are now stored in the server with backup every night e The Sales invoice and Payment receipt could have improved but this is still acceptable e The interface design looks professional and I really like the design of the P amp Co logo on each forms and reports From Jonathan Pitayanukul Owner of P amp Co 75 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix K Testing Appendix K Testing of the Database System Filled in by users Client Form Add Client Form permits a Client Details to be added to the Client and As Client Business table Input into every field in the form is bound expected properly to the table Ensures that a number is entered into the unique Client Id field Find Edit Find a Client record correctly and permits amendments on that As Client record expected Delete Client Warning message turns up to avoid unintended deletion of records As and the record should be deleted from the related table s while the expected client has not required any P amp Co service Find Sales This button is implemented in order to convenience the staff in case As Ledger the client wants to check their account details Once the button expected Find Sales Ledger is pressed it will bring the staff to the Form Client Sales Ledger and the staff will be able to look for the client accounts detail
28. ead w Record il 4 II 1 Lou r of 1 Figure 3 1 Client Details Screen 82 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix L User Manual 3 1 Add Client Record Add New Client Record can be added by clicking this button esl in the Client Details form a new screen as indicated in Figure 3 2 will turn up The user can then input all the client details in the form amp Client BEE P amp CO Client Details CHARTERED ACCOUNTANTS NEIN Probie Business Id Business Name Type_Of_Business Date Business Start Business Ti Record 14 4 1 feles of 1 Find Sales D Add Find Edit Delete We Ledger Record Record Record Record 1 2 x ples of 2 Figure 3 2 New Client Details Screen 3 2 Find Edit Client Client record can be found and edited once the details are entered in the database Clients details can be found and edited by the E When this button is clicked the Find Replace box will turn up see Figure 3 3 and the user can enter any key fields in the Find What box and then select one of the field Look in If Client is selected in the Look in box it means that the database will be able to search through the database to retrieve the right Client record that matches the Find What field Afterthat F
29. field in the form is bound expected properly to the table Ensures that a number is entered into the unique Bkkeeping Ledger Id field Find Edit Find a Bookkeeping record correctly and permits amendments on As Bookkeeping that record expected Quit Once the quit button is pressed it should bring the staff back to the As main menu expected VAT Form Add VAT Form permits a VAT Details to be added to the VAT table Input As into every field in the form is bound properly to the table Ensures expected that a number is entered into the unique VAT Ledger Id field Find Edit Find a VAT record correctly and permits amendments on that As VAT record expected Quit Once the quit button is pressed it should bring the staff back to the As main menu expected PAYE Form AddPAYE Form permits a PAYE Details to be added to the PAYE table As Input into every field in the form is bound properly to the table expected Ensures that a number is entered into the unique PAYE Sheet Id field Find Edit Find a PAYE record correctly and permits amendments on that As PAYE record expected Quit Once the quit button is pressed it should bring the staff back to the As main menu expected 13 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix K Testing Tax Form Add Tax Form permits a Tax Details to be added to the Tax table Input As into every field
30. finding editing and deleting Clients Business details must be possible Bookkeeping Adding finding and editing Bookeeping details of the client business must be possible VAT Adding finding and editing VAT details of the client business must be possible PAYE Adding finding and editing PAYE details of the client business must be possible Tax Adding finding and editing the Tax details of the client must be possible Sales Adding finding and editing Sales details of the client business must be possible Ability to print the Sales Invoice and the Clients Sales Summary Report Payment Adding finding and editing Payment details of the client business must be possible Ability to print the Payment Invoice Client Sales Ability to find the client sales ledger record and print the Client Sales Ledger Ledger For Bookkeeping VAT PAYE Tax Sales and Payment Details there is no need to have a delete function because the services and invoices that P amp Co offered to client or his business will need to keep track of the record for future practice If the records are deleted the information will be lost However for Client or Business Details the record can be deleted if the client or his business has not needed any service from P amp Co The Client Sales Ledger Details is retrieved from the sales and payment details and therefore records should be added and updated in those two tables
31. in the database contain only atomic values Second Normal Form 2NF Second Normal Form states A relation schema R is in 2NF if every nonprime attribute A in R is fully dependent on the primary key of R 7 p488 As seen from the functional dependencies 24 Client and Sales Record Management System for P amp Co Chartered Accountant Database Design defined above the database is in 2NF since there are only primary keys determining the nonprime attribute Third Normal Form 3NF Third Normal Form is based on the concept of transitive dependency A functional dependency X gt Y ina relation schema R is a transitive dependency if X Z and Z Y hold 7 p489 According to Codd s original definition a relation schema R is in 3NF if it satisfies 2NF and no nonprime attribute of R is transitively dependent on the primary key 7 p490 Since the database is in 2NF and no transitive dependencies exist in the relation schemas 3NF holds in the database Boyce Codd Normal Form BCNF A relation schema R is in BCNF if wherever there is a non trivial functional dependency X gt A holds in R then X is a superkey of R 7 p494 BCNF is slightly stricter than 3NF but in practice most relation schemas that are in 3NF are also in BCNF 7 p494 In the database relation schemas there are no functional dependencies that do not have a superkey and so BCNF holds 4 5 User Interface Design HCI stand
32. in the form is bound properly to the table Ensures expected that a number is entered into the unique Tax Sheet Id field Find Edit Tax Find a Tax record correctly and permits amendments on that record As expected Quit Once the quit button is pressed it should bring the staff back to the As main menu expected Sales Form Add Sales Form permits a Sales Details to be added to the Sales and As Sales_Service table Input into every field in the form is bound expected properly to the table Ensures that a number is entered into the unique Invoice_No field Find Edit Find a Sales record correctly and permits amendments on that AS Sales record expected Save Sales Once Save Record button is pressed the figures for the fields AS Record Subtotal VAT and Total are automatically calculated expected correctly Preview and The user interface of the form Sales is also the standard sales AS Print a Sales invoice that can be printed out to clients Preview of the sales expected Invoice invoice in print layout is possible and allow the invoice to be printed Quit Once the quit button is pressed it should bring the staff back to the AS main menu expected Payment Add Payment Form permits a Payment Details to be added to the Payment and AS Form Sales_Payment table Input into every field in the form is bound expected properly to the table Ensures that a number is entered into the uni
33. invoice to Invoice be printed Find Balance Once the Find Balance button is pressed it is linked to the form AS Client Sales Ledger to allow the user to look for the clients expected outstanding balance Quit Once the quit button is pressed it should bring the staff back to the AS main menu expected Client Sales Find Record This form is made up by the combination of Sales and Payment AS Ledger tables to show the P amp Co clients account details so update expected Form edit and delete of the clients record should be either done in forms Sales or Payment Once the Find Record is pressed a Client accounts record is find correctly Client Sales Preview and The user interface of the form Client Sales Ledger can be printed AS Ledger Print a out to clients Preview of the client sales ledger in print layout is expected Form Clients Sales possible and allow the client sales ledger to be printed Ledger 77 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix K Testing Client Sales Preview and Preview all reports in print layout and allow the report to be printed As Summary Print a Report expected Report VAT Preview and Preview all reports in print layout and allow the report to be printed As Report Print a Report expected Main Menu Open each When the View button is cl
34. is based on McCall s studies 29 His guidelines focus on the three important factors in terms of functionality usability and maintainability of a software product which is very relevant to any system development like this Therefore it had been chosen to be the evaluation criteria The guidelines of the criteria and the findings from the evaluation are as follows 1 Correctness Extent to which the database fulfil the user requirements All the requirements defined from the users with additional enhancements such as the production of VAT report are being included in the database and all the users were pleased that their requirements are met Thus the database fulfils the user requirements and meets the correctness criteria 2 Reliability Extent to which no errors are found in terms of the functions of the database The data input find edit and delete was examined thoroughly for each form by the author and the users The database operated properly during testing and all data was stored accurately 3 Efficiency Extent to which the database improves on current paper based system The database efficiency is determined by the time taken to complete a task the two users were timed to record the time it took to complete a task in the paper based system and using the new database Two tasks were given to the two users 1 input client details and 2 find a clients VAT registration number The users were enquired to com
35. national insurance contributions NIC and tax on what they earn Manchester office is now responsible for all three branches PAYE services the accountant who deals with it always needs to request other branches staff to look for information in order to complete the work Details of PAYE information is placed in another block of filing cabinets which the staff will need to search through This is again very time consuming Tax Details Each person who is self employed needs to review his her tax situation every year The London office is now responsible for completing self assessment tax returns as the specialist accountant is situated in London Thus the Manchester and Leeds offices staff always need to find the required information for the London accountant to complete the work Client Sales Ledger Details same as Clients Sales and Payment Record Details Only London and Manchester offices have the system to record clients sales and payments while Leeds clients accounting system is being kept in Manchester office Therefore once a service is provided to the client or the client has made a payment the staff in Leeds office will need to inform Manchester either by phone or by fax for updating the clients sales and payment records 14 Client and Sales Record Management System for P amp Co Chartered Accountant Requirements and Analysis e Jfa client rings up the Leeds office to check how much he she has owed P amp
36. the Leeds Manchester and London offices on different days of the week Thus he should have the knowledge of what is needed in the new system The detailed minutes of the meeting with Jonathan can be founded in Appendix D Pages 42 45 The following is a summary description of the procedures and problems in the current paper based system of each branch Client and Business Details e A new paper based file is created to record the new client and business details Additional correspondence is also filed into it for instance their bookkeeping details and tax details etc The file is then placed in one of the filing cabinets according to the business identification number e If the staff wish to find specific client or business details they will search through the specific cabinet which contains the file e Updates and deletions of clients or business details are always written on paper first until the staff have time to find the specific client files to make the correction However the staff sometimes forget to do this or lose the paper which cannot make the amendment Bookkeeping Details e Clients who make less than 55 000 profit a year in their businesses need a bookkeeping service every three months The staff who collects the thirteen weeks bookkeeping takings sheets the sheets which record each weeks profits and expenses and purchase invoices from a client will record the date the bookkeeping is brought in and completed as well
37. the user can add find edit and delete the business record which is the same as the Client Form Also the user can quit and go back to the main menu Therefore for details of how to operate this form can be found in Section 3 8 Bookkeeping PAYE Tax These form are operated the same as the VAT Form the user just need to input the Client Id Business_Id and the subforms then the rest of the client or business details will turn up automatically because they are either linked to the client table or business table For the functionality such as add find edit and produce invoice are operated the same as VAT Thus for details of how to operate these forms can be founded in Section 4 89 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix L User Manual 9 Client Sales Ledger The sample interface form of this can be found in Figure 3 4 the user can only find client sales ledger by clicking the Find Record button since amendments will need to be made either from Sales or Payment form When the changes are made either to Sales or Payment the Client Sales Ledger form will be updated automatically Also there is a print button for the user to print out the Client Sales Ledger 90
38. then be followed by a conclusion of how this project turned out and possible recommendations for the future Archievements I have met all the above requirements with additional features implemented into the database the details of which may be found in this report The database is now working which allows the administrators and accountants in P amp Co to store information in the database in a timely and suitable manner The end users are very satisfied with my finished working system Client and Sales Record Management System for P amp Co Chartered Accountant Acknowledgements Acknowledgements I would like to thank the following people for their help and support in the completion of this project Haiko Muller my project supervisor for all his advice and encouragement in guiding me throughout this project Stuart Roberts my assessor for providing me valuable suggestions after the mid project report and during the progress meeting Jonathan Pitayanukul the owner of P amp Co Chartered Accountant who has been always ready to answer questions throughout the development of the database Owen Johnson for his advice on software development methodology Mark Chan for his technical advice in the database design and implementation Jacky Chan and Amy Williams for their love support and understanding throughout my project Client and Sales Record Management System for P amp Co Chartered Accountant Table of
39. this was fixed immediately 6 4 Installation of the Software and Maintenance Installation of the software was carried out by the author in the P amp Co Leeds office the other two branches will also install the software soon by the P amp Co technician The process of installation included zipping the database to a floppy disk and then installing it onto the P amp Co server To ensure the database could be reinstalled if one storage medium was corrupted back ups of the database were made to a floppy disk and this was kept by the owner in P amp Co Since the author will not be able to perform maintenance of the database the P amp Co technician has agreed to take on this role 6 5 Users Training Training the users is essential in ensuring that the database can and will be used by them Given that P amp Co are very busy with their accountancy role a one day training had been provided in the Leeds office based on using the database itself rather than given as a lecture This ensures users are comfortable with using the database quickly and become familiar with it A detailed user manual as 35 Client and Sales Record Management System for P amp Co Chartered Accountant Testing installation and evaluation shown in Appendix L Page 74 85 had been given to the technician so that he can also train the other staffs in Manchester and London 6 6 Evaluation The criteria that was used to evaluate against the database
40. will search through the specific cabinet which contains the file While the information is requested from other branches staff the staff will need to ring or fax the required information back to reply Occasionally client files may be lost and therefore cannot find the required information Updates and deletions of clients or business details are always being written on paper first until the staffs have time to find the specific client files to make the correction However the staffs sometimes forget to or lose the paper which cannot make the amendment Bookkeeping Details Clients who make less than 55 000 profit a year in their business need a bookkeeping service in every three months The staff who collects the thirteen weeks bookkeeping taking sheets the sheet which records each weeks profits and expenses and purchase invoices from a client will record the date the bookkeeping is brought in and completed as well as who received it on a piece of paper as a record The staffs may occasionally forget to or lose the paper that cannot keep track of the record In the worst case the bookkeeping may be left uncompleted until the client ring up and check the process 47 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix D Minutes of Meeting VAT Details Clients who make more than or equal to 55 000 a year in their business need a VAT service in every three months There is a paper prepa
41. 12 13 14 Develop a database which is able to store all details for Client Clients Business Bookkeeping VAT PAYE Tax Sales Invoice and Payment Adding finding editing and deleting for a Client details must be possible Adding finding editing and deleting for a Clients Business details must be possible Adding finding and editing for the Bookkeeping details of the client business must be possible Adding finding and editing for the VAT details of the client business must be possible Adding finding and editing for the PAYE details of the client business must be possible Adding finding and editing for the Tax details of the client business must be possible Adding finding and editing for the Sales record details of the client or its business must be possible Adding finding and editing for the Payment record details of the client or its business must be possible Finding a Clients Sales Ledger details must be possible Able to print the Sales Invoice and the Clients Sales Summary Report Able to print the Payment Invoice Produce an up to date printout of monthly VAT report provided there is sufficient time available Design the architecture of the networking of the database provided there is sufficient time available so that all three branches can access the same database The above user requirements have been agreed by the users 63 Client and Sales Record Management System for P amp Co Chartered A
42. Client and Sales Record Management System for P amp Co Chartered Accountant Summary Summary The main aim of this project was to implement a client and sales record management system for a medium sized accountancy firm named P amp Co Chartered Accountant based in England This is achieved by developing a PC based database using Microsoft Access 2000 Below is a list of revised minimum requirements for the project 1 To replace the paper based system in P amp Co Chartered Accountant to a computerised system 2 To gather user requirements from the administrator and accountant in P amp Co in short to obtain a clear view of the problem on the existing paper based system 3 To meet the user requirements and to recommend a new system by introducing database to store client and sales records 4 To design and implement a database holding information about P amp Co customers and their account details 5 To evaluate the system My personal objectives for this project are e To review much of the work done during my studies at University e To gain experience of managing project especially to apply a software development methodology to a real life development situation e To gain a practical understanding of how to design and implement a database and its interface This report will document the methodology used for this project as well as the design implementation testing and evaluation stages of the database It will
43. E Requirement Analysis Appendix E Use Case Description Use Case Specification Delete Client Details Revision History 03 Dec 02 Use Case Description was completed after the Use Case Diagram Ka Man Chan were drawn and agreed by the owner in P amp Co Use Case Name Delete Client Details Pre Conditions Summary This use case extends Find Edit when a client record is no longer needed the Member of Staff then deletes it from the database Primary Actor Member of Staff Other Actor None Goal To delete the client details into the database Basic Course of Use Case begins after a Member of Staff found a client record If there are no Events transaction records for the customer the customer record maybe deleted Alternative Paths None Exception Paths Cannot be deleted if transactions records exist Trigger The Member of Staff wishes to clean up the inactive records in the database Assumptions Client record is no longer required The record has already been found Post Conditions The client record has been successfully deleted from the database Project Author Date Client and Sales Management System for P amp Co Chartered Accountant Ka Man Chan 03 Dec 02 62 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix F User Requirement Appendix F Use Requirements DOs c gy UA ER I9 9 10 11
44. Enter database password o CN Figure 2 3 Enter a Password After the password is entered and OK is clicked the database is now running the user will be able to see the Main Menu Screen as shown in Figure 2 4 The user can then choose any of the nine options by clicking on the tab button with the relevant text on it 8l Client and Sales Record Management System for P amp Co Chartered Accountant Appendix L User Manual iBixi Client and Sales Record Management s View Client Details j Record 14 4 If 1 gt nrlrs of 1 Figure 2 4 Main Menu By selecting the relevant tab button on the main menu the database will navigate the user to the relevant screens The Quit option will close the Database and return the user to the Windows menu 3 Client When the Client option is chosen from the Main Menu the user is directed to the screen shown in Figure 3 1 below 1 x P amp CO Client Details CHARTERED ACCOUNTANTS gt EX 45 Birdton Road Ms E Leads Susan L512 3DE Wong m 01132 668125 12 05 1885 Mobile mo poes Business ld Business Name Type Of Business Date Business Start Business T gt BcLOD1 Big Chef Chinese Takeaway 19 06 2002 01132 58958 DRL002 Dragon Rose Chinese Takeaway 30 03 2003 01132 59112 14 lt Record 1 nr of 2 Find Sal Add Find Edit Delet nes Record azar
45. F1 For help on field names Validation Rule Validation Text Required Yes Indexed Yes No Duplicates PAYE Table Design 68 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix I Design View loxi E E OE A E EE 9 Payment Id Number The unique number used to identify the clients payment Payment Date Date Time The date the client rade the payment Colected By Text The administrator who received the payment Amount Owed Currency The amount owed from the client as shown in the invoice Amount Outstanding Currency The amount still outstanding From previous payment Amount Paid Currency The amount paid from the client gt Balance Currency The clients balance that is left For next payment m m 3 E L zi Field Properties General Lookup Format Currency Decimal Places Auto Input Mask TEE The field description is optional It hel ield description is optional elps you Sa a describe the field and is also displayed in the j status bar when you select this field on a validation Text form Press F1 for help on descriptions Required No Indexed No Payment Table Design ioi E Field Name pasty S a EH Service Id Number The unique identification number used to identify a particular service type Description Text Description of the service Service Charge Currency The service charge of the service provided Field Properties
46. Feb May Aug Nov 15 04 2003 25 04 2003 Kirst Record I4 Figure 4 2 VAT subform 85 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix L User Manual 4 2 Find Edit VAT Record Find Edit for VAT record of the client business operates the same as the Client Form as mentioned in Section 3 2 VAT details can be found and edited by the pee button When this button is clicked the Find Replace box will turn up see Figure 3 3 and the user can enter any key fields in the Find What box and then select one of the field Look in If VAT is selected in the Look in box it means that the database will be able to search through the database to retrieve the right VAT record that matches the Find What field Afterthat Find Next button need to be clicked to operate the search function Once the VAT record is found the user can view VAT details and make necessary changes if necessary 4 3 Produce Invoice Produce Sales Invoice can be produced by clicking this Invoice button to bring the user to the Sales form which is the sales invoice to the client see Figure 4 3 The user can then create new invoice by clicking the Add Invoice button This button is implemented as an enhancement to convenient the user amp Sales a loj x CHARTERED ACCOUNTANTS Sales BENE j Dragon Rose 2 Date 12 04 2003 Service Id De
47. L DB2 and Microsoft Access The table drawn below is the summary of features and performance of these four different DBMS devised from 6 9 10 and 11 They are in the order of importance relating to the development of this project Features and performance Oracle Microsoft SQL DB2 Microsoft Access Server 7 2000 Ease of use 3 Ease of use 2 Ease of use 3 Ease of use 1 Ease of use Cost Most Expensive Third Expensive Second Expensive Cheapest Security Best Good Good Reasonable Concurrent access Available Available Available Available High transactions data entry per Suitable Suitable Suitable Not Suitable day Functionality Good Platform All platforms Windows only All platforms Windows only Figure 3 1 Comparison of four different DBMS The factors that are most vital in deciding which DBMS to use for this project are Cost Ease of use and Security Ease of use is the most important issue in this development because the end users only have limited knowledge in using computers Therefore the DBMS used must be sufficiently easy to use Cost is also another important factor since P amp Co will need to invest money in updating new PCs monitors and in purchasing the ScanSoft PaperPort software The company budget for the development of this project is 200 In terms of security as the data holding in the database is confidential the DBMS chosen must be able to provide reasonable security such as a
48. RR RR RR RN Business Id Text The unique identifier of the business Business Name Text The business name that the clients operate Type Of Business Text The type of business that the clients operate Date Business Start Date Time The starting date of the business Business Tel Text Business telephone number Business Add Line1 Text The first line of the business address Business Add Line2 Text The second line of the business address P Postcode Text The postcode of the business address __ Status Text The status of the owner s e g sole trader partnership limited company changed status or ceased trading ajC vE Text The date of the year end account sel Field Properties General Lookup Field Size 50 Format Input Mask gt LLOO OLL Caption Default value The field description is optional It helps you describe the field and is also Validation Rule displayed in the status bar when you select this Field on a Form Press F1 For Validation Text help on descriptions Required No Allow Zero Length No Indexed Yes Duplicates OK Unicode Compression Yes Business Table Design 67 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix I Design View ipis E Feld N Name omaTpe Dem 0 BkKeeping Ledger Id Number The unique summary sheet number when a bookkeeping is completed Business Id Text The unique identifier of the business Date Received Dat
49. Staff will search the database to retrieve information and make necessary changes to the client record as needed Primary Actor Member of Staff Other Actor None Basic Course of Use Case begins when the Member of Staff receives additional different Events information about a client The Member of Staff uses a range of key fields to search for the client in the database finds him her and makes the necessary changes Alternative Paths 1 The changes may have already been made and thus no editing is required 2 No changes are required the Member of Staff is only retrieving client information 3 Member of Staff receive a request for information about a client the Member of Staff finds the client details from the database and reply back Exception Paths The client may not have been added in the database Trigger 1 Request for client details to be changed is received by the staff member 2 Request for retrieving client details from other branches staff is received by the Member of Staff Assumptions The client is the member of P amp Co Chartered Accountant Pre Conditions 1 Theclient or staff has identified the changes that need to be made and told the Member of Staff Post Conditions Searching the client record successfully or changing the client record successfully Author Ka Man Chan Date 03 Dec 02 61 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix
50. ales Ledger is the combination of tables Invoice and Payment This architecture should also help the implementation phase that will be outlined in Chapter 5 25 Client and Sales Record Management System for P amp Co Chartered Accountant Database Design FormClient Client Details option FormBusiness FormLogin Business Details option User Authorisation FormBookkeeping Bookkeeping Details option FormVAT FormMainMenu VAT Details option Main Menu option FormPAYE PAYE Details option FormTax Tax Details option FormSales ReportClientSalesSummary Sales Details option Print Client Sales Summary renort FormPayment Payment Details option Figure 4 3 User Interface Architecture FormClientSalesLedger Client Sales Ledger option 4 5 2 Forms Design Some useful guidelines regarding HCI issues in creating more usable forms are set out below Meaningful title and familiar field labels avoid computer terminology titles use terms which users are familiar with Logical grouping and sequencing of fields related fields should be adjacent sequencing should reflect common patterns Visually appealing layout of the form pay attention to the spacing of entry fields ensure each alignment is consistent and do not overcrowd the screen Consistent terminology and abbreviation label the text boxes or buttons with the same name if they perform the same function abbreviation can be used but have to be consistent and brief Vis
51. at works are centralised Specialist work e g VAT PAYE etc is increasingly transferred from one branch to another leading to the need to share information When the London branches staff request information the Manchester or Leeds branches staff will need to telephone or fax the required information back as a reply The owner therefore realised the problems associated with the current paper based system He intended to update the three branches with a computerised system in July 2003 using document scanning storage system ScanSoft PaperPort Deluxe 8 0 7 to reduce the piles of paper in the office However this system only scans the document into the file directories on the network Since the documents will be indexed by client id and date therefore when data needs to be viewed the staff will still have to search through the records in the file directories to look for clients information and the problem of time consumption still exists As a result the owner saw the need to build a computerised system that can be used in all three branches to centre all the clients information in one place The owner of P amp Co would also like to change the accounting system since both Manchester and London offices accounting systems are difficult to use and cannot provide standard sales invoice and payment receipt 1 4 Problems There are two types of problems in P amp Co Chartered Accountant and they are shown below Existing probl
52. aved as a VBA function which will be referred directly by the command button name in the properties of the Access icon see Figure 5 6 and Figure 5 7 below The VBA code is developed within the Access Design Form by selecting the Built Event Option Once the user clicks the button the VBA code performs the function 7j i Command Button Command23 4 X IE se ke sk oe ke zb oe be zl oe be zl ole be zl oe bez oe be zl oe be zl ole be zb oe bez oe be zb oe bez oe be zb oe k k k k k kk Format Data Event Other Al Function Command23 On Enter ces dsi Once the Save Record button is being clicked it will SIME DURUM ERE automatically calculate the following figures OnGotFocus ees y 8 lg OnLostFocus ses ON GMC sana Md eta EY Event Procedure dl Private Sub Command23 Click jets RS Ta Sere RTETO eee cen on p A DS D S Subtotal DSum Service_Charge Query1 Invoice No On Mouse Move ss 2 2 forms Sales Invoice_No dues A EEG VAT Subtotal 0 175 OnKey DOWN sss isses Onkev Ups oso nore Total Subtotal VAT OM KEY Press cse enn End Sub LLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLI Figure 5 6 Access Icon Properties option Figure 5 7 VBA function Command 23 32 Client and Sales Record Management System for P amp Co Chartered Accountant Implementation Visual Basic Access code has also been used in form Payment the form is given in Figure 5 7 Appe
53. ayment Payment Id Payment Date Collected By Amount Owed Amount Outstanding Amount Paid Balance The primary key in this table is Payment Id a unique identifier to determine different payments made by the client to P amp Co Sales Payment Payment Id Invoice No This table is used to create a M N relationships between the tables Sales and Payment The primary key is a composite this allows both Payment_Id and Invoice No to be duplicated but no combination of the two can be duplicated Service Service Id Description Service Charge The primary key in this table is Service Id a unique identifier to determine different services provided by P amp Co to the client or its business Sales Service Invoice No Service Id Sheet No This table is used to create a M N relationships between the tables Sales and Service The primary key is a composite this allows both Invoice_No and Service_Id to be duplicated but no combination of the two can be duplicated 22 Client and Sales Record Management System for P amp Co Chartered Accountant Database Design 4 3 Integrity Constraints The relational model created for the database relies mainly on the use of primary keys and foreign keys The primary key uniquely identifies each record in the table 20 A foreign key is a field in a relational table that matches the primary key column of another table it can be used to cross r
54. ber of times the interface misleads the users and number of favourable unfavourable user comments By testing the database the user had not been mislead by the fields because they were familiar with them For example for the field National_Ins_No they would know that it means National_Insurance_No The number of unfavourable comment was one which is the form Client Sales ledger could have been designed in a different way but they said it is still acceptable However the numbers of favourable comments made in total by the users far exceed the unfavourable comments which were five such as the navigation is easy to follow and it is much quicker and easier to find a record using the database Overall the database was approved by the users that it is functional and usable 6 Maintainability Extent to which errors or parts require changing can be spotted easily by the maintenance staff The P amp Co IT technician who is responsible for maintenance was asked to confirm that the field names used in the database is understandable later for maintenance purposes He confirmed that they are understandable 7 Performance This is the assessment of response time and accuracy of the database Ten records were filled in each table in the database by the author in order to test the response time and accuracy of the database They were then being searched edited or deleted and the average response time for
55. ble Wizard allows you to select the table you want the fields to originate from The fields you selected will then be placed on the form automatically but how you want the form to be viewed requires you to move the fields manually to the position that suits it most The user interface of the forms were designed as simply as possible and the information on the screens designed in such a way that the user could logically navigate his way around the system The criteria regarding the form design is set out in Section 4 5 2 and the sample of the forms can be found in the Macros Section further on in this chapter 30 Client and Sales Record Management System for P amp Co Chartered Accountant Implementation Queries The function that queries perform is to retrieve and manipulate specified data Five queries have been created in this database development to extract specific records Queries were created by selecting Design View A show table box with all the database tables then came up allowing me to select the tables and the fields I want in order to retrieve the specific data Once all the tables and fields were selected the queries were saved Three queries were used within the VAT report option one query was used in Sales form option and the other was used in Payment form option Report It was only necessary to create one report the Client Sales Summary Report for this database but as time allowed the enhancement fo
56. ccountant Appendix G User Interface Form Appendix G Sample of the User Interface Form P amp CO Client Details CHARTERED ACCOUNTANTS LE iBixd gt E i Bro Road LN mi sra gt BcLo01 g Chef Bi DRLOO2 Dragon Ros 1 gt gt t b of 2 m mm E Record aal aA 1 x Lon r of 2 Figure 4 4 Client Details Interface Form P amp CO CHARTERED ACCOUNTANTS Sales Business _Id Business_Name Invoice_No n Subtotal Save Record Record i 4T 1 gt rire of 2 Figure 5 5 Sales Record Interface Form Service Id Description Service Charge Sheet Record 14 lt 1 nir of 1 120 00 21 00 141 00 E amp 64 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix G User Interface Form iBi xi P amp CO CHARTERED ACCOUNTANTS Payments Payment Id T 28 64 2065 Collected_By Carmen C Invoice_No Business Id Business Name Total Big Chef Record u 4 If 1 tjrs of 1 sci Amount_Outstanding 0 00 za 00 sj e e xx s x Record salaj T rile of 2 Figure 5 7 Payment Record Interface Form w Text Box Balance p x Format Data Event other ai Before Update NCC e eoe eoe eoe eoe 2k os eee eoe eee k k k eoe k ok ok ok ok ok After Update ss sees Function Balance OnChange
57. ction The fourth stage of the Waterfall model is Testing Testing and evaluation play an important role in any system development This chapter will therefore feature the procedures used to both test and evaluate the database and discuss some issues involved in installing the database into its operational environment and the maintenance which is the fifth stage of the Waterfall model 6 2 System Testing Testing is carried out to examine that the database functions correctly and meets the requirements as described in Section 3 3 1 Testing ought to be a continual process rather than a single event carried out once the working database is completed 26 p13 Therefore the system has been subject to a continual testing Through testing the database the author implemented it was showed that the relationship did not link one table to another table properly Therefore the author had to work backwards from the testing stage to the designing stage to find out the mistakes As a result of this the enhancement period is being reduced from two weeks to one week Two types of testing had been carried out on each interface form they are Function Testing and Validation Testing as shown below 6 2 1 Function Testing Function testing confirms that the function performed as required Each time while a new form was implemented it was tested to make sure that it was functioning properly Examples include testing all the buttons
58. e Case Descriptions both of which can be found in Appendix E Pages 46 57 Use Case Diagrams are used primarily to capture the high level user functional requirements of a system 6 p24 It aids the developer to model the database requirements and to communicate with the end users on what the database will do Use Case Descriptions describe the process behind each use case within the diagrams Moreover due to the time limit on this project only some of the client s use case descriptions are included as an example A list of user requirements agreed by the client devised from the use case models can be found in Appendix F Page 58 They are not in order of priority The requirements are then translated into the 15 Client and Sales Record Management System for P amp Co Chartered Accountant Requirements and Analysis functional requirements for the purposed database which is shown in Section 3 3 1 There are also some non functional requirements of the system suggested from the client below in Section 3 3 2 3 4 1 Functional requirements Functional requirements are activities that the new system is required to perform i e what functions the system supports The activities that they cover consist of storing and retrieving data or producing reports Below is the list that the system must enable the user to do Details Function Client Adding finding editing and deleting Client details must be possible Business Adding
59. e Time The date the client brings in the bookkeeping for P amp Co Chartered Accountant to work on P Date Completed Date Time The date the clients bookkeeping is Finished by P amp Co Record By Text The administrator who receives the clients bookkeeping service Charge Text The service charge For the bookkeeping service provided by P amp Co zl Field Properties General Lookup Format Input Mask o0 00 0000 0 _ Caption Default Value Validation Rule The Field description is optional It helps you describe the field Validation Text and is also displayed in the status bar when you select this Required No Field on a Form Press F1 for help on descriptions Indexed No Bookkeeping Table Design oix aee T aate 1 o n U AYE Sheet Id Number The unique no for the PAYE worksheet es Business_Id Text The unique identifier of the business PAYE Ref Text The reference number of pay as you earn in the client business relate to employers weekly wages report to Inland Revenue PAvE Office Text The office that deals with the clients PAYE Date Finished Date Time The date the PAYE service is completed PAYE Service Charge Text The amount that P amp Co charged the client when PAYE service is provided E E E usi Field Properties General Lookup Field Size Long Integer Format Decimal Places Auto Input Mask Caption Default value A field name can be up to 64 characters long including spaces Press
60. e are only four types in Title while many different First Name are possible Mr Mrs Miss and Ms Screen size is also one of the factors in this database development since the design of the user interface form varies from size 15 monitor to size 17 monitor Therefore all the forms have been fixed to one size so that the design would appear the same among different size monitors In order to have a consistent terminology to assist the users to use the database easily buttons with a common functionality have been labelled with either the same text or icon on every form Abbreviations have been used in some of the attributes but they are being implemented because the user is familiar with them Finally the data entry fields such as Home Tel and Mobile have an input mask associated with it and so when the users click on those two fields field will appear This will enable the users to see what they are expected to type Colour and Text The colour and font style are also issues that can make an interface more usable The colours of the forms are designed according to the Industrial style which is one of the functions built inside the Access One of the user interface form Client as shown in Appendix H has been shown to the end user before the implementation of the others The end user commands that the industrial style looks professional but he would like to have a company logo placed on all
61. e database should be restricted to authorised users only Therefore a Password Required Login screen was implemented which is displayed when the database is opened A correct password must first be entered into the login screen to gain access to the database This password is held only by the P amp Co staff The password is implemented by opening the P amp Co database to exclusive use then click Tools select Security and Set Database Password Figures 5 12 and 5 13 below show the procedures of setting the password while Figure 5 14 shows the login screen E Microsoft Access 3x File Edit View Insert Tools Window Help i E Kaman project 7 Q X Ci Be Toks Dee SR Vs 7 W g is ee og d IP amp Co PI Copy of Copy of most update Online Collaboration gt A Copy of P amp Co Database E lP amp Co Database Analyze gt Database Utilities gt Startup tp a User and Group Permissions Customize User and Group Accounts Options User Level Security Wizard Y Encrypt Decrypt Database Files of type Data Fies Figure 5 12 Set database to Open Exclusive Figure 5 13 Set Password Enter database password imei Figure 5 14 Login Screen 33 Client and Sales Record Management System for P amp Co Chartered Accountant Testing installation and evaluation Chapter 6 TESTING INSTALLATION AND EVALUATION 6 1 Introdu
62. e relationship have been created they are named Client Business for the M N relationships between Client and Business Sales Payment for the M N relationships between Sales and Payment and Sales Service for the M N relationships between Sales and Service 21 Client and Sales Record Management System for P amp Co Chartered Accountant Database Design The list of tables accompanied with all the attributes are given in the table below Attributes Client Client Id Title First Name Surname Home Tel Mobile Home Add Linel Home Add Line2 Postcode Email DOB National Ins No The primary key in this table is Client Id a unique identifier to determine different clients Business Business Id Business Name Type Of Business Date Business Start Business Tel Business Add Linel Business Add Line2 Postcode Status A C YE The primary key in this table is Business Id a unique identifier to determine different businesses Client Business Client Id Business Id This table is used to create a M N relationships between the tables Client and Business The primary key is a composite this allows both Client Id and Business Id to be duplicated but no combination of the two can be duplicated Tax Sheet Id Client Id Tax Ref Tax Office Date Finished Tax Service Charge The primary key in this table is Tax Sheet Id a unique identifier to determine different Tax services completed by P amp
63. each task was two seconds with accuracy The results show that the database performance is in an excellent condition The overall results of evaluating the database are favourable The database fulfils the user requirements and the reliability is 100 Efficiency and performance are achieved to allow users to operate much quicker in using new database than the paper based system Access is restricted to authorised users only Several favourable comments were received from the users regarding the usability of the database and the staff have already been using the new database to store client and sales records in Leeds office The other two branches will also start using the database soon In terms of maintenance issue the technician found it easy to maintain because he has already got knowledge in using Access and the field names are made clear to him It can therefore be summed up that the database meets the evaluation criteria and has been deemed a success by both the author and the user 37 Client and Sales Record Management System for P amp Co Chartered Accountant Conclusion Chapter 7 CONCLUSION 7 1 Introduction The preceding chapters featured the process of meeting the project s main aim of developing a database for P amp Co Chartered Accountant This chapter vitally assesses the project aligned with the minimum requirements outlined in Section 1 2 It also includes possibilities of the further enhancements that could be impl
64. ed Accountant Appendix E Requirement Analysis Appendix E Use Case Diagram Bookkeeping Part of the Database Add Bookkeeping Details S Member of Staff E X o 3 Q Edit Find Bookkeeping Details MCN Administrator Accountant 53 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix E Requirement Analysis Appendix E Use Case Diagram VAT Part of the Database ma R A Add VAT Details p ame Member of Ae TS PY ON k S pu Edit Find VAT Details i Administrator Accountant 54 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix E Requirement Analysis Appendix E Use Case Diagram PAYE Part of the Database Add PAYE Details a Member of Staff 4 b CM Sam Administrator Accountant 55 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix E Requirement Analysis Appendix E Use Case Diagram Self Assessment Tax Return Part of the Database Add Tax Details mec du Member of a EL E 9 X E a Edit Find Tax Details x Administrator Accountant 56 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix E Requirement Analysis Appendix E Use Case Diagram Sales Record Part of the Database Sales Invoice ge Sales Details Member ctum Edit Find Sales Details c gt Print Sales Invoice
65. eference tables 2 To ensure both primary and foreign keys are properly defined there are two rules to be followed they are Entity Integrity and Referential Integrity 4 3 1 Entity Integrity This rule states that No part of a primary key can be null no part of cover cases where the primary key comprises more than one attribute 22 p6 2 It means that for every row in the relation a value must be given for the primary key field For example the primary key field for any record within the Client Business Bookkeeping VAT PAYE Tax Invoice Payment and Service table must consist of a value 4 3 2 Referential Integrity This rule states that If a base table R includes a foreign key Fi matching the primary key Px of some base table R then every value of Fi in R must either be a equal to the value of P in some row of R or b wholly null 22 p6 3 Referential integrity means that if a Client Id in the Client table has a value then it must match a Client Id of the Tax table Enforcing the referential integrity ensures that records in one table match the records in a related table which can protect the relationships between tables This is vital to avoid erroneous differences between records in different tables All the tables in the proposed database have enforced the referential integrity rules in this development 4 4 Normalisation
66. emented to the database and a overall conclusion of this project 7 2 Project Evaluation The implemented system successfully meets the initial overall project aim which is To implement a client and sales record management system for P amp Co Chartered Accountant The minimum requirements are also being fulfilled and the extent to which they were met are described in the following e To replace the paper based system in P amp Co Chartered Accountant to a computerised system This is achieved by building a new database to replace the current paper based system in P amp Co Chartered Accountant e To gather user requirements from the owner in P amp Co in short to obtain a clear view of the problem on the existing paper based system To obtain a clear view of the problem existed in P amp Co interviews with the P amp Co owner and research were conducted on the background and current paper based system procedures in P amp Co Chartered Accountant While problems were defined they are then being analysed using suitable analysis tool to aid in devising the user requirements e To meet the user requirements and to recommend a new system by introducing database to store client and sales records The user requirements were met by building a database since the development will involve a large amount of related data Some enhancements had also been met which is to produce the VAT report In order to meet the user require
67. ems in a branch e Difficulties in searching Searching for specific clients information is very difficult in all three branches as the staff will need to look in the filing cabinet to retrieve files For example when a letter is being sent to P amp Co office with only the client s name the staff will not know what Client and Sales Record Management System for P amp Co Chartered Accountant Introduction business it should belong to The problems get worse if clients files are missing e Time consumption Looking for a specific details in clients folder can be very time consuming e No standard accounting system across all branches The accounting system to record clients sales and payment are only available in the Manchester and London branches but not in the Leeds branch which is very inconvenient Also the present accounting system does not produce sales invoices and payment receipts Problems associated with shared information access across the branches e Time consuming and geographical problem As works are now transferred across branches the ease of information retrieval becomes a very important factor to completing a job efficiently The staff in one branch would often need to telephone other branches staff for clients information which is inconvenient inefficient cost ineffective and time consuming The above problems prompted P amp Co to consider buying an off the shelf database based package but there
68. ervice Charge Sheet No 110 00 Record wlll 1 rire of 1 am VAT 0 00 am Save Add Find Edit Record Record Record amp Ns Record 14 4 3 nire of 3 Figure 5 1 Sales Invoice Form 87 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix L User Manual 5 4 Print Sales Invoice Once the S button is clicked the sales invoice form can be printed which is the same as the sales interface form 5 5 Quit and Back to Main Menu hls The user can go back to the main menu by clicking this button 6 Payment When the Payment option is chosen from the Main Menu the user is directed to the screen shown in Figure 6 1 below CC iBixi P amp CO CHARTERED ACCOUNTANTS Payments Payment_Id T 284 005 Collected By Carmen C Invoice No Business Id Business Name Total 1 BCLOO1 Big Chef Record i 4 I 1 ou fr of 1 FTO Amount_Outstanding 0 00 Fro mon mee S Si x Record 14 1 gt onlrx of 2 Figure 6 1 Payment Receipt Form 6 1 Add Payment Record Add New Payment Record can be added by clicking this button sei in the Payment Details form a new screen will turn up The user can then input all the client payment details in the form When the user has filled in the top half of the form after the payment sub
69. for example importance attached to the role of users 3 p167 This methodology has primarily emphasis on documentation data modelling and the involvement of the user but not with later project stages including implementation and testing This breaks down into a hierarchy of stages and they are as follows Stage 0 Feasibility Stage 1 Investigation of Current Environment Stage 2 Business System Options Stage 3 Definition of Requirements Stage 4 Technical System Options Stage 5 Logical Design System Stage 6 Physical Design System 10 Client and Sales Record Management System for P amp Co Chartered Accountant Background Research Waterfall Model The Waterfall model is a very old methodology that originates from the manufacturing industry A project is split up into five stages as shown in the diagram below The waterfall model this is the classic model of system development 4 p36 Analysis amp Specification T Design i t Implementation AY Testing Maintenance amp Upgrades By comparing SSADM to Waterfall model the author decided to choose Waterfall model to guide me through the duration of this project in order to fulfil the minimum requirements Although SSADM has the feasibility stages and provides a more detailed analysis and design stages of systems development it does not allow iteration and does not cover issues of the implementation testing and evaluation of the software
70. form the user can just click on the field Amount Owed the total amount the client owed will be calculated automatically For the field 88 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix L User Manual Find Amount Outstanding the user will need to make use of the pa button to find the client Find outstanding balance This sues button will bring the user to the Client Sales Ledger form which is shown in Figure 3 4 the user will need to look up the field Balance and then enter that in the Amount_Outstanding field Also the field Amount_Paid will also need to be entered by the user since the client may not pay the entire bill at once However the field Balance will then be automatically calculated when the above fields are entered 6 2 Find Edit Payment Record Find Edit for Payment record of the client operates the same as the Client Form and VAT Form as Find Edit mentioned in Section 3 2 and Section 4 2 Payment details can be found and edited by the feel button 6 3 Print Payment Invoice Once the a button is clicked the payment invoice form can be printed which is the same as the payment interface form 6 4 Quit and Back to Main Menu The user can go back to the main menu by clicking this button 7 Business This form is operate very similar to the Client Form apart from the Find Balance do not appear in this form
71. forms Therefore the company logo P amp Co Chartered Accountant has been placed for every form The font used should be what the user uses this enables the text to be easily read 26 p23 The end user in P amp Co confirmed that the most commonly used font in the accountant firm is Arial in size ten therefore this font and its size has been implemented throughout the database Main Menu In order to assist the new users get to where they want quickly and easily a Main Menu form has been created The Main Menu form will automatically appear when the database is being opened This gives the new user a starting point which they can go back to if they get lost in the database The form contains nine different pages with obvious function buttons that bring the new users to each input form they want The sample of the form Main Menu is given in Chapter 5 Section 5 4 Page 26 27 Client and Sales Record Management System for P amp Co Chartered Accountant Implementation Chapter 5 IMPLEMENTATION 5 1 Introduction Implementation is the third stage according to the Waterfall life cycle methodology This chapter will first outline the features of Microsoft Access the description of the implementation of the database tables and relationships based on the design outlined in Chapter 4 followed The user data entry interface will then be possible to create once the tables and relationships have been c
72. have been added in the interface forms Bookkeeping VAT PAYE and Tax so that when the service is completed the staff can just click the Produce Invoice button to create sales invoice for the client 39 Client and Sales Record Management System for P amp Co Chartered Accountant Conclusion A function button Find Balance has been added in the interface forms Client so that when client request for the account balance the staff can just click on the button to retrieve information from the Client Sales Ledger form The print button on the Sales and Payment forms print all invoices not just the invoice for the selected record sample of the invoice and receipt can be found in Appendix G Page 59 60 which is the same as the interface forms Therefore this needs to be altered to ensure only the selected record is printed and there is a proper layout Again the print button on the Client Sales Ledger form and Client Sales Summary Report prints all the clients account details not just the account details for the selected client therefore these needs to be altered to ensure only the selected client is printed The design of the Client Sales Ledger form can be redesigned but since it is still acceptable by the client the author has not spent any more time on this 7 4 Project Conclusion To conclude the new system met all the minimum requirements identified in t
73. he project as well as the project objectives By following the Waterfall model methodology produced comprehensive and high quality reports throughout this project development Client involvements was also being ensured at all times which enabled any amendments to be made immediately to suit the client s performance The project was deemed to be a success for all parties concerned The client was very pleased with the database and gained a working system for his company that met his requirements while saving him the financial cost of contracting a software developer to produce the system The client also verified that he had gained an interesting insight into the skills requirements and technique to generate a Information System 40 Client and Sales Record Management System for P amp Co Chartered Accountant Reference Reference 1 Softwarecasa ScanSoft PaperPort Deluxe 8 0 URL http www softwarecasa com products p11260 htm source goo 279 January 2003 2 Best Sage Software URL http www sota com configurator index cfm action s1 27 January 2003 3 Avison D E and Fitzgerald G 1995 Information Systems Development Mehtodologies Techniques and Tools McGraw Hill 4 Cotterell M and Hughes B 1999 Software Project Management 2 Edition McGraw Hill 5 Martyn Clark 2000 Introduction to Information Systems Module Summary Notes University of Leeds School of Computing 6 Owen Jo
74. hnson 2001 Object Orientated Analysis and Design Module Notes University of Leeds School of Computing 7 Elmasri R and Navathe S 2000 Fundamentals of Database Systems 3 Edition Addison Wesley 8 A Comparison of SQL Server 2000 with DB2 v8 1 URL http www databasejournal com features mssql article php 10894 2177441 3 10 February 2003 9 The Comparison of SQL Server 2000 with Oracle9i URL http www databasejournal com features mssql article php 2170201 10 February 2003 10 Access97 2000 and SQL Server 7 2000 Compared URL http databasejournal com features mssql article php 144278 1 part_2 10 February 2003 11 Microsoft Access Database Design URL http www clearform com microsoft_access htm 10 February 2003 41 Client and Sales Record Management System for P amp Co Chartered Accountant Reference 12 Oracle licenses at discount prices URL http www pro dba com pricing html 12 February 2003 13 Neil Pike 2000 SQL Server Common Problems Tested Solutions Apress 14 Compare Prices for MICROSOFT SQL SERVER 7 in Programming Tools Productivity Applications or Internet and Communication URL http www dealtime com xPP Programming Tools KW MICROSOFT_SQL_SERVER_7 CR 1 125 February 2003 15 Microsoft Access 1997 Sanjaya Hettihewa URL http www exam ta ac uk access97199b htm 12 February 2003 16 Hawryszkiewycz LT 1994 Introduction to systems analysis
75. ible space and boundaries for data entry fields use underscores to indicate the number of characters in a field for example a small box with indicates the date format entry with the similar format 25 12 2002 is required 25 p494 While designing the user interface forms the above guidelines have been followed A sample of the form is given in Figure 4 4 Appendix G Page 59 60 All the labels have a meaningful title e g the field that needs to input the Surname has the title Surname In terms of logical grouping and sequencing of fields related fields are grouped together such as Home_Address Also buttons with similar functions are grouped together for example Add Record Find Edit Record and Delete Record belong to the editing function they are being put together on the middle bottom of the form 26 Client and Sales Record Management System for P amp Co Chartered Accountant Database Design for the searching function button Find Sales Ledger as it is a different function button it has been put on the left of the form Form operations button such as exit has been put on the right hand side of the form All text boxes and labels are aligned and spaced consistently as seen from the form text boxes that expect to have a longer input entry are relatively longer than those who have a shorter input entry i e Text box First Name is longer than Title because ther
76. icked the relevant form should open As Form form expected Close Main While the Quit button is clicked the main menu should close and As Menu bring the user back to the Window option expected View VAT Once the View VAT Report Menu button is pressed in Section As Report Sub VAT the relevant VAT sub menu should open expected Menu View Client Once the View Client Sales Summary Report Menu button is As Sales pressed in Section Sales the relevant Client Sales Summary sub expected Summary menu should open Report Sub Menu Comments e Jt is much quicker and easier to find a record using the database e We can now update the client record in real time whilst talking to the clients This avoid the lose of clients details being written on paper e There are some forms with subform where we need to scroll through to enter the details which is a bit inconvenient e The database provides a comprehensive functions that allow us to do the job quickly From Kitty Wan Member of P amp Co Staff 78 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix L User Manual D amp Co Chartered Accountant Company Database User Manual Written and Produced by Ka Man Chan Version 1 0 29th April 2003 79 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix L User Manual 1 INTRODUCTION This menu was designed and
77. ind Next button need to be clicked to operate the search function Once the client record is found the user can view client details and make necessary changes if necessary 3x Find Replace mma fol Cancel Look In cient z Client Id Match Client More gt gt Figure 3 3 Find Box 83 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix L User Manual 3 3 Delete Client Client record may only be deleted when there is no transactions records exist This means that the client has not required any service from P amp Co Chartered Account and thus the record do not need Find Edit to be kept When a member of staff wishes to delete the client record the EM will first need to be pressed to go to the corresponding client record Once the client record is found the Delete _Record button can be clicked to delete the client record 3 4 Find Sales Ledger Find Sales Client Sales Ledger record can be found by clicking this Ledger __ button to bring the user to the Client Sales Ledger form see Figure 3 4 The user can then find the corresponding client sales ledger record to get the account details This button is implemented as an enhancement to convenient the user E3 Client Sales Ledger ini xi P amp CO Client Sales Ledger CHARTERED ACCOUNTANTS CUN ED Sales Invoice No Subtotal 1 15 06 2003 120
78. into the unique Invoice No field Find Edit Find a Sales record correctly and permits amendments on that As Sales record expected Save Sales Once Save Record button is pressed the figures for the fields As Record Subtotal VAT and Total are automatically calculated expected correctly Preview and The user interface of the form Sales is also the standard sales AS Print a Sales invoice that can be printed out to clients Preview of the sales expected Invoice invoice in print layout is possible and allow the invoice to be printed Quit Once the quit button is pressed it should bring the staff back to the As main menu expected Payment Add Payment Form permits a Payment Details to be added to the Payment and As Form Sales Payment table Input into every field in the form is bound expected properly to the table Ensures that a number is entered into the unique Payment Id field Find Edit Find a Payment record correctly and permits amendments on that As Payment record expected Automatic Once the fields Amount Owed and Balance are clicked the As calculation VBA calculation functions are performed automatically expected for fields Amount Owed and Balance Preview and The user interface of the form Payment is also the standard AS Print a payment invoice that can be printed out to clients Preview of the expected Payment payment invoice in print layout is possible and allow the
79. irst introduced by Chen in 1976 and is now widely used It is an important method for modelling all the information needed in the database ready for database implementation This model can be used to assist in the communication between the programmer and the user The structure of the information that is to be represented in a database is thought of in three types of component e Entities which are distinct things in the enterprise e Relationships which are meaningful interactions between the objects e Attributes which are the properties of the entities and relationships 16 p177 Entities are represented as rectangular boxes in E R modelling Nine entities are defined in this database application according to the user requirements They are Client Business Bookkeeping VAT PAYE Tax Sales Payment and Service Note that Bookkeeping VAT PAYE Tax are the four types of services provided by P amp Co but they are named as the different entities Entity Service is identified to assist in recording sales and producing invoices As the nine entities are identified the next step is to identify the relationship between entities A relationship can be classified by differing degrees They can be one to one 1 1 one to many 1 M or many to many M N relationships 17 p169 For example there is a relationship between entities 19 Client and Sales Record Management System for P amp Co Chartered Accountant Database Design
80. is only estimated to be 0 1 GB This is still far less than the 1GB that an Access database application can be Therefore Microsoft Access 2000 is the most suitable DBMS to use in this database development 18 Client and Sales Record Management System for P amp Co Chartered Accountant Database Design Chapter 4 DATABASE DESIGN 4 1 Introduction Design is the second stage in the Waterfall model The design of the database is very important and cannot be overlooked The P amp Co database was first designed incorrectly which cost an amount of time to correct the design and fix the problems in the next stage the implementation phase Therefore it is vital to ensure the design is accurate and complete all the requirements in the analysis phase are captured and that redundancy and inconsistencies in the data are kept to a minimum This chapter is intended to identify the entities and attributes required in the P amp Co Chartered Accountant database It will also show the relationships between the tables by means of an entity relationship modelling diagram and to look at the functional dependencies of each table The design of the database is based on the requirements specified by the users the flow diagram in Section 3 3 and the use cases diagrams drawn in Appendix E Page 46 57 The user data entry interface design will also be examined after the database design 4 2 Entity Relationship Modelling E R modelling was f
81. lds comprising the attributes of each entity type 9 p48 This rule implies that a primary key must be chosen to uniquely identify each record in the table in order to ensure that data redundancy and record duplication do not occur which would result in errors and space wastage The nine entities in the tables have appointed a primary key to uniquely identify each record Therefore it would prevent data redundancy and record duplication occurring in the database 2 Mapping 1 M Relationship types Relationships are classified into relationship types 75 1 M relationship in the E R Model is represented in the database by posting the primary key of the master entity type into the scheme of the detail entity type For example scheme Business is the master entity and schemes Bookkeeping VAT PAYE and Sales are the detail entities The relationships between them are one to many the primary key of Business is therefore posted into the schemes of Bookkeeping VAT PAYE and Sales 3 Mapping M N Relationship types M N relationship types are mapped onto new relationship schemes In other words a new table must be created to represent the relationship type which will contain the two primary keys of the entity types participating in the relationship 9 p50 There are three M N relationships in this E R Model and they are shown in Figure 4 1 and 4 2 Three new tables containing the two primary keys of the entity types participating in th
82. leased with the database overall some changes and enhancements could also be made to the database to improve its functionality and usability Some of the enhancements have already been implemented while the rest will need to be made in the future due to the time limitations The enhancements are outlined below e An up to date printout of monthly VAT report was implemented so that the staff will no longer need to prepare the VAT taking sheet every month e The database can be shared across three branches by installing a database server in two of the branches Then the database should be installed in the database server to allow sharing inside the branch Active Server Page codes can then be written to post the database online An Active Server Page ASP is an HTML page that include small embedded programs that are processed on a Microsoft Web server before the page is sent to the user 30 It allows database to be retrieved online with login password to restrict public access Backup of the database will have to be made every night in two of the branches and the backups should be kept privately by the seniors This is done in order to avoid sudden accidents such as fire when one of the backup is burnt there is still another that can be reinstalled and used The follow up of the networking of the database will be carried out by the P amp Co technicians since he has strong knowledge in networking e A function button Produce Invoice
83. login password Concurrent access and transactions entry per day are not so vital as the maximum concurrent users and the average transactions data entry per day are low as shown in Appendix D Page 42 45 17 Client and Sales Record Management System for P amp Co Chartered Accountant Requirements and Analysis From figure 3 1 it can be seen that Oracle and DB2 support all known platforms provide concurrent access good functionality good security and are suitable for high transactions data entry per day However they are difficult to use and very expensive which disregard the two most important factors that needs to be considered Oracle costs 7616 with licenses 12 which has far exceeded P amp Co s budget By comparing Microsoft SQL Server 7 to Microsoft Access 2000 they both support a window based platform Microsoft SQL Server is cheaper to buy than Oracle and DB2 for most configurations 73 p354 However it costs approximately 910 14 which exceeds the budget set Microsoft Access is the cheapest amongst the four DBMS compared and the easiest to use although Microsoft SQL scores better in terms of security and functionality As a result the selected database software tool is Microsoft Access 2000 because it scores better in the two most important factors ease of use and cost This is essential as P amp Co only has Access 2000 available on its PC and therefore does not need to purchase another piece of software
84. mation 27 p2 Macros Using macros allows the developer to transform the interactive database into an automated database application 66 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix I Design View Appendix I Design View of the Database System iix Field Name DataTyp 11 71 Desripin Client Id Text The unique indentifier of client ritle Text The title of the client e g Miss Mrs Ms Mr First Name Text Clients First name Surname Text Clients surname Home Tel Text Clients home telephone number Mobile Text Clients mobile number Home Add Linei Text The first line of the clients home address Home Add Line2 Text The second line of the clients home address Postcode Text The postcode of the clients home address Email Text Email of the client poB Date Time Clients date of birth National Ins No Text Clients national insurance number m p i 5 zi Field Properties General Lookup Field Size 50 Format Input Mask 99999 009009 Caption The field description is Default Value optional It helps you describe the Field and is also displayed in the status bar when you select this field on a Form Press F1 Validation Rule Validation Text Required No for help on descriptions Allow Zero Length No Indexed No Unicode Compression No Client Table Design oxi E T T E T T TOR
85. ments efficiently and properly research was conducted into various software development methodologies to guide me through this development Research on the selection of different database software tools was also being carried out to choose the most appropriate tool to use for this development The choice was influenced by the software that P amp Co had available to them e To design and implement a database holding information about P amp Co customers and their accounts details To design the database correctly involved investigating and applying database 38 Client and Sales Record Management System for P amp Co Chartered Accountant Conclusion design theory the resources was found either from previous database modules notes textbooks or online HCI issues were also investigated and applied in order to design an easy to use interface Implementation was aided by reading Access textbooks such as Access 2002 VBA Handbook 27 and asking friends who have knowledge in building a database in Access e To evaluate the system To ensure the database is usable functional and of good quality evaluation criteria were examined to decide on the most suitable criteria to employ in this development The evaluation criteria were successfully applied to evaluate the database quality and so this requirement was achieved 7 3 Further Enhancements Although the system produced achieved the users requirements and the users were very p
86. ndix G Page for the fields Amount_Owed as shown in Figure 5 8 and 5 9 below and Balance the VBA code is given in Figure 5 10 and 5 11 in Appendix G Page 59 60 The VBA codes were implemented in the same way as the form Sales Once the user clicks the field Amount Owed the total amount that the client owed us from the invoices will be summed up While the user clicks the field Balance it will automatically calculate the Balance by summing up the fields Amount_Owed and Amount_Outstanding minus Amount_Paid is Text Box Amount_Owed x toeledototetetototetototetetototetotoetetotoletetoleletotoeleteteleletoteletetetelet Format Data Event othe at Function Amount_Owed Before Update iss 7 ABS OS IRAN S d Once the user clicks the field Amount_Owed the On Change ev ana total amount from the invoices will be summed up Ot EEE ict vein wpe wire ane plz EPA HN A e OEE TO ONE RAI Private Sub Amount Owed Click On Lost Focus isses BEN on EAS HERNAN Event Procedure zj Amount_Owed DSum Total Query2 Payment Id forms Payment Payment_Id End Sub FEK K K K K K K K K K K K K K K K K K K K K K K K K K K K K K K K K K K K K K K K K Figure 5 8 Access Icon Properties option Figure 5 9 VBA function Amount_Owed Security As mentioned in Section 3 3 2 one of the non functional requirements stated by the client was that the access of th
87. nt i e clients might not have paid the full amount and a payment made has one or more sales invoice Involves Service A sales from P amp Co to client involves one or more service and a service involves in one or more sales invoice Figure 4 2 The explanations of the relationships between the entities 20 Client and Sales Record Management System for P amp Co Chartered Accountant Database Design 4 2 1 Mapping the E R Model into Relational Tables The E R diagram created in Figure 4 1 aids the design of the tables and implementation in Microsoft Access The main aim of tables in a database is for the storage and retrieval of data required to generate the information necessary for the user The tables were designed according to the user requirements identified in Section 3 3 1 and the E R diagram shown in Figure 4 1 The relationships between the entities will then be mapped onto the tables Data related to each entity are stored as attributes in the tables The nine entity names will be kept the same as the table names so as to eliminate confusion Six rules should be applied when undertaking the mapping of the E R Model into the actual tables schema within the database but due to the fact that only three rules apply to my database application I will only outline those three rules 1 Mapping Entity types Entities are classified into entity types 8 Each entity type maps onto a table scheme with associated fie
88. on Systems 2 Edition Springer 29 McCall J Richards P and Walters G Nov 1997 Factors in Software Quality Vol I AD A049014 015 055 30 Active Server Page a searchWin2000 definition URL http searchwin2000 techtarget com sDefinition 0 sid1_gci213787 00 html 20 April 2003 43 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix A Personal Reflection Appendix A Personal Reflection By reviewing the duration of this project this has proved to be a challenging but rewarding experience The personal objectives identified for this project as outlined in Summary have been fulfilled much of the work completed during my studies at University were reviewed skills were gained in project development and in database design and implementation resulting in a product that the P amp Co staff were pleased with and useful to the organisation The two greatest challenges I found in this project were the database design and implementation due to the lack of experience As a result more time had been spent in these two phases However more than the minimum functions were implemented at the end and this has been the part of the project I have found most valuable due to the practical skills gained Also I found it very effective and beneficial by following the waterfall model since it provides a framework for developing the project Given the many other coursework commitments i
89. on every year London office is now responsible in completing self assessment tax return as the specialist accountant is situated in London Thus Manchester and Leeds offices staff always need to find the required information for the London accountant to complete the work Client Sales Ledger Details same as Clients Sales and Payment Record Details Only London and Manchester offices have the system to record clients sales and payments while Leeds clients accounting system is being kept in Manchester office Therefore once a service is provided to the client or the client has made a payment the staff in Leeds office will need to inform Manchester either by phone or by fax for updating the clients sales and payment records If a client rings up to Leeds office checking how much he she has owed P amp Co the Leeds branch staff will then ring up the Manchester branch to get the required information which may cause low workload while there are many requests from clients 48 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix D Minutes of Meeting e There are no standard sales invoices and payment invoices in the current system means that the staff will need to produce invoices every time they charge client or receive payment from client JP stated that a summary report of the clients sales record would be wanted in order to calculate the average amount P amp Co earns from each client
90. on the form that could be clicked to go to other screens were operating the data that was input or requested was stored in the correct table or retrieved from it 6 2 2 Validation Testing Validation Testing is carried out to ensure that data input is of the correct format and that the data integrity rules as mentioned in Section 4 3 are not violated The fields on the form with field properties specified were tested to ensure that only valid data is input into the database For instance the field Home Tel should only accept a number field type of format The results of the tests show that an error message would appear if other format of the data type were input into the field To ensure the integrity rules are not violated it is necessary to make sure the records are not duplicated For example the Client Details form was tested to ensure that if the Client_Id field is not 34 Client and Sales Record Management System for P amp Co Chartered Accountant Testing installation and evaluation being filled the corresponding record could not be saved The outcome of the test demonstrates that the database would not permit records to be saved if Client_Id has not been entered This could avoid data duplication Each form was being tested by not entering the primary key field and the result was that the corresponding record could not be saved and thus ensuring the integrity rules is being met 6 3
91. or accountant to enter details whilst on the phone KMC asked JP how much he would be willing to spend in this project how many people in P amp Co would be using the database at one time and is there a lot of data entry in one day JP replied since he would be investing money in updating the companies PCs monitors and purchasing the ScanSoft PaperPort his budget for this development would be 200 He also replied that there is not a lot data entry in one day the maximum is twenty transactions per day and the maximum concurrent access is eight people as some employees are part time KMC thanked JP for the time for the interviews and has agreed by JP that he would test the system once the system is completed 50 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix E Requirement Analysis Appendix E Use Case Diagram Client Part of the Database ee Client Details Member te Edit Find Client Details A lt lt extends gt gt Administrator Accountant Delete Client Details 51 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix E Requirement Analysis Appendix E Use Case Diagram Business Part of the Database T tlli Details Member te Edit Find Business Details A lt lt extends gt gt C Delete Business Details Administrator Accountant 32 Client and Sales Record Management System for P amp Co Charter
92. orrectly implemented 5 2 Microsoft Access As shown in Section 3 4 1 selection of software tools Microsoft Access 2000 was chosen as the software tool used in this database development Access has a useful set of tools and wizards to help create a database including Tables Queries Forms Reports Macros and Modules 27 p2 Modules were not used in this database and therefore will not be discussed The function of each tool is briefly outlined in Appendix H Page 61 10 3 Table Implementation Tables All the tables listed in Section 4 2 1 in Chapter 4 Design are implemented in Microsoft Access The tables were inserted into Microsoft Access in the Design View as opposed to Table Wizard This allows the attributes to be typed into the table directly the data types to be selected using drop down menus and the field properties to be defined For example when the attribute DOB Date Of Birth was put in the table Client via Design View data type Date was selected using drop down menus and the field properties were then specified such as defining an input musk for the date field e g 00 00 0000 0 Defining input mask permits users to see the data format once they input a record When empty tables have been created field names are added to every table to state their attributes The list of attributes can be found in Section 4 2 1 The processes of creating a table are described
93. plete each task in the paper based system first and then using the new database The results are indicated in Figure 6 1 Owner in P amp Co Administrator Take taken using Task 1 Paper based system 6 mins 5 mins 4 mins 4 mins New Database 4 mins 2 5 mins 2 mins 2 mins The owner in P amp Co spent most of the time meeting clients and dealing with accountancy matters rather than doing the administration work therefore he took longer to finish these two tasks then the administrator However the time taken to complete both tasks using the new database is shorter which shows an improvement on the paper based system The administrator who has a basic knowledge in using database and is familiar with the paper based system completed the two tasks by halving the time The results show that overall the users perform quicker to input and find records with the new database 36 Client and Sales Record Management System for P amp Co Chartered Accountant Testing installation and evaluation 4 Integrity Extent to which only authorised access to the database is allowed A login password is required every time the users use the database to avoid unauthorised access This was tested by the author and the users the results show that the database will disallow invalid passwords 5 Usability Extent to which the database is functional and usable The usability of the database is determined by three measurements num
94. que Payment_Id field Find Edit Find a Payment record correctly and permits amendments on that AS Payment record expected Automatic Once the fields Amount_Owed and Balance are clicked the AS calculation VBA calculation functions are performed automatically expected for fields Amount_ Owed and Balance Preview and The user interface of the form Payment is also the standard AS Print a payment invoice that can be printed out to clients Preview of the expected Payment payment invoice in print layout is possible and allow the invoice to Invoice be printed Find Balance Once the Find Balance button is pressed it is linked to the form AS Client Sales Ledger to allow the user to look for the clients expected outstanding balance Quit Once the quit button is pressed it should bring the staff back to the AS main menu expected Client Sales Find Record This form is made up by the combination of Sales and Payment AS Ledger tables to show the P amp Co clients account details so update expected Form edit and delete of the clients record should be either done in forms Sales or Payment Once the Find Record is pressed a Client accounts record is find correctly Client Sales Preview and The user interface of the form Client Sales Ledger can be printed AS Ledger Print a out to clients Preview of the client sales ledger in print layout is
95. r the VAT report was also achieved The reports were implemented in the Report Design View option and the relevant attributes were selected from the correct database tables There were three report options involved in the VAT Reports form since P amp Co clients varied in the months they needed V AT services Examples of all the reports were given in Appendix J Page 66 67 The reports have been approved by the P amp Co client Macros In order to provide navigation to the user around the system a tab style Main Menu bar was created All the forms were linked to the Main Menu using the navigation button such as this L3 and the Main Menu form was also linked to all input forms using the navigation buttons details of the design is given in Section 4 5 2 The Menu screen was loaded automatically once the user opened the database the user could then access any section of the system by clicking on the tab from the main menu Figure 5 3 below shows the Client option by clicking on the Find Client Details button the user is taken to the client details form that can find a specific record The navigation buttons and tabs are all standard macro functions built in Access and the wizard was used to create them E Manmen ini x i Business Bookkeeping VAT PAYE Tax Sales Payment Clie 4i Client and Sales Record Management View Client Details Quit Record wa 1 p fouls of 1 Figure 5 3 Main Menu 31 Clien
96. record expected Delete Client Warning message turns up to avoid unintended deletion of records As and the record should be deleted from the related table s while the expected client has not required any P amp Co service Find Sales This button is implemented in order to convenience the staff in case As Ledger the client wants to check their account details Once the button expected Find Sales Ledger is pressed it will bring the staff to the Form Client Sales Ledger and the staff will be able to look for the client accounts details Quit Once the quit button is pressed it should bring the staff back to the As main menu expected Business Add Business Form permits a Business Details to be added to the Business and As Form Client Business table Input into every field in the form is bound expected properly to the table Ensures that a number is entered into the unique Business Id field Find Edit Find a Business record correctly and permits amendments on that As Business record expected Delete Warning message turns up to avoid unintended deletion of records As Business and the record should be deleted from the related table s while the expected client has not required any P amp Co service Quit Once the quit button is pressed it should bring the staff back to the As main menu expected Bookkeeping Add Form permits a Bookkeeping Details to be added to the As Form Bookkeeping Bookkeeping table Input into every
97. red every month by the staff to record which client needs to complete the VAT and when the client has brought the VAT taking sheets but sometimes the paper prepared may be out of date in The staff must make sure all the clients have brought the VAT in by fifteenth of every month for the accountant to work on since there will be a penalty from HMCE if the payment is not posted in by the end of the month Again the staff may sometimes forget to or lose the paper that cause problems e g penalty As mention in Section 1 4 and 1 5 works are now transferred across three branches while client rings up to check the process of the VAT workings the staff will have to ring the responsible branch for the process and then reply back to the client This process is very time consuming which will delay the job that the staff is working on PAYE Details PAYE means pay as you earn the employees and employers have to pay national insurance contributions NIC and tax on what they earn Manchester office is now responsible for all three branches PAYE services the accountant who deals with it always need to request other branches staff to look for information in order to complete the work Details of PAYE information is placed in another block of filing cabinet which the staff will need to search through the whole filing cabinet This is again very time consuming Tax Details Each person who is self employed needs to review his her tax situati
98. rimary key of the source table to the foreign key of the relational table created the relationships 29 Client and Sales Record Management System for P amp Co Chartered Accountant Implementation zii PAYE Sheet Id Business Id PAWE Service Charge Business Id Business Mame Invoice No Service Id Sheet No Service Id Description Service Charge Business Id Date Received Date Completed Record By Service Charge Tax Office Date Finished Tax Service Charge Figure 5 2 Microsoft Access Relationships To guarantee that the integrity of the database is held the integrity rules discussed in Section 4 3 must be adhered to in the implementation of the database Entity integrity is achieved by setting the primary key field property Required to Yes Selecting the Enforce Referential Integrity option in the Edit Relationship dialogue box enforces Referential Integrity 5 4 User Interface Implementation Creating the user interface as described in Section 4 5 2 forms design allows the users to add edit delete and find records in a user friendly manner Forms Forms are created by using Table Wizard mode provided by Microsoft Access Twelve forms were created as shown in Section 4 5 1 user interface architecture There is a Main Menu form two report forms and nine main forms for data inputs Implementing user interface using Ta
99. s Quit Once the quit button is pressed it should bring the staff back to the As main menu expected Business Add Business Form permits a Business Details to be added to the Business and As Form Client Business table Input into every field in the form is bound expected properly to the table Ensures that a number is entered into the unique Business Id field Find Edit Find a Business record correctly and permits amendments on that As Business record expected Delete Warning message turns up to avoid unintended deletion of records As Business and the record should be deleted from the related table s while the expected client has not required any P amp Co service Quit Once the quit button is pressed it should bring the staff back to the As main menu expected Bookkeeping Add Form permits a Bookkeeping Details to be added to the As Form Bookkeeping Bookkeeping table Input into every field in the form is bound expected properly to the table Ensures that a number is entered into the unique Bkkeeping Ledger Id field Find Edit Find a Bookkeeping record correctly and permits amendments on As Bookkeeping that record expected Quit Once the quit button is pressed it should bring the staff back to the As main menu expected VAT Form Add VAT Form permits a VAT Details to be added to the VAT table Input As into every field in the form is bound properly to the table Ensures expected that a number is entered in
100. s for human computer interaction it is the study of the way in which humans operate computers 24 p5 In this project studies into HCI ensures the database interface looks professional and to be as user friendly as possible This is particularly important because a system will be useless if people cannot understand how to use it even if the system does exactly what it is meant to do and is technically brilliant The goals of HCI can be summarised as to develop or improve the safety utility effectiveness efficiency and usability of systems that are interacting with computers 25 p14 Amongst those goals usability is the key concept which is concerned with making systems easy to learn as well as easy to use When using Microsoft Access 2000 to build the database Forms are one of the most efficient ways of providing the user with an interface Therefore forms have been created to permit users to input the data into the database quickly effectively and easily 4 5 1 User Interface Architecture Before considering the user data entry interface design it 1s vital to first build the user interface hierarchy which illustrates the different data entry screens and how they would connect together The diagram below Figure 4 3 is devised from user requirements and Section 4 2 E R Model It shows how navigation through the forms is structured For entity Service it has been integrated with the FormInvoice The Client S
101. scription Service Charge Sheet No 3 PAYE 110 00 VAT 19 25 s Add Fi xd Record Record Roed ad M Record 1 2 x njrx of 2 Figure 4 3 Sales Invoice Form 4 4 Quit and Back to Main Menu hls The user can go back to the main menu by clicking this button 86 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix L User Manual 5 Sales When the Sales option is chosen from the Main Menu the user is directed to the screen shown in Figure 4 3 above 5 1 Add Sales Record Add New Sales Record can be added by clicking this button ei in the Sales Details form a new screen will turn up The user can then input all the client sales details in the form 5 2 Find Edit Sales Record Find Edit for Sales record of the client business operates the same as the Client Form and VAT Form Find Edit as mentioned in Section 3 2 and Section 4 2 Sales details can be found and edited by the el button 5 3 Save Record Save Once this button re is clicked the fields Subtotal VAT and Total will be calculated automatically provided that the user has entered the top half of the form the sample can be found in Figure 5 1 amp Sales E nml x o x P amp CO CHARTERED ACCOUNTANTS Sales Business _Id RENS Business Name Dragon Rose 3 NEN 3s Service Id i S
102. so it is very time consuming to retrieve the files when clients and other branches staff request information There were occasions when client files went missing and delayed the time taken for information retrieval Client and Sales Record Management System for P amp Co Chartered Accountant Introduction There is a computerised accounting system in Manchester and London branches to record clients sales and payment details However the Leeds clients accounting system is being recorded in Manchester which caused a lot of problems when Leeds clients want to view their account details The staff in the Leeds branch would have to telephone the Manchester branch to get the required information As a result of this the clients had to wait in the Leeds office for a reply which is not very efficient in terms of customer service Also the accounting software only record the clients sales and payments details but does not produce sales invoice and payments receipt where the accountant need to produce them separately in Microsoft Word Again this is very time consuming However since the original owner of P amp Co was used to the way the company operated and did not want to invest money to change to a computerised system the owner and the staff just bear with the problems caused by the paper based system Six months ago a new owner took over the business The business was run as three autonomous branches but is now being reorganised so th
103. stalled in PCs already e Social Feasibility By ensuring user involvement in the development of the project the social aspect is feasible to achieve Users will be involved in setting the minimum requirements of the new system and the evaluation stages allowing the system to be focused closely to the user needs Client and Sales Record Management System for P amp Co Chartered Accountant Introduction the system created has been designed to be as straightforward as possible since most staff lack strong computer skills The project was considered feasible by the client sponsored 1 6 Scope of the Project The aim of this project is to implement a client and sales record management system for P amp Co Chartered Accountant In order to meet the project aim the solution will be to design and implement a database that can add update delete and find clients records as well as to record clients sales and payments for the company Each member of the branch staff can therefore use the database to keep a client s information and to retrieve the information they require The architecture of the database to be networked will also be introduced provided there is sufficient time available so that other branch staff can also obtain access to the updated database e g Manchester and London branch staff can also get access to Leeds client and sales record database using Active Server Page Client and Sales Record Management System for P
104. t and Sales Record Management System for P amp Co Chartered Accountant Implementation In terms of adding finding editing and deleting a record buttons were also created to all input forms using the standard macro functions built in Access To ensure there is a consistent terminology to assist the users to use the database easily buttons with a common functionality have been labelled with either the same text or icon on every form The button Find Edit Record allows the users to find a specific clients record For example when the users click on the Find Edit Record button in the form Client shown in Appendix G Page 59 60 the box below in Figure 5 4 will come up allowing users to type in the details they want to find which matches the whole field in the form The result of the search will appear once the Find What data is entered and Find Next button is pressed Find and Replace 1 21x Find Replace Find What Little Dragon Find Next Cancel Look In Match Whole Field More gt gt Figure 5 4 Find Box Visual Basic Access VBA code In the Form Sales as shown in Figure 5 5 Appendix G Page 59 60 there is a macro button implemented named Save Record in the bottom left corner which will automatically calculate the Subtotal VAT and Total of the client sales record when the button is clicked This is achieved by writing a piece of VBA code The code is then s
105. t because of the practical skills I gained in database design implementation and project management that I previously did not have 44 Appendix B Project Schedule Client and Sales Record Management System for P amp Co Chartered Accountant Appendix B Project Schedule Outdated 45 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix C Project Schedule Appendix C Project Schedule Revised 46 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix D Minutes of Meeting Appendix D Correspondence with the User Minutes of Meeting with Jonathan Pitayanukul Date 4 November 2002 Present Ka Man Chan KMC Jonathan Pitayanukul JP P amp Co Chartered Accountant KMC asked JP if he could describe the current paper based procedures and problems in P amp Co Chartered Accountant JP said that there is seven different client details need to be stored in the client folders and the details of the procedures and problems are summarised as below Client and Business Details A new paper file is created to record the new client and business details Additional correspondence is also filed into it for instance their bookkeeping details and tax details etc The file is then place in one of the filing cabinets according to the business identification number If the staffs wish to find specific client or business details he she
106. t is very difficult to set specific targets to keep the project progress on course However through planning a realistic project schedule was of great help ensuring the tasks were completed on time to prevent a build up of work This has assisted me to realize the significance of the project management Contacting the owner in P amp Co was another problem encountered during the project since he was always busy at work which led to the delay of confirmation of some requirements Arranging an earlier appointment with him would have been helpful to solve this If I have another opportunity to complete this project again I would ensure the database is designed correctly before the implementation since much time was wasted on the correction of the database design and implementation I would also start this project earlier than I did The following is the advice I would give to students embarking on a similar project 1 Start early and do not underestimate the time it takes to design and implement the database as well as its interface especially for those who need to learn the database software tool as I did 2 Devising a realistic project schedule is vital to guide you through the project taking into consideration that things will most likely need more time than you expect 3 Avoid getting frustrated with the amount of work to be completed keep on working and maintain the productivity In general I am very pleased that I did this projec
107. the same way A sample of the other tables can be seen in Appendix I Page 62 65 The table in Figure 5 1 has Invoice No as the primary key and Business Id as the foreign key linking the Business table amp Sales Table loj x E Field Name DaaType Description 8 Invoice No Number The unique number used to identify the clients invoice Business Id Text The unique identifier of the business Date Date Time The date that the invoice is produced __ Subtotal Currency The total amount of the invoice vAT Currency The VAT 17 5 added to the subtotal Total Currency Total is the sum of Subtotal and VAT Field Properties General Lookup Format Input Mask 00 00 0000 0 Caption The Field description is Default value optional It helps you Validation Rule describe the Field and is Validation Text also displayed in the status Required No bar when you select this A No field on a form Press F1 For help on descriptions Figure 5 1 Sales Table Design With the correct implementation of the tables the next step is to define the relationships and insert the integrity constraints The tool to link the relationships between tables is already built inside Microsoft Access All the tables and relationships can be viewed in a simple graphical representation as shown in Figure 5 2 Through selecting the view relationship option in Microsoft Access all the tables were added and by dragging the p
108. ther Actor None Goal To add the client details into the database Basic Course of Use Case begins when the Member of Staff receive a client information sheet Events The staff then checks if all the details are being filled in and then inputs into the database Alternative 1 Some of the details are being missed out that the client will have to bring it Paths back and fill it in again 2 Client details are already in the database so checks are made to determine if the client has been already P amp Cos client None An information sheet is received from the Member of Staff An information sheet is completed Pre Conditions The client had identified the service s they require from P amp Co Project Author Date Post Conditions The client details are input into the database as a new client record Client and Sales Management System for P amp Co Chartered Accountant Ka Man Chan 03 Dec 02 60 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix E Requirement Analysis Appendix E Use Case Description Use Case Specification Find Edit Client Details Revision History 03 Dec 02 Use Case Description was completed after the Use Case Diagram Ka Man Chan were drawn and agreed by the owner in P amp Co Use Case Name Find Edit Client Details Summary The Member of Staff receives additional different information about a client the Member of
109. to the unique VAT Ledger Id field Find Edit Find a VAT record correctly and permits amendments on that As VAT record expected Quit Once the quit button is pressed it should bring the staff back to the As main menu expected PAYE Form AddPAYE Form permits a PAYE Details to be added to the PAYE table As Input into every field in the form is bound properly to the table expected Ensures that a number is entered into the unique PAYE Sheet Id field Find Edit Find a PAYE record correctly and permits amendments on that As PAYE record expected Quit Once the quit button is pressed it should bring the staff back to the As main menu expected 76 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix K Testing Tax Form Add Tax Form permits a Tax Details to be added to the Tax table Input As into every field in the form is bound properly to the table Ensures expected that a number is entered into the unique Tax Sheet Id field Find Edit Tax Find a Tax record correctly and permits amendments on that record As expected Quit Once the quit button is pressed it should bring the staff back to the As main menu expected Sales Form Add Sales Form permits a Sales Details to be added to the Sales and As Sales_Service table Input into every field in the form is bound expected properly to the table Ensures that a number is entered
110. usiness Id First Name Date 12 04 2003 Surname Invoice No Business Id Subtotal VAT Total 71 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix J Database Report VAT Report Jan Apr Jul Oct P amp CO CHARTERED ACCOUNTANTS Business kl Business Name Business Tel VAT Reg No VAT Period DRL002 Dragon Rose 01132 591123 7822652218 Jan Apr Jul Oct VAT Report Feb May Aug Nov P amp CO CHARTERED ACCOUNTANTS Business Id Business Name Business Tel VAT Reg No VAT Period BCLOO1 Big Chef 01132 589665 7850798522 Feb May Aug Nov Sole Trader DBL003 Dinner Box 01132 556846 1584961888 Feb May Aug Nov Sole Trader VAT Report Mar Jun Sep Dec P amp CO CHARTERED ACCOUNTANTS Business Id Business Name Business Tel VAT Reg No VAT Period GCL004 Golden City 01274 655822 7872926787 Mar Jun Sep Dec Status 12 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix K Testing Appendix K Testing of the Database System Filled in by users Client Form Add Client Form permits a Client Details to be added to the Client and As Client Business table Input into every field in the form is bound expected properly to the table Ensures that a number is entered into the unique Client Id field Find Edit Find a Client record correctly and permits amendments on that As Client
111. viera Gold Casino E Winzip j Programs vvv v vvv wi Documents Microsoft Access al Microsoft Excel Search y e Microsoft Outlook Settings gt Microsoft PowerPoint Microsoft Publisher Run Microsoft Word Ss Ma Outlook Express ap eo fa Hewlett Packard t amp s A E3 MSN Messenger 5 0 Help Figure 2 1 Starting Microsoft Access 80 Client and Sales Record Management System for P amp Co Chartered Accountant Appendix L User Manual Microsoft Access is then loaded and the file P amp Co Database mdb needs to be opened The display screen will turn up when Access commences and the database can then be chosen See Figure 2 2 below Create a new database using C Blank Access database BN Access database wizards pages and projects s Open an existing File More Files L C Documents and Settings P amp Co Database C1 kaman project Copy of P amp Co Database Final year project Project Database C Copy of Copy of most update xl Cancel Figure 2 2 Selecting the Database By clicking on the name P amp Co Database mdb and select OK the database will automatically load up and the user will be prompted to enter a login password as shown in Figure 2 3 This is implemented in order to restrict the database access to authorised users only The password will be supplied at the appropriate time Password Required 2 x
112. written by the system developer Ka Man Chan It is written and presented in an easy to understand and brief style as demand by the client The menu will provide a brief overview of the Database for P amp Co Chartered Accountant and should be used in combination with the user training and thereafter as a reference manual The Database covers nine main sections Client Business Bookkeeping VAT PAYE Tax Sales Payment and Client Sales Ledger A brief explanation of the processes involved in each section will be included in this section using screen shot of the system to assist in understanding Due to the time constraints and similarity of the form such as Client and Business Bookkeeping VAT PAYE and Tax this version will only contain detailed information on the Client VAT Sales and Payment processes A more detailed version will be completed and given to the user 2 OPENING THE DATABASE This system is a Microsoft Access database package To open the database e Click on the Start Menu e Choose Programs then Choose Microsoft Access E New Office Document Ta E Open Office Document Windows Update E Accessories Ej Adobe Acrobat 4 0 Fe Ahead Nero J Launch RealOne Player BOE MOLES Sizzle amp amp CyberLink PowerDVD Microsoft Office Small Business Tools Ej Microsoft Office Tools A Sis630 730 v2 08 amp Startup e Internet Explorer 7 Ri

Summary - VLE - University of Leeds

Contents

Download Pdf Manuals

Related Search

Related Contents