Home

Stock, Sales and Purchasing System for a Carpet Company

1. Supplier I D which must be no more than 4 characters in length A supplier name which should determine the Supplier ID for ease of use First line of the supplier s address The post code of the supplier When completing supplier information attributes marked with a must be completed Once you have filled in all the details for a new supplier it is necessary to save the supplier To do this you need to click on the button labelled Save If you try to move to a different supplier or wish to return to the supplier list you will be presented with a pop up box which asks you if you would like to save the supplier Click on Yes or No depending on what you would like to do To generate a purchase order for the currently selected supplier click on the New Order button The Find button and drop down box in the top right of the screen allow you to quickly jump to a specific supplier You may need to change the Look In part of the search box after clicking on Find to Carpet World Supplier Information and Match to Any Part of Field To delete a supplier click on the button marked Delete This will delete the currently selected supplier but only if there are no purchase orders for this supplier To find out whether a supplier has any outstanding purchase orders or has supplied any a stock you can click on the labelled tabs towards the top of the screen The button marked Print on each of these screens will enable you to generate hard copies of the i
2. Buttons are clearly labelled and describe what the report is that the button relates to Advanced users may wish to create new reports which may be added to this menu To return to the main menu click on the button in the bottom right of the screen labelled Main Menu 37 Maintenance Menu This menu is only available to authorised users The maintenance menu is accessed by clicking on the Maintenance button on the main menu lt provides facilities for fast manipulation of the data in the Carpet World System Figure 41 Maintenance Menu Delete Unordered Delete Ordered Purchase Products Orders 12 Months Delete Unused Delete Ordered Purchase Categories Orders All Delete Suppliers With No Orders Delete Customers With Bl Delete Unconfirmed Sales No Orders Orders 12 Months Delete Unconfirmed Sales Orders All Buttons are clearly labelled and allow redundant data in the system to be deleted quickly A message asking for confirmation is always displayed before the action is carried out Fig 41 Maintenance Menu To return to the main menu click on the button in the bottom right of the screen labelled Main Menu 38 Appendix K Product Form Test Plan Test Data Purpose Expected Result Actual Result A 1 Product No 1 To test that an autonumber is The Product No text box remains As expected created for the product Autonumber A 2
3. Further interviews were conducted with Mr O Hara as well as other Carpet World employees From these interviews was able to draw up a full list of user requirements 58 Appendix D Detailed E R Diagram Customer Sales Order Sales Order History Purchase Order 59 Appendix E Final Entity Relationship Diagram Description MC Colour PK SalesOrderNo SalesOrderNo DateOrdered DateConfirmed CustomerNo Status DateOrdered DateDelivered tbiSalesOrderHistory oo DateConfirmed Stock SalesOrderNo Request Date Description PurchaseOrderNo Date Description 60 Appendix F Data Dictionary CI Table Field Data Type Field Size Format Required tblCategory Category Text 15 Yes Type Text 6 Yes tblCustomer CustomerNo AutoNumber 15 Yes Title Text 6 Yes Initial Text 1 No Surname Text 50 Yes Address1 Text 50 Yes Address2 Text 50 No Address3 Text 50 No PostCode Text 8 Yes Phone1 Text 20 No Phone2 Text 20 No Email Text 50 No tblOrderStatus StatusID Number Long Integer Yes Status Text 15 Yes tblProduct ProductNo AutoNumber Long Integer 00000000 Yes Description Text 50 Yes Category Text 15 Yes Colour Text 50 No Length Number Double Standard Yes Width Number Double Standard Yes CostPrice Number Double Standard Yes Sale
4. History tab towards the top of the screen To print the order history click on the Print button 27 Sales orders may occasionally result in stock requests for specific products To view these requests click on the Question Mark button and select Stock Requests A list of requested products will be displayed with the sales order number it has been ordered on and the quantity required When the requested item is delivered on a purchase order a message box will be displayed notifying you that it may now be possible to complete the sales order Figure 29 amp Carpet World Requested Stock MORD Requested Stock Next Stock No 02 04 2003 Mr C O Hara 00000006 Uniclick Quick Step Fig 29 Stock Requests The Find button and drop down box in the top right of the screen allow you to quickly jump to a specific purchase order You may need to change the Look In part of the search box after clicking on Find to Carpet World Purchase Order Information and the Match to Any Part of Field To delete a purchase order click on the button marked Delete This will delete the currently selected purchase order If items on the order have been delivered it is not recommended as stock adjustments will have to be made To return to the purchase orders list click on the Exit button Click on the Exit button on the purchase orders list screen to return to the main menu 28 Ae Customers Customers purchase items of stock through sale
5. Open the purchase orders screen Create a new purchase order using order number 321 and Carpet Supplier UK as the supplier Add one item to the order using the Stock Requests report as a guide and assign it stock number 4 Add a second item to the order e Quick Step Uniclick in Mirabow Print off the purchase order Deliver the first item on the order Return to the Main Menu Open the sales order screen and edit the order that was created previously Update the stock number for the item that was requested Confirm the order Print off a cutting sheet Change the order to fitted Print of a sales invoice Change the order to paid Return to the Main Menu Exit the Carpet World system 108 User Test Results Kath Test No Rank 1 5 Notes 1 1 2 2 Kath was a bit slow in finding the customer button but once the screen was present she was able to enter the data quickly 3 3 Clicked on the New Customer button before finding the New Order button 4 1 5 1 6 1 7 2 Couldn t find the Stock Requests report button at first 8 1 9 1 10 1 11 4 Pressed the Close button when trying to access the Stock Requests form Confused by having to double click on the appropriate option 12 1 13 3 Again almost selected the Close button before realising she needed to double click 14
6. clicking on Find to Carpet World Customer Information and Match to Any Part of Field To delete a customer click on the button marked Delete This will delete the currently selected customer but only if there are no sales orders for this customer To find out whether a customer has any outstanding sales orders or what stock they have purchased you can click on the labelled tabs towards the top of the screen The button marked Print on each of these screens will enable you to generate hard copies of the information being displayed To return to the customer list click on the Exit button Click on the Exit button on the customer list screen to return to the main menu 31 D I Sales Orders Sales orders are used to order products for customers It is important to fully understand how sales orders are processed to make the most of the Carpet World system Sale Order List You can access the sales order section of the Carpet World System by clicking on the Sales Orders button from the Main Menu The screen you be presented with is shown below and displays the sales orders currently stored on the system When you first start the Carpet World program there will be no sales orders listed however as the number of sales orders increases the screen will fill up To sort the list on a particular attribute double click the appropriate header Figure 33 WO Sales Orders Find Edit Mr C O Hara 0204 2003 Mr c Ohara 0370412003
7. 0370472003 Fig 33 Sales Order List 32 NE When the number of records becomes excessively large it may become difficult to find the correct sale order You can overcome this by clicking on the Find button at the top of the screen A box will appear allowing you to enter words to search for You may need to change the Look In part of the search box to Carpet World Sales Orders and Match to Any Part of Field Figure 34 Find Find What Doe v Find Next Cancel Look In Carpet World Sales Orders y Match Any Part of Field y Search all a J Match Case JW Search Fields 4s Formatted Fig 34 Find Sales Order The buttons at the top can be used to add and edit existing sales orders The Print button enables you to print the list of sales orders that are currently on the system Individual Sales Orders When you click on the Add or Edit button on the sales orders list screen you will be presented with the screen shown below Figure 35 A Ad Sales Order Information New Find Find Order Details History 03 04 200 Hara X old E H Mohawk Horizon 5 00 13 13 12 00 87 50 1531 O Mohawk Horizon 6 00 13 13 12 00 105 00 1838 O Uniclick Quick Step 2 00 1 00 16 00 32 00 sen O e 1 Print Confirm Fitted Paid Lemos contem mal eaa Undo Save Delete Exit Fig 35 Sales Order Information From here you can add sales orders to the Ca
8. Description Mohawk Horizon To ensure that a product Description is accepted and As expected description can be added and a Product No text box is assigned product number is automatically the next available number assigned A 3 Click save button To ensure that all required Message box is displayed asking Message box is displayed with attributes of the product need to the user to complete necessary Error 94 Invalid Use of Null be completed before it can be data saved Run Tests Again Bi Product No 1 To test that an autonumber is The Product No text box remains As expected created for the product Autonumber B 2 Description Mohawk Horizon To ensure that a product Description is accepted and As expected description can be added and a Product No text box is assigned product number is automatically the next available number assigned B 3 Save the product To ensure that all required Message box is displayed asking As expected attributes of the product need to the user to complete necessary be completed before it can be data saved B 4 Category Cut To ensure that colour length and Colour Length Units and Width As expected width can be added for the text boxes are enabled allowing product data to be entered B 5 Category Remnant To ensure that colour length and Colour Length Units and Width As expected width can be added for the text boxes are enabled allowing product data to
9. Final Entity Relationship Diagram Error Bookmark not defined Appendix F Data Dictonanm nenn rnn nnen rnn nrnna Error Bookmark not defined Appendix G Microsoft Access Relationship Diagram Error Bookmark not defined Appendix H Sales Order Form VBA Code Extract Error Bookmark not defined Appendix l Sample Henorts Error Bookmark not defined Appendix J User Manual oocccccccncccncccnnncnnnnnnncnnnenincnnnnnos Error Bookmark not defined Appendix K Product Form Test Plan Error Bookmark not defined Appendix L Acceptance Tests Error Bookmark not defined 4 Introduction The first chapter of this report introduces the project It begins with background information on Carpet World the company for which the system is being produced before explaining the overall objectives Deliverables that will result in project completion are then highlighted followed by the importance of producing a project schedule This chapter ends with details on the structure of the remainder of the report 1 1 Carpet World Carpet World is a small carpet company located in the West Midlands They have two premises both located in Nuneaton Warwickshire The head office is located on Weddington Road a primary route into Nuneaton The second property is based on the opposite side of Nuneaton at Aston Road They are currently in the process of expanding to a third location in Hinckley Leicestershire whic
10. Leeds Nielsen J 1993 Usability Engineering Academic Press Office Marketing 1998 Microsoft Access 2000 Product Enhancements Guide Microsoft Peters J F Pedrycz W 2000 Software Engineering An Engineering Approach John Wiley 8 Sons Inc Redmill F 1997 Software Projects Evolutionary vs Big Bang Delivery Wiley pp12 17 Rock Evans R 1989 A Simple Introduction to Data and Activity Analysis Computer Weekly Roland R 2002 To Be or Not To Be Normal That is the Database Question URL http builder com com article jhtml id u00320020819ero01 htm 11 December 2002 Ruddle R 2000 Introduction to Human Computer Interaction University of Leeds Silberschatz A 1997 Database System Concepts McGraw Hill pp 36 37 Sommerville l 2001 Software Requirements in Software Engineering 6 Edition Addison Wesley pp 98 99 Stapleton J 1999 Dynamic Systems Development Method Addison Wesley Sumin D 2000 Microsoft Access Security and Passwords URL http www vb123 com toolshed 00_accvb accesssecurity htm 27 November 2002 Sutcliffe A G 1995 Human Computer Interface Design 2nd Edition Macmillan 50 Appendix A Reflection This appendix is to review my overall experience of this project It begins with a look at the project as a whole examining what could be improved and what has been done well It also includes advice for students who may complete a similar database
11. SS UU ace as bce es ai ees Beeson Ss 33 4 7 Documentation 35 4 8 Deployment and Integration E 37 CI Testing E Error Bookmark not defined 5 1 Validation and Verfication Error Bookmark not defined 5 2 Methods of Testing 222 cccceeeeessesecneeeeeeeeeneeees Error Bookmark not defined 5 3 Testing the Carpet World System ssseeeeeeeeenn Error Bookmark not defined SAA Error Bookmark not defined 6 1 User Requirement cccccceccceeeeeeeeeeeeeeeeeeeeeneeees Error Bookmark not defined 6 2 Technol sica Error Bookmark not defined 6 3 Methodology EE Error Bookmark not defined 6 4 Implementation and Testing eeeeeeeeeeeeeeeeee Error Bookmark not defined 6 5 EE oe SEENEN dees Error Bookmark not defined CONCIUSION dani dere Error Bookmark not defined 7 1 System HEED geed Error Bookmark not defined 7 2 Future Enhancements c cccecceeeeeeeeeeeeeeeeeeeeees Error Bookmark not defined 7 3 Project QUICOM sierra Error Bookmark not defined Bibliography and References oommoonocccccccccnonnnnnnananncnnnnnnnnnnas Error Bookmark not defined Appendix A Reflection snsennnenntnenttenntnnrtnnrnnn renn nnnn nenna Error Bookmark not defined Appendix B Project Schedule Error Bookmark not defined Appendix C Interview Transcript occcccccccncncnnnnnnnnnnnnns Error Bookmark not defined Appendix D Detailed E R Diagram Error Bookmark not defined Appendix E
12. Yes No Yes No No Request Yes No Yes No No tbiStock StockNo Number Long Integer Yes ProductNo Number Long Integer 00000000 Yes Length Number Double Yes tbIStockAdjust AdjustNo AutoNumber Long Integer Yes StockNo Number Long Integer Yes Date Date Time Short Date Yes OldLength Number Double Yes Reason Memo Yes tbISupplier SupplierlD Text 8 Yes Name Text 50 Yes Contact Text 50 No Address1 Text 50 Yes Address2 Text 50 No Address3 Text 50 No PostCode Text 8 Yes Phone Text 20 No Fax Text 20 No Email Text 50 No Primary keys are in bold Foreign keys are underlined 62 Appendix G Microsoft Access Relationship Diagram tbiCategory Category Type tbisalesOrderltems tbiProduct 1 tblSalesOrderHistor HistoryNo SalesOrderNo DateOrdered DateOrdered DateDelivered DateConfirmed SupplierID CustomerNo Status Status 63 Appendix H Sales Order Form VBA Code Extract Allocate elements of dynamic array and set boolean to true to add to array ReDim dblStock intNoRecs 1 blndssignStock True Check quantities are not greater than the amount in stock Do While intCurrentRec lt intNoRecs If IsNull Forms frmSalesOrder frmSalesOrderSubform cbOS5tockNo Value False And IsNull Forms frmSalesOrder frmSalesOrderSubform txtDateConfirmed Value Then intStockNo Forms frmSalesO0rder frmSales0rderSubform cbOStockNo Value intProductNo Forms frmSal
13. all the details for a new purchase order you may add products to the order This is done by selecting product numbers from the drop down box in the table in the middle of the form Products will only appear if they have already been added to the system When you have added a product the description category length width and cost price will be automatically completed based on the information stored on the product It is possible to change the length but this is not recommended When adding items to a purchase order it is not possible to order two products with the same product number each item on the order must be different When all items have been added to the order you may print out a purchase order to send to the supplier by clicking on the Print button When delivery of items on the order has taken place they must be given stock numbers To find the next available stock number click on the button labelled with a Question Mark A form with two options will appear which also displays the next available stock number Double clicking on Stock Lookup will show you stock numbers for products that are already in stock that appear on your purchase order Products where the category type is Unit can be given the same stock number as long as they are identical product numbers Products with category type Length must be assigned unique stock numbers Clicking on the Show All button will display all stock items Figure 26 ES Carpet World Stock L
14. any errors to resolve or features that they would like to see added Showing the Carpet World system alongside the Business Works software allows a direct comparison between the two The screenshot below shows the sales order screens for both systems Figure 6 1 Description tengal wen Gay Unit_ Usish Enl Gecke Tatai SP Ex Del Date LINES TOTAL EX 530809 Figure 6 1 Business Works and Carpet World sales order screens CI From the screenshots it is clear how simple and attractive the interface in the Carpet World system is when compared to the Business Works application Focusing on the important aspects of stock control and order processing allows the Carpet World system to be relatively simple whilst providing the necessary functionality to meet Carpet World s user requirements The Business Works software does record more information and offers more features but when looking at the results of the acceptance tests in section 5 3 and the complexity of the Business Works software it becomes apparent that it was unsuitable for Carpet World Employees at Carpet World do not have the necessary IT skills to make use of the Business Works application The customised Carpet World system provides the key features necessary for Carpet World to record and process relevant information without an expensive outlay or lengthy period of training 6 3 Methodology The iterative methodology chosen in section 2 2 w
15. as soon as the system was seen by Carpet World a further requirement was noted Carpet World stated that they would like to be able to add product categories Although it was not a difficult task to adapt the database the category type is extensively used throughout the system If this requirement had been recorded at the beginning fewer interface changes would have been needed and time could have been spent elsewhere on the system It was fortunate that the database was designed to include a categories table before this change was requested 6 2 Technology Looking back Microsoft Access was the most suitable technology to implement the Carpet World system in Although it lacks some of the advanced features of Microsoft SQL Server the system does exactly what was required in a fast efficient and user friendly manner The system is a secure stock purchasing and sales order system that supports multiple users accessing the same records simultaneously A future change may involve migrating to SQL Server to improve performance but this is not currently necessary Choosing Microsoft Access as the implementation technology helped development of the software to proceed quickly Creation of database objects is simple allowing new features to be added rapidly As Microsoft Access is already installed on computers at Carpet World latest versions of the system could be sent to show how it was progressing Feedback could then be provided highlighting
16. as stated by Hughes amp Cotterell 1999 to raise any issues or concerns they may have regarding the system 2 3 Choice of Technology One of the most important aspects of this project is the platform that the software will be implemented on Computers at Carpet World are all Windows based machines all installed with standard Microsoft applications including Excel Word and Access Carpet World would like to see a solution that would integrate with existing software but would allow customisation after the product has been delivered The following table using information provided by Clearform 2001 and Office Marketing 1998 identifies the three main solutions that were available and the advantages and disadvantages of each Advantages Disadvantages MS Access e Limited in what it can do with Business Works specialist software Installed at Carpet World e Quick to implement e Supports 30 70 concurrent users Supports backups archiving Converts to SQL Server database Secure Networking support MS Office integration Experience Supports very large databases e Support for triggers stored procedures and other database analysis tools Supports 30 000 concurrent users Supports backups archiving Scalable Secure Networking support MS Office integration Aimed specifically at carpet industry Includes accounting package respect to real database manipulations those contained in SQL Limited to s
17. as well as provide cost estimates using a single application A user manual has been supplied providing the necessary information required for users to make the most of the system The time available restricted further enhancements that could have been added to the system but it provides a stable base for future improvements Looking back at the user requirements and the acceptance testing in section 5 3 the system can be considered a SUCCESS 7 2 Future Enhancements The system as it stands is what was asked for by Carpet World It meets all of the requirements that were identified but there are a number of features that could be made to improve the system e Carpet World was provided with no information regarding adding reports and modifying the reports menu even though this is supported by the system e E mail addresses are stored for both customers and suppliers E mailing purchase orders and sales invoices would be a simple but effective enhancement for the system e When choosing a product for a sales or purchase order it is found using the product number Although it is sorted on the product name and colour it could be improved if the user could type in the first few characters of the name to reduce the size of the product list Consequently products could be found faster e The system does not record the date that an order was paid on other than in the history table A field could be added to the purchase and sales order ta
18. for selling products to customers PO When a customer visits Carpet World we write down the products they wish to purchase We add these to a sales order and give the order number to the customer The order is then filed The customer may then contact us and confirm the order using the order number they were given A cutting sheet is then produced with all the products and required quantities for the customer s order We gather all the order items together and then contact the customer to arrange a date for fitting When the products have been fitted we send a sales invoice to the customer On receipt of payment the order is complete CO How long do you record unconfirmed orders for PO Customers may not confirm the order for a lengthy period of time It can be longer than six months before a customer returns and confirms the order 56 PO The stock is only adjusted when an order is confirmed This is because a customer CO When do you adjust stock quantities Is it when the order is first raised may never confirm the order CO What happens if a product isn t in stock PO We raise a stock request This means that a purchase order is raised for the required product The same goes for cuts of carpet as these are special orders CO forgot to mention this before but how do you assign order numbers to orders PO Purchase order numbers are just incremented but sales orders are slightly different We have numbered notepaper th
19. forms as once a category has been added the type cannot be changed This is to ensure data in the system such as purchase and sales order totals remains valid There are two types of category in the system length and unit which are used to calculate the cost and sale price of products in the system If a category type is unit the quantity will be multiplied by the width length and price to calculate the overall value A category of type length will again be multiplied by the width length and price but will then be divided by nine This division is necessary as carpet is bought and sold in square yards unlike items such as boxes of wood and carpet glue When a customer wishes to purchase stock from Carpet World a sales order is raised The sales order screen is responsible for processing all stages of a customer sales order consequently it is the most complex form in the system Figure 4 5 Ht Sales Order Information New Find Details History 03 04 200 Hara y pl old Mohawk Horizon 5 00 13 13 12 00 87 50 1531 O Mohawk Horizon 6 00 13 13 1200 105 00 138 O 8 Uniclick Quick Step 2 00 1 00 16 00 32 00 ss O e 224 50 39 29 Print Confirm Fitted Paid 263 79 Figure 4 5 Sales Order form 27 CI qi CAN S Once the customer has been added to the database a new sales order is created The sales order is automatically given the current date but the order number
20. great deal from working individually for such a long period of time have acquired new skills and enjoyed building a system that will be a valuable aid to Carpet World It was beneficial seeing how could apply expertise attained at University to a situation in a business environment The system that has been produced is what was required and although it could be improved is a reliable efficient multi user application 52 Appendix B Project Schedule Task Nama SE H Are A Mnimam Requirements ort wa el zaw Ou 0111 2002 MPa Report besiess e Een w D T DET esst LB MENS ER EECH RN BE it z COCA T 53 Appendix C Interview Transcript In order to understand the requirements of the system interviews were conducted with Carpet World employees These were done on an individual basis and varied in length The transcript below is an abridged version from an interview with Mr O Hara Date 31 October 2002 Time 10 30am Location Carpet World Weddington Road After preliminaries Christopher O Hara How does the stock process at Carpet World work Phil O Hara Stock is purchased from suppliers using purchase orders Products are ordered and then assigned a stock number when the product is delivered CO Do the stock numbers have a specific format or are they assigned a stock number based on the product type PO Stock is just given the next available stock number These are standard numbers and do
21. i e the whole database will not be locked e Records will not be locked when no procedures are called e Database will be accessible from remote locations 3 The system shall allow additional custom reports to be added after the system is completed This shall only be done by the manager or supervisors 4 The system shall generate graphs depicting cash flow between two given dates 5 The system shall be compatible with other software currently in use to allow for importing and exporting of data Non Functional User Requirements Maintenance 6 The system shall automatically backup all data 2 2 Choice of Methodology Methodologies specify as quoted by Avison and Fitzgerald 1995 how the project is to be broken down in states what tasks are to be carried out at each stage the outputs to be produced and which people are to be involved Using a process model helps reflect the natural order of events over a series of broad project stages according to Redmill 1997 There are a number of models available to a software engineer to aid in the software engineering process including the waterfall spiral and matrix models The waterfall model sometimes referred to as the Systems Development Lifecycle SDLC is shown below Figure 2 1 Specification C Design pi Construction gi L Validation pi t Installation amp Acceptance Figure 2 1 Waterfa
22. is length such as carpet rolls need to be assigned unique stock numbers so multiple rolls cannot be ordered unless two purchase orders are created However products that are of type unit can have the same stock number as an existing stock item as long as the product number is identical The user is notified of invalid and duplicate stock numbers when they confirm delivery of the purchase order allowing them to take the appropriate action Figure 4 7 Duplicate Stock Number xi Invalid Stock Number x ES o Would you like to update the current stock with Stock number 2 assigned to product 0007 already exists 2 the stock quantites from this order as Product for a different product 0002 Uniclic Laminate Flooring already has stock number 2 associated with it Please enter an alternative stock number For this item If you would like to assign it a new stock number click No Yes No Figure 4 7 Examples of message boxes on delivery of duplicate stock numbers Products from the same order may occasionally be delivered on different dates so the system is required to record the delivery date of each product individually Once all of the items on the order have been delivered the order status is changed If a product has been requested for a sales order and the item has been delivered on a purchase order the user is alerted with a message box Figure 4 8 Deliver Purchase Order E x gt This product has
23. is accepted As expected be added B 16 Length 94 00 To ensure that only positive Message box is displayed asking As expected lengths can be entered the user to correct the data B 17 Cost Price 6 00 To ensure that only positive prices Message box is displayed asking As expected can be entered the user to correct the data B 18 Cost Price 6 00 To ensure that a product price can Price is accepted As expected be added B 19 Sale Price 9 00 To ensure that only positive prices Message box is displayed asking As expected can be entered the user to correct the data B 20 Cost Price 9 00 To ensure that a product price can Price is accepted As expected be added B 21 Click save button To test the product can be saved Product is saved As expected when all data has been entered 107 Appendix L Acceptance Tests ee Pi E 9 q 11 12 13 14 15 16 17 18 19 20 21 22 23 24 User Test Log in with the user name user and the password pass Open the customers screen and add yourself as a customer Create a new sales order for yourself with the order number 123 Add two items to the order inserting stock numbers and lengths of your choice e Mohawk Horizon in Playa Clay e Mohawk Horizon in Birch Bark Confirm the order Return to the Main Menu Open the reports menu and print of the Stock Requests report Return to the Main Menu
24. not differ depending on the product type CO I m a little confused as to the difference between a product and an item of stock Could you elaborate on these PO Products are the actual items that are purchased and sold When we raise a purchase order for a supplier we use the product number to order the item These items that have been ordered are then assigned stock numbers when delivery takes place When a customer orders some items the product number is recorded The stock item with that product number is then adjusted accordingly CO Can there be more than one product per stock number PO Rolls remnants and cuts must all have unique stock numbers Other items such as door claws and boxes of wood can have the same stock number CO What s the difference between the product types that you have just mentioned PO Rolls are the rolls of carpet you usually see in carpet shops Remnants are the remains of the roll and are usually sold at a reduced price Cuts are pieces of carpet that are not stocked and need to be ordered specially for a customer Boxes of wood and other items such as carpet glue that are also assigned stock numbers 54 CI Li CAMS CO Are Roll Remnant Cut Wood and Other the only product categories that you currently use PO Yes we do not use any other types for the products CO How do you calculate the prices for items PO To calculate the price for an item say
25. processed will not be processed again and the stock request will be removed Ordered items where stock has previously been processed are marked with a tick in the Stock column of the table When all items have been added to the order you may print out a cutting sheet to give to staff by clicking the Print button and selecting Cutting Sheet After the items have been given to the customer or carpet has been fitted it is necessary to change the status of the order Clicking the Delivered button will change the order status to Delivered Figure 38 Sales Order Information J x l D The status of this order is DELIVERED JU Changing items and quantities on this order may cause errors Fig 38 Sales Order Delivered 35 EI y The final stage of a sales order involves receiving payment from the customer To print an invoice click the Print button and double click Sales Invoice When payment is received from the customer you can click on the Paid button This will change the status of the order to Paid and the sales order is complete Figure 39 Sales Order Information x r i The status of this order is PAID A Changing items and quantities on this order may cause errors Fig 39 Sales Order Paid A sales order history is kept for each sale order and records information on when items were delivered and when the order status was changed To view this click on the History tab towards the top of the screen To pr
26. roll The status of a sales order is set to Hold when a sales order is first created If a customer wishes to purchase the items on the sales order the status of the order needs to be changed This is done by clicking the Confirm button If all items on the order are in stock the stock quantities are adjusted and the order status is changed to Fit Figure 36 Sales Order Information 77 x 3 The status of this order is FIT A Changing items and quantities on this order may cause errors Fig 36 Sales Order Fitted 34 You may find that since the sales order was created items have become out of stock or there is not enough stock to satisfy the order If this is the case you may need to select a different stock number or alternatively create a stock request A prompt will appear asking for confirmation that this is what you would like to do Answering Yes will create the stock request and adjust the stock for items on the order where stock is available The status of the order is changed to Ordered Figure 37 Confirm Sales Order d J xj 9 Product 0006 has no stock number which will mean 2 a request will be made For this item to be added to a purchase order Would you still like to confirm this order Fig 37 Confirm Sales Order On delivery of the requested stock item you will be able to change the status of the order to Fit This is done by again clicking the Confirm button Items that have already been
27. shall record customer number name address phone number and e mail address where applicable for all customers 5 The system shall record purchase order number date ordered date delivered supplier ID and status for each purchase order 6 The system shall record sales order number date ordered date delivered customer number and status for each sales order 7 The system shall record purchase order number product number stock number date delivered length width and cost price for all purchase order items 8 The system shall record sales order number product number stock number length width sale price and confirmation date for each sales order item 9 The system shall record product number and sales order item number for product requests from customer sales orders System Features 10 The system shall not enforce product numbers stock numbers or order numbers on the user The user will enter these manually and can be any positive integer 11 The system shall warn users of duplicate entries that will occur if a value is entered that is already present on the system see requirement 10 12 The system shall warn users when any purchase order has been Delivered or Paid 13 The system shall warn users when any sales order is Confirmed Ready to Fit Delivered Awaiting payment or Paid CI qi CANT 5 14 The system shall allow purchase and sales order
28. strMsg You have entered a length which exceeds the strNL e _ quantity in stock for Product amp strProductNo e amp _ strNL e strNL amp You will need to raise a Purchase Order e _ strNL amp against this product to or adjust the quanties _ strNL amp to complete this order End If Else strMsg You have entered a length which exceeds the e strNL e _ quantity in stock for Product e strProductNo amp _ strNL amp strNL You will need to raise a Purchase Order amp _ strNL amp against this product to or adjust the quanties e strNL to complete this order End If strTitle Confirm Sales Order intStyle vb0K0nly vbQuestion MsgBox strMsg intStyle strTitle GoTo Exit_btnConfirm Click End If See if the stock item has previously been read into the array For intI 0 To intNoRecs If dblStock intI 0 intStockNo Then If item is already in the array add the existing length to the length from the subform dbl tock intI 1 dblStock intI 1 dbllength blndssignStock False Exit For End If Next Add to array if it is not present If blnassignStock True Then dblStock intCurrentRec 0 intStockNo dblStock intCurrentRec 1 dblLength End If End If Reset value for next stock item on order blndssignStock True DoCmd GoToRecord acNext intCurrentRec intCurrentRec 1 Loop 64 Appendix I Sample Reports Stock Requests Order No Custo
29. that the Carpet World staff are confident in using the database before the current methods are gradually phased out It is estimated that it will be between two to three months before Carpet World rely solely on this new system 37 5 Testing Chapter five describes the testing phase of the Carpet World system The chapter begins by introducing the difference between validation and verification before explaining software testing methods and how test results can be recorded using test plans The chapter ends with details on how the Carpet World system was tested and the feedback from user acceptance tests 5 1 Validation and Verification Software testing as defined by Peters A Pedrycz 2000 is used to determine when a system can be released and acts as a gauge for future performance Jesty 2000 says that testing can only prove that software is correct if all possible states are tested Unfortunately this is not usually possible with software due to the complexity of current systems It can however be made more manageable by splitting it into two distinct areas as identified by Sommerville 2001 e Verification Are we building the product right e Validation Are we building the right product Verification of the system ensures that the system conforms to its specification The system should meet its functional and non functional requirements as outlined in section 2 1 Validation of the Carpet World system ensures that the s
30. together for swift navigation Fig 11 Main Menu If you wish to exit the Carpet World System click on the button in the bottom right of the screen labelled Exit 11 Products Product List QE Products form the basis for the whole of the Carpet World System Without adding products to the system no stock can be recorded and no sales or purchase orders can be processed You can access the products section of the Carpet World System by clicking on the Products button from the Main Menu The screen you are presented with is shown below and lists the products currently stored on the system When you first start the Carpet World program there will be no products listed however as the number of products increases the screen will fill up To sort the list on a particular attribute double click the appropriate header Figure 12 Find Edit 00000001 Mohawk Horizon Speckled Slate 00000001 00000003 00000004 Fig 12 Product List 12 When the number of records becomes excessively large it may become difficult to find the correct product You can overcome this by clicking on the Find button at the top of the screen A box will appear allowing you to enter words to search for You may need to change the Look In part of the search box to Carpet World Products and Match to Any Part of Field Figure 13 Find Cancel Look In Carpet World Products y Match Any Part of F
31. 001 mentions that it is good practice to design online help in conjunction with paper documentation although he does state that paper documentation should be more detailed Sommerville 2001 identifies five document types that should be delivered with a software system e Functional description Brief description of what the system provides allowing user to decide whether they need the system e Installation document Details on how to install the system including information on accompanying disks and any hardware and software requirements e Introductory manual An informal introduction to the system It describes the typical usage of the system and should explain how to recover from mistakes e Reference manual Lists error messages with possible causes and how to recover from detected errors Also includes system facilities and their usage e Administrator s manual Only provided for a few systems to explain messages generated when the system interacts with other systems It may include information on recognising hardware related errors The Carpet World system is different from off the shelf applications in that it is being built for a specific company and purpose For this reason it is not necessary for a functional description of the system to be included as part of the documentation The remaining four documents that are recommended by Sommerville 2001 are combined into a single user manual Two separate versions of system do
32. 3 Unsure on how to deliver the items on the order but she was fine once the error message had appeared with instructions on how to do this 15 1 16 1 17 1 18 2 Tried to click on the Stock checkbox in a similar way to the Purchase Order screen 19 1 20 1 21 1 22 1 23 1 24 1 109 User Questions Kath 1 Do you like the appearance of the system Yes The screens are well laid out and it s easy to read the text like the colour scheme as well 2 How easy was it to use found it reasonably easy but was slightly confused by the double clicking on some forms I m sure once I ve used it for a while it will become easier 3 Is there anything you don t like about the system As mentioned didn t like the way there was no Print button when I had to print off a sales order and cutting sheet It would be easier to highlight it and then click a button rather than having to double click 4 Is there anything that you would like to see added to the system I didn t notice anything that I d like while was running through the tests 5 Overall do you believe it will assist Carpet World in processing stock purchase orders and sales orders Yes definitely Once everyone gets the hang of it I m sure it will be a great benefit to Carpet World 110
33. Email tblStatus StatusID Status tolPurchaseOrder PurchaseOrderNo DateOrdered DateDelivered SupplierlD StatusID tolPurchaseOrderltems PurchaseOrderNo ProductNo StockNo DateDelivered Length Width CostPrice Deliver tblPurchaseOrderHistory HistoryNo PurchaseOrderNo Date Description tblSalesOrder SalesOrderNo DateOrdered DateConfirmed CustomerNo Status D tbISalesOrderltems OrderltemNo SalesOrderNo ProductNo StockNo Length Width SalePrice DateConfirmed StockAdjust StockRequest tblSalesOrderHistory HistoryNo SalesOrderNo Date Description 17 CI From the schema we can assess the functional dependencies and therefore the normal form of each table tblCategory CategorylD Category Type tbIProduct ProductNo Description CategoryID Colour Length Width CostPrice SalePrice tbIStock ProductNo ProductNo Length tbIStockAdjust AdjustNo StockNo Date OldLength Reason tblCustomer CustomerNo Title Initial Surname Address1 Address2 Address3 PostCode Phone1 Phone2 Email tblSupplier SupplierlD Name Contact Address1 Address2 Address3 PostCode Phone Fax Email tbIStatus StatusID Status tblPurchaseOrder PurchaseOrderNo DateOrdered DateDelivered SupplierlD StatusID tblPurchaseOrderltems CategorylD StockNo DateDelivered Length ProductNo Width CostPrice Deliver ProductNo Categor
34. Jenkins 2000 explain that black box testing involves running all tests inspecting the test results rectifying any errors and then running all tests again until all tests are passed It is important to re run tests after changes have been made as these modifications may introduce new errors White box testing is a logical testing method as explained by Heathcote 1996 which splits the system into its smallest units and then tests each path through that unit for failure Due to the complexity of the Carpet World system it is difficult to test all paths through the system White box testing was carried out as comprehensively as possible in the time that was available Avison amp Shab 1997 define acceptance testing as being used to validate the performance of the system for the user Acceptance tests typically take the form of scenarios and test the usability of the system For example in the case of Carpet World a sales order could be processed using test data The system should behave as expected by the user with the appropriate level of feedback Stapleton 1999 stresses the importance of the presence of users in the development team which places an early emphasis on validation This was not possible with the Carpet World system so instead incomplete versions of the system were sent to Carpet World allowing them to provide feedback on the current implementation Unlike traditional acceptance testing which typically occurs at
35. Price Number Double Standard Yes tblPurchaseOrder PurchaseOrderNo Number Long Integer Yes DateOrdered Date Time Short Date Yes DateDelivered Date Time Short Date No SupplierID Text 8 Yes Status Number Long Integer Yes tblPurchaseOrderHistory HistoryNo AutoNumber Long Integer Yes PurchaseOrderNo Number Long Integer Yes Date Date Time Yes Description Memo Yes tblPurchaseOrderltems PurchaseOrderNo Number Long Integer Yes ProductNo Number Long Integer 00000000 Yes StockNo Number Long Integer No DateDelivered Date Time Short Date No Length Number Double Yes Width Number Double Yes CostPrice Number Double Yes Deliver Yes No Yes No No 61 CI tblSalesOrder SalesOrderNo Number Long Integer Yes DateOrdered Date Time Short Date Yes DateConfirmed Date Time Short Date No CustomerNo Number Long Integer Yes Status Number Long Integer Yes tblSalesOrderHistory HistoryNo Autonumber Long Integer Yes SalesOrderNo Number Long Integer Yes Date Date Time Yes Description Memo Yes tblISalesOrderltems OrderltemNo AutoNumber Long Integer Yes PurchaseOrderNo Number Long Integer Yes ProductNo Number Long Integer 00000000 Yes StockNo Number Long Integer No DateDelivered Date Time Short Date No Length Number Double Yes Width Number Double Yes SalePrice Number Double Yes DateConfirmed Date Time Short Date No Stock
36. Stock Sales and Purchasing System for a Carpet Company Christopher O Hara BSc Hons Computing Ind 2002 2003 The candidate confirms that the work submitted is their own and the appropriate credit has been given where reference has been made to the work of others understand that failure to attribute material which is obtained from another source may be considered as plagiarism Signature of student ANG Summary Carpet World is a small carpet company located in Nuneaton West Midlands Their existing manual system for recording transactions was proving inadequate as the number of orders increased The overall objective of this project was to produce a multi user stock sales and purchasing system which would replace the existing system The system needed to provide features for handling purchase orders sales orders and stock This report covers the creation of the application including database tables input forms user security and system maintenance A user manual was produced providing users with valuable assistance when accessing the system Two versions of this were created a paper based document and integrated online help facility All of the project aims and objectives were successfully completed The system was created in Microsoft Access 2000 using a client server architecture A simplified version of the system can be found in Appendix M or online at http www chrisohara co uk cw html Acknowledge
37. a the Carpet World Accountant and a number of Carpet World employees An examination of the current software was also undertaken The most important features to be implemented were drawn together from interviews with Mr O Hara who explained in detail the processes that the business followed during purchases and sales A transcript from an interview with Mr O Hara can be found in Appendix C The employees were then asked what they would like to be able to do with the system citing order history as an important facility to include The user requirements have been split into what is essential to the system and possible enhancements that could be made to it They have been documented using a format devised by the European Commission 2001 and explained by Jesty 2000 These requirements have been written in natural language to communicate to readers who do not have a detailed technical knowledge of the system guidelines covered by Somerville 2001 2 1 1 Essential User Requirements Functional User Requirements Data Stored 1 The system shall record number name category length width cost price sale price and colour where applicable for all products 2 The system shall record the stock number product number and length for each stock item 3 The system shall record supplier ID name contact address phone number fax number where applicable and e mail address where applicable for every supplier 4 The system
38. a necessary step to take when the business expands further 11 EI A Design This section will explain the design phase of the Carpet World system The first part of the design phase involves producing a detailed entity relationship diagram along with how it was devised from the user requirements in the Analysis section of this report The techniques for converting the entity relationship diagram into a relational database schema including information on database constraints functional dependencies and the process of normalisation will then be discussed The final part of the Design section will detail the design of the system s graphical user interface 3 1 Entity Relationship Diagram An Entity Relationship E R Diagram helps us to explain diagrammatically the overall high level structure of a database as concluded by Silberschatz 1997 Elmasri amp Navathe 1999 state an E R diagram can be used as a reference to ensure all users requirements are met and that no conflicting requirements occur As the model does not include technical details it was also presented to Carpet World to demonstrate how the system would be implemented The graphical notation used in the E R diagram Figure 3 1 is based on the representation that Rock Evans 1989 favours and shows the logical structure of the Carpet World database Customer EE Sales Order i Sales Order History Supplier Purchase Order Hist
39. a piece of carpet we multiply the length by the width by the price and then divide it by nine This is to convert it into square yards Boxes of wood and other items are calculated by simply multiplying the quantity by the price CO What information do you record for a product PO We record the product number a description of the product the product type colour if applicable length width cost price and sale price Carpet comes in standard widths which I ll have to find out for you but the length can vary CO What about stock items PO The length of the item is recorded along with the product number The width is the same as that of the original product so this does not need to be recorded CO Moving onto purchase orders how do you order products PO We raise a purchase order for a supplier The order has a list of products and the amount required This is sent to the supplier who despatch the goods have a copy of a purchase order that you can take away with you CO What information do you hold on a supplier PO We store the company name address telephone and fax number There is sometimes a contact at the supplier who we deal with but this isn t always the case The supplier also has a unique code so for example MC Distribution would have the code MCD CO So the purchase order has a date a supplier and a list of products that are required How do you know whether the order has been sent or paid and what produc
40. aa aa aE ESEE 15 SLOCK inci 17 SEET 17 Individual Stock It MS nono nononononnnnnnnnnnnnnnononnnnnonnnnnnnnnnnnnnnnnnnnnnnnnrnnnnnnos 18 A s 2geuvedsaaehcevetanasdanvescaetndtsasedsagadsanasexccsducceguees cas tahiacadtaadSasaedexinesaaedazeasesiancaceeetaxas 21 Supplier E 21 Individual Gupplers eisrerii esiin enin eane Eea era a E iire i ie ra ieai EEEa Enare EUr Ei eniiS 22 Purchase Odes sra iii a a 24 Purchase Order Lisi 24 Individual Purchase Orders oooooooooccooocconcnonononcnoncnnnnnnonnnononnnnnonnnonnnnnnnnnnnnnnnnnnnnonnnonnnnnnns 25 GUSTOMELIS ee 29 Customer D EE 29 Individual Customers oooooooooccooocconcnonnnnnncnonononononnnnnnnnnnonnnnnnnnnnnnnnnnnnnn nn nn nn nnnnnnnnnnnnnnrnnnnnnnns 30 Sales Orders miii A asis 32 sale Order Ult doin diia Eed 32 Individual Sales Orders ono nono nono nnnnnnnnnnnnnnnnnnnnnnnnns 33 Reports MON Uiisicdivsndsncccchocasinctsovelercedascaccacuecetedesssa vcdelcaveldavestuadicescesteaetdes aeustatiionvataidedts 37 Maintenance Men 38 9 Introduction Welcome to the Carpet World System manual Contained within these pages is all the information you will require to use the database successfully To access help while running the system press the F1 key Pressing F1 will bring up information relevant to the form that you are currently accessing If you cannot find the required information you can also use the index facility or select a page from the contents list EI Minimum Requirements T
41. an be processed in a variety of quantities including yards feet and metres Sage is proving more of a hindrance than a benefit The existing paper based system has proved adequate for a number of years but something which is more sophisticated and tailored specifically towards Carpet World is now required The new application is to be implemented at the two existing locations and possibly the third 1 2 Aims and Objectives The aim of this project is to evaluate possible solutions for a multi user stock sales and purchasing system for Carpet World On selection of the most appropriate solution a software development methodology will be followed to design implement and deploy the final system at Carpet World s premises A user manual will also be provided for the employees of Carpet World to refer to The project aim will be achieved by completing the following objectives 1 Document user and system requirements by examining existing solution and interviewing Carpet World staff 2 Evaluate alternative solutions against the system requirements from 1 and choose the most suitable method 3 Design implement and test the most appropriate solution against a set of acceptance tests 4 Deploy the system at one of the three Carpet World locations with accompanying user manual The system will be installed on two computers using a client server architecture 5 Install the system at the two remaining Carpet World locations and provide sta
42. are dependent only on the primary key and not on other non key values thus removing transitive dependencies i e using the functional dependencies X Y and Y gt Z we can determine that X transitively determines Y 16 o e Boyce Codd Normal Form A relation schema R is in BCNF when nontrivial functional dependency X A holds in the relation R and the attribute A is not in X therefore X is a superkey of R The only difference between BCNF and 3NF is that 3NF allows A to be prime i e it is a member of some candidate key of R Ideally relational database design should strive to achieve BCNF or 3NF for every relation schema according to Elmasri amp Navathe 1999 Using the normalisation techniques described by Mott 2000 and the entities from the E R diagram the following table schemas were devised for the Carpet World database Attributes in bold indicate the primary key underlined attributes represent foreign keys from related tables tblCategory CategoryID Category Type tblProduct ProductNo Description CategoryID Colour Length Width CostPrice Saleprice tblStock StockNo ProductNo Length tblStockAdjust AdjustNo StockNo Date OldLength Reason tblCustomer CustomerNo Title Initial Surname Address1 Address2 Address3 PostCode Phone1 Phone2 Email tblSupplier SupplierlD Name Contact Address1 Address2 Address3 PostCode Phone Fax
43. arpet World Stock Information and Match to Any Part of Field To delete a product click on the button marked Delete This will delete the currently selected stock item but only if there are no items in stock with this product number 19 9 To find out whether an item of stock is on a sales order or which purchase orders the stock item was delivered on you can click on the labelled tabs towards the top of the screen The button marked Print on each of these screens will enable you to generate hard copies of the information being displayed To return to the stock list click on the Exit button Click on the Exit button on the stock list screen to return to the main menu 20 Suppliers Suppliers are those companies whom you will be purchasing products from i e raising purchase orders against Suppliers must be added to the system as purchase orders are the only way in which stock should be added to the system although this can be overridden Supplier List You can access the supplier section of the Carpet World System by clicking on the Suppliers button from the main menu The screen you be presented with is shown below and displays the suppliers currently stored on the system When you first start the Carpet World program there will be no suppliers listed however as the number of suppliers increases the screen will fill up To sort the list on a particular attribute double click the appropriate header Figure 20 Find E
44. as a valuable aid in completing this project The main reason for choosing an iterative methodology was support for requirements changes and the size of project it is aimed at Using an alternative methodology such as the waterfall method would not have allowed the requirements changes identified in section 6 2 to be implemented Previously Carpet World has never been involved with developing a piece of software tailored for their business and the likelihood of requirements changes was a major factor in using an iterative approach This proved well founded when some user requirements changed almost immediately once Carpet World had seen the first build of the software 6 4 Implementation and Testing The implementation phase of this project took considerably longer than was anticipated due to the complexity of the software Although the system could be mistaken for a simple stock control application owing to the way Carpet World operates and the types of products that are sold in the carpet industry a great deal of customisation was required to ensure the system worked correctly The underlying VBA code in the forms and reports of the Carpet World system allowed powerful procedures to be run It may have been more useful to write VBA module objects that can be used by multiple forms within the database but lack of experience in this and 44 CI Li CAMS variation in how the forms behave deemed it inappropriate The benefit from c
45. at new users are guided to the next information point as outlined by Matravers 1999 There will be a careful balance between how much information is displayed and the relevance of it to the user Screens will have a consistent layout so that controls are in the same place across the whole of the system to reduce the learning curve From the top of the screen you will be able to create and move between records the middle of the screen will contain information on each record and the bottom of the screen will contain buttons for printing and saving Exit buttons will be located at the bottom right hand side of the screen away from the other controls Similar controls will be grouped together as recommended by Dix et al 1998 e The ability to tab between controls on individual screens allowing for more confident users to speed up the data entry process 21 CI Black on a white background will form the primary colours of the Carpet World system Using the Carpet World corporate colours of blue and yellow will enable the system to draw the user s attention to important areas of the screen and help to break up the monotony of black and white Although blue can be difficult to perceive by many individuals as documented by Ruddle 2000 and blue yellow colour blindness does exist this should not prove to be a problem There is no commonly available blue yellow test that can be performed so a very dark blue and a bright yellow has been ch
46. at we use to record the stock the customer wishes to purchase When everything has been written down we use the same number that is on the notepaper as the order number CO Are sales orders the same as purchase orders in that you can only order one item for each product number PO No A customer may wish to carpet their whole house in the same carpet so we need to be able to record the multiple items for the same product CO Do sales orders go through the same stages as a purchase order PO They are slightly different Unlike purchase orders which are almost certainly going to be ordered sales orders may never be confirmed Also we have an extra stage where the order is fitted So sales orders go Unconfirmed Confirmed Ready to Fit Fitted and Paid CO Can they be part delivered like purchase orders PO No we only fit the carpet or deliver the products if we have all the items CO Does a sales order just have an order number date and customer then PO We also record when the order was confirmed CO What information do you record for a customer PO When the order is taken we record their name address and telephone number If they already have an account with us we use their existing information 57 CO think this should be enough for the time being unless there is anything else should know PO No can t think of anything else at the moment CO Thank you for your time
47. be entered B 6 Category Wood To ensure that colour and length Colour and Length Units text As expected can be added for the product boxes are enabled allowing data to be entered 106 B 7 Category Other To ensure that colour and length Colour and Length Units text As expected can be added for the product boxes are enabled allowing data to be entered B 8 Category Roll To ensure that colour length and Colour Length Units and Width As expected width can be added for the text boxes are enabled allowing product data to be entered B 9 Click save button To ensure that all required Message box is displayed asking As expected attributes of the product need to the user to complete necessary be completed before it can be data saved B 11 Colour Speckled Slate To ensure that a product colour Colour is accepted As expected can be added B 12 Length 94 00 To ensure that only positive Message box is displayed asking As expected lengths can be entered the user to correct the data B 13 Length 94 00 To ensure that a product length Length is accepted As expected can be added B 14 Width 45 00 To ensure that only lengths from Message box is displayed asking As expected the drop down box can be the user to choose a value from selelected the select box B 15 Width 13 125 To ensure that a product width can Width
48. been requested For at least 2 one sales order It may be possible to complete the order now the item is in stock Figure 4 8 Product delivered that has been requested 29 EI AMS As with sales orders a purchase order is not complete until the products have been paid for A history is also kept regarding purchase orders allowing the user to track the order throughout its lifetime 4 3 Reports A report in Microsoft Access is a method to present information from tables in the database in a printable format The Carpet World database incorporates over thirty reports including reports for customers purchase orders and sales invoices There are specific reports for each form in the system so from the customer screen we can print off customer information details of their sales orders and a list of stock they have purchased Reports as with forms also support VBA code allowing customisation of reports before they are printed The example below shows how quantities can be hidden using VBA code depending on the category type of the product Figure 4 9 Customer Mr C O Hara Order No 3 Date Ordered 09 03 2003 Status Ordered Qty Product Code Product Description Colour Unit Price Net Price VAT Amount 8 00 0006 Uniclic Laminate Flooring Mirabow 10 00 80 00 14 00 4 00 x 13 13 0008 Harris Collection Oak 7 00 40 83 7 15 Total Net Amount 120 83 Total VAT Amount 21 15 Order Total 141 98 Figure 4 9 Report with q
49. bles to record this so that cash flow reports could be produced 47 CI Li CAN P e The system allows any user to overwrite the cost of an item on an order Carpet World stated that they would like to be able to restrict this to only authorised users i e management e It was noted that occasionally users misspell customer addresses Software is available to retrieve the correct address using a post code which could be added to the customer information and supplier information screens to ease data entry e The installation process is relatively complex due to linked tables and the use of user level security It would be better if an installation wizard could be created to install shortcuts linked tables and file locations depending on whether the software is being deployed on a client or server e The system is currently implemented for a resolution of 800 by 600 As some computers are set to a resolution of 1024 by 768 at Carpet World the system would benefit by having the user interface resize automatically depending on the screen size 7 3 Project Outcome This project began with interviews with Carpet World employees and an examination of their existing stock control system This allowed extensive user requirements to be defined covering all aspects of the system Thorough database design followed the systems analysis before implementation of the system was undertaken User requirements did change during creation of the implem
50. ck request is created and the product has to be added to a purchase order An extract of VBA code for the sales order screen can be found in Appendix H From the sales order screen a cutting sheet can be printed allowing Carpet World staff to prepare the stock to deliver to the customer After the order has been delivered the status of the sales order is changed to Delivered An invoice can then be issued to the customer from the information in the sales order items table When payment has been received the order status is changed to Paid and the sales order is complete An order history is recorded for each sales order allowing the user to find out the date that the order was confirmed when stock was adjusted and how long the order took to complete 28 The purchase order screen is very similar in behaviour to the sales order form but there are some minor differences Unlike a sales order there are only three possible statuses for a purchase order to take Ordered Delivered and Paid There are fewer stages as it is assumed that if a purchase order has been created it is almost certainly going to be sent to the assigned supplier thus it is Ordered Unlike a sales order where the same product can be ordered multiple times the user may only order a product once per order This is outlined in section 3 5 and takes into account the way that stock is handled at Carpet World Products where the category type
51. cumentation are present for the Carpet World system The first of these is a paper based document which will be distributed as part of the software package along with a PDF version allowing additional copies to be printed This can be seen in Appendix J The second method of documentation is an online version of the manual created using Microsoft HTML Help Workshop to produce a single help file which can be stored on a user s computer 35 CI EI i Microsoft HTML Help as defined by Microsoft 2003 is the standard help system for the Windows platform Using the HTML Help Workshop allows a combination of HTML pages images and java applets to be compiled into a single help file using hyperlinks to navigate through the file This file can then be read by any Windows system using underlying components of Microsoft Internet Explorer to view the online manual Figure 4 11 oO E EE Hide Back Print Options Content i Contents Index Introduction 2 Minimum Requirements 2 Installation 2 Getting Started A Using the Carpet World System Welcome to the Carpet World System help pages Contained within these pages is all the information you will require to use the database successfully 2 Main Menu 2 Products To access help press the F1 2 Categories key while running the Carpet 2 Stock World program Pressing F1 will bring up 2 Suppliers informa
52. d at creating the application as a multi user system through a client server architecture The system uses a combination of linked tables and record locking to make this possible allowing several Carpet World employees to access the system simultaneously Reports can be added to the system by specific users who have been authorised to modify the reports menu Unfortunately no specific documentation has been provided for Carpet World for accomplishing this due to time constraints However they do have access to existing reports on the system which will provide them with the necessary information for retrieving the correct information from the database The system is fully compatible with the software that Carpet World currently use Records can be exported from the database into Microsoft Excel spreadsheets and reports can be saved as Microsoft Word documents It is also possible to combine the Carpet World system with Microsoft Outlook but this was not implemented Further information on this will be provided in the final chapter On first impressions the user requirements documented through interviews with the Carpet World staff seemed to be sufficient The only area where there was a problem was external outputs that they required from the system e the reports that should be produced The level of detail provided by these interviews enabled the system to be designed quickly once the analysis had been completed 42 Unfortunately
53. dit Print RUS 0987 654 321 ESUK Carpet Supplier UK ur Doe Jo SV BD Fig 20 Supplier List 21 QE When the number of records becomes excessively large it may become difficult to find the correct supplier You can overcome this by clicking on the Find button at the top of the screen A box will appear allowing you to enter words to search for You may need to change the Look In part of the search box to Carpet World Suppliers and Match to Any Part of Field Figure 21 Find Find What crus e Find Next Cancel Look In Carpet World Suppliers y Match Any Part of Field y Search all J Match Case JW Search Fields 4s Formatted Fig 21 Find Supplier The buttons at the top can be used to add and edit existing suppliers The Print button enables you to print the list of suppliers that are currently on the system Individual Suppliers When you click the Add or Edit button on the supplier list screen you will be presented with the screen shown below Figure 22 Kegel Supplier Information Find CSUK X Details orders Stock Supplied contact csuk co uk EE New Order Undo save Delete _ ex Fig 22 Supplier Information From here you can add suppliers to the Carpet World system enabling purchase orders to be processed 22 CI When adding suppliers they must be given certain attributes Each supplier must have the following
54. e entity relationship diagram located in Appendix E can be found in Appendix G 4 2 Forms Microsoft Access provides a graphical interface to the data stored in the tables through the use of forms These forms can access specific data by setting the form source to a table within the database SQL queries can be used as a record source allowing multiple tables to serve as the data for the form Forms can also be used as switchboards allowing a user to open other forms and reports in the database Throughout this section form and screen will be used interchangeably The Carpet World database consists of almost fifty individual forms providing a plethora of features for Carpet World Every form was implemented whilst following the usability guidelines explained by Nielsen 1993 and covered in detail in section 3 6 There are three menu forms in the Carpet World system that are not directly linked to any underlying data in the database The first of these is the main menu which is displayed when the Carpet World system is opened Figure 4 2 This allows the user to navigate to other parts of the system including areas relating to products customers and suppliers The second of these switchboard forms is the reports menu which lists the printable reports for Carpet World As a user requirement was specified to allow new reports to be added after the system was completed it is envisaged that this form may be split into
55. e read but they can not be edited deleted or added e Edited record The page of records is locked as soon as any user starts editing any field in the record and stays locked until they move to another record Consequently only one user can edit the record at a time This is sometimes referred to as pessimistic locking The forms and queries in the Carpet World system all support pessimistic record locking Optimistic locking is entirely unsuitable as it is likely to result in confusion and record changes being lost Consistency of related data may also be compromised as changes made by one user could be overwritten by a second user if they are both accessing the same record simultaneously Using an all records locking strategy would make it difficult to the system to be used on multiple computers as records could be locked for long periods of time As the Carpet World system was implemented as a single user and multiple user application testing was required for both versions Further information on this can be found in section 5 3 32 4 6 Security Security was not cited by Carpet World as a fundamental aspect of the Carpet World system However any system which is critical to the running of a business as will be the case with the Carpet World system needs to have a reasonable level of security Without this as Sommerville 2001 states the availability reliability and safety of the system may be compromised The comp
56. ed to be recorded hence the need for the purchase order items and sales order items table Referential integrity has been enforced to ensure that data is accurate and consistent This is an available option in Microsoft Access along with support for cascading updating and deletion of records which means that any changes made in one table will also be changed in the related table for the record that has been altered 19 CI Using normalisation and identifying the relational database constraints enables a data dictionary to be defined This is where the database records information about the data known as metadata rather than the data itself as defined by Mott amp Roberts 1999 and Elmasri amp Navathe 1999 The data dictionary typically stores the names of tables attributes of tables and attribute data types Mott amp Roberts 1999 also identify the importance of providing text descriptions of database tables and attributes which act as a basis for database documentation for the database designer allowing database changes to be noted The data dictionary for the Carpet World system can be found in Appendix F 3 6 User Interface Design User interface design is a very important aspect of the design process for the Carpet World system The database will have a graphical user interface GUI enabling the user to interact with the database without opening individual tables to insert and retrieve information Using a set of guidelin
57. emented As changes were requested throughout the implementation of the system including the ability to add new product categories the choice of an iterative methodology as outlined in section 2 2 was beneficial Once the system was completed three employees at Carpet World along with Mr Hambidge and Mr O Hara were asked to run through a series of common procedures This included creating themselves as customers adding products and creating sales orders All of the Carpet World staff successfully managed to complete the tasks but there was some variation in the amount of time it took to complete them As only Mr Hambidge and Mr O Hara had the seen the system beforehand and no manual had been provided to assist them the members of staff took a little time to find locations of buttons and work out where they were to input data Details on the acceptance tests can be found in Appendix L 40 EI AMS Overall acceptance testing was a success with encouraging feedback from the staff who were participating in the exercise Positive comments on the system mainly related to the ease of navigation consistency of layout and logical order of tasks when processing orders The manager of Carpet World Mr Hambidge 2003 was happy with system and believed it would prove a valuable part of Carpet World s business Mr O Hara also had kind words for the delivered system highlighting the ease of functionality and likelihood of members of sta
58. ent on the system 21 The system shall record supplier IDs which can be up to 8 characters in length Constraints 22 The system shall record product numbers that are 8 characters in length 23 The system shall record stock numbers as integer values 24 Product categories can only be predetermined values Roll Remnant Cut and Other 25 Order status can only be predetermined values Hold Ordered Fit Delivered Awaiting Payment and Paid 26 The system shall not allow stock to be sold to customers that exceeds what is available Non Functional User Requirements Maintenance 27 The system shall be maintainable with minimal interference and system repair shall be accomplished easily by an external source Usability 28 The system shall not be usable other than by authorised users through the use of password protection 29 The system shall only allow managers and supervisors to access the maintenance side of the system 30 The system shall be user friendly throughout in terms of the syntax layout and colour schemes used thus catering for novice users 2 1 2 User Requirements Enhancements Functional User Requirements Data Stored 1 The system shall record product manufacturer information System Features 2 The system shall work on at least two computers simultaneously e Record locking for specific records during append update and delete procedures
59. entation but using an iterative methodology allowed these requirements to be incorporated The final stages of the project involved evaluating the system against a set of acceptance tests deploying the software at Carpet World and producing a user manual In conclusion the project has been a success in that all objectives were met and Carpet World have a fully operational multi user stock sales and purchasing system Although there are areas where the system could be improved all user requirements have been fulfilled and a number of the enhancements have also been satisfied The objectives may have been slightly ambitious for the size of system to be developed but effective management enabled the project to be completed on time 48 El ANS Bibliography and References Aaby A 2000 Functional Dependency URL http cs wwc edu aabyan 415 FunDep html 04 December 2002 Avison D E Fitzgerald G 1995 Information Systems Development Methodologies Techniques and Tools McGraw Hill Avison D Shab H 1997 The Information Systems Development Lifecycle A First Course in Information Systems McGraw Hill Chipman M Baron A Bell C Kaplan M Litwin P Torrico R 2000 Frequently Asked Questions About Microsoft Access Security for Microsoft Access versions 2 0 through 2000 Microsoft Clearform Software 2001 Microsoft Access Database Design URL http www clearform com microsoft_access ht
60. er sales orders will also behave in a similar way to purchase orders When a sales order is being raised the customer orders products based on their stock number It is important that the product number is selected first by the user so that the number of stock items to display is as small as possible minimising the risk of user error Therefore the product number needs to be added to this table allowing it to be selected individually for each item on the sales order The customer can also order more than one cut of carpet from the same roll which is why the tblSalesOrderltems table differs from the tblPurchaseOrderltems table where only one of each product type can be ordered More detailed information on the behaviour of the purchase and sales orders will be given in the Implementation chapter of this report Elmasri 8 Navathe 1999 say that normalisation is sometimes not required to the highest possible level Database designers may wish to use a lower normalisation status for performance reasons which is the case with the Carpet World database This process is known as denormalisation After the normalisation process we are able to draw up a more accurate E R diagram which can be found in Appendix E This differs somewhat from the original low level E R diagram due to the level of detail required and the way in which information is stored on sales and purchase orders Each order has a separate list of products or stock items that ne
61. es as devised by Nielsen 1993 ensures an appropriate user interface is developed which will cater for the various users The guidelines with accompanying text taken from Matravers 1999 are as follows e Simple and natural dialogue Reduce text to a minimum keeping the screen free from excessive information Cluster similar items and highlight importance with bold type and capitals e Speak the user s language Use a level of vocabulary that the user will understand e Minimise user memory load Provide guidance on for textual inputs such as dates and product codes and base interaction on a small number of rules that appear everywhere e Consistency Keep the layout including position and formatting of the individual screens consistent with one another e Feedback Inform the user of what is happening and an idea of how long it will take e Clearly marked exits All dialogues should have clearly visible escape routes e Shortcuts Anticipate common commands and use shortcut keys wherever possible Benefits expert users greatly e Good error messages Use clear messages that make sense to the user Guide the user as much as possible into rectifying the problem e Prevent errors Many situations where errors may arise can be programmed out e Help This is a last resort for many users and therefore needs to be very efficient allowing the user to quickly find what they are looking for 20 CI These guidelines provide a ba
62. esOrder frmSalesOrderSubform cboProductNo Value dblLength CDbl Forms frmSalesOrder frm alesOrderSubform txtLength Value Need to set this otherwise get Null error message Forms frmSalesOrder frm alesOrderSubform chO0StockNo SetFocus dblStockLength CDbl Forms frmSalesOrder frmSalesOrderSubform chOStockNo Column 1l strProductNo CStr intProductNo strCategory Forms frmSalesOrder frmSalesOrderSubform txtType Value Format product number for output Do While Len strProductNo lt 4 strProductNo 0 strProductNo Loop If dblLength lt 0 Then strMsg Please check the quantity for Product strProductNo amp strNL amp This should be a positive number greater than 0 strTitle Confirm Sales Order intStyle vb0K0nly vbQuestion MsgBox strMsg intStyle strTitle GoTo Exit_btnConfirm Click End If Does the length requested exceed what is in stock If dbllength gt dbl3tockLength Then If unit category can split into multiple lines using another stock no If strCategory Unit Then dblIn tock DSum Length tblStock ProductNo amp intProductNo If dbllength lt dblInStock Then strMsg The amount you have entered for Product amp strProductNo e _ is amp strNL more than is in stock on that Stock Number amp _ strNL amp strNL amp However there is enough in stock if you _ strNL created another line using a different stock number Else
63. ese figures are based on cost price sale price and information recorded on purchase and sales orders Once you have filled in all the details for a new product it is necessary to save the product To do this you need to click on the button labelled Save If you try to move to a different product or wish to return to the product list you will be presented with a pop up box which asks you if you would like to save the product Click on Yes or No depending on what you would like to do The Find button and drop down box in the top right of the screen allow you to quickly jump to a specific product You may need to change the Look In part of the search box after clicking on Find to Carpet World Product Information and the Match to Any Part of Field To delete a product click on the button marked Delete This will delete the currently selected product but only if there are no items in stock with this product number and there are no purchase orders with the associated product To find out whether a product is in stock or is on a purchase order or sales order you can click on the labelled tabs towards the top of the screen The button marked Print on each of these screens will enable you to generate hard copies of the information being displayed To return to the product list click on the Exit button Click on the Exit button on the product list screen to return to the main menu 14 Categories Products have to be in a specific category and are
64. ext Unfortunately Microsoft Access does not dynamically resize forms depending on the monitor resolution The system will have to be implemented on the basis of an 800 by 600 screen resolution as this is how the majority of Carpet World computers are configured It will however also need to support the larger 1024 by 768 size screen resolution 22 y 4 Implementation The fourth section of this report covers the implementation stage of the Carpet World system The start of the implementation phase involves the creation of database tables before other objects such as forms and reports can be added Backup and maintenance of the system is then described before the two implementation strategies are detailed The importance of database security is then covered The penultimate stage of the implementation section encompasses system documentation This section ends with an explanation of the integration and deployment process at Carpet World 4 1 Tables Database tables provide the basis for storing a collection of data about specific topics Using the entity relationship diagram in section 3 1 and the data dictionary from Appendix F allow tables to be created for the Carpet World system within Microsoft Access Tables are created using the Design View of Microsoft Access Table fields are defined with associated data types input masks and default values for new records Once the tables have been created they can be related t
65. ff in being able to use it Although many enhancements were suggested only some could be implemented Minor changes such as re labelling specific text boxes and adding a print button to two forms were easy to implement but major modifications such as allowing only certain users to modify the price of products could not be implemented Enhancements of this magnitude were recorded and are covered in the final chapter of this report Had the system not been sent to Carpet World throughout the duration of the project the system could have failed the acceptance tests The importance of reporting back to Carpet World on a regular basis cannot be overlooked 41 6 Evaluation The penultimate chapter covers the evaluation of this project It begins with an evaluation of the user requirements including which requirements were met and whether any were missed The following sections evaluate the choice of technology and methodology for this project This is followed by an evaluation of the system implementation and testing methods The chapter ends by returning to the objectives of the project and evaluating whether they were appropriate 6 1 User Requirements The user requirements for the Carpet World system are documented in the Analysis chapter of this report The system successfully meets all twenty nine essential requirements as well as a number of the user requirement enhancements One of the enhanced user requirements was aime
66. ff with technical documentation for customisation and further deployment The conclusion of the fourth objective will result in the minimum requirements for the project being achieved The completion of the fifth goal will exceed the minimum requirements and provide Carpet World with a system ideal for the business 1 3 Deliverables From the objectives in section 1 2 a number of deliverables can be identified that will result in completion of the project The deliverables that are defined are part of a structured methodology to software engineering Identifying deliverables so early in the project enables the project to be monitored throughout e Multi user Database System e User Manual e Project Report 1 4 Project Schedule Due to the nature and timescale of this project it is necessary to manage the project effectively As objectives and deliverables have been documented the project can be broken down into a set of manageable tasks allowing the project to be completed in a professional manner The tasks are scheduled in a logical way to reflect the stages of a structured methodology analysis is before design which precedes implementation The Gantt chart found in Appendix C demonstrates the estimated timescale for completion As an iterative approach is to be followed the Gantt chart also highlights the timeframe of each iteration Further information on the methodology to be used can be found in section 2 2 1 5 Repo
67. gth Width Colour Purchase Price Sale Price Stock Stock No Product No Length Customer Customer No Title Name Address Telephone No Email Address Supplier Supplier ID Name Contact Address Telephone No Fax No Email Address Sales Order Order No Date Status Customer No Stock No Length Sale Price Purchase Order Date Ordered Date Delivered Date Confirmed Supplier ID Status Product No Length Cost Price Sales Order History History No Date Order No Description Purchase Order History History No Date Order No Description These nine key entities will form the basis for the Carpet World database and allow a relational database schema to be produced by identifying constraints functional dependencies and following the normalisation process 3 3 Database Constraints Constraints within a relational database are present to restrict the data that can be entered into the system These include as identified by Elmasri amp Navathe 1999 domain constraints key constraints entity integrity referential integrity and functional dependencies These constraints are specified at the data level as published by Mott 2000 e Domain constraints These specify what data type each attribute must be and the values it is allowed to take For example an attribute may be restricted to integer values a fixed length string or currency data type It is also possible to confine it to a certain range of explicitly listed
68. h is scheduled to open in summer 2003 There are a number of staff employed by Carpet World but they do not have any IT specialists on site The computers at Carpet World were set up by the company accountant and are all standalone except the two located at Aston Road which are connected via a crossover cable When files are required to be transferred between the Aston Road and Weddington Road premises a remote access application is used over dial up modems There are currently no plans for a secure always on virtual network to be implemented by Carpet World between the three premises but this may change when the Hinckley branch is opened As part of the agreement in completing this project for Carpet World support will be provided in networking the remaining computers The current stock control system is almost entirely paper based All orders be it a sales order or purchase order are stored in filing cabinets Information on stock is added to a simple Microsoft Works database located at the head office Accounting information is processed using Sage Line 50 an accounts and finance package Carpet estimates are calculated on one of the two PCs at Aston Road The key problem that has been identified is that it is getting more difficult to trace stock sales orders and purchase orders due to the amount of paperwork being held Unfortunately Sage Line 50 is also causing problems as it can only deal in standard units of measure As stock c
69. has to be manually added This was specified as a user requirement in section 2 2 1 and is primarily because orders are taken on note paper before being finalised The user then adds items to the order through a sales order items subform This form s data source is the sales order items table and is related to the sales order table by the sales order number The form allows multiple rows to be entered for each item on the order This may include boxes of wood remnants or cuts of carpet that are specially ordered If a request for an item is made that is not already in the system the user adds this to the system using the product form before it is added to the sales order When all items on the order have been recorded and the customer has agreed on the transaction the order is confirmed The status of the order is changed and the user is notified that any changes made to the sales order items may result in errors Figure 4 6 Sales Order Information x i The status of this order is ORDERED Changing items and quantities on this order may cause errors Figure 4 6 Message box after confirmation When the order is confirmed the items on the order are checked against the quantity in stock If the item is in stock the quantities are adjusted accordingly If there is not enough in stock for the quantity ordered a check is made to see if an alternative stock number can be used to satisfy the order If this is not possible a sto
70. he project have been achieved Although parts of the fifth objective have been completed including technical documentation for further deployment and installation at all three carpet World premises no information was provided on adding further reports or customising the system menus lt was important that the objectives chosen for this project were appropriate for the system being developed and the timescale for completion The system was more complex than anticipated but ongoing communication with Carpet World and careful planning allowed the project to proceed at a steady pace The availability of Carpet World employees also enabled any problems to be resolved almost immediately The fifth objective which involved converting the system to a multi user application did not cause any major problems but this was mainly because the system had been implemented with this in mind from the beginning Overall the objectives for a multi user stock sales and purchasing system were realistic 46 7 Conclusion The final chapter of this report is the conclusion It is used to summarise the outcomes of the system and project as well as provide a list of areas of the system that could be improved 7 1 System Outcome The completed system is a fully functional multi user system that has been deployed at each of Carpet World s premises All user requirements from section 2 1 have been met allowing Carpet World to monitor the purchase and sale of stock
71. hen the requirements were defined however it became clear during implementation that the system needed to be protected from unauthorised users If analysis of security solutions had been done before the system was implemented time would have been saved at the end of the project Instead the Microsoft Access FAQ Chipman et al 2000 had to be consulted and alternative security methods were tested on the completed system As the Carpet World system uses a client server architecture this made it even harder to secure 45 6 5 Objectives To evaluate the success of any project it must be assessed against the original requirements The objectives set out at the start of the project were as follows 1 Document user and system requirements by examining existing solution and interviewing Carpet World staff 2 Evaluate alternative solutions against the system requirements from 1 and choose the most suitable method 3 Design implement and test the most appropriate solution against a set of acceptance tests 4 Deploy the system at one of the three Carpet World locations with accompanying user manual The system will be installed on two computers using a client server architecture 5 Install the system at the two remaining Carpet World locations and provide staff with technical documentation for customisation and further deployment The first four objectives of this project have all been met consequently the minimum requirements for t
72. idual user is recorded along with their permissions for working with tables forms queries reports and macros Microsoft 2002 testify that this is the most flexible and extensive method of securing a database e Prevent users from database replication Replicating a database results in users being able to make a copy of a shared database and fields being added Some attributes including fields with automatically generated numbers are changed during 33 EI ANG replication Only members of the administrators group within user level security can make database replications e Securing Visual Basic for Applications VBA code VBA code can be removed from the database by saving the database as an MDE file which removes all editable source code and compacts the destination database An alternative is to use a password to protect the VBA code which prevents unauthorized users from editing the code e Securing data access pages A data access page is a HTML document which contains references to data in a Microsoft Access file Access has no control over security of these pages as they can be stored anywhere on a local or remote computer Securing these files is done by using the system security of the computer where they are located The Carpet World system is protected by a combination of these strategies All database objects except reports are hidden to prevent accidental or intentional modification and deletion Reports will
73. ield y Search All e J Match Case JW Search Fields As Formatted Fig 13 Find Product The buttons at the top can be used to add and edit existing products The Print button enables you to print the list of products and their attributes that are currently on the system Individual Products When you click the Add or Edit button on the Product List screen you will be presented with the screen shown below Figure 14 Product Information Details Purchases Sales 0000000 80 120 ohawk Horizon Speckled Slate 94 01 13 125 8 Fig 14 Product Information From here you can add specific products to the Carpet World system enabling purchase orders sales orders and stock to be recorded 13 CI When adding products they must be given certain attributes Each product must have the following Product number which is automatically completed Description of the product Category selectable using the drop down box Length Units which correspond to the carpet length or quantity in a box Width selectable using the drop down box which is automatically set for certain product categories e Cost price of product e Sale price of product When completing product information attributes marked with a must be completed If the category type is Unit you must enter a whole number for the length Mark up price mark up total cost total sales and profit loss are automatically calculated Th
74. igure 17 Find Cancel Look In Carpet World Stock y Match Any Part of Field y Search All e J Match Case V Search Fields s Formatted Fig 17 Find Stock The buttons at the top can be used to add and edit existing products The Print button enables you to print the list of stock and their attributes that are currently on the system Individual Stock Items When you click the Add or Edit button on the stock list screen you will be presented with the screen shown below Figure 18 NORD Stock Information w Find Details Purchases Sales Adjustments N 0000000 ohawk Horizon Speckled Slate 12 0 Lee Ei alla Sie ength Undo Save Delete ce Fig 18 Stock Information If there are no items of stock currently on the system a message box will appear Unlike the products screen you are not able to edit any attributes of stock items The only way that 18 e items should come into stock is through purchase orders and the only way they should become out of stock is through sales orders If you need to add an item of stock without using a purchase order you may do this by clicking on the New button at the top of the screen Clicking on this will display the screen shown below When adding items of they must be given certain attributes Each stock item must have the following e Stock number e Product number e Length Units which correspond t
75. ing the length by the width by the price This type is present for categories where products are sold in whole units for example boxes of wood 15 Categories can only be deleted when there are no products in the category to be deleted This is done by clicking on the Delete button To return to the main menu click on the Exit button 16 Stock A Stock item is a product that is available to sell to customers through a sales order Stock List You can access the stock section of the Carpet World System by clicking on the Stock button from the main menu The screen you are presented with is shown below and lists the stock items currently stored on the system When you first start the Carpet World program there will be no stock listed however as the number of stock items increases the screen will fill up To sort the list on a particular attribute double click the appropriate header Figure 16 Find Edit 00000001 Mohawk Horizon Speckled Slate 00000002 Mohawk Horizon Brushwood 00000003 Mohawk Horizon Shagbark Fig 16 Stock List 17 When the number of records becomes excessively large it may become difficult to find the correct product You can overcome this by clicking on the Find button at the top of the screen A box will appear allowing you to enter words to search for You may need to change the Look In part of the search box to Carpet World Stock and Match to Any Part of Field F
76. int the order history click on the Print button on this screen or select Order History from the print menu which is displayed when the Print button is clicked on the main form The Find button and drop down box in the top right of the screen allow you to quickly jump to a specific sales order You may need to change the Look In part of the search box after clicking on Find to Carpet World Sales Order Information and the Match to Any Part of Field To delete a sales order click on the button marked Delete This will delete the currently selected sale order If items on the order have been confirmed it is not recommended as stock adjustments will have to be made To return to the sales orders list click on the Exit button Click on the Exit button on the sales orders list screen to return to the main menu 36 Reports Menu The reports menu is accessed by clicking on the Reports button on the main menu There are a number of reports available which allow data in the Carpet World system to be printed Figure 40 Reports Menu Products Product Purchases Product Sales Customers Sales Orders Individual Sales Orders Outstanding Sales Orders Unconfirmed Sales Orders Suppliers Purchase Orders Individual Purchase Orders Outstanding Purchase Orders Stock Stock Purchases Stock Sales Stock Purchases and Sales Main Menu Stock Requests Fig 40 Reports Menu
77. items to be dealt with individually and as a group allowing for part processing of orders This will support part delivery of purchase orders and also sales orders when items are not in stock 15 The system shall automatically calculate the size in square yards and square metres depending on what is required purchase order items and sales order items are dealt with in different units 16 The system shall record the history of purchase orders and sales orders for future reference This will include details of stock changes when items were delivered important if it was a part delivery and whether the order is complete 17 The system shall record a complete history of a stock item recording when it came into stock and on which sales orders the item was sold on 18 The systems shall produce specific reports as printable documents e Customer Details e Supplier Details e Product Details e Product Purchases e Product Sales e Stock Details e Stock Purchases and Sales e Stock Requests e Purchase Order Details e Outstanding Purchase Orders e Sales Order Details e Unpaid Sales Orders e Unconfirmed Sales Orders 19 The system shall allow the user to add and delete products stock suppliers customers purchase orders and sales orders to and from the system 20 The system shall allow the user to revise and update previously entered details regarding suppliers customers products stock purchase orders and sales orders pres
78. licking the Carpet World icon on the desktop Figure 8 Carpet World Fig 8 Desktop Shortcut A logon prompt will appear asking for your user name and password Both user name and password are case sensitive Figure 9 Name ws SS Password aed bel Fig 9 Logon Prompt If you mistype your user name or password you will be presented with a message box informing you of your mistake Figure 10 Microsoft Access 3 x AN Not a valid account name or password Help Fig 10 Invalid User Name Password When you have successfully opened the Carpet World system the Main Menu will be displayed You may now wish to change your password This is done by clicking Tools gt Security gt User and Group Accounts and selecting the Change Logon Password tab Enter your current password and then input the new password The next time you open the Carpet World system your password will be updated 10 Using the Carpet World System Main Menu On entering the Carpet World System the main menu is displayed From here you can access all aspects of the Carpet World System including customer supplier and stock information Figure 11 Main Menu Press F1 At Any Time For Help Products Suppliers Stock Purchase Orders Categories Customers Sales Orders To move to another part of the system click on one of the buttons on the main menu Buttons are clearly marked and are grouped
79. ll model The model above is a slightly more sophisticated version of the standard waterfall model allowing stages to be revisited Unfortunately as stated by Redmill 1997 the model is not as effective where requirements changes are likely to occur and the systems specification is incomplete As this is the first software project that Carpet World has been involved with 8 requirements changes are likely to be a frequent occurrence It is important that the project schedule should not change dramatically if a stage that was thought to be complete needs to be revisited A more advanced approach as defined by Johnson amp Bao 2001 which allows for errors inconsistencies and changes in the system implementation process is an iterative model Figure 2 2 Coding Unit Testing Integration Testing Figure 2 2 Iterative model Each large step in the model is based on a number of individual smaller steps allowing for changes to be taken into account before the next stage begins This is based on the spiral approach a model aimed at larger projects and used as part of the Structured Systems Analysis and Design Methodology SSADM It does not include unlike SSADM a feasibility study business options definition or prototyping as according to Hughes amp Cotterell 1999 they are not necessary for a project of this size Using an iterative approach allows progress reports to be sent to Carpet World enabling them
80. m 07 November 2002 Davy J Jenkins T 2000 SO12 Introduction to Programming 2 University of Leeds Dix A J Finlay J E Abowd G D Beale R 1998 Human Computer Interaction 2nd Edition Prentice Hall Elmasri R amp Navathe S 1999 Fundamentals of Database Systems 3rd Edition Addison Wesley pp 42 61 European Commission 2001 CORDIS TAP for Transport Karen URL http www cordis lu telematics tap_transport research projects karen html 29 January 2003 Heathcote P M 1996 Program Production and Testing in Computing An Active Learning Approach SC Edition Letts Hill S 1995 A Practical Introduction to the Human Computer Interface S P Publications Ltd Hughes B amp Cotterell M 1999 Selection Of An Appropriate Project Approach in Software Project Management McGraw Hill pp 59 68 Jesty P 2000 SO22 Software Project management University of Leeds Johnson O A 8 Bao C H 2001 Lecture 2 in IN22 Information Systems Development University of Leeds 49 Matravers J 1999 S113 Introduction to Human Computer Interaction University of Leeds Slides 5 5 5 8 7 1 7 12 Microsoft 2002 Microsoft Access Help Microsoft 2003 HTML Help Start Page in Microsoft Help 1 3 SDK Mott P 2000 DB21 Database Principles and Practice University of Leeds Lectures 12 14 Mott P Roberts S 1999 DB11 Introduction to Databases University of
81. mall databases Less reliable Expensive Significant learning curve No experience Expensive Little support No report customisation No MS Office integration Poor user interfaces No networking support 10 EI ANS Although not identified in the table Hughes 1999 states it is also important that system maintenance is taken into consideration when choosing a suitable technology After much consideration it was decided that the most appropriate solution for this project is Microsoft Access Although it lacks many of the features of SQL Server it is suitable for Carpet World Access is already installed on all Carpet World machines and will therefore require little time to deploy Carpet World employees have some experience so training time would also be reduced System maintenance can be undertaken onsite with no specialist required unlike SQL Server and the Business Works software Security is also a very important issue due to the database being accessible across the Carpet World network Access 2000 unlike Access 95 and 97 is very secure with a variety of security choices including database password hidden objects and user level security A single byte change in the database header will no longer result in the password being removed according to Sumin 2000 Access also includes a wizard to convert any Access database into an SQL Server database Although Carpet World is not yet ready to move to this platform it may be
82. ments First and foremost a special thanks to Carpet World for their assistance feedback and patience without which would not have been able to complete this project Thanks to my supervisor Dr J Davy for his advice and guidance And finally thanks to my parents for their support and understanding Contents 1 ntrod cfiOm PA E E dansnaieesd 1 1 1 Carpet World 1 1 2 Aims and Ke eeng 2 1 3 DeliVeTableS iii A A Age 3 1 4 A IT 3 1 5 Report Ovenlew eter nrenarenrren errenten ennenen een 3 2 PUMA OE A 4 2 1 User Requirements c cccecccccccccceeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeseeeeeeeceneeeneeeneeenesenseeneneness 4 2 1 1 Essential User Reourements nn nan rra nn rn cnnnnnnnnnn 5 2 1 2 User Requirements Enhancement cceccceceeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeees 7 2 2 Choice of Method OO lio sia 8 2 3 Choice of Ee e EE 10 3 DRS ia E 12 3 1 Entity Relationship EM eege ege EN 12 3 2 Entities and Attributes ooooooonininninininnnnnnonncccocccnnccnnnrnnr rn 14 3 3 Database Constraints neee 14 3 4 Functional Dependencies ccceccceeeceeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeees 15 3 5 Normalisation ersin iaa ae eri e EEE OE E ENEKE 16 3 6 User Intertace RT BEE 20 4 Implementation s in asearen ebe anaa 23 4 1 Tale iii is 23 4 2 SEELEN 24 4 3 GEET EE EE 30 4 4 Backup and Maintenance 31 4 5 Single and Multi user Implementation cece e eee eeeeeeeeeeeeeeeeeeeeeeeeeenaaaees 31 4 6
83. mer Product Description 1 Mr J Doe 00000006 Quick Step Uniclick 2 Mr C O Hara 00000005 Quick Step Uniclick Unconfirmed Sales Orders Customer Mr C O Hara Order No 2 Date Ordered 06 04 2003 Status Hold Qty Product Code Product Description 11 00x 13 13 0001 3 00 x 13 13 0002 Mohawk Horizon Mohawk Horizon Colour Mirabow Rustic French Oak Colour Playa Clay Birch Bark Page 1 06 04 2003 Category Cost Price Qty Price Wood 12 00 3 00 36 00 Wood 12 00 4 00 48 00 Total 84 00 Page 1 Unit Price NetPrice VAT Amount 11 00 176 46 30 88 11 00 48 13 8 42 Total Net Amount 224 58 Total VAT Amount 39 30 Order Total 263 89 Total Net 224 58 Total VAT 39 30 Total 263 89 65 Appendix J User Manual Stock Sales and Purchasing System User Manual Contents An 1 A eea aeea iee e eea aae E ea araida S e aae aa aa aeaiee ana 2 Minimum Requirements 00ooococococcooooooooccononnnononnnnnnon cnn nono n non nono nnnnnnnnnnnn nn nn nnnnrnnnnnnnn nan nnnnnnnnnnas 3 InstallatiO0N ooooooconnncnnccnnnnonnnonononononononononnnonnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnennninnnnss 4 Server Computer 4 Client COMPU tap oo 7 Getting Started with the Carpet World Gvstem cece cesseeeeeeeeeeeeeeenneeeeeeeeeeeeeeneneeneeeeeees 10 Using the Carpet World System cocinar 11 Main ET 11 PrOQUE S vicodin ai dd enc 12 Product a a a 12 Individual Products sesiunea a eian A R 13 A A a
84. missions Figure 2 Permissions for Carpet World E 2 xi Share Permissions Name ooo RIESS 8 amp Christopher O Hara HOMEWCHRIS15Chris Remove Deny Permissions Full Control Change Read Fig 2 Folder Permissions 6 Copy cw_data mdb cw mdw and cw_data ico to the directory HE Copy the Carpet World Data shortcut to the desktop Right click the shortcut and then click Properties In Target enter the following path to Microsoft Access and the path to the two files copied previously Figure 3 PATH TO MS ACCESS MSACCESS EXE PATH TO FOLDER cw_data mdb WRKGRP PATH TO FOLDER cw mdw Carpet World Data Properties ax General Shortcut Security BK Carpet World Data Target type Application Target location Office10 d d Target ES mdb WRKGRP C Carpet World cw mdw Carpet World Data V Runin separate memory space J Run as different user Start in C Shortcut key None Run Normal window Comment Find Target Change Icon Fig 3 Shortcut Properties 3 10 Verify that this has worked by double clicking the shortcut If this is successful you will be prompted for a password NE Client Computer 1 IAN EECH Open Windows Explorer and create a directory to store the database in C Carpet World is recommended Copy cw mdb cw ico and cw chm to this directory On the Tools menu click Map Network Drive In D
85. multiple forms to reduce the amount of information displayed The final switchboard form is the maintenance menu which allows the user to delete data that is no longer required from the system This includes unconfirmed sales orders and purchase orders awaiting delivery These forms have been designed so that similar buttons are grouped together This includes customers and sales orders on the main menu and the stock sales report and stock purchases report on the reports menu 24 Main Menu Press F1 At Any Time For Help Suppliers Purchase Orders Sales Orders Products Stock Categories Reports Maintenance Exit Figure 4 2 Carpet World main menu To navigate between forms and exit the Carpet World system Visual Basic for Applications VBA code is invoked when the user clicks on a button VBA is a version of Microsoft Visual Basic which allows customisation of standard Microsoft applications including Access and integration with existing systems VBA is more suitable than macro objects which are actions that can be used to automate tasks as it is flexible handles error messages gracefully and can manipulate individual records Navigating through the Carpet World system can be completed with a macro but where there are a number of actions that need to be processed as is the case with confirming sales and purchase orders it would not be possible to use a macro Using VBA code results i
86. n fewer database objects as it is saved as part of the form object that it applies to VBA code also allows SQL queries to be run within it resulting in fewer database query objects Moving from the main menu to another form other than the maintenance and reports menus presents the user with a list of records within the database for the specific category For example clicking on the supplier button on the main menu shows a list of suppliers including supplier ID name contact at company and phone number Figure 4 3 Similar screens are displayed for stock products customers sales orders and purchase orders These forms then allow the user to progress to forms which handle individual records find a record in the list or alternatively print off the list of records in the current category A button is also present to return to the main menu 25 Find Edit Print y Carpet Supplier UK Mr J Smith 0123 456 7890 MCD Distribution Mr A Man 0123 012 0123 Figure 4 3 Supplier list When the user clicks on the edit or new button they are presented with a form to edit or create a new record in that category These forms have a table as a data source so if they were to edit an individual supplier one record from the supplier table is retrieved The form displays a series of text boxes each corresponding to a field in the table For example editing a supplier would include input boxes for the supplier ID contact at company addres
87. nformation being displayed To return to the supplier list click on the Exit button Click on the Exit button on the supplier list screen to return to the main menu 23 Purchase Orders Purchase orders are used to order Products from Suppliers to bring into Stock It is important to fully understand how purchase orders are processed to make the most of the Carpet World system Purchase Order List You can access the purchase order section of the Carpet World System by clicking on the Purchase Orders button from the main menu The screen you be presented with is shown below and displays the purchase orders currently stored on the system When you first start the Carpet World program there will be no purchase orders listed however as the number of purchase orders increases the screen will fill up To sort the list on a particular attribute double click the appropriate header Figure 23 CHT Purchase Orders Find Edit Print Carpets r Us 02 04 2003 Delivere Carpet Supplier UK 02 04 2003 Ordere Carpet Supplier UK 02 04 2003 Pai Fig 23 Purchase Order List 24 When the number of records becomes excessively large it may become difficult to find the correct purchase order You can overcome this by clicking on the Find button at the top of the screen A box will appear allowing you to enter words to search for You may need to change the Look In part of the search box to Carpet World Purchase Order
88. ng it to them rarely had the chance to visit and would have benefited from building a system for a local company My project schedule was slightly optimistic but believe my time management was reasonably good Work proceeded at a steady rate and finished the project with time to spare invested a great deal of effort into the project and believe the system and report that has been produced reflects this 51 CI d CAMS The list below is a summary of the key points that will benefit students if they are undertaking a similar project to this e Keep the client informed as to how the system is progressing e Set yourself weekly goals to aim for e Start the project report as early as possible as the implementation phase will almost certainly overrun e System design is often overlooked Following a database design methodology and spending time on this reduces implementation time e Try to understand the business processes as early as possible e Plan for requirements changes throughout the whole project e Do not be afraid to stop system implementation if the requirements have been met and you are running out of time to complete the project It may difficult for the company to accept but you do have other things to do to complete the project e Take security into consideration before system implementation In conclusion believe that this project was both a personal and academic success learnt a
89. not be hidden allowing Carpet World to use these as templates for new reports This satisfies one of the user requirements from section 2 1 2 Start up options have been set so that the first form to be displayed is the main menu This will allow users to navigate quickly though the Carpet World system The database window has also been removed so only advanced users who may need to make modifications to database objects can access this The system will be used by two types of employees at Carpet World supervisors and employees Adopting the user level security approach outlined above allows permissions to be set for these users restricting the level of access they will have to the system An administrator user is to remain to allow modification of the database and objects within it User level security will also prevent database replication When the database is opened a user name and password prompt is displayed preventing unauthorised users from accessing the system Figure 4 10 Name b oo Password SCT Figure 4 10 User name and password prompt 34 The database is not saved as an MDE file as this would prevent modifications to the design of forms and reports as described by Microsoft 2002 Carpet World would be unable to add further reports to the system after completion 4 7 Documentation Documentation as defined by Matravers 1999 explains to people how they are supposed to perform tasks Sommerville 2
90. o one another using the Relationships tool Relationships are created based on those identified in the entity relationship diagram found in Appendix E Referential integrity is also set along with cascading updates and deletions of related records to ensure consistency of data Related fields can then be modified in the Design View of Microsoft Access so that the fields can use the row source from a related table as an input selection This reduces user memory load and restricts the user to entering valid data An example of this for the product table is displayed below Figure 4 1 showing how the product category can only be selected from a record already present in the category table tblProduct Table E lO xi Field Name Data Type Description AutoNumber Product No Text Product Description Text Product Category Text Product Colour Ges Number Product Length Category Number Product Width Number Product Cost Price Number Product Sale Price sl A _ Field Properties General Lookup Display Control Combo Box Row Source Type Table Query Row Source SELECT tblCategory Category FROM tblCategory Bound Column 1 Figure 4 1 Product table definition with category combo box 23 CI Li AW S The Carpet World database consists of thirteen individual tables each of which is related to at least one other table The Microsoft Access view of the Carpet World database which is almost identical to th
91. o set up the Carpet World system requires at least one PC with the following Microsoft Windows 98 2000 XP Microsoft Office 2000 Display resolution of at least 800 by 600 pixels 100 Mb Hard Disk Space 32 Mb RAM Compatible printer For installation on more than one PC each computer also requires a TCP IP compatible network card with appropriate configuration Installation To install the Carpet World system under Windows 2000 XP follow these instructions Server Computer 1 Open Windows Explorer and create a directory to store the database in C Carpet World is recommended 2 Right click on the folder and then click Sharing 3 On the Sharing tab click Share this folder Figure 1 Carpet World Properties E ax General Sharing Security You can share this folder among other users on your network To enable sharing for this folder click Share this folder C Do not share this folder Share this folder Share name Carpet World Comment User limit Maximum allowed C Allow Users To set permissions for how users access this Permissions folder over the network click Permissions ec To configure settings for Offline access to gt this shared folder click Caching Caching Cancel Apply Fig 1 Folder Properties 4 To set shared folder permissions on the shared folder click Permissions 5 Ensure that every one who will be connecting to the database has Change per
92. o the carpet length or quantity in a box When completing stock information attributes marked with a must be completed You do not need to add description category colour or length as this is automatically completed when a product number is selected If the category type is Unit you must enter a whole number for the length Once you have filled in all the details for a new stock item it is necessary to save it To do this you need to click on the button labelled Save If you try to exit you will be presented with a pop up box which asks you if you would like to save the item of stock Click on Yes or No depending on what you would like to do It may sometimes be necessary to adjust the quantity of a stock item for example a roll of carpet may have been cut incorrectly or their may have been some faulty products To adjust the quantity for the current stock item click on the Adjust button The screen shown below will appear and you can adjust the quantity of the stock item accordingly You must provide both a new length and a reason for the adjustment before confirming the change Figure 19 83 Carpet World Stock Adjustment ne 5 x Stock Adjustment 94 0 93 0 Scuff marks on one side of roll Fig 19 Stock Adjustment The Find button and drop down box in the top right of the screen allow you to quickly jump to a specific stock item You may need to change the Look In part of the search box after clicking on Find to C
93. oes not need to be backed up it is only the actual data that is important Carpet World is licensed to use an application which is responsible for handling backups of their critical files Files are saved to a secondary disk drive and can be used for restoring corrupt files Over a longer period of time files are duplicated onto CD which is then archived in a secure location The existing technique used by Carpet World is a suitable backup method for a company of this size and will be adapted to include the Carpet World database System maintenance involves ensuring that the data within the Carpet World database is valid consistent and up to date Validation and consistency is handled by a combination of VBA code referential integrity and restrictions on the data that can be entered Keeping information up to date is problematic although using related tables helps to alleviate this For example if a product name was to be changed in the product table this would also be changed on any purchase orders that the product was present on It is however primarily the responsibility of the system users to enter valid data There is a maintenance menu within the Carpet World system which allows authorised users to perform a number of actions on the data This includes deleting customers who have no sales orders removing unconfirmed sales orders and deleting products that are not in stock and are not present on any purchase or sales orders Providing su
94. onstraints will speed up data access and may improve update modify and delete performance e Reduce redundancy The use of primary foreign key constraints reduces the number of null or empty values thus reducing the size of the database and conserving disk space e Minimise anomalies Insertion modification and deletion anomalies are avoided resulting in data being consistent throughout e Enforce referential integrity Many designers do not relate tables to one another allowing for example a sales order to be present with no associated customer The use of normalisation helps to reduce many common database errors The normal form of a relation refers to the highest normal form condition it meets be it first normal form 1NF second normal form 2NF third normal form 3NF or Boyce Codd normal form BCNF The following as discussed by Elmasri amp Navathe 1999 describe the key attributes of these normal forms e First Normal Form This requires that all column values in a table are atomic and that the value of any attribute must be a single value i e no multi valued attributes e Second Normal Form Relations in 2NF are based on full functional dependency where an attribute removed from X in the functional dependency X Y results in the dependency no longer being valid It eliminates non key attributes from being functionally dependent on part of the key e Third Normal Form A relation is in 3NF when all non key fields
95. ookup PE Uniclick Quick Step o a Uniclick Quick Step iL Hide All Show All Fig 26 Stock Lookup 26 When you have added the stock number to the purchase order item click on the checkbox for that item on the right side of the table When all delivered items have been ticked click on the Delivered button This will run through each item on the order validate the quantities and stock numbers and then add a delivery date to the item Each item that was checked will now be in stock and may be sold to customers As purchase orders items may be part delivered you can check each item off as and when they arrive repeating the process outlined above When all items on the delivery have been delivered the order status will change to Delivered Figure 27 Purchase Order E x i The status of this order is DELIVERED Changing items on this order may cause errors Fig 27 Purchase Order Delivered When all items have been delivered and you have paid the supplier you can click on the Paid button This will change the status of the order to Paid and the purchase order is complete Figure 28 Purchase Order E x 4 The status of this order is PAID Changing items on this order may cause errors Fig 28 Purchase Order Paid A purchase order history is kept for each purchase order and records information on when items were delivered and when the order status was changed To view this click on the
96. ory Figure 3 1 Entity Relationship Diagram 12 CI Li AW S The attributes for the entities in Figure 1 have not been specified This has been done to ensure that the diagram is as easy to understand as possible and reduce possible confusion from displaying each entity s attributes The attributes for each of the entities in the diagram can be added based on the information supplied in the user requirements section This more detailed diagram can be found in Appendix D The diagram shows that the relationship between Customer and Sales Order is one to many This means that every Customer on the system can have more than one Sales Order but a Sales Order cannot be related to more than one Customer The dashed line indicates partial participation which means that not every Customer has to have a Sales Order The participation from Sales Order to Customer is total as Sales Orders have to have a Customer This relationship is identical to the Suppliers and Purchase Order relationship type Again Suppliers are deemed to be partial as they do not have to be associated with a Purchase Order but they can have more than one A Purchase Order has to have a Supplier resulting in a one to many relationship The relation between Sales Orders and Stock is partial from Sales Order a Sales Order usually has one item of Stock on the order but when the order is first created this is not necessarily the case This one to many relationship also
97. osen Testing of appropriate colours can be achieved by copying print screens of the system into a paint program and converting to greyscale as advised by Dix et al 1998 The system will return helpful messages to the user if there has been a problem with data that has been entered The reason for the error will be communicated to the user along with how to resolve this More detailed information will be provided in the Carpet World help facility if required by the user There are three types of data entry failure as stated by Sutcliffe 1995 each of which needs to be dealt with separately o Fatal Errors which prevent further processing such as invalid telephone numbers User must abort or correct the value o Warning Highly unlikely values that should result in process termination and a prompt for the user to re enter valid information A default value should be provided as an alternative for the user o Advisory Unlikely values which should not halt processing but should be communicated to the user They can then edit the data if necessary The system will include automation of data entry such as sales and purchase order dates and input masks so restricting data that can be entered This will reduce errors and speed up the data entry process Fonts will be chosen that are clear to read at a variety of screen resolutions Bold type will be used where appropriate but all input boxes and system feedback will be standard t
98. pport for tasks such as these assists the user in keeping the data up to date 4 5 Single and Multi user Implementation From an early stage of implementation it was important to realise that the Carpet World system was to be used on a number of computers simultaneously To begin with the Carpet World system was implemented as a single database but incorporated aspects of a full multi user system This allowed minimal implementation changes to take place once the single user system was completed 31 CI Single user systems are considerably less complex to develop than multi user systems as only one user will be changing data in the system at one time Multi user systems need to incorporate record locking to prevent users accessing the same data which could result in lost updates temporary updates and incorrect summaries as identified by Elmasri and Navathe 1999 Microsoft Access forms and queries contain an attribute which allow specific locking strategies to be used on the data The three methods supported by Microsoft Access are e No locks Multiple users can edit the same record simultaneously This is sometimes referred to as optimistic locking If two users attempt to save the record the user who saved the record second is notified and they can then replace the changes made by the first user or discard the record e All records All records in the underlying table are locked while the form is open The records can still b
99. project This project began by contacting Carpet World and conducting interviews with members of staff to determine the user requirements of the system Talking to the employees and spending time at Carpet World enabled me to see how they processed stock and purchased and sold products Nevertheless found that although these interviews were very helpful it was hard for the employees to grasp what the system was capable of doing consequently requirements changed throughout could have spent longer on the analysis phase but this would not necessarily have resulted in a complete list of user requirements While was on my industrial placement was involved with creating a number of database applications This assisted me in completing this project as was able to build the system relatively quickly but unfortunately it was more complex than realised There was a great deal of programming involved to ensure that purchase and sales orders were processed correctly and this was one of the key reasons that the implementation phase slipped had not fully grasped Carpet World s business processes when handling orders Again investing further time in analysis may have helped to alleviate some of the problems encountered The distance between myself and Carpet World also caused problems Although could send them new versions of the system and communicate via e mail and telephone it was hard to explain how the system worked without demonstrati
100. r 15 Click Select All and then OK If a window appears asking for the location to update from browse to the mapped drive where the cw_data mdb database is located If default settings have been used you may receive an error message this can be ignored 16 Click on the Products button on the main menu If the Product List screen opens installation has been successful The table below shows default settings for the Carpet World system under Windows 2000 XP Attribute Computer Setting cw_data mdb Database Location Server C Carpet World cw mdw Security File Location Server C Carpet World Carpet World Data Shortcut Location Server C Documents and Settings All Users Desktop Carpet World Data Shortcut Path Server C Program Files Microsoft Office Office MSACCESS EXE C Carpet World cw_data mdb WRKGRP C Carpet World cw mdw cw mdb Database Location Client C Carpet World Mapped Drive Letter Client Z Carpet World Carpet World Help Client C Documents and Settings All Users Desktop Shortcut Location Carpet World Help Shortcut Path Client C Carpet World cw chm Carpet World Shortcut Path Client C Program Files Microsoft Office Office MSACCESS EXE C Carpet World cw mdb WRKGRP Z cw mdw Linked Table Manager Setting Client Z cw_data mdb a Getting Started with the Carpet World System Once the system has been successfully installed it can be run by double c
101. reating VBA modules was not significant enough to implement them The graphical user interface GUI for the Carpet World system was designed by following guidelines devised by Nielsen 1993 Due to the level of IT skills at Carpet World it was important that the individual screens were consistent error messages were at an appropriate level and exit buttons were clearly marked This can be seen from the numerous screenshots in chapter 4 Although not required the inclusion of HTML Help enhances the usability of the system by allowing users to quickly find assistance on the area of the application they are accessing The guidelines were a valuable aid in implementing this system and they have resulted in an attractive easy to use piece of software Testing the Carpet World system was a very important task As stated previously the database is a very complex system and converting it into a multi user application introduced new issues to resolve The testing strategies that were adopted for the system were suitable to verify the system but due to the time available not all paths through the system may have been tested Using an iterative methodology and white box testing reduced the number of errors in the final system but even so testing every possible path is almost impossible The most difficult task of the implementation phase involved incorporating security features into the Carpet World system No security was requested by Carpet World w
102. rive select the drive letter to map to the shared resource Z is recommended In Folder type the server and directory name where the cw_data mdb and cw mdw files are located in the form servername sharename Or click Browse to locate the resource Figure 4 Map Network Drive A x Windows can help you connect to a shared network Folder and assign a drive letter to the connection so that you can apa access the folder using My Computer a conti Specify the drive letter For the connection and the folder that you want to connect to Drive z D Folder SERVER Carpet World y Browse Example server share V Reconnect at logon Connect using a different user name Create a shortcut to a Web Folder or FTP site Cancel Fig 4 Map Network Drive Copy the Carpet World shortcut to the desktop Copy the Carpet World shortcut and the Carpet World Help shortcut to the desktop Right click the Carpet World Help shortcut and then click Properties In Target enter the following path to the help file PATH TO FOLDER ON CLIENT cw chm 10 Right click the Carpet World shortcut and then click Properties 11 In Target enter the following path to Microsoft Access and the path to the cw mdb database and the cw mdw file on the server Figure 5 PATH TO MS ACCESS MSACCESS EXE PATH TO FOLDER ON CLIENTAcw mdb WRKGRP PATH TO FOLDER ON SERVER cw mdw Carpet World Data Properties 3 ax General Shortcu
103. rpet World system enabling stock to be sold 33 EI When adding sales orders they must be given certain attributes Each sales order must have the following e Unique order number e Order date automatically completed e Customer number surname is displayed e Order status automatically completed When completing sales order information attributes marked with a must be completed Once you have filled in all the details for a new sales order you may add products to the order This is done by selecting product numbers from the drop down box in the table in the middle of the form Products will only appear if they have already been added to the system When you have added a product the description category length width and cost price will be automatically completed based on the information stored on the product You must then select a stock number in the adjacent box If the product is not in stock it is left blank There may be more than one stock number for a particular product in which case you must ensure the correct stock number is selected The length for each order item will be the amount that is currently in stock It is unlikely that this will be how much the customer wishes to purchase so it must be adjusted accordingly Unlike purchase orders it is possible to order multiple products where the product and stock numbers are the same This may occur when a customer wishes to purchase multiple cuts of carpet from the same
104. rt Overview The second chapter of this report details the analysis stage of the project including choice of methodology user requirements of the system and the choice of technology Chapter three describes the design phase of the project This covers all aspects of database design such as entity relationship diagrams referential integrity and normalisation The fourth chapter explains the implementation stage of the project including topics on constructing the database tables and reports Chapter five relates to testing before an evaluation of the system is conducted The final chapter concludes the project reviewing the project achievements against the original aims 2 Analysis Chapter two of this report covers analysis The chapter explains how the user requirements were gathered for the Carpet World system and then lists the requirements that the final system must satisfy to be considered a success The choice of implementation technology is then covered before the chapter ends with information on the selection of systems development methodology 2 1 User Requirements Requirements as defined by Sommerville 2001 are used to establish exactly what the system should do User requirements are the services the system is expected to provide and the constraints under which it must operate To determine the requirements of the Carpet World software interviews were conducted with Mr P Hambidge the Carpet World Manager Mr P O Har
105. s provided on CD and available online to specific locations by the user The data for the Carpet World system is all contained in a Microsoft Access database named cw_data mdb This file along with the workgroup information file which holds data on user accounts and permissions must reside in a shared directory on the server The front end to the database is stored in a second Microsoft Access database named cw mdb This file contains the forms reports and queries necessary for the system to function correctly and is copied to the client computer Custom icons have also been created allowing the Carpet World system to be accessed directly from the desktop Figure 4 12 y ee d Carpet World Figure 4 12 Carpet World icon The user sets up the system by joining the cw mdb database to the workgroup information file on the server The user then has to create a number of linked tables pointing to the cw_data mdb database tables This process is repeated on every client computer resulting in a fully implemented multi user stock sales and purchasing system Unfortunately implementing linked tables and user level security has resulted in a complex installation procedure Detailed installation instructions with accompanying screenshots are documented in the user manual The Carpet World system is now deployed at all three Carpet World locations and is at first to run alongside the existing stock control process This is to ensure
106. s telephone number and fax number The user is free to create edit and delete records as long as valid data is entered and there are no related records that are dependent on the unedited record Further information on this will be provided in section 5 Every form uses tabs allowing a user to quickly see information related to the current record In the case of a supplier this includes tabs for purchase orders and stock that has been supplied The supplier form is shown below Figure 4 4 WOREDA Supplier Information New Find PS Details orders Stock Supplied New Order Figure 4 4 Supplier form H 26 e The customer supplier and product screens are very similar allowing any modifications in one to be implemented quickly on the others The stock form although presented to the user in the same way as these screens is fundamentally different The reason for this is that Carpet World made it clear that the only way an item of stock should be created or modified is through processing purchase and sales orders Therefore all the text fields within the stock form are locked It is feasible however that an item of stock may not have come through the usual channels hence a second stock form specifically for adding stock is present A final feature of the stock form is the ability to adjust stock quantities along with a reason for the adjustment for administrative purposes The category form is unlike the other
107. s and Match to Any Part of Field Figure 24 Find Find What UK e Find Next Cancel Look In Carpet World Purchase Orders y Match Any Part of Field y Search all J Match Case V Search Fields 4s Formatted Fig 24 Find Purchase Order The buttons at the top can be used to add and edit existing purchase orders The Print button enables you to print the list of purchase orders that are currently on the system Individual Purchase Orders When you click on the Add or Edit button on the Purchase Orders List screen you will be presented with the screen shown below Figure 26 Ad Purchase Order Information New Find Details History Carpet Supplier UK Uniclick Quick Step Wood 15 00 1 000 12 00 180 00 31 50 02 04 2003 Uniclick Quick Step Wood 15 00 1 000 12 00 180 00 31 50 Uniclick Quick Step wood 15 00 1 000 12 00 180 00 31 50 150 00 Print Delivered Paid mp petvered en Fig 25 Purchase Order Information From here you can add purchase orders to the Carpet World system enabling stock to be processed 25 When adding purchase orders they must be given certain attributes Each purchase order must have the following Unique order number Order date automatically completed Supplier I D Order status automatically completed When completing supplier information attributes marked with a must be completed Once you have filled in
108. s orders Customers must be added to the system before a sales order can be raised Customer List You can access the customer section of the Carpet World System by clicking on the Customers button from the main menu The screen you be presented with is shown below and displays the customers currently stored on the system When you first start the Carpet World program there will be no customers listed however as the number of customers increases the screen will fill up To sort the list on a particular attribute double click the appropriate header Figure 30 Find Edit O Hara 412 380 0123 456 789 Fig 30 Customer List 29 When the number of records becomes excessively large it may become difficult to find the correct customer You can overcome this by clicking on the Find button at the top of the screen A box will appear allowing you to enter words to search for You may need to change the Look In part of the search box to Carpet World Customers and Match to Any Part of Field Figure 31 Find Find What O Hara v Find Next Cancel Look In Carpet World Customers y Match Any Part of Field y Search all J Match Case JW Search Fields 4s Formatted Fig 31 Find Customer The buttons at the top can be used to add and edit existing customers The Print button enables you to print the list of customers that are currently on the system Individual Customers When you click
109. shows that Stock items do not necessarily have to be allocated to a Sales Order hence the partial participation This is again similar to the one to many relationship between Purchase Order and Product A Purchase Order can contain at least one Product but a Product does not have to be ona Sales Order The participation attributes apply in the same way as the Sales Order and Stock relationship The relationship type between Product and Stock is one to many Products may also be on the system but may not always be in stock hence the partial participation Stock always has to be associated with a Product therefore the participation is total Both Purchase Order and Sales Order have to have an Order History hence the total participation Total participation as defined by Elmasri amp Navathe 1999 is where every entity in one set must relate to an entity in another set They are both one to one relationships as an order cannot have more than one History and the History cannot be related to more than one order They have separate history tables as it is possible for a sales order and a purchase order to have the same order number 13 3 2 Entities and Attributes Using the information gathered from the user requirements and the entity relationship diagram the following entities with accompanying attributes were identified Category Category ID Category Type Product Product No Name Category Len
110. sis for designing the GUI for the Carpet World system However there are other aspects of user interface design that need to be taken into consideration when developing it As stated by Sutcliffe 1995 colour is a very effective technique for highlighting By using appropriate colours in the design of the Carpet World application it can be more aesthetically pleasing than black and white and help the user to operate the system more effectively However as Sutcliffe 1995 mentions there is a trade off between the impression made by the colour and the amount of detail that can be displayed implying it is important not to overuse colours The environment that the system will be used in must also be taken into account when choosing colour schemes In low luminance environments as is the case with many offices colours with good visibility should be chosen such as yellow They should also be displayed in high contrast where possible e g black on white Colours can also be used for providing the user with additional information for example red signifies danger Finally colour blindness is a major factor of GUI design so red green and brown red combinations are best avoided as according to Sutcliffe 1995 these are the most common Based on the guidelines devised by Nielsen 1993 and the other aspects of GUI design outlined above the Carpet World user interface will incorporate the following e A logical screen layout so th
111. t Security 4 Carpet World Target type Application Target location Office10 E AP Target Carpet World cw mdb AWRKGRP Z cw mdw Carpet World V Runin separate memory space J Run as different user Start in E EY Shortcut key Noes Bun Normal window zl Comment nnn Find Target Change Icon Fig 5 Shortcut Properties 12 Verify that this has worked by double clicking the shortcut If this is successful you will be prompted for a user name and password Figure 6 Name fer oo P d Fig 6 Logon Prompt 13 Entering the correct combination of user name and password will present you with the Carpet World Main Menu 14 Click on Tools gt Database Utilities gt Linked Table Manager Figure 7 amp Linked Table Manager Select the linked tables to be updated 7 E tblCategory 2 cw_data mdb M E tbiCustomer 2 4cw_data mdb M pE tblOrderStatus Z cw_data mdb al tbiProduct 2 cw_data mdb O E tbiPurchaseOrder 2 cw_data mdb E tbiPurchaseOrderltems Z cw_data mdb M yE tbisalesOrder Z cw_data mdb M pE thiSalesOrderHistory Z cw_data mdb T pE tblSalesOrderItems Z cw_data mdb C E tblPurchaseOrderHistory 2 1cw_data mdb M E tblStock 2 cw_data mdb 7 pE tbistockAdjust Z cw_data mdb Always prompt for new location C E tblStockRequest 2 cw_data mdb Fig 7 Linked Table Manage
112. terface GUI of the database This will ensure that the database holds valid and consistent data and will allow helpful messages to be displayed if a constraint is about to be broken by the user 3 4 Functional Dependencies According to Elmasri amp Navathe 1999 the most important concept in relational schema design is that of functional dependency Functional dependencies are denoted by X Y between two sets of attributes where attribute X uniquely determines attribute Y so for X and Y we can say that Y is functionally dependent on X An example of this similar to that of Aaby s 2000 would be a table listing employee information including National Insurance Number NI and name We can say that name is functionally dependent on NI or NI gt Name as an employee s name can be uniquely determined by their National Insurance Number The National Insurance Number is not functionally dependent on name as two employees may have the same name i e the name alone cannot determine the national insurance number 15 3 5 Normalisation Elmasri amp Navathe 1999 describe normalisation as the process of examining relational schemas based on their functional dependencies and primary keys against a set of certain conditions the normal form tests Normalising of relational schema has a number of advantages as documented by Roland 2002 e Provide Indexing The creation of clustered indexes associated with primary key c
113. the Add or Edit button on the customer list screen you will be presented with the screen shown below Figure 32 Cty Customer Information New Find Sg Details orders stock Ordered Customer so MA EES tee New Order Undo save Delete _ ee Fig 32 Customer Information Lessel From here you can add customers to the Carpet World system enabling sales orders to be processed 30 CI Li CAN P When adding customers they must be given certain attributes Each customer must have the following e Customer number automatically completed e Customer s title initial and surname e First line of the customer s address e The post code of the customer When completing customer information attributes marked with a must be completed Once you have filled in all the details for a new customer it is necessary to save the customer To do this you need to click on the button labelled Save If you try to move to a different customer or wish to return to the customer list you will be presented with a pop up box which asks you if you would like to save the customer Click on Yes or No depending on what you would like to do To generate a purchase order for the currently selected customer click on the New Order button The Find button and drop down box in the top right of the screen allow you to quickly jump to a specific customer You may need to change the Look In part of the search box after
114. the end of development as noted by Stapleton 1999 this testing method results in problems being discovered earlier than would normally be the case 39 5 3 Testing the Carpet World System The method used for verifying the Carpet World system was a combination of white box black box and acceptance testing The system as described in section 4 5 was implemented in two stages single user and multi user White box and black box testing had to be done for both implementations of the Carpet World system Although many of the tests run for the single user version of the system could be applied to the multi user version it is important to rerun all the tests for the multi user system as new errors are likely to be uncovered For white box and black box testing test plans were used to record the results Test plans as noted by Sommerville 2001 indicate the scope approach resources and the schedule for testing These test plans should be devised before any software is produced which helps to keep the project on schedule as mentioned by Stapleton 1999 Davy 4 Jenkins 2000 adopt the following format for test plans Fig 5 1 Test Data Purpose Expected Result Actual Result Figure 5 1 Test Plan Format An example of a test plan for the Carpet World system can be found in Appendix J Acceptance testing was an ongoing process with the Carpet World system allowing any changes to be quickly impl
115. tion relevant to the form that you 2 Purchase Orders are currently accessing If you cannot find the required information you can also use 2 Customers the index facility or select a page from the 2 Sales Orders 2 Reports Menu Ki Maintenance Menu contents list Figure 4 11 Carpet World HTML Help The Carpet World HTML help file can be accessed directly from the system by pressing the F1 key The form that the user is currently viewing has an attribute which is mapped to a specific page of the online manual When the user presses F1 the system examines this attribute and then opens the online manual on the page corresponding to the value of the attribute For example if the user was to press F1 on the supplier form the supplier page from the user manual is displayed The system documentation follows guidelines outlined by Hill 1995 including short paragraphs clear section headings and only one main section for each major topic Hill 1995 also states that there should be no appendix index or glossary All of these guidelines are adhered to for the paper based manual The Microsoft HTML Help Workshop provides support for an index in the file using keywords in the HTML files to identify relevant pages Using this facility can only benefit the user and was therefore deemed worthy of inclusion 36 4 8 Deployment and Integration The system is deployed by copying the database and accompanying security file
116. ts have been delivered PO Well we file the orders in different locations depending on the status of the order When items are delivered they are assigned stock items as mentioned previously When all of the items on the order have been delivered we can pay the supplier 55 PO No You often get part deliveries and it can be a couple of weeks before all items from CO Are all items on the same purchase order delivered at the same time the same order are delivered CO Does this affect what stock number they are given PO No it doesn t matter They are just assigned stock numbers when they are actually delivered CO What stages of a purchase order are there PO When the purchase order is raised we say that it is Ordered This changes to Delivered when all items have been assigned stock numbers The order is completed when we pay the supplier CO Is there anything else that need to know about purchase orders PO We never order the same product more than once per order CO Does that mean you can order a number of rolls of carpet but they must all be different products Does the same apply to other items like boxes of wood PO Yes There would be no need to order more than one roll of carpet for the same product number As for boxes of wood this is the same Unlike rolls of carpet though if we require more than the standard amount we adjust the quantities on the purchase order CO What is the process
117. uantity display variation using VBA code The reports menu which can be accessed from the main menu of the Carpet World system contains reports that cover the whole of the Carpet World system Reports that can be opened from this screen include an unconfirmed sales order report and a stock requests report Examples of these can be found in Appendix I An enhancement of the system as recorded in the user requirements in section 2 1 2 was the ability to add reports after the system is delivered For this reason the reports menu is likely to be split into a number of smaller forms as the amount of reports increases The existing reports can be used as templates as a basis for future reports although they can be created from scratch Only specific database users will be able to add new reports and modify the reports menu This is managed through user permissions and is covered in further detail in section 4 5 30 4 4 Backup and Maintenance Elmasri 8 Navathe 1999 state that the main technique used to handle catastrophic database failures such as disk crashes is to adopt a backup and recovery technique This involves making a duplicate copy of the database to a secondary location If there is a system failure the latest backup copy can be reloaded and the system can be restarted The Carpet World database uses a client server architecture where the data is stored on the server machine The front end interface is separate from the data so d
118. used to calculate the total cost of items on sales orders and purchase orders The category screen can be opened by clicking on the Category button from the main menu The category screen displayed below shows a specific category with associated type and a list of Products in the current category Figure 15 NOR UD Category Information 00000001 Mohawk Horizon Speckled Slate 94 00 13 13 8 00 00000002 Mohawk Horizon Brushwood 94 00 13 13 8 00 00000003 Mohawk Horizon Shagbark 94 00 13 13 8 00 omo core Tee en Fig 15 Category Information There are five categories in the system when it is first run Cut Length Other Unit Remnant Length Roll Length Wood Unit New categories can be added by clicking the New button at the top of the screen You may rename an existing category but it is not possible to change the type This is to ensure that orders currently in the system are not corrupted When adding a new category it is imperative that the correct category type is assigned This can either be Length or Unit and relates to how orders are calculated Selecting Length will mean that any products in this category have their price calculated by multiplying the length by the width by the price and then dividing by nine The division is necessary as it is used to handle items bought and sold in square yards such as rolls cuts and remnants The Unit type results in the item price being calculated by multiply
119. uters that the Carpet World system will be deployed on may also be connected to the internet which is likely to increase the risk of external attacks to the system Microsoft Access provides various strategies for database protection as outlined by Microsoft 2002 each of which provides a different level of security e Encryption decryption The database is compacted and is indecipherable by third party applications This has no affect on unsecured databases as users can open the database and gain full access to all objects e Show or hide objects Protects casual users from viewing database objects Least secure as it is as quoted by Microsoft 2002 relatively simple to show any hidden objects e Start up options The database window which displays all database objects can be hidden and the initial form to be displayed when the database is opened can be set e Database password A secure password can be set for accessing the database This only applies to opening the database so once the database is open all of the objects are available to the user unless other forms of security have been implemented Microsoft 2002 mention that this security strategy is often all that is required where the database is on a single computer or shared amongst a small number of users e User level security Levels of access can be specified for sensitive data and objects within the database A workgroup information file is created where each indiv
120. values e Key constraints The value of a key attribute can be used to identify uniquely each attribute in a record It ensures that no two records have the same combination of values and that the addition of any further records still results in the key being valid 14 CI Constraints on null also fall under this category so attributes to be constrained must not be empty e Entity and referential integrity The entity integrity constraint ensures that no primary key is null Referential integrity differs from entity and key constraints in that it is specified between two relations to ensure consistency It guarantees that a foreign key the primary key from a second relation must refer to an existing value in the second relation e Functional dependencies This is a constraint between two sets of attributes from the database and specifies the possible records that can form a viable relation within the database More detailed information on this is in section 3 4 Microsoft Access supports the use of all of the constraints outlined above It is possible to specify the data types of each field restricting the data that is allowed to be entered and allowing attributes to be set to not null Referential integrity of foreign keys is also supported and is set when implementing the database relationships As Access uses Visual Basic for Application VBA it will also be possible to add further constraints within the Graphical User In
121. ylD StockNo ProductNo CostPrice tblPurchaseOrderHistory HistoryNo PurchaseOrderNo Date Description tbISalesOrder SalesOrderNo DateOrdered DateConfirmed CustomerNo StatusID tbISalesOrderltems OrderltemNo SalesOrderNo ProductNo StockNo Length Width SalePrice DateConfirmed StockAdjust StockRequest StockNo ProductNo SalePrice tbISalesOrderHistory HistoryNo PurchaseOrderNo Date Description The tables were all normalised to 3NF where possible Unfortunately problems arose as described below in both the tblPurchaseOrderltems and tblSalesOrderltems tables A sacrifice was made due to the way the user is to interact with the system hence improving usability and performance 18 CI When a purchase order is created the user does not have to specify a stock number as the product is being ordered and therefore it is not known However when the product is delivered a stock number needs to be added which will in turn create a record in the tbIStock table Although we could remove the tblStock table we would lose information from the tblPurchaseOrderltems table when this item of stock was sold and the quantity was adjusted The cost price also needs to be included so that we have a record of how much the item was purchased for If the cost price was adjusted in the stock table and a join was used from the purchase order items table we would lose information on how much the product was originally purchased for Custom
122. ystem meets Carpet World s expectations Sommerville 2001 states that validation goes beyond checking conformance of the system to its specification to showing that the system does what is expected Validation of the system is completed through the use of acceptance tests as outlined in section 5 2 5 2 Methods of Testing The two major methods of software testing for software verification as outlined by Peters amp Pedrycz 2000 and explained by Jesty 2000 are as follows e Black box testing These are tests to ensure that the software performs fault free under both normal and abnormal testing It focuses on the inputs outputs and principle function of the software 38 CI e White box testing This method of testing is based on the underlining structure of the code Tests are chosen to provide coverage of all possible inputs and paths through the system This testing is known as defect testing and aims to expose deficiencies in the software before it is delivered Sommerville 2001 emphasises the point that defect tests demonstrate the presence not the absence of program faults As exhaustive software testing is impractical unit testing can be applied to verify individual components of the system which are then integrated to form a larger system This is sometimes referred to as bottom up testing where the system is built from the lowest level upwards as described by Avison amp Shab 1997 Davy amp

Stock, Sales and Purchasing System for a Carpet Company

Contents

Download Pdf Manuals

Related Search

Related Contents