Access Advantages Disadvantages Security Scalability Information Technology Essay

Interim Research Report

Prepared

By

Claire M Butler

Course: Final Computing Project

Module: CST 3100-10-J

Tutor: Steve Manning

Sponsor: Nick Morson

Date: 27th May 2011

CONTENTS Page No

1 Introduction

1.1 Introduction

2. Approach and Methods

2.1 Approach and Methods

3 Findings

3.1 Findings

4 Conclusion

4.1 Conclusion

5 Evaluation and Reflection

5.1 Evaluation

6 References

6.1 References

7 Bibliography

7.1 Bibliography

8 Appendix

8.1 Appendix A Booth Commercials Analysis

8.2 Appendix B Supply Chain overview

8.3 Appendix C Interface Design, Evaluation & Usability

8.4 Appendix D Good Interface Design

8.5 Appendix E Access advantages & disadvantages, Security & Scalability

8.6 Appendix F Market Research

1.1 Introduction

Booth Commercials are part of a supply-chain they design, construct and install blast resistant, fire resistant and acoustic doors for military, petrochemical and other industries. Currently the company has a paper based system, and at present they cannot produce automatic re-order quantities of stock or lists and rely on spreadsheets for finding customer orders. Therefore my main task is to conduct requirements analysis and develop an Order System using Access 2007 as this will incur no costs for the company. The Order System will allow the company to enter and edit orders, products and customers. The system will also provide analytics, automatic re-order quantities and other reports.

As this project is for a company, requirements analysis will be conducted and incorporated into this report. The purpose of this is to gain a good understanding of the company and understand the requirements for the proposed system. Documentation gathering and Interviews will be conducted and from this information Data flow diagrams will be produced, company documentation will be normalised and entity relationship diagrams will be produced. It is important to clarify here that interface designs will not be shown at this stage but will be incorporated into the final report.

To gain further understanding, literature will be reviewed; as Booths are part of a supply chain the first literature researched will be supply chain, a basic understanding of what supply chain is and its relevance within the industry will be discussed and market research will be conducted on current available off the shelf software and costs.

As user interfaces are being created it is important to research what makes a good user interface and how usability affects interaction. Designing a good interface is just as important as understanding the supply chain, therefore this report will also research this area.

Finally the advantages and disadvantages of Access have been reviewed together with security and scalability. In addition a comparison with SQL Server has been incorporated.

This report researches:

Supply chain

Interface design and usability.

Good GUI practices

Access advantages and disadvantages, security and scalability

This reports appendix’s includes:

Supply Chain review

Interface design and usability review

Good GUI practices

Advantages and disadvantages of Access together with security and scalability

Market research of Supply Chain products

Analysis of Booth Commercials

This reports objective is to achieve the above tasks and demonstrate a good understanding and provide detailed documentation of supply chain and its relevance to Booth Commericals. Illustrate the importance of good interface design when creating systems which create a human computer interaction environment, discuss the advantages and disadvantages of Access, scalability and a comparison to SQL Server together with security issues. Finally a good understanding of Booth Commercials will be produced in the form of data flow diagrams and normalised company documentation together with entity relationship diagrams.

2.1 Approach and Methods

As Booths are an authentic company many different areas of research had to be undertaken for this project:

Requirements Gathering and Analysis of Booth Commercials

Research into Supply Chain

Advantages and Disadvantages of Microsoft Access, security and scalability

Rules of Interface Design, Evaluation and Usability

Good GUI practices

Market research of available software

Firstly the approach to software development was considered and after consideration the waterfall method will be used to evaluate requirements, requirements gathering and analysis being the first stage, of course the waterfall method has disadvantages, these have been discussed within findings. The primary concern for this interim research was to gain requirements, analyse Booth Commercials and undertake a user centred approach. Observations, interviews and documentation gathering have been conducted to gain understanding of the company, user requirements and take on a user centred approach to design. From this research dataflow diagrams, normalisation and entity relationship diagrams have been produced to form an analysis of Booth Commercials and the end product being produced. In addition user requirements and performance indicators / requirements are included. Appendix A shows these findings.

