Creating a database Essay Example for Free

Creating a database Essay Introduction RS Supplies in London is a small business which specialises in the import and export of specialist medical equipment for hospitals as well as for other independent businesses. At the moment all orders have to be processed manually including invoices as there is no order processing system. However, the clientele is growing and Mr Parkes wishes to have a computerised order processing system. He has a computer which is used for typing up and storing invoices, but feels that a computerised system for processing orders and producing invoices is needed. User Requirements. Specifically he would like a system which will:   Hold details of customers and stock;   Process and calculate orders;   Print and store invoices;   Hold and check customer payment details ANALYSIS Initial Investigation Mr Parkes was interviewed to find out more detail about the proposed new order processing system. Payments Any orders for i 10,000 or less will have been paid in advance by customers, and an invoice will follow the payment. Orders for more than i 10,000 require instructions from the customer, therefore in invoice will be sent to the customer and the payment will follow this. Supplier orders and payments. The ordering of products (stock), and payment for them, will not form part of the new system. This will be handled manually by the user. Integration with existing system Each month a record of all payments from customers (in the form of invoices) will be stored in an archive file. This file will be deleted after being copied to an archive file in the existing system. This process will be repeated at the end of each month. Hardware and software Mr Parkes currently has a Pentium 3 500MHz PC with 64MB RAM, 10Gb hard disk and an inkjet printer. He uses Windows 98 as the operating system and has Microsoft Office XP installed on the system. This hardware will be sufficient enough to implement a system. The new system will be developed on a college network and on a desktop similar to the one described above. All files will need to fit on a 31/2 floppy disk for easy transportation between home and college, and some files may need to be compressed. The college network uses 800MHZ Pentium Dell PCs with 128MB RAM and are connected to both a laser and inkjet printer. A comparison of available solutions and software packages can be found in the Design Section. Users skill level Mr Parkes uses his PC for word processing and calculating orders as well as using the internet and E-mail. He is familiar with Internet Explorer, Word and Excel, but has never encountered Access. Data flow diagram The following data flow diagram illustrates the process of entering orders, invoicing and payments Objectives of the new system The system must perform the following functions:   Allow customer details and orders to be entered and edited easily and quickly   Allow invoices to be calculated, created, stored and printed Allow payment details to be entered and stored   Security of data via passwords and backup of data   Allow the user to search for specific products Performance Indicators The following are performance indicators: 1. The system must be flexible and versatile enough to hold any number of orders/invoices for a particular client. 2. Invoices should be calculated and printed in under 2 minutes. 3. The system should backup the information quickly and must be accessible easily. 4. It must take no longer than 10 seconds to search for a particular product. 5. It must not take longer than 5 seconds to search for the details of a particular customer. 6. The system must be able to allow the user to contact a client for any purpose by phone or e-mail. DESIGN Consideration of solutions There are a number of methods of developing the new system. 1. Manual system. This would be very tedious as invoices have to be word processed, printed and then kept in a folder for future reference. Clients would have to send an order by fax only and not over the phone, which means that the user would have to look up products and their prices in catalogues which would be very time-consuming. The calculation of invoices would be a lengthy process and there may be a lot of errors. 2. Spreadsheet system. This would allow Mr Parkes to keep details of customers, products and payments on separate sheets. The calculation of invoices would be made easier to some degree. However, it would take a long time to enter data and the format of reports is much harder in Excel as well. 3. A purposely designed system. This would obviously have a lot of advantages as the system would cater for every aspect of the business activities and functions. However the cost of this kind of package is very high and Mr Parkes would have to think twice before going ahead with this. 4. Existing system. The existing system doesnt have the ability to calculate, store and print invoices and consists of a very lengthy procedure (as stated in Manual system above), so it would not be appropriate. 5. Access database. The new system can be developed using Access, as it is a relational database which is currently installed on the users computer and on the college network. Final choice software This system will be implemented using Access XP. This package is suitable as it has many features which can be used in developing the system, such as:   The ability to link tables via a primary key and foreign key;   The ability to create a menu by using the switchboard function;   The use of macros to automate certain tasks;   The use of mail merge to create standardised documents; The ability to set a password to prevent unauthorised access;   Facilities which allow the creation of reports which can be formatted as required, and previewed before printing. Database design Entity-relationship diagram There are four entities in the database related as follows: Table design The four tables are defined as follows: CUSTOMER TABLE Field Name Data Type Description/Validation CUSTOMER ID Text (5) Primary key CUSTOMER NAME Text (20) ADDRESS Text (50) TELEPHONE NUMBER 1 Number Long Integer TELEPHONE NUMBER 2 Number Long Integer MOBILE NUMBER Number Long Integer FAX NUMBER Number Long Integer. Text (40) PRICE Number Long Integer, Currency QUANTITY Number Long Integer TOTAL Number Long Integer, Currency Form Design There will be four forms for data entry: CUSTOMER FORM This form allows the user to add, edit and delete customer details, as well as allowing the user to contact a customer using an AutoDialer. PAYMENT FORM This allows the user to import customer details from the Invoice form, enter the total amount of a customers order, and store the data in the Payment table. INVOICE FORM This allows the user to enter the details of a customers order(s), such as the product and its price etc. It also allows the user to create and print an invoice for any customer. The user can contact customers via e-mail through the use of a hyperlink to the e-mail client. PRODUCT TABLE This allows the user to add, edit and delete products. FORM Query design All processing of data will be carried out using queries which are run by macros on specific forms as described below: 1. Query1 When an order is entered through the Invoice form, the data is stored in Invoice table. From this table, the data is sent to the query, which allows the user to search for a specific order and check the details. This is possible because an expression is entered in the criteria section of the Customer ID field in the query. Invoices are created and printed from this form via a report with the same name as the query. 2. ProductQuery The user can search for specific products using the PRODUCT CODE. The following expression will be entered into the criteria section of the Product Code field of the query: [ENTER PRODUCT CODE]. When the query is opened, a dialogue box will appear asking the user to enter the product code, after which the query appears displaying the relevant data. 3. PAYMENT This allows the user to search for the payment details of QUERY specific customers using their CUSTOMER ID. The expression: [ENTER CUSTOMER ID], is entered in the Criteria section of the Customer ID field in the query. This displays the total amount of the order. 4. CUSTOMER QUERY This query uses the same expression as that for Payment Query to display the contact details of a specific customer. Report design Reports are used to produce the invoice as they can be customised by the user. Query1 This report is used to create the invoice. It is made from the query of the same name. The invoice structure consists of the records for each product a customer Macro design Macros are used for opening and closing forms, queries and reports, as well as importing data from one form to another, backup of tables, message boxes and record navigation. QTYmacro This is a macro which imports the Customer name and ID from the Customer Order Form to the Payment Form. (This is run when the user clicks on IMPORT CUSTOMER DETAILS on the Payment Form, but only runs when the Customer Order Form is open and minimised) MSG11 This macro displays two dialogue boxes when certain conditions in the design of the macro are satisfied. This macro is attached to the Total field on the Payment Form. (This macro is run when the user enters an amount in the Total field, which is less than i 10,000 or greater than i 10,000 and then clicks on CHECK PAYMENT) BACKUP INVOICE TABLEmacro This macro is used to backup data from the Invoice table to a database on a floppy disk. (This macro is run when the user clicks on BACKUP INVOICE TABLE on the Backup Form. There are three more macros like this which copy the tables in Table design above to the floppy disk) Menu design The menu will be created manually. Below are the notes on the implementation of the menu: There will be six buttons on the main menu which open the following forms: The main menu will appear automatically when the database is opened, and the main database window will be hidden. The application will show the company name and logo in the top left-hand corner (Title bar) of the application window. This will be done using TOOLS, STARTUP. Security The database will be password protected as it is vital that only authorised personnel have access to the database. All printed invoices will be stored in a separate file and kept safely in an archive, therefore fraud/tampering with documents can be avoided. Test Strategy The test strategy will include the following:   The testing of each form, macro, report query;   Testing the functionality of the system i. e. each menu item, command button;   Checking the system by running through a series of tests;   Establishing whether or not the system meets the end-users requirements. Test plan Test No. Test Expected result 1. Test password/startup Only 147 accepted and Main menu opens with company logo and name 2. Test Main Menu options All forms open correctly, Application closes when CLOSE selected. 3. Delete customer record for DJ001 Record deleted 4. Check that totals are calculated correctly by opening Query1 for AP001 Query opens showing all records for AP001 with correct totals 5. Check Invoice details for AP001 Invoice opens in print preview showing all records for AP001 6. Check Print Invoice function for AP001 Invoice prints correctly 7. Check AutoDialer for D. JACKSON Number dialled correctly 8. Check E-mail hyperlink Correct web page (www. hotmail. com) opened 9. Check Backup of Invoice Table The backup is successful and no error messages appear 10. Check Import of customer details for AP001 Details imported successfully 11. Check CHECK PAYMENT function for AP001. Dialogue box appears displaying the message CONTACT CUSTOMER FOR INSTRUCTIONS 12. Test Quit Application Application closes when CLOSE selected Test data set 1: Customer Table Test data set 2: Invoice table Test data set 3: Test data set 4: User testing The system will be tested by the end-user. This testing may reveal some functions and features of the system which do not work as the user would have liked them to. IMPLEMENTATION AND TESTING Test results Test 1: Test password/startup (Only 147 accepted) (Main menu opens after correct password is entered, displaying company name and logo on title bar) Test 2: Test Main Menu options. (All forms open, application closes when CLOSE is selected see screenshots in User Guide. ) Test 3: Delete customer record for DJ001 (Record deleted after DELETE RECORD was selected) Test 4: Check that totals are calculated correctly by opening Query1 for AP001 The invoice is calculated by selecting the required products from the combo boxes, entering the quantity required of each product, then clicking in the Total field to calculate the total for that record. Each successive product is entered by the same procedure, except that a new record has to be added for every new product which is to be entered. Below is a screenshot of the Invoice form from which the details of AP001 will be imported. The Invoice form is minimised and the IMPORT CUSTOMER DETAILS button on the Payment form is selected to perform the operation, as shown on the next page. The above screenshot shows the imported details for AP001 (The details were imported successfully) Test 11: Check CHECK PAYMENT function for AP001 The above screenshot shows the dialogue box PAYMENT CHECK. (The dialogue box appears displaying the message CONTACT CUSTOMER FOR INSTRUCTIONS) Test 12: Test Quit Application (Application closes when CLOSE is selected and the above screen is displayed. ) EVALUATION Performance criteria 1. The system can hold any number of orders for a particular client because each client has a unique CUSTOMER ID which distinguishes one customers order from another customers order. 2. It takes around 1-2 minutes to calculate, view and print a customers invoice. 3. The system is able to backup the tables, which hold important information, easily and can be accessed quickly as well. 4. It takes a maximum of 5 seconds to search for a particular product. 5. It takes around 5 seconds to search for the details of a customer, whether its payment details or contact details. 6. The system allows the user to contact a customer by both telephone and e-mail. USER MANUAL Introduction This application is designed to:   Hold details of customers and their orders;   Hold details of products;   Create and print invoices;   Allow the user to search for both customers and products. It is designed to run on a PC with Windows 98 as its operating system and needs Access XP to be installed. It requires around 2 Mb of disk space to store the application and at least the same again to store data. Starting the system Load Access XP and from the menu select File, Open. The database is called RS Supplies. Security and passwords You will be asked to enter the password on opening the database. The password is currently 147, but you should change this immediately by opening the database in OPEN EXCLUSIVE mode, then select Tools, Security, Unset Database Password. The main menu When the correct password is entered, the main menu will open, as shown below: The Invoice Form The Backup Form The Product Query (Product Query for product code C5073) The Payment Form The Customer Form Invoice Form Selecting this form will bring up the following: Calculate and print invoices The following steps are used to calculate and print invoices:   The combo boxes on the right-hand side are used to enter information in the corresponding text boxes on the left-hand side;   The quantity of a product is typed in manually;   Once all data has been entered, click on the TOTAL text box;   This calculates the total for the chosen quantity and adds VAT at 17. 5%; If more products need to be added, simply click on NEW RECORD and enter the CUSTOMER ID, then carry on as above;   Once the order is complete, go back to the first record and click on MAKE INVOICE. You will be asked to enter the Customer ID. After this, the query opens, check the data, then close the query.   You will return to the Invoice Form, where you click on V  Enter the total amount of the order in the TOTAL field;   Click on CHECK PAYMENT to determine whether or not a payment has been made for that order. Quit Application This is done by clicking on CLOSE on the main menu. However, this doesnt close Access. Backups You should backup the database to a floppy disk at regular intervals, and store the disk in a safe place. TECHNICAL MANUAL This manual is for more experienced users of Access as this allows the user to maintain or enhance the system. Macros The screenshot of the macro MSG11 gives an overview of the message box macro: This macro is attached to the On Click event property of the CHECK PAYMENT button on the Payment form The above two screenshots show the macro MSG11 in design view. There are two different conditions which display two different messages. The screenshot of the macro QTYmcr gives an overview of the SetValue macro: This is a SetValue macro which imports data from the Invoice form to the Payment form. This screenshot of the BACKUP INVOICE TABLEmacro gives an overview of the copy object macro: This macro is used to copy the INVOICE TABLE from the database RS Supplies.