Creating a database Essay Example for Free

Creating a infobase EssayIntroduction RS Supplies in capital of the United Kingdom is a small business which specialises in the import and export of specialist medical equipment for hospitals as comfortably as for other independent businesses. At the moment all pronounces have to be processed manually including invoices as there is no grade treat trunk. However, the nodeele is growing and Mr Parkes wishes to have a computerised order processing ashes. He has a computer which is used for typing up and storing invoices, but feels that a computerised placement for processing orders and producing invoices is needed. User Requirements.Specifically he would like a system which volition Hold elaborate of customers and stock Process and calculate orders Print and keep invoices Hold and check customer payment expatiate ANALYSIS Initial Investigation Mr Parkes was interviewed to find out much detail about the proposed newfangled order processing system. Payments Any orders for i 10,000 or less leave have been paid in resurrect 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 orderliness of products (stock), and payment for them, will not form part of the new system. This will be handled manually by the drug 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 point. This file will be deleted later being copied to an archive file in the existing system. This process will be repeated at the end of from all(prenominal) one month. ironware and software Mr Parkes currently has a Pentium 3 500MHz PC with 64MB RAM, 10Gb hard disk and an inkjet impresser. He uses Windows 98 as the operating system and has Microsoft Office XP installed on the system.This hardware will be sufficient enough to execute a system. The new system will be developed on a college network and on a desktop similar to the one described preceding(prenominal). All files will need to fit on a 31/2 floppy disk for easy transportation between home and college, and almost files whitethorn 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 tail end be found in the aspiration Section. Users skill level Mr Parkes uses his PC for word processing and calculating orders as well as utilise the internet and E-mail.He is familiar with Internet Explorer, Word and Excel, but has never encountered Access. Data flow diagram The hobby data flow diagram illustrates the process of entering orders, invoicing and payments Objectives of the new system The system must(prenominal) perform the following ferments accord customer exposit and orders to be entered and shortened easily and quickly part with invoices to be mensural, 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 assay for specialised products Performance Indicators The following are performance indicators1. The system must be flexible and versatile enough to hold any number of orders/invoices for a particular client. 2. business relationships should be mensural and printed in under 2 transactions. 3. The system should backup the information quickly and must be neighborly 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 throw in the user to contact a client for any purpose by address or electronic mail. DESIGN Consideration of solutions thither 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 deliberateness 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(prenominal) aspect of the business activities and functions. However the cost of this kind of package is very high and Mr Parkes would have to bet twice before g oing ahead with this. 4. Existing system. The existing system doesnt have the major power 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 suffer be developed victimisation 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 give the gate be used in developing the system, such as The ability to link tables via a primary election key and foreign key The ability to create a circuit board by using the switchboard function The use of larges to automate certain tasks The use of mail merge to create standardised documentsThe ability to arrange a password to prevent unauthorised access Facilities which allow the creation of reports which can be formatted as requi red, and previewed before printing. Database design Entity-relationship diagram on that point are four entities in the database related as follows Table design The four tables are defined as follows client TABLE Field break Data Type Description/Validation CUSTOMER ID Text (5) Primary key CUSTOMER touch Text (20) ADDRESS Text (50) call off 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 total Number Long Integer TOTAL Number Long Integer, Currency course of action 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. compensation FORM This allows the user to import customer details from the beak form, enter the total summate 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 bigs 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 doubt, which allows the user to search for a specific order and check the details.This is possible because an looking at 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 hit as the query. 2. ProductQuery The user can search for specific products using the PRODUCT CODE. The foll owing expression will be entered into the criteria section of the Product Code field of the query ENTER PRODUCT CODE. When the query is undefendable, a talk cuff will appear asking the user to enter the product code, after which the query appears displaying the germane(predicate) data.3. PAYMENT This allows the user to search for the payment details of ask 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, centre boxes and record navigation. QTYmacro This is a macro which imports the Customer name and ID from the Customer Order contour line to the Payment Form.(This is run when the user snarls 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) rest period 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 support Form. There are three more macros like this whic h 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 executing of the menu There will be six firings on the main menu which open the following formsThe main menu will appear automatically when the database is opened, and the main database window will be hidden. The coating will show the company name and logo in the top left corner (Title bar) of the cover 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. turn out Strategy The test strategy will include the following The testing of each form, macro, report query rivuleting the functionality of the system i. e. each menu item, command button conditioning 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 primary(prenominal) menu opens with company logo and name 2. Test Main Menu options All forms open aright, finish closes when CLOSE recogniseed. 3. invalidate customer record for DJ001 Record deleted 4. snag 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 diligence Application closes when CLOSE selected Test data delimit 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 want 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, finishing closes when CLOSE is selected see sort outshots 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 box es, entering the amount of money required of each product, then clicking in the Total field to calculate the total for that record. Each in series(p) 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 orde rs for a particular client because each client has a unique CUSTOMER ID which distinguishes one customers order from another customers order. 2. It takes nigh 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 coating 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 visualize and print invoices The following steps are used to calculate and print invoices The combo boxes on the rightfield 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 primary 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 count on 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 macroThis 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.