Whilst conducting requirements analysis, research books also had to be sourced, sensible web based searches conducted and articles reviewed. As Booths are part of the supply chain it seemed feasible that a small study into the history of supply chain, what supply chain is and how it related to Booth Commercials should be conducted, originally the study was for supply chain and logistics but after analysing Booths it became clear that the logistics side didn’t affect them as they subcontract a haulage firm to deliver their product to the consumer, therefore logistics has not been reviewed. Reading academic material on supply chain and looking at web based searches gained a basic understanding of what supply chain is. It is impossible to include all my findings of supply chain within this research report, therefore Appendix B contains a brief outline of supply chain and an analysis of Booths supply chain.

One of the constraints of this project is to develop the application using Access 2007 as it will incur no costs for the company. Therefore books were reviewed and searches conducted to look into the advantages and disadvantages of Access, in addition the research also focused on the scalability of Access should the application become too large, as part of this research a comparison has been provided of Access vs SQL Server, finally it was important to review security within Access and assess how the system could be made secure. A full review of each of these aspects has been provided in Appendix C

3.1 Findings

The disadvantages of the Waterfall Method are that as it’s a very thorough approach if all the requirements are not gathered at the first stage it affects the development process and its success, secondly if any problems with a particular phase arise after the phase is signed of this can result in a badly structured system, finally if requirements of the customer keep getting added to the list, this can result in not all the requirements being fulfilled and can result in an unusable system.

The supply chain has been evolving since the 1980’s; it is essentially the link between suppliers and customers to the consumer. A successful supply chain must have good supplier relationships and have good customer focus and service. The internal workings of a business within the chain must be working in an effective, efficient and co-operative manor otherwise the chain will break. Booths are consumer demand driven and as such have to have a close integration of supply and demand sides, finally Inventory management is an essential part of supply chain and forecasting and analysis are a must to evaluate risks.

Interface design, evaluation and usability are essential if a good system is to be created. A user’s view of a computer system is often limited to and based solely on their experience of the interface. Usability within an interface defines that the interface must be effective, efficient and provide satisfaction to the user. Poorly designed systems cause software failures.

Good interface design dictates that as a developer you must understand individuals, be aware of perspectives, and provide clear cut design that the user can understand, be consistent and give clear feedback. In addition have traceable paths, keyboard support, correct use of controls and consistent interfaces aid to provide a usable system.

Access is a rapid application development environment, however to achieve a more sophisticated application integration with VBA is an important aspect. The interface and menus are widely known and it can be easily shared over a network. Access can also be relatively easily migrated to SQL server should the application grow beyond the boundaries of Access.

Market research shows that of the shelf products can be costly and have too many things included which may not be required and as a result a company can end up changing their procedures to suit the IT system, which results in the IT system running the company. In addition some systems such as barcode inventory software can be very time consuming and therefore reduce productivity

4.1 Conclusions

Reviewing Supply Chain although challenging due to their being so much literature proved invaluable as it showed how lead times, customer focus and more importantly how inventory management is essential. Therefore the order system being developed is not only feasible but should be of great benefit to Booths. Being able to know how much stock you have, which customer purchased which product, and providing analysis reports such as which stock has the highest demand in a given period or which stock needs re-ordering, will aid booths in creating a successful supply chain and the Order system being created for this project will provide this for Booths.

As a software package is being designed and implemented for booths the research into Design, Evaluation & Usability proved invaluable, a user centred approach is necessary. The user is never concerned with the underlying technology, what is important is the interface. Research showed that the interfaces being created need to be easy to use and understand otherwise the system could become difficult and problematic for the user making the system unusable. In addition different users have different IT skills and all these factors have to be considered. To be usable an interface has to be viewed as being “usable” by the user for whom the system has been designed. In addition the capacity for the design should also look beyond the user’s immediate working environment and look at the wider situation within which the system is expected to function. After all a user interface that the user finds pleasant will be more acceptable than one that frustrates the user, which in turn makes the system more productive. Software failures and problems arise as a result of poorly designed interfaces and not meeting user requirements can result in loss of productivity, efficiency and money. Classic mistakes made by developers include:

Not thinking about the user: As a developer it is easy to fall into the trap of designing what you know rather than what the user does.

Failing to give control to the user: True event driven design is all about letting the user dictate what events occur, providing this gives the user an element of satisfaction.

Overkill on navigation and features: Too much navigation and features can confuse a user, frequently used features should be made easily available for the user.

A successful GUI should be speedy and responsive a lot of speed issues are a result of the design of the GUI and not the hardware, in addition the use of real world metaphors i.e. when using graphical representation the graphic should identify and be intuitive to the user which will help the users learn the application faster. A good design should accommodate the following:

Understand individuals

Be aware of peoples perspectives

Provide clear cut design

Be consistent

Give clear feedback

Keep it simple

Have a traceable path

Give keyboard support

Provide correct use of controls

Create a good vibe

Designing and creating good interfaces is an essential skill for the developer and a necessity for the end user. The design must make the user enjoy working with the system for it to be productive and consistency is a key issue within the GUI environment.

As the software is being developed in Access 2007 the research into the advantages and disadvantages together with scalability and security issues was extremely important. The research showed that using Access:

It is possible to build an application relatively quickly but an application can be further tweaked using macros and VBA coding to create more sophisticated software.

Access has a widely known interface and menus.

Access can be integrated with other Microsoft products.

Access supports industry standards, i.e. relational database: identity keys, joins and unions etc.

Access can easily work over a network.

Lots of support is available for access including an integrated help system.

Using ODBC drivers, access can communicate with most of the major big-system databases

Access can relatively easily be migrated to Microsoft SQL server.

Disadvantages:

Access has a size limit of around 2GB and if you exceed that limit the database will seize. However as stated above you can port the back-end data to MS SQL Server.

Although access can work over a network it can only support a small number of users.

Incorporating security within access can be a little tricky and can be limited, if you want good security its best to code your own, however this requires technical ability.

Usually in an organisation each copy of Access has to have its own individual licence which can be costly.

Although access has some issues most of the disadvantages can be overcome, for instance should the application for Booths become over sized it can be easily migrated onto MS SQL Server (a comparison is discussed later). Networking issues will not be an issue for Booths as only 3-4 users will be using the system and they already have licences for all the networked computers using Office 2007. Security measures although a little cumbersome can be incorporated:

File Permissions: Microsoft windows network file permissions can be put onto the file or folder.

Passwords: A single password or multi-level workgroup security can be incorporated to allow different privileges to different users. In Access 2007 the security model is a lot stronger than in earlier versions.

File Encoding: Contents of a database can be encoded at the file level.

Hiding navigation pains such as hiding design UI, navigation shortcuts and category’s.

Create a custom menu.

Split the database to create a front and back-end.

It must be stated here that all of the above are designed more for usability than for securing the file. However on a secure network and with only a few users the security should be sufficient, but no matter what you do to Access it will never be as secure as it could be if it was deployed on MS SQL Server. To illustrate the differences a comparison table shows the differences between Access and MS SQL Server

ACCESS

SQL SERVER

Description

Development environment supports: tables, queries, forms, reports and programming logic

Scalable, reliable and more secure client / server database engine

Maximum Database size

2 gigabytes (GB)

1 terabyte or greater

Maximum concurrent users

Up to 20 concurrent editors

Up to 100 concurrent reports being run

Unlimited

Security

File access-based security

Enterprise-Level security

Performance

Limited by file share model

Limited only by hardware and application design

Reliability

Adequate for individuals and small team usage.

Recovery from network failures cannot be rolled back.

High reliability. SQL server is a mission-critical database

Backup and administration tools available.

Chung, L & Haught, D (2007) When to Migrate from Microsoft Access to Microsoft SQL Server.

It is obvious looking at the above table that SQL Server is a high-end solution but it is also a costly one. One of the benefits of Access is that it’s a cheaper alternative and should the number of users exceed “the maximum concurrent users” the database can be placed onto SQL Server, meaning access allows for scalability. Many different approaches can be taken to secure databases but the important aspect is to constantly appraise the security of your database.

5.1 Evaluation and Reflection

Booth Commercials have evolved since undertaking this project, they have moved premises, employed extra staff and as Booth Industries have also undergone a change in their staffing structure this has also affected Booth Commercials. During this process the requirements of Booths kept changing and evolving and due to Booth order deadlines it was not always possible for me to arrange meetings with the project sponsor. Originally an order system and content management system were proposed for this project but after looking at the requirements and analysing Booths and with the researched learned from supply chain, observations and interviews it became clear that an order system was the essential product needed. Therefore I asked my supervisor if I would be able to drop the content management for the following reasons:

Booths didn’t need it.

The sales order system became more complex and developed into an inventory stock system.

It would allow focus to be centred on the system required by Booths.

My supervisor advised that this was fine and that focusing on one thing was good, however as with any system the first design is never going to be correct and several designs ended up being produced for Booths due to the requirements keep being changed. The first design was for a relatively simple sales order system with the inclusion of a supplier table, however this would not create a correct relational database and after conducting more interviews would not meet the company’s requirements, next a purchase order system was incorporate into the design to develop a more complex system as per Booths requirements. However this in itself brought issues because the company wanted to know supplier details, but to produce an integral relational database the only way to do this and give them better analysis was to include purchase orders, Booths wanted the data but didn’t want to enter purchase orders and as with any system an input has to be made to get an output. This was perfectly understandable however as I discovered later that purchasing allow a read only access to their system, however this has all of Booths Industries purchases and not just Booth Commercials purchases and is not necessarily specific enough for Booths requirements, therefore other considerations had to be reviewed:

Entering purchase orders would in fact be data duplication.

Consideration of task time e.g. unnecessarily taking up employee time to enter data and incur costs to the company as productivity is reduced as a consequence.

Time taken to complete data entry for users.

Constraints of University deadlines.

It was then asked if data could be pulled from the purchasing departments database to update purchase orders on the system being built for Booths Commercials or allowing me to write a query to update, but after speaking to Nick Morson he explained that this was unlikely to be allowed as they are allowed no privileges to purchasing. In addition consideration had to be given to the university deadlines and could a system this complex be able to be built within the deadlines and timelines constrained to this project? The simple answer to this is no, a good purchasing system and order system can take anything from 12 to 18 months to be correctly designed, developed and implemented. Therefore I had to re-evaluate the design once again and even though it’s not the correct way to do things as data should be normalised, after speaking to my supervisor the best solution was to simply include a purchase reference from the delivery/ goods received note, this would mean should Booths wish to know further details they would have to look at the file but at least they would have a reference and it allowed the design to be centred around the users requirements, which after all, as the design approach is a user centred one I have to do what the user requires and wants rather than what I as a developer think should be done.

6.1 References

Pg8 Section 4.1 Conclusions

Chung, L & Haught, D (2007) When to Migrate from Microsoft Access to Microsoft SQL Server. Available from: < http://download.microsoft.com/…/when_to_migrate_from_access.doc > [Accessed: 7th April 2011]

Pg_ Appendix B Supply Chain

Emmett, S (2005) The Ninety Minute Series: Supply Chain in 90 minutes. Gloucestershire: Management Books 2000 Ltd

Pg_ Appendix B Supply Chain

Emmett, S (2005) The Ninety Minute Series: Supply Chain in 90 minutes. Gloucestershire: Management Books 2000 Ltd

Pg_ Appendix C Interface Design, Evaluation & Usability

Stone, D et al (2005) User Interface Design and Evaluation. San Francisco: Morgan Kaufmann Publishers

Pg_ Appendix C Interface Design, Evaluation & Usability

Http://www.statistics.gov.uk/cci/nugger.asp?id=868

Pg_ Appendix C Interface Design, Evaluation & Usability

Http://www.w3c.org

Pg_ Appendix D Good Interface Design

Hobart, J (1995) Principles of Good GUI Design: Available from: < http://www.classicsys.com/css06/cfm/article.cfm?articleid=20> [Accessed 9th April 2011]

Pg_ Appendix E Access: Advantages & Disadvantages, Security and Scalability

Order Now