Management Information Systems Assignment
CHAPTER
11
LEARNING OUTCOMES
After reading this chapter, you will be able to:
■ define the difference between analysis and design and the overlap between them;
■ synthesise the relationship between good design and good- quality information systems;
■ define the way relational databases are designed;
■ evaluate the importance of the different elements of design for different applications.
MANAGEMENT ISSUES
Design is also a critical phase of BIS development since errors at this stage can lead to a system that is unsatisfactory for the user. From a managerial perspective, this chapter addresses the following questions:
■ What different types of design need to be conducted for a quality BIS to be developed?
■ What are the key aspects of design for an e-business system?
■ How do we create an effective information architecture for our organisation?
CHAPTER AT A GLANCE
MAIN TOPICS
■ Aims of system design 392 ■ Constraints on system design 394 ■ The relationship between analysis
and design 395 ■ Elements of design 395 ■ System or outline design 397 ■ Detailed design (module design) 405 ■ Design of input and output 421 ■ User interface design 423 ■ Input design 426 ■ Output design 428 ■ Designing interfaces between
systems 428 ■ Defining the structure of program
modules 428 ■ Security design 429 ■ Design tools: CASE (computer-aided
software engineering) tools 430 ■ Error handling and exceptions 430 ■ Help and documentation 430
FOCUS ON . . .
■ Relational database design and normalisation 405
■ Web-site design for B2C e-commerce 424
■ Object-oriented design (OOD) 431
CASE STUDY
11.1 Beaverbrooks the Jewellers 393
11.2 Systems management: driving innovation should be the main objective 401
Systems design
M11_BOCI6455_05_SE_C11.indd 391 30/09/14 7:11 AM
Part 2 BUSINESS INFORMATION SYSTEMS DEVELOPMENT392
The design phase of information systems development involves producing a specification or ‘blueprint’ of how the system will work. This forms the input specification for the final stage of building the system by programmers and database administrators. The design phase is also closely linked to the previous analysis phase, since the users’ requirements directly determine the characteristics of the system to be designed.
The systems design is given in a design specification defining the best structure for the application and the best methods of data input, output and user interaction via the user interface. The design specification is based on the requirements collected at the analysis stage.
Design is important, since it will govern how well the information system works for the end-users in the key areas of performance, usability and security. It also determines whether the system will meet business requirements – whether it will deliver the return on investment. The design specification will include the architecture of the system, how security will be implemented, and methods for entry, storage, retrieval and display of data.
Before the widespread adoption of Internet technologies from the mid-1990s onwards, system design for BIS tended to focus on the design of applications for the different functional areas of the business (as described in Chapter 6). In the era of e-business, design of such applications for purposes such as electronic procurement, supply chain management and customer relationship is still required. However, the adoption of standard solutions applications such as SAP and Oracle for enterprise applications has meant that system design has changed in its nature. Many of the challenges of design now involve tailoring the user interfaces and data storage and transfer for these standard applications. It is now less common for systems to be designed without the use of pre-existing software applications or components.
A further change in the emphasis of design has been caused by the increasing volume of unstructured information that it is made available to businesses and consumers via the Internet and the World Wide Web. A design challenge faced by all organisations, large and small, is managing this content in order to deliver relevant, timely information to their stakeholders whether they be employees, customers, suppliers, partners or government agencies. So designing an effective information architecture for an organisation to enable it to deliver content via the intranets, extranet and Internet networks introduced earlier (in Chapters 5 and 6) has become a major challenge.
In this chapter, we explore the elements both of traditional application design and of delivery of web-based content. We start by introducing the concepts of effective design which apply to all types of information system. We then look at how input design, output design and interfacing with other systems occurs for traditional applications and for those delivered via web browsers. Finally, we look at approaches to building information architecture. Throughout the chapter, we will refer to the example of information systems required by a bank to illustrate today’s challenges of system design.
INTRODUCTION
Systems design
The design phase of the lifecycle defines how the finished information system will operate. This is defined in a design specification of the best structure for the application and the best methods of data input, output and user interaction via the user interface. The design specification is based on the requirements collected at the analysis stage.
AIMS OF SYSTEM DESIGN
In systems design we are concerned with producing an appropriate design that results in a good-quality information system that:
■ is easy to use; ■ provides the correct functions for end-users;
M11_BOCI6455_05_SE_C11.indd 392 30/09/14 7:11 AM
393ChaPter 11 SYSTEMS DESIgN
■ is rapid in retrieving data and moving between different screen views of the data; ■ is reliable; ■ is secure; ■ is well integrated with other systems.
These factors are clearly all important to delivering a satisfactory experience to end- users and a satisfactory return on investment to the business. Consider an online banking service for customers which may also be accessed by staff – all these factors are vital to the success of the system and so the design is vital to the success of the system also.
Beaverbrooks the Jewellers is a family business established a century ago and with staff dispersed between branches and head office locations, increased paperwork, administration and information demands had become a significant issue. Much of Beaverbrooks’ business concerns providing special order items wedding and engagement rings, necklaces, engraved silverware and watches. While each shop stocks the full range of wedding and engagement rings, it is rare that they will have all sizes in stock at every store.
Patrick Walker, head of management information systems, says that most couples choose the design of the ring together and then the ring will be ordered either from a central warehouse, another local branch or directly from a supplier. ‘The result is that much of Beaverbrooks’ staff time is taken up following the progression of the order’, he says. ‘We needed a system that would manage this for us. In addition, we discovered that there was a mass of email going round and round the organisation and not always reaching the appropriate person.’ Collaboration between head office and the branches, a central repository for documentation and a framework to enable company information and knowledge to be exchanged were the desired outcomes that arose from a cross-company focus group.
All company data, regardless of its format or origin, is now held in one place on a central server where it can be easily shared, searched, retrieved, backed up and managed. KnowledgeWorker collaboration, search and
workflow tools sit over the top of the data, which is accessed locally or remotely through a web browser interface. Branch staff now use the central data system extensively for stock en-quires, placing special orders, sharing company information and making sure the merchandising in each branch conforms to the current company branding and directives.
For Beaverbrooks, integrating its information and processes into a central collaborative system has led to improved productivity throughout the organisation. ‘Improving our methods of storing information and then sharing it between employees has halved our administration time’, says Walker.
‘We keep finding more activities the system can help us with, so we are spending that extra time doing new things with our information to make us even more effective as a business.’
Source: Linda More, Computing, 25 October 2007, http://www. computing.co.uk/ctg/analysis/1821325/case-study-beaverbrooks- jewellers
Beaverbrooks the Jewellers Beaverbrooks the Jewellers created a system that collates data from all stores and places it in one location
CASE STUDY 11.1
QUESTIONS
1. Read the case study and identify the main design elements that needed to be considered.
2. Identify any design features that can be directly linked to specific business benefits.
M11_BOCI6455_05_SE_C11.indd 393 30/09/14 7:11 AM
Part 2 BUSINESS INFORMATION SYSTEMS DEVELOPMENT394
It is also important to think forward to future releases of the software. When the software is updated in the maintenance phase, it is important to have a system that can be easily modified. Good documentation is important to this, but equally import-ant is that the design be flexible enough to accommodate changes to its structure. To achieve flexibility, simplicity in design is a requirement. Many designers and developers adopt the maxim ‘KISS’ or ‘Keep It Simple, Stupid!’.
Whitten and Bentley (2006) point out that design does not simply involve producing an architectural and detailed design, but is also an evaluation of different implementation methods. For example, an end-user designing an application will consider whether to implement a system within an application such as Microsoft Access or develop a separate Visual Basic application. However, it is usually possible to take the ‘make-or-buy’ decision earlier in the software lifecycle, even when the detailed design constraints are unknown. The acquisition method is described in more detail earlier (in Chapters 7 and 8) on the start-up phases of a project.
CONSTRAINTS ON SYSTEM DESIGN
The system design is directly constrained by the user requirements specification, which has been produced as a result of systems analysis (as described in Chapter 10). This will describe the functions that are required by the user and must be implemented as part of the design. As well as the requirements mentioned in the previous section, there are environmental constraints on design which are a result of the hardware and software environment of implementation. These include:
■ hardware platform (PC, Apple or Unix workstation); ■ operating system (Windows XP, Apple or Unix/Linux); ■ web browsers to be supported (different versions of Microsoft Internet Explorer and
open-source rivals such as Opera, Mozilla, Firefox, etc.); ■ data links required between the application and other programs or a particular relational
database such as Oracle or Microsoft SQL Server; ■ design tools such as CASE tools; ■ methodologies or standards adopted by the organisation, such as SSADM; ■ industry standards such as data exchange using XML; ■ system development tools or development environments for programming, such as
open-source technology or proprietary tools such as Microsoft Visual Studio; ■ number of users to be supported concurrently and the performance required.
Hoffer et al. (2010) refer to a design strategy as the high-level statement defining how the development of an information system should proceed which addresses all the issues described above. They identify three different aspects of design:
1. Dividing requirements (from the analysis phase discussed in the previous chapter) into sets of essential requirements and optional requirements which may be built into future versions.
2. Enumerating different potential implementation environments (hardware, system software and network platforms: discussed in Chapters 3, 4 and 5).
3. Proposing different ways to source or acquire the various sets of capabilities, for example outsourcing, purchase of pre-existing applications software or development of new capabilities (as discussed in Chapters 7 and 8 of this text).
Design strategy
A high-level statement about the approach to developing an information system. It includes statements on the system’s functionality, hardware and system software platform, and the method of acquisition.
M11_BOCI6455_05_SE_C11.indd 394 30/09/14 7:11 AM
395ChaPter 11 SYSTEMS DESIgN
While this is a useful way of breaking down decisions that need to be taken about design of an information system, the reality is that by the time a systems development project enters the main design phase, all three of these areas will have been agreed. They are part of the feasibility analysis described earlier (in Chapter 8). So, in this chapter we focus on the approaches to detailed design needed to implement the system requirements. These include design of the user interface, database and security of a system within the technical environment and the acquisition method that has already been selected.
THE RELATIONSHIP BETWEEN ANALYSIS AND DESIGN
As Yeates and Wakefield (2003) point out, there is considerable overlap between analysis and design. To help ensure completion of the project on time, preliminary design of the architecture of the system will start while the analysis phase is progressing. Furthermore, the design phase may raise issues on requirements that may require further analysis with the end-users, particularly with the prototyping approach.
The distinction is often made between the logical representation of data or processes during the analysis stage and the physical representation at the design stage. Consider, for example, data analysis: here the entity relationship diagram of the analysis phase described earlier (in Chapter 10) will be transformed into a physical database table definition at the design stage as described later in this chapter. A logical entity ‘customer’ will be specified as a physical database table ‘Customer’ in which customer records are stored. Similarly, the dataflow diagram will be transformed into a structure chart indicating how the different submodules of the software will interact at the design stage.
ELEMENTS OF DESIGN
The different activities that occur during the design phase of an information systems project can be broken down in a variety of ways. In this section we consider different ways of approaching system design. These alternatives are often used in a complementary fashion rather than exclusively.
A common approach to design is to consider different levels of detail. In the next main section we start by considering an overall design for the architecture of the system. This is referred to as ‘system design’. Once this is established, we then design the individual modules and the interactions between them. This is known as ‘module design’. Through using this approach we are tackling design by using a functional decomposition or top- down approach, similar to that referred to earlier (in Chapter 9) on project management as the ‘work breakdown structure’. Major modules for an online banking system will be those for capturing and displaying data and interacting with the user, data access modules which interface to the bank’s legacy customer database, and security or user access modules.
Since many systems are made from existing modules or pre-built components that need to be constructed, the design approach that is most commonly employed is a top-down strategy. In this approach, it is best to consider the overall architecture first and then perform the detailed design on the individual functional modules of the system. The ‘divide and conquer’ approach can then be used to assign the design and implementation tasks for
Top-down or bottom-up?Top-down design
The top-down approach to design involves specifying the overall control architecture of the application before designing the individual modules.
M11_BOCI6455_05_SE_C11.indd 395 30/09/14 7:11 AM
Part 2 BUSINESS INFORMATION SYSTEMS DEVELOPMENT396
each module to different development team members. The description in this chapter will follow this approach by looking at the overall design first and then at the detailed module design.
The bottom-up approach to design starts with the design of individual modules such as the security module, establishing their inputs and outputs, and then builds an overall design from these modules.
Bottom-up design
The bottom-up approach to design starts with the design of individual modules, establishing their inputs and outputs, and then builds an over-all design from these modules.
An aspect of the design which is quite easy to overlook is testing that the design we produce is the right one. Checking the design involves validation and verification.
In validation we will check against the requirements specification and ask ‘Are we building the right product?’ In other words, we test whether the system meets the needs of the end-users identified during analysis such as functions required and speed of response. Validation will occur during testing of the system by the end-users; it highlights the value of prototyping in giving immediate feedback of whether a design is appropriate.
When undertaking verification we will ‘walk through’ the design and ask ‘Are we building the product right?’ Since there are a number of design alternatives, designers need to consult to ensure they are choosing the optimal solution. Verification is a test of the design to ensure that the one chosen is the best available and that it is error-free.
The two questions should be considered throughout the design process and also form the basis for producing a test specification to be used at the implementation stage.
Validation and verification
Validation
This is a test of the design where we check that the design fulfils the requirements of the business users which are defined in the requirements specification.
Verification
This is a test of the design to ensure that the design chosen is the best available and that it is error-free.
Scalability is the potential of an information system or piece of software or hardware to move from supporting a small number of users to supporting a large number of users without a marked decrease in reliability or performance.
When designing information systems, the design target must always be for the maximum anticipated number of users. Many implementations have failed, or have had to be redesigned at considerable cost, because the system used in the development and test environment with a small number of users does not scale to the live system with many more users.
If the system does not scale, there may be major problems with performance which makes the system unusable. Volume or capacity planning (Chapter 12), in which the anticipated workload of the live environment is simulated, can help us foresee problems of scalability.
Scalability
Scalability
The potential of an information system or piece of software or hardware to move from supporting a small number of users to a large number of users without a marked decrease in reliability or performance.
Another common approach to design is to consider data modelling and process modelling separately. The design of the data structures required to support the system, such as input and output files or database tables, are considered in relation to information collected at the analysis stage as the entity relationship diagram (ERD) and data requirements. In SSADM a separate stage is identified for data design which is followed by process design although the two are often combined.
Process modelling is the design of the different modules of the system, each of which is a process with clearly defined inputs and outputs and a transformation process. (Note that this term is also used as an approach to design in business process re-engineering.) Dataflow diagrams are often used to define system processes.
Data modelling and process modelling
Process modelling
Involves the design of the different modules of the system, each of which is a process with clearly defined inputs and outputs and a transformation process. Dataflow diagrams are often used to define processes in the system.
M11_BOCI6455_05_SE_C11.indd 396 30/09/14 7:11 AM
397ChaPter 11 SYSTEMS DESIgN
Data modelling considers how to represent data objects within a system, both logically and physically. The entity relationship diagram is used to model the data and a data dictionary is used to store details about the characteristics of the data, which is sometimes referred to as ‘metadata’.
The processes or program modules which will manipulate these data are designed based on information gathered at the analysis stage in the form of functional requirements and dataflow diagrams. This approach is used, for example, by Curtis (2008). While this is a natural division, there is a growing realisation that for a more efficient design these two aspects cannot be considered in isolation. Object-oriented techniques, which are increasing in popularity, consider the design of process and associated data as unified software objects. These are considered in more detail at the end of this chapter.
Other elements of design are required by the constraints on the system. To ensure that the system is easy to use we must design the user interface carefully.
To ensure that the system is reliable and secure, these capabilities must be designed into the system. User interface and security design are elements of design that will be considered at both the overall or system design phase and the detailed design phase.
Data modelling
Data modelling involves considering how to represent data objects within a system, both logically and physically. The entity relationship diagram is used to model the data.
In this chapter we will review the following major elements of systems design:
1. Overall design or system design. What are the best architecture and client/server infra- structure? The overall design defines how the system will be broken down into different modules and how the user will navigate between different functions and different views of the data.
2. Detailed design of modules and user interface components. This defines the details of how the system will operate. It will be reviewed by looking at user interface and input/output design.
3. Database design. How to design the most efficient structure using normalisation. 4. User interface design. How to design the interface to make it easy to learn and use. For
web-based systems this includes the information architecture. 5. Security design. Measures for restricting access data and safeguarding data against
deletion.
What needs to be designed?
System or outline design
A high-level definition of the different components that make up the architecture of a system and how they interact.
SYSTEM OR OUTLINE DESIGN
System or outline design involves specifying an overall structure or systems architecture for all the different components that will make up the system. It is a high-level overview of the different components that make up the architecture of a system and how they interact. The components include software modules that have a particular function such as a print module, the data they access, and the hardware components that may be part of the system. Hardware will include specifying the characteristics of the client PC and servers, plus any additional hardware such as an image scanner or specialised printer.
Designing the overall architecture involves specification of how the different hardware and software components of the system fit together. To produce this design, a good starting point is to consider the business process definition that will indicate which high-level tasks will be performed using the different components of the system. Flow process charts or process maps such as Figure 11.1 can be used to inform the architectural design directly since they help to identify the different components needed and how they link. Figure 11.2 concentrates on hardware, but also describes location of data and applications.
Systems architecture
The design relationship between software applications, hardware, process and data for an information system.
M11_BOCI6455_05_SE_C11.indd 397 30/09/14 7:11 AM
Part 2 BUSINESS INFORMATION SYSTEMS DEVELOPMENT398
Figure 11.1 Flow process chart for a workflow processing system
Yes
Write to request
additional info
No
No
No
Yes
YesYes Yes
No
Key
Inbound goodsDelayInspection/
measurementProcessDecision Transportation
Authorised?
Phone to request
more info
Is info available?
Case complete
Mail in
Awaiting sorting Categorise New
application? Key in
application
Action priority
task from queue
Assign priority
according to date
Associate to
case
Scan-in documen-
tation
More info required?
Mark item complete
Last task for case?
Manager checks
Process modelling Process modelling is used to identify the different activities required from a system, as explained in Chapter 10. These functions can be summarised using a flow process chart as shown in Figure 11.1.
The overall architecture description will also include details of the navigation between the main screens or views of data in the application which can be based on this type of diagram.
Screen functions needed in this software are to categorise the type of mail received, associate it with a particular ‘case’ or customer and review items of work in the workflow queue, marking them as complete where appropriate. Table 11.1 summarises what is achieved during the different types of design.
M11_BOCI6455_05_SE_C11.indd 398 30/09/14 7:11 AM
399ChaPter 11 SYSTEMS DESIgN
Figure 11.2 System architecture for a workflow processing system
Workflow and image
index database
Optical jukebox images
Processing PCScanner control PC Processing PC
Document scanner
Workflow server
Scanner PC: – Scanning client – Image client – Network and database connectivity
Processing PC: – Workflow client – Image client – Network and database connectivity – Terminal emulation software
Ethernet LAN
Mainframe
Printer (customer letters)
Hub
Tape backup
Table 11.1 Comparison between the coverage of system and detailed design
Design function System design Detailed design
Architecture Specification of different modules and communication between them; specification of hardware components and software tools
Internal design of modules
User interface Flow of control between different views of data
Detailed specification of input forms and dialogues
Database Data modelling of tables Normalisation
File structure Main file types and contents Detailed ‘record and field structure’
Security Define constraints Design security method
M11_BOCI6455_05_SE_C11.indd 399 30/09/14 7:11 AM
Part 2 BUSINESS INFORMATION SYSTEMS DEVELOPMENT400
Management of a business applications infrastructure involves delivering appropriate applications and levels of service to all users of information systems services. The objective of the designer, at the behest of the IS manager, is to deliver access to effective, integrated applications and data that are available across the whole company. Traditionally businesses have tended to develop applications silos or islands of information as depicted in Figure 11.3(a) – these correspond to the functional parts of the organisation. Figure 11.3(b) shows that these silos have three different levels of applications:
1. there may be different technology architectures or hardware used in different functional areas;
2. there may also be different applications and separate databases in different areas; 3. processes or activities followed in the different functional areas may also be different.
Designing enterprise applications
Figure 11.3 (a) Fragmented applications infrastructure, (b) integrated applications infrastructure
Source: Adapted from Hasselbring (2000)
Business process architecture
Application / data architecture
Technology architecture
Business process architecture
Application / data architecture
Technology architecture
Business process architecture
Application / data architecture
Technology architecture
Procurement and logistics Finance Marketing
Fu nc
tio na
l b ar
rie r
(a)
Fu nc
tio na
l b ar
rie r
Business process architecture
Application / data architecture
Technology architecture
Procurement and logistics Finance
Functional integration
Marketing
(b)
M11_BOCI6455_05_SE_C11.indd 400 30/09/14 7:11 AM
401ChaPter 11 SYSTEMS DESIgN
These applications silos are often a result of decentralisation or poorly controlled investment in information systems, with different departmental managers selecting different systems from different vendors. An operational example of the problems this may cause is if a customer phones a B2B company for the status of a bespoke item they have ordered, the person in customer support may have access to their personal details, but not the status of their job which is stored on a separate information system in the manufacturing unit.
To avoid the problems of a fragmented applications infrastructure, companies have been attempting, since the early 1990s, to achieve the more integrated position shown in 11.3(b). Here the technology architecture, applications and data architecture and process architecture are uniform and integrated across the organisation. To achieve this many companies turned to enterprise systems vendors such as SAP and Oracle. Here, they are effectively using a pre-existing design from the off-the-shelf package, and the design involves selecting appropriate modules and tailoring them for the revised business process. Enterprise systems software is discussed in more detail earlier (in Chapter 6).
A typical IT department is like an old house that has been mended and extended so that the original design and infrastructure have almost disappeared, says Peter Chadha, chief executive of DrPete, an IT strategy consultancy.
Nobody remembers the location of gas pipes and electricity cables or how the plumbing works, making the building difficult to adapt to a new purpose.
‘The ‘house’ is so difficult to update and ill-suited to modern living, it is now easier to put a Portakabin in the garden than continue using it,’ says Mr Chadha.
Similarly, meeting a business’s technology needs can often now be better accomplished using addi - tional products and services – such as smartphones, tablet computers, software as a service (SaaS) and outsourcing – rather than upgrading legacy IT systems.
For example, Mr Chadha helped to implement an iPad-based electronic reception logbook for The Office Group, the meetings and events organiser, using Google Apps with Google Scripting. ‘It gave reception a modern feel and meant anyone in the building could instantly know who was in,’ he says.
Business executives have seen how quickly apps can be implemented on their personal mobile devices, and they expect IT departments to be equally responsive to their business needs.
But most applications need to be integrated with exist- ing systems – a difficult, expensive, time-consuming process, as IT departments point out.
Executives often think of the IT department as blocking innovation, says Roop Singh, managing partner at Bangalore-based Wipro Consulting Services.
‘This is a bit unfair, because IT departments have to worry about complexity, security, risk and support,’ says Mr Singh.
‘Moreover, since 2009 they have been forced to focus on minimising costs, keeping the lights on rather than making a difference to the business.’
This showed in a recent UK survey of 1,000 senior IT decision makers, commissioned by KCom, a services communications provider.
Some 72.5 per cent of respondents said they had no plans to invest in IT systems during the coming year, and 26 per cent cited an inability to demonstrate that IT will help meet strategic objectives and provide a return on investment as the reason for holding back.
This lack of focus on business objectives is crucial, says Stephen Pratt, managing partner of worldwide consulting at Infosys in California. ‘It is very common for the business to say it wants something done in two years and for the IT department to say it will take four,’ says Mr Pratt.
‘Most executives say technology infrastructure is limiting their ability to achieve business goals,’ he adds. ‘It ought to be doing the opposite: driving the innovation that pushes the business faster than it’s comfortable with.’
Mr Pratt says IT should be split into two parts, infrastructure and innovation. Infrastructure should
Systems management: driving innovation should be the main objective By Jane Bird
CASE STUDY 11.2
➨
M11_BOCI6455_05_SE_C11.indd 401 30/09/14 7:11 AM
Part 2 BUSINESS INFORMATION SYSTEMS DEVELOPMENT402
provide a basic service, as do the office’s air conditioning and coffee machines.
Innovation should focus on ways to help the business achieve its strategic goals. You need two distinct personalities to lead these functions, Mr Pratt says.
‘People focused on innovation are more likely to look for progressive ways to deploy technology – they are more likely to embrace change than resist it.’
Outsourcing applications, such as enterprise resource planning, finance and supply chain, is a good way to focus on innovation, says Brendan O’Rourke, chief information officer of TelefÓnica Digital, the telecommunications company, which outsources many applications to IT services consultancy Cognizant.
‘An outsourcer can get on with the management, opera- tion and maintenance of IT, and is best placed to opti- mise costs,’ says Mr O’Rourke. He involves Cognizant closely in developing applications. ‘This means we can hand over application maintenance to Cognizant at an early stage, and eventually send it offshore.’
Mr Chadha cites applications that make it possible to implement customer relationship systems in ‘days or weeks rather than the months or years it might if organisations implemented it themselves’.
Cloud-based SaaS systems are also easier to try out, says Mr Chadha. This frees time to concentrate on improving processes and training people.
Wipro’s Mr Singh says: ‘IT needs to engage with the business and explain how difficult it is simply keeping the lights on.’
But he notes that IT departments are recruiting more people who understand the business side. Banks, for example, are hiring regulatory experts who can take proactive steps to ensure compliance.
In retail, communications experts are joining IT teams. This helps them not only respond better to business needs but also lets them articulate the benefits and prob- lems with technological developments more clearly. Such approaches will help teams communicate more effectively and so drive the business forward rather than hold it back.
The majority of modern information systems are designed with a client/server architecture. In the client/server model, the clients are typically desktop PCs which give the ‘front-end’ access point to business applications. The clients are connected to a ‘back-end’ server computer via a local- or wide-area network. As explained earlier (in Chapter 5), applications accessed through a web browser across the Internet are also client/server applications. These include e-commerce applications for online purchase and application service provider solutions such as remote e-mail management.
When it was introduced, the client/server model represented a radically new architecture compared with the traditional centralised processing method of a main-frame with character-based ‘dumb terminals’.
Client/server is popular since it provides the opportunity for processing tasks to be shared between one or more servers and the desktop clients. This gives the potential for faster execution, as processing is shared between many clients and the server(s), rather than all occurring on a single server or mainframe. Client/server also makes it easier for end-users to customise their applications. Centralised control of the user administration and data security and archiving can still be retained. With these advantages, there are also system management problems which have led to an evolution in client/server architecture from two- to three-tier as described below. The advantages and disadvantages of client/ server are discussed earlier (in Chapter 5).
When designing an information system for the client/server architecture, the designer has to decide how to divide tasks between the server and the client. These tasks include:
■ data storage; ■ query processing;
The client/server model of computing
Client/server model
This describes a system architecture in which end-user computers access data from more powerful server computers. Processing can be split in various ways between the server and client.
QUESTION
Evaluate the approach to systems management discussed in the case study.
Source: Bird, J. (2012) Systems management: driving innovation should be the main objective. Financial Times. 6 November. © The Financial Times Limited 2012. All Rights Reserved.
M11_BOCI6455_05_SE_C11.indd 402 30/09/14 7:11 AM
403ChaPter 11 SYSTEMS DESIgN
■ display; ■ application logic including the business rules.
Client/server design generally follows just two main approaches: two-tier and three-tier client/ server. Two-tier client/server is sometimes referred to as fat client, the application running on the PC being a large program containing all the application logic and display code. It retrieves data from a separate database server. Three-tier client/server is an arrangement in which the client is mainly used for display, with application logic and the business rules partitioned on a server as a second tier and the database server the third tier. Here the client is sometimes referred to as a thin client, because the size of the application’s executable program is smaller. It is important to understand the distinctions between these, since they involve two quite different design approaches that can have significant implications for application performance and scalability. These are the ‘thin client’ approach where the client only handles display and the ‘fat client’ approach where a larger program runs on the client and handles both display and application logic. In the ‘fat client’ model the client handles the display and local processing, with the server holding the data (typically in a database) and responsible for handling processing of queries on the back end. This model, which is known as two-tier client/server, is still widely used, but more recently the three-tier client/server has become widespread due to problems with unreliability and lack of scalability with two-tier systems.
Figure 11.4 shows a simple two-tier client/server arrangement. In this, a client application directly accesses the server to retrieve information requested by the user, such as a report of ‘aged debtors’ in an accounting system. In this two-tier model, the client handles all application logic such as control flow, the display of dialogues and formatting of views.
In a three-tier client/server model (Figure 11.4(b)), the GUI or ‘thin client’ forms the first tier, with the application and function logic separated out as a second tier and the data source forming the third tier. In this model there may be a separate application and database server, although these could reside on the same machine. Two-tier client/server may be the more rapid to develop in a RAD project, but it will not be the more efficient at run time or the easier to update. Through separating out the display coding and the business application into three tiers, it is much easier to update the application as business rules change (which will happen frequently). It also offers better security through fine-tuning according to the service required.
Two-tier client/server or fat client
Application running on the PC being a large program containing all the application logic and display code. It retrieves data from a separate database server.
Three-tier client/server or thin client
The client is mainly used for display with application logic and the business rules partitioned on a second- tier server and a third- tier database server.
Figure 11.4 (a) Two-tier and (b) three-tier client/server architecture compared
Database server
Client GUI application
and business logic
Database server
(a)
SQL query/ retrieval
Client GUI presentation
Application server
function/ logic
(b)
RPCs, object methods
SQL query/ retrieval
M11_BOCI6455_05_SE_C11.indd 403 30/09/14 7:11 AM
Part 2 BUSINESS INFORMATION SYSTEMS DEVELOPMENT404
The module and program structure will also be outlined at the system design stage. There are various notations used by programmers to indicate the structure that will be used. An example is the structure chart which is used in the design methodology JSD (Jackson system development – Jackson, 1983). An example of a structure chart is illustrated in Figure 11.5. A structure chart shows how the software will be broken down into different modules and gives an indication of how they will interact. Here the main control module is calling a variety of other modules with different functions. The interaction or exchange of data items between procedures is also shown. For example, the ‘edit customer’ module is passed the name (or customer code) of the customer to edit and if the user changes the data a ‘flag’ (True or False) parameter is passed back to the control module, indicating that the data were updated. Similarly, the credit check module is passed the name of the customer and a flag indicates whether the customer is creditworthy or not.
The interactions between modules will normally be defined at this stage rather than at the detailed design stage. For example, there may be a function to produce a customer report of credit history. Here, the function will need to know the customer and the time period for which a report is required. Thus the system design will specify the function with three parameters as shown in Figure 11.6:
Function: Print_Credit_history Parameters: Cust_id, Period_start, Period_end Return value: Print Successful
Program and module structure
Figure 11.5 Example of a program structure chart
Control program
Display all customer
Credit check
Permit mortgage
Display customer
history
Edit customer
Flag
parameter
Data item
parameter
NameName
Credit
worthy?
D at
e ra
ng e
Name
Data
updated? Nam
e
Figure 11.6 Part of a structure chart showing how parameters are passed from a control module to a module to print a credit history
Print_ credit_ history
Print_successful
e.g. True
Period_end
e.g. 30/04/99
Period_start
e.g. 01/04/99
Cust_id
e.g. 03574
Control module
M11_BOCI6455_05_SE_C11.indd 404 30/09/14 7:11 AM
405ChaPter 11 SYSTEMS DESIgN
DETAILED DESIGN (MODULE DESIGN)
Detailed design involves considering how individual modules will function and how information will be transferred between them. For this reason, it is sometimes referred to as module design. A modular design offers the benefit of breaking the system down into different units which will be easier to work on by the team developing the system. It will also be easier to modify modules when changes are required in the future.
Module design includes:
■ how the user interface will function at the level of individual user dialogues; ■ how data will be input and output from the system; ■ how information will be stored by the system using files or a database.
Detailed design is sometimes divided further into external and internal design. The external design refers to how the system will interact with users, while the internal design describes the detailed workings of the modules.
Detailed or module design
Detailed design involves the specification of how an individual component of a system will function in terms of its data input and output, user interface and security.
RELATIONAL DATABASE DESIGN AND NORMALISATIONFOCUS ON…
Business users are often involved in the design of relational databases, either in an advisory capacity (specifying what data they should contain) or when building a small personal database, perhaps of customer contacts. For this reason, the terminology used when working with databases and the process of producing a well-designed database are described in some detail.
Relational database terminology was introduced earlier (in Chapter 4), but it is restated here since understanding the terms is important to understanding the design process. In the previous chapter we saw how entity-relationship modelling is used to analyse the conceptual design of a database. In this section we look at the next stage, which is the creation of a logical data model and then a physical database where tables and fields are created and then populated with data in records. The example used is a sales order processing database for a clothing manufacturer, ‘Clothez’, and we illustrate the creation of tables within a database using Microsoft Access.
Databases are used for the management of information and data within organisations. The functions of a database, whether it is an address book on a phone or a corporate database supporting an entire organisation, are to enter, modify, retrieve and report information.
The terms defining the structure of a relational database can be considered as a hierarchy or tree structure. A single database is typically made up of several tables. Each table contains many records. Each record contains several fields. These terms can be related to the Clothez example as follows:
1. Database – all information for one business application (normally made up of many tables). Example: sales order database.
2. Table – a collection of records for a similar entity. Example: all customers of the company within the sales order database. Other tables in the database are product and order.
3. Record – information relating to a single instance of an entity (comprising many fields). Example: single customer such as Poole.
4. Field – an attribute of the entity. Example: customer name or address for a particular customer such as Poole.
Databases – fundamental terms
Database
All information for one business application (normally made up of many tables).
Table
Collection of records for a similar entity.
Record
Information relating to a single entity (comprising many fields).
Field
An attribute of the entity.
M11_BOCI6455_05_SE_C11.indd 405 30/09/14 7:11 AM
Part 2 BUSINESS INFORMATION SYSTEMS DEVELOPMENT406
This structure is represented as a diagram in Figure 11.7 for the Clothez database. It can be seen that the sales order processing database for Clothez could be designed and implemented as three tables: customer, order and product. Each table such as customer is made up of several records for different customers and then each record is divided down further into fields or attributes which describe the characteristics of the customers such as name and address. Note that this example database is simplified and this structure only permits one product to be ordered when each order is placed. The reason for this restriction is that the database has not been fully normalised by breaking down the order table into separate order-header and order-line tables which then allow more than one product to be placed per order. The normalisation process is described in a later section.
If the data were entered into a database such as Microsoft Access, the tables and their records and fields would appear as in Figure 11.8. All three tables are shown. Fields and records for the product table are shown in Figure 11.9.
Figure 11.7 Diagram illustrating the tree-like structure used to structure data within a relational database. This example refers to the Clothez database. The fields are only shown for the first record in each table
Database Tables Records Fields
Sales order processing
Customer
Cust_id:1
Address: 1, Kedleston Rd Address: 1,
Kedleston Rd
Num: 01332 622 222
First name: Mary
Last name: Poole
Smith
Poole
Legg
Judd
Brown
Order
Order_id:1
Quantity: 3
Cust_id:6
Date: 1/3/99
2
1
3
4
5 Order fulfilled: Yes
Product
Product_id:1
Cost: £45
Shirt
Jeans
Suit
Wedding dress
Denim Jeans
Description: Jeans
M11_BOCI6455_05_SE_C11.indd 406 30/09/14 7:11 AM
407ChaPter 11 SYSTEMS DESIgN
A further term that needs to be introduced is key field. This is the field by which each record is referred, such as customer number. The key field provides a unique code such as ‘001’ or ‘993AXR’, comprising numbers or letters or both. It is required to refer to each record to help distinguish between different customers (perhaps three different customers called Smith). Key fields are also used to link different tables, as explained in the next section.
Source: Screenshot frame reprinted by permission from Microsoft Corporation
Figure 11.8 Clothez database in Microsoft Access
Figure 11.9 Product table showing records and fields
Source: Screenshot frame reprinted by permission from Microsoft Corporation
Key field
This is a field with a unique code for each record. It is used to refer to each record and link different tables.
The term relational is used to describe the way the different tables in a database are linked to one another. Key fields are vital to this. In recognition of the importance of key fields, Microsoft uses the key as the logo or brand icon for the Access database.
What makes an Access database relational?
M11_BOCI6455_05_SE_C11.indd 407 30/09/14 7:11 AM
Part 2 BUSINESS INFORMATION SYSTEMS DEVELOPMENT408
In the Clothez databases, the key fields are: Customer_id, Product_id and Order_id (id is short for identifier; reference (ref) or code number (num) could also be used for these field names). These fields are used to relate the three tables, as shown in Figure 11.10.
Figure 11.10 shows how the highlighted record in the order table (Order_id = 4) uses key fields to refer to the customer, Mary Poole, who has placed the order (Cust_id = 1) and the product (Shirt) she has ordered (Prod_id = 2).
To understand how the key fields are used to link different tables, two different types of fields need to be distinguished: primary and foreign keys.
Primary keys provide a unique identifier for each table which refers directly to the entity represented in the table. For example, in the product table, the primary key is Prod_id. There is only one primary key per table, as follows:
Customer table: Customer_id Order table: Order_id Product table: Prod_id
Foreign keys are used to link tables by referring to the primary key of another table. For example, in the order table, the foreign key Cust_id is used to indicate which customer has placed the order. The order table also contains Prod_id as a foreign key, but neither of the other tables has foreign keys. There may be zero, one or more foreign key fields per table.
Figure 11.11 shows how the primary key fields in the customer and product tables are used to link to their corresponding foreign keys (Cust_id and Prod_id) in the order table when constructing a query in Microsoft Access. This is a summary query which summarises the details of orders by taking data from each table. The result of the query is shown in Figure 11.12. The highlighted record in Figure 11.12 is the example relationship which was used to illustrate the links between tables in Figure 11.10.
Primary key fields
These fields are used to uniquely identify each record in a table and link to similar foreign key fields (usually of the same name) in other tables.
Figure 11.10 Clothez database in Microsoft Access, showing how the Order table is related to Customer and Product
Source: Screenshot frame reprinted by permission from Microsoft Corporation
M11_BOCI6455_05_SE_C11.indd 408 30/09/14 7:11 AM
409ChaPter 11 SYSTEMS DESIgN
Figure 11.11 Query design screen for the summary query in the Clothez database
Source: Screenshot frame reprinted by permission from Microsoft Corporation
Figure 11.12 Summary query for orders placed from Clothez database
Source: Screenshot frame reprinted by permission from Microsoft Corporation
Rules for identifying primary and foreign keys
1. Primary keys
■ The primary key provides a unique identifier for each record.
■ There is usually one primary key per table (unless a compound key of several fields is used).
■ The name of the field is usually the name of the entity or table followed by code, reference, identifier or id.
2. Foreign key
■ The foreign key always links to a primary key in another table(s).
■ There may be 0, 1 or several foreign keys in each table.
M11_BOCI6455_05_SE_C11.indd 409 30/09/14 7:12 AM
Part 2 BUSINESS INFORMATION SYSTEMS DEVELOPMENT410
A relatively straightforward aspect of database design is deciding on the field definitions. Fields need to be defined in terms of:
■ field name; ■ field data type; ■ field data size; ■ field validation rules.
These are defined when the database is created, since storage space for each field is pre- allocated in a database. During analysis and design, the field characteristics are managed in a data dictionary, often referred to as the metadata or ‘data about data’, particularly with reference to data warehouses (Chapter 4).
Let us now consider each of the characteristics of a field in more detail:
1. Field name. Field names should clearly indicate the content of the field. It is conventional in some databases to use underscores rather than spaces to define the name, since some databases may not recognise spaces (e.g. Order_fulfilled rather than Order fulfilled). In some databases the number of characters is restricted to eight, but this is now rare.
2. Field data type. Data types define whether the field is a number, a word, a date or a specialised data type. The main data types used in a database such as Microsoft Access are:
■ Number. Whole number or decimal. (Most databases recognise a range of numeric data types such as integer, real, double, byte, etc.)
■ Currency. This data type is not supported for all databases.
■ Text. Often referred to as character, string or alphanumeric. Phone numbers are of this data type, since they may need to include spaces or brackets for the area code.
■ Date. Should include four digits for the year! Can also include time.
■ Yes/No. Referred to as Boolean or true/false in other databases.
Key fields can be defined as either number or text.
3. Field data size. Field data sizes need to be pre-allocated in many databases. This is to help minimise the space requirements. Field size is defined in terms of the number of digits or characters which the designer thinks is required. For example, a user may define 20 characters for a first name and 40 characters for an address. It is better to overestimate than to risk having to modify the field later.
4. Field validation rule. Validation rules are necessary to check whether the user has entered valid data. Basic types of validation are:
■ Is field essential? For example, postcodes are usually mandatory to help identify a customer’s address.
■ Is field format correct? For example, postcodes or ZIP codes usually follow a set format.
■ Is value within range? For example, an applicant for a mortgage would have to be more than 18 years of age.
■ Does field match a restricted list? An entry for marital status might need to be ‘married’, ‘divorced’ or ‘single’. Restricted list choices can be defined in separate ‘lookup tables’.
Defining field data types and sizes
Data dictionary
A repository that is used to store the details of the entities of the database. It will define tables, relations and field details which are sometimes referred to as metadata or ‘data about data’.
M11_BOCI6455_05_SE_C11.indd 410 30/09/14 7:12 AM
411ChaPter 11 SYSTEMS DESIgN
To maintain data quality validation is an important, but sometimes neglected, aspect of detailed design which is covered in more detail in the section on input design below.
Table 11.2 shows how the field definitions for a table can be summarised. Note that setting the key fields to a field size of six allows a maximum number of customers of 999,999.
Normalisation is a design activity that is used to optimise the logical storage of data within a database. It involves simplification of entities and removal of duplication of data.
It is one of the most important activities that occurs during database design. The main purpose of data normalisation is to group data items together into database structures of tables and records which are simple to understand, accommodate change, contain a minimum of redundant data and are free of insertion, deletion and update anomalies. These anomalies can occur when a database is modified, resulting in erroneous and/ or duplicate data. These anomalies are explained in the next section. Since this activity should be conducted when all databases are designed, and since databases are so widely used in business applications, we consider the process of normalisation in some detail.
Normalisation is essentially a simplifying process that takes complex ‘user views’ of data (such as end-user, customer and supplier) and converts them into a well-structured logical representation of the data.
Normalisation has its origins in the relational data model developed by Dr E.F. Codd from 1970 onwards and is based on the mathematics of set theory. In this section we present a brief, straightforward explanation of the steps involved in normalising data, which can be applied to simple and complex data structures alike. The description of normalisation involves a series of stages which convert unnormalised data to normalised data. There are a series of intermediate stages which are referred to as first, second, third and fourth normal forms.
What is normalisation?
Normalisation
This design activity is a procedure that is used to optimise the physical storage of data within a database. It involves simplification of entities and minimisation of duplication of data.
Table 11.2 Definition of field details for the order table in the Clothez database (with fields added to show range)
Field name Field type Field size Validation rule Key field
Order_id Number 6 Mandatory Primary
Cust_id Number 6 Mandatory Foreign
Prod_id Number 6 Mandatory Foreign
Date_placed Date 10 Mandatory, must be valid date
Order_fulfilled Yes/No 3 Restricted, must be Yes/No
Special_instructions Text 120 Not mandatory
Total_order_value Currency 10 Not mandatory
Before commencing the steps of normalisation, it is worth providing some key definitions in order to simplify the flow of the following sections. These definitions are summarised in Table 11.3.
Some definitions
M11_BOCI6455_05_SE_C11.indd 411 30/09/14 7:12 AM
Part 2 BUSINESS INFORMATION SYSTEMS DEVELOPMENT412
Table 11.3 Summary of terms used to describe databases and normalisation
term Definition
Normalisation The process of grouping attributes into well-structured relations between records linked with those in other tables
Table Used to store multiple records of different instances of the same type of entity such as customer or employee
Relation A named, two-dimensional table of data. An equivalent term for ‘table’ used in normalisation
Attribute The smallest named unit in a database; other names include ‘data item’ and ‘field’
Update anomaly The inability to change a single occurrence of a data item in a relation without having to change others in order to maintain data
Insertion anomaly The inability to insert a new occurrence (record) into a relation without having to insert one into another relation first
Deletion anomaly The inability to delete some information from a relation without also losing some other information that might be required
Functional dependency A functional dependency is a relationship between two attributes and concerns determining which attributes are dependent on which other attributes: ‘attribute B is fully functionally dependent on attribute A if, at any given point in time, the value of A determines the value of B’ – this can be diagrammed as A ➝ B
Determinant An attribute whose value determines the value of another attribute
Primary key An attribute or group of attributes that uniquely identifies other non-key attributes in a single occurrence of a relation
Foreign key An attribute or group of attributes that can be used to link different tables; the foreign key will link to the primary key in another table
Composite key A key made up of more than one key within a relation
Candidate key A candidate key is a determinant that can be used for a relation; a relation may have one or more determinants; determinants can be either single attributes or a composite key
Unnormalised data are characterised by having one or more repeating groups of attributes. Many user views of data contain repeating groups. Consider a customer order form for the Clothez company (Figure 11.13): there might be such information as customer name, customer address and order date recorded at the top of the form; there might also be a section in the main body of the form that allows multiple items to be ordered.
It is possible to represent the user view described above in diagrammatic form which is equivalent to a physical database table. Note that the example in Figure 11.14 uses a subset of the information shown in the order form example.
The possibility of entering multiple lines into a single order form is clearly a repeat-ing group, i.e. order no. is being used to identify multiple order lines within the view and so, therefore, is not a unique determinant of each order line and its details.
It might also be argued that address also represents a repeating group, because there are two address lines. However, in practice a set number of address lines would be given a unique data name for each line and could be identified by a customer number. (Address is
Unnormalised data
M11_BOCI6455_05_SE_C11.indd 412 30/09/14 7:12 AM
413ChaPter 11 SYSTEMS DESIgN
an example of a non-repeating ‘data aggregate’, whereas the line details are an example of a repeating data aggregate.)
By constructing such a diagram, it becomes much easier to identify repeating groups of data and thus pave the way to progressing to first normal form (1NF).
Figure 11.13 Customer order form for the Clothez company
Name: Address:
Post code:
Mary Poole 1 Kedleston Road Derby DE22 1GB
Order date: Tel no:
5/3/99 01332 622 222
Line no 1
Product no 2
Product description Shirt
Quantity 1
Price £12.00
Order no: Cust no:
4 1
Figure 11.14 Repeating groups for the Clothez database
Cust name
Cust no
Cust addr
Tel no
Order date
Order no
Prod no
Prod des
Prod qty
Price
At this stage it is not obvious why repeating groups of data are a bad thing! If Figure 11.14 is transformed into a table, however, updating it could result in errors or inconsistencies. Each of the three different types of anomalies is now explained in turn with reference to Table 11.4.
Insertion anomaly
If it was desired to enter a new customer into the table, it would not be possible without having an order to enter at the same time.
Insertion/update/deletion anomalies
Table 11.4 Table with example data for the structure shown in Figure 11.14
Customer no.
Customer name
Customer address
tel no. Order date
Order no.
Product no.
Product des
Product qty
Price
1 Poole 1, Ked 01332 5/03/99 4 2 Shirt 1 12
2 Smith 2, The 01773 2/03/99 6 5 Denim 3 60
3 Legg 3, The 01929 2/03/99 2 4 Wedding 2 199
3 Poole 1, Ked 01332 3/03/99 5 3 Suit 1 115
Insertion anomaly
It is not possible to insert a new occurrence record into a relation (table) without having to also insert one into another relation first.
M11_BOCI6455_05_SE_C11.indd 413 30/09/14 7:12 AM
Part 2 BUSINESS INFORMATION SYSTEMS DEVELOPMENT414
Update anomaly
An update anomaly indicates that it is not possible to change a single occurrence of a data item (a field) in a relation (table) without having to make changes in other tables in order to maintain the correctness of data.
If a customer such as ‘Poole’ had several orders in the table and that customer moved to a new address, all the entries in the table where that customer appeared would have to be updated if inconsistencies were not to appear.
Deletion anomaly
A deletion anomaly indicates it is not possible to delete a record from a relation without also losing some other information which might still be required.
If a customer such as ‘Smith’ had only one order in the table and that table entry were deleted, information about the customer would also be deleted.
The way to get round some of these problems is by normalising the data. Stage one of this process is the removal of repeating groups of data, i.e. proceeding to first normal form (1NF).
Update anomaly
It is not possible to change a single occurrence of a data item (a field) in a relation (table) without having to change others in order to maintain the correctness of data.
Deletion anomaly
It is not possible to delete a record from a relation without also losing some other information which might still be required.
First normal form (1NF)
Transforming unnormalised data into its first normal form state involves the removal of repeating groups of data.
In the example above, the repeating group comprises product number, product quantity and price. Removing these attributes into a separate table will not suffice, however. For example, how could each entry in the newly created table be related to the order to which it is attached? The answer lies in including a linking attribute (also known as a ‘foreign key’, as described earlier in the chapter) which is present in both the modified table and the new table. In this case, a sensible attribute to use would be order number. The first step in normalisation has thus resulted in the transformation of one table into two new ones. The two new tables are shown in Figure 11.15. The example shows the relationship between fields at the top and example records below.
Removing insertion/update/deletion anomalies
Even though repeating groups have been removed by splitting the unnormalised data into two tables (relations), anomalies of all three types still exist.
Insert anomaly
■ In the customer/order relation, an order cannot be entered without also entering the customer’s name and address details, even though they may already exist on another order; a customer cannot be added if there is no order to be placed.
■ In the order/product relation, an item cannot be added without also adding an order for that item.
Update anomaly
■ In the customer/order relation, a customer’s name and address details cannot be amended without needing to amend all occurrences (where the customer has more than one order).
■ In the order/product relation, an item description could appear on many order lines for many different customers – if the description of the item were to change, all occurrences
First normal form (1NF)
M11_BOCI6455_05_SE_C11.indd 414 30/09/14 7:12 AM
415ChaPter 11 SYSTEMS DESIgN
where that item appeared would have to be changed if database inconsistencies were not to appear.
Deletion anomaly
■ In the customer/order relation, an order cannot be deleted without also deleting the customer’s details.
■ In the order/product relation, an order line cannot be deleted without also deleting the item number and description.
Figure 11.15 The revised table structure and example data for two tables
Order no
Prod no
Prod des
Prod qty
Price
24
Cust name
Cust no
Cust addr
Tel no
Order date
Customer/order relation
Order no
Prod no
Prod des
Prod qty
Price
Order no
Primary key Foreign key
Order/product relation
Cust name
Cust no
Cust addr
Tel no
Order date
Order no
Customer/order relation
Poole1 1 Ked 01332 5/03/99 4
Smith2 2 The 01773 2/03/99 6
Legg3 3 The 01929 2/03/99 2
Poole3 1 Ked 01332 3/03/99 5
Shirt
56 Denim
42 Weddin
35
1 12
3 60
2 199
1 115Suit
Order/product relation
This activity shows a prototype database that has been produced by an employee of a toy manufacturer relating to its customers and sales activities. The designer, a business user, is not aware of the need for normalisation and has stored all the data in a single table. This has resulted in some fields like customer number and customer address repeating unnecessarily.
Identification and removal of insertion, deletion and update anomaliesActivity 11.1
➨
M11_BOCI6455_05_SE_C11.indd 415 30/09/14 7:12 AM
Part 2 BUSINESS INFORMATION SYSTEMS DEVELOPMENT416
Customer no.
Customer name
Customer address
Order no.
Product code
Product description
Quantity ordered
Price per item
total cost
Order date
Salesperson no.
100 Fred's Toys
7 High Street
10001 324 Action Ma 3 13.46 40.38 7/10/99 007
100 Fred's Toys
7 High Street
10001 567 Silly Dog 6 5.15 30.9 7/10/99 007
100 Fred's Toys
7 High Street
10001 425 Slimy Hand 12 1.39 16.68 7/10/99 007
100 Fred's Toys
7 High Street
10001 869 Kiddy Doh 4 0.68 2.72 7/10/99 007
200 Super Toys
25 West Mall
13001 869 Kiddy Doh 12 0.68 8.16 7/17/99 021
200 Super Toys
25 West Mall
13001 637 Risky 3 17.42 52.26 7/17/99 021
200 Super Toys
25 West Mall
13001 567 Silly Dog 2 32.76 43.52 7/17/99 021
300 Cheapo Toys
61 The Arcade
23201 751 Diplomat 24 5.15 123.6 6/21/99 007
QUESTIONS
1. Identify an insertion anomaly which might cause a problem when adding a new product to the range.
2. Identify two deletion anomalies which would occur if Cheapo Toys cancelled its order and a record was removed.
3. Identify an update anomaly if the product Silly Dog was renamed Fancy Dog.
4. How could the table be split up to remove the anomalies? Define the fields which would be placed in each table and define the foreign keys which would be used to link the tables.
Second normal form (2NF) states that ‘each attribute in a record (relation) must be functionally dependent on the whole key of that record’. To continue the normalisation process to second normal form, it is necessary to explore further some of the terms defined in the introductory section.
Functional dependencies
Within each of the relations produced above, a set of functional dependencies exists. These dependencies will be governed by the relationships that exist between different data items, which in turn will depend on the ‘business rules’, i.e. the purposes for which data are held and how they are used.
Once the functional dependencies have been established, it is then possible to select a candidate key for the relation.
Candidate keys
The process of analysing the functional dependencies within a relation will reveal one or more possible candidate keys – a candidate key is the minimum number of determinants (key fields) which uniquely determines all the non-key attributes. Consider the following record:
Second normal form (2NF)
Second normal form (2NF)
Second normal form states that ‘each attribute in a record (relation) must be functionally dependent on the whole key of that record’.
It is anomalies of this kind which indicate that the normalisation process needs to be taken a step further – that is, we must now proceed to second normal form (2NF).
M11_BOCI6455_05_SE_C11.indd 416 30/09/14 7:12 AM
417ChaPter 11 SYSTEMS DESIgN
An example
Consider the following record. Note that this example is different from that given in first normal form, since it illustrates the principles better.
The functional dependencies are as follows:
Part no and supplier no → Price Supplier no → Supplier name Supplier no → Supplier details
A possible candidate key might be thought to be supplier number. However, supplier number alone cannot be a determinant of price, since a supplier may supply many items.
Similarly, part number alone cannot be a determinant of price, because a part may be supplied by many different suppliers at different prices.
The candidate key is, therefore, a composite key comprising part number and supplier number.
We can express this more clearly by employing a dependency diagram (Figure 11.16). Two additional properties relating to candidate keys can now be introduced:
1. For every record occurrence, the key must uniquely identify the relation. 2. No data item in the key can be discarded without destroying the property of unique
identification.
The dependency diagram in Figure 11.16 indicates a number of problems:
■ If supplier number is discarded, it will no longer be possible to identify the remaining attributes uniquely, even though part number remains.
■ Details of a supplier cannot be added until there is a part to supply; if a supplier does not supply a part, there is no key.
■ If supplier details are to be updated, all records which contain that supplier as part of the key must be accessed – i.e. there are redundant data.
This situation is known as a partial key dependency and is resolved by splitting the record into two or more smaller records (Figure 11.17).
Part Supplier Supplier Supplier Price
No No Name details
Figure 11.16 Example of a dependency diagram for supplier example
Supplier no
Part no
Supplier name
Supplier details
Price
Figure 11.17 Revised dependency diagram for supplier example
Supplier no
Supplier name
Supplier details
Supplier no
Part no
Price
M11_BOCI6455_05_SE_C11.indd 417 30/09/14 7:12 AM
Part 2 BUSINESS INFORMATION SYSTEMS DEVELOPMENT418
A record is, therefore, in at least second normal form when any partial key dependencies have been removed.
Removing insertion/update/deletion anomalies
Consider the record structure shown in Figure 11.18. If it is assumed that an employee only works on one project at a time; then employee number is a suitable candidate key, in that all other attributes can reasonably be said to be fully functionally dependent on it.
Note: the record is already in second normal form because there is only one key attribute (therefore partial key dependencies cannot exist). However, some problems still exist:
■ Insertion anomaly: before any employees are recruited for a project, the completion date for the project cannot be recorded because there is no employee record.
■ Update anomaly: if a project completion date is changed, it will be necessary to search all employee records and change those where an employee works on that project.
■ Deletion anomaly: if all employees are deleted for a project, all records containing a project completion date would be deleted also.
To resolve these anomalies, a record in second normal form must be converted into a number of third normal form records.
Figure 11.18 Example of a structure diagram – employee details
Employee name
Employee no
Salary Project no
Completion date
Transitive dependency
A data item that is not a key (or part of a key) but which itself identifies other data items is a transitive dependency.
Third normal form (3NF): a record is in third normal form if each non-key attribute ‘depends on the key, the whole key and nothing but the key’.
An example
Consider the previous example. To convert the record into two third normal form records, any transitive dependencies must be removed. When this is done the result is the two records in Figure 11.19.
Third normal form (3NF)
Third normal form (3NF)
A record is in third normal form if each non- key attribute ‘depends on the key, the whole key and nothing but the key’.
Figure 11.19 Dependency diagram for employee example and revised structure
Employee name
Employee no
Salary Project no
Completion date
Employee name
Employee no
Salary Project no
Completion date
Project no
M11_BOCI6455_05_SE_C11.indd 418 30/09/14 7:12 AM
419ChaPter 11 SYSTEMS DESIgN
Removing insertion/update/deletion anomalies
If a record has only one candidate key and both partial key and transitive dependencies have been removed, then no insertion, update or deletion anomalies should result.
However, if a record has more than one candidate key problems can still arise. In this situation we can take the normalisation process still further.
Further normalisation may be necessary for some applications. In these normalisation can proceed to the fourth and fifth normal forms. These are described in Hoffer et al. (2013): ‘In 4NF multi-valued dependencies are removed. A multi-valued dependency exists when there are at least three attributes in a relation and for each value of A there is a well-defined set of values of B and a well-defined set of values of C. However, the set of values of B is independent of set C and vice versa.’
In 5NF it is necessary to account for the potential of decomposing some relations from an earlier stage of normalisation into more than two relations. In most practical applications, decomposition to 3NF gives acceptable database performance and is often easier to design and maintain.
Fourth normal form (4NF) and fifth normal form (5NF)
As well as the logical design of the database there are aspects of physical database design that should be taken into account. These are specialised functions performed by a database administrator or DBA. A company which does not employ a specialist risks a poor performance system or, worse still, a loss or corruption of data. These design and database implementation tasks include:
1. Design of optimal database performance. Use of specialist techniques such as indexes or stored procedures will accelerate the display of common user views such as a list of all customer orders. Queries can also be optimised, but this is mainly performed automatically by the database engines such as Oracle, Microsoft SQL Server or Informix. To verify the design is good, volume testing is essential to ensure that the system can cope with the number of transactions that will occur.
2. Designing for multi-user access. When defining a new system, it is important to consider what happens when two users want to access the same data, such as the same customer record. If access to records is unlimited, then there will be anomalous data in the database if users save data about the customer at a similar time. Since multi-user access will not be frequent, the best method for dealing with it will be to implement record locking. Here, the first user to access a record will cause the database to restrict subsequent users to read-only access to the record rather than read–write. Subsequent users should be informed that a lock is in place and access is read-only.
Other significant database design issues
This activity builds on the ABC case study from Chapter 10. It is not necessary to have completed the Chapter 10 exercise to be able to undertake this one. You should use the extract in Chapter 10 describing ABC and in particular the paper forms of the existing system to identify which fields are required in the database.
Database design exercise using the ABC case studyActivity 11.2
➨
M11_BOCI6455_05_SE_C11.indd 419 30/09/14 7:12 AM
Part 2 BUSINESS INFORMATION SYSTEMS DEVELOPMENT420
QUESTIONS
1. Either:
(a) Use normalisation to third normal form to identify tables and fields for an ABC database; or
(b) Assume the following entities for the ABC database:
■ customer details; ■ salesperson details; ■ sales order header details; ■ sales order line details; ■ item details.
2. For each table in the database, define details of:
■ table names; ■ primary and foreign key fields for each table; ■ name of each field; ■ data type of each field; ■ size of each field; ■ any validation rules which may apply to each field (e.g. a limit on maximum price or
quantity etc.).
You may find it most efficient to summarise the database definition using a table (in your word processor).
3. Planning for failed transactions. Recovery methods can be specified in the design for how to deal with failed transactions which may occur when there is a software bug or power interruption. Databases contain the facility to ‘roll back’ to the situation before a failure occurred.
4. Referential integrity. The database must be designed so that when records in one table are deleted, this does not adversely affect other tables. Impact should be minimal if normalisation has occurred. Sometimes it is necessary to perform a ‘cascading delete’, which means deleting related records in linked tables.
5. Design to safeguard against media, hardware or power failure. A backup strategy should be designed to ensure that minimal disruption occurs if the database server fails. The main design decision is whether a point-in-time backup is required or whether restoring to the previous day’s data will be sufficient. Frequently, a point- in-time backup will be required. Of course, a backup strategy is not much use if it cannot be used to restore the data, so backup and recovery must be well tested. To reduce the likelihood of having to fall back on a backup, using a fault-tolerant server is important. Specifying a server with an uninterruptible power supply, disk mirroring or RAID level 2 is essential for any corporate system. The frequency of archiving also will be specified.
6. Replication. Duplication and distribution of data to servers at different company locations and for mobile users is supported to different degrees by different database vendors.
7. Database sizing. The database administrator will size the database and perform capacity planning to ensure that sufficient space is available on the server for the system to remain functional.
8. Data migration. Data migration will occur at the system build phase, but it must be planned for at the design stage. This will involve an assessment of the different data sources which will be used to populate the database.
M11_BOCI6455_05_SE_C11.indd 420 30/09/14 7:12 AM
421ChaPter 11 SYSTEMS DESIgN
Most modern information systems use relational database management systems (RDBMS) for the storage of data. RDBMS provide management facilities which means that programmers or users do not have to become directly involved with file management. Because of this, most business users will not hear these terms unless eavesdropping on systems designers and this section is therefore kept brief. How-ever, some older systems and large-scale transaction processing systems requiring superior performance do not use RDBMS for data storage.
DESIGN OF INPUT AND OUTPUT
File-based systems are alternatives to database systems which are traditionally used for accessing data from a file directly from program code rather than a database query. Note though that when databases are designed, these are themselves made up of many files from which data are accessed directly. Database users and database programmers are shielded from this complexity. Designers will specify systems that access data that are stored in a file using two main methods:
1. Sequential access. The program reading or writing a file will start processing the file record by record (usually from the beginning). Sequential access is often used when batch processing a file which involves processing each record. Sequential file access involves reading or writing each record in a file in a set order.
2. Direct (random) access. Access can occur to any point (record) in the file without the need to start at the beginning. Direct access is preferable when finding a subset of records such as in a query, since it is much faster. Random or direct file access allows any record to be read or written.
File access methods
Sequential and random or direct file access methods
Sequential file access involves reading or writing each record in a file in a set order. Random or direct file access allows any record to be read or written.
To enable rapid retrieval of data in a random access file (and also a database table), it is conventional to use an index which will find the location of the record more rapidly. These files are sometimes referred to as ‘indexed sequential files’. A file index is an additional file that is used to ‘point’ to records in a direct access file for more rapid access. An index file for a customer file would contain two fields only for each record – the indexed item such as a customer number and the number of the record in the parent file (also known as the ‘offset’ or ‘pointer’) which contains details on this customer.
Indexing
Index
A file index is an additional file which is used to ‘point’ to records in a direct access file for more rapid access.
In transaction processing systems which use standard native files accepted directly by programs for processing rather than through the operating system rather like RDBMS, there are additional terms that are used to describe the types of files. These types include:
1. The master file. This is used to store relatively static information that does not change frequently. An example would be a file containing product details.
2. The transaction file. This contain records of particular exchanges, usually related to a transaction such as a customer placing an order or an invoice being produced. This file has records added more frequently.
File descriptions
M11_BOCI6455_05_SE_C11.indd 421 30/09/14 7:12 AM
Part 2 BUSINESS INFORMATION SYSTEMS DEVELOPMENT422
3. Archive file. To reduce storage requirements and improve performance, transactions that occurred some time ago to which businesses are unlikely to wish to refer are removed from the online system as an archive which is usually stored on a tape or optical disk. It will be still available for reference, but access will be slower.
4. Temporary files. These provide temporary storage space for the system which might be used during batch processing, when comparing data sets for example. The information would not be of value to a business user.
5. Log file. The log file is a system file used to store information on updates to other files. Its information would not be of value to a business user.
Table 11.5 Methods of file organisation
Organisation method
access method application Brief description
Sequential Sequential Batch process of a customer master file
An ordered sequential access file, e.g. ordered by customer number
Serial Sequential A sequential access file, but without any ordering
random Random + index Querying data for decision support; unsuitable for frequent updates due to overhead of updating index
Organisation is provided by index
Indexed sequential
Sequential + index Querying data for decision support and sequential batch processing
Best compromise between methods above
Information can be organised in file-based systems in a variety of ways, which are not of general relevance to the business user, so the terms are only summarised in tabular form (Table 11.5). Note that the indexed-sequential technique offers the best balance between speed of access to individual records and for achieving updates.
File organisation
When designing information processing systems, designers have to decide which is the more appropriate method for handling transactions:
■ Batch – data are ‘post-processed’ after collection, usually at times of low system workload.
■ Real-time or online processing – data are processed instantaneously on collection.
Table 11.6 compares the merits of batch and real-time systems according to several criteria.
There is a general trend from batch systems to real-time processing, but it can be seen from the table that batch processing is superior in some areas, not least cost. For a system such as a national lottery, a real-time system must be used, but it is expensive to set up the necessary infrastructure.
Batch and real-time processingBatch system
A batch system involves processing of many transactions in sequence. This will typically occur over some time after the transactions have occurred.
Real-time system
In a real-time system processing occurs immediately data are collected. Processing follows each transaction.
M11_BOCI6455_05_SE_C11.indd 422 30/09/14 7:12 AM
423ChaPter 11 SYSTEMS DESIgN
Batch systems are still widely used, since they are appropriate for data processing before analysis. For example, batch processing is used in data warehousing when transferring data from the operational system to the warehouse (Chapter 6). A batch process can be run overnight to transfer the data from one location to another and to perform aggregation such as summing sales figures across different market or product segments.
Table 11.6 A comparison of batch and real-time data processing
Factor Batch real-time
Speed of delivery to information user
Slower – depends on how frequently batch process is run – daily, weekly or monthly
Faster – effectively delivered immediately
ability to deal with failure Better – if a batch process fails overnight there is usually sufficient time to solve the problem and rerun the batch
Worse – when a real-time system is offline there is major customer disruption and orders may be lost
Data validation Worse – validation can occur, but it is time-consuming to correct errors
Better – validation errors are notified and corrected immediately
Cost Better – performance is less critical, so cheaper hardware communications can be purchased
Worse – high-specification databases and infrastructure are necessary to achieve the required number of transactions per second
Disruption to users when data processing needs to be performed
Better – can occur in slack periods such as at weekends or overnight
Worse – can disrupt customers if time-consuming calculations occur as each record is processed
USER INTERFACE DESIGN
The design of the user interface is key to ensuring that information systems are easy to use and that users are productive. User interface design involves three main parts: first, defining the different views of the data such as input forms and output tables; second, defining how the user moves or navigates from one view to another; and, third, providing options for the user.
Each module can be broken down into interface elements such as forms which are used to enter and update information such as a customer’s details, views which tabulate results as a report or graphically display related information such as a ‘to-do’ list and dialogs which are used for users to select options such as a print options dialog box. Menus provide selection of different options. Figure 11.20 gives an example of these different interface components.
User interface design is a specialist field which is the preserve of graphic designers and psychologists. This field is often known as human–computer interaction (HCI) design. HCI involves the study of methods for designing the input and output of information systems to ensure they are ‘user-friendly’. It is covered well in Rogers et al. (2011) and Yeates and Wakefield (2003). Many of the design parameters can be assisted by a knowledge of HCI.
Form
An on-screen equivalent of a paper form which is used for entering data and will have validation routines to help improve the accuracy of the entered data.
Data views
Different screens of an application which review information in a different form such as table, graph, report or map.
Dialog
An on-screen window (box) which is used by a user to input data or select options.
Menu
Provides user selection of options for different application functions.
Human–computer in- teraction (HCI) design
HCI involves the study of methods for designing the input and output of information systems to ensure they are ‘user-friendly’.
M11_BOCI6455_05_SE_C11.indd 423 30/09/14 7:12 AM
Part 2 BUSINESS INFORMATION SYSTEMS DEVELOPMENT424
This ‘Focus on’ looks at a number of issues relating to web site design. The intention is not to give an in-depth explanation of web site design specifics, but rather to look at those elements that go to make up a well-designed web site.
Cox and Dale (2002) identify a number of key quality factors that help to create web sites that meet customer needs and expectations. These include:
■ Clarity of purpose – it must be clear to the customer whether the site is providing just information or whether it enables the customer to make transactions online; the information should be clearly and logically organised and clear instructions should be provided directly from the home page to avoid confusion and frustration.
■ Design – a key objective here is to ensure that the image that the company is appropriately projected and that the customer will remember and return to site. Specific design factors include: links – valid links are needed to enable a customer to navigate around the web site and should readily enable easy navigation between the pages that the customer is most likely to want to view; consistency, menus and site maps – since web sites vary considerably from site to site, it is important for any one site to be internally consistent so that the same procedures occur for similar or related things wherever the user may be within the site; the use of such features as site maps, menus and a ‘home page’ button on every page can help guide the user around the site; pages, text and clicks – it is suggested the pages on a web site should ideally be short, or where this is not feasible, headings and paragraphs and other navigation aids (e.g. a button to scroll to the top of the page; for web sites that enable customer transactions, customers
Figure 11.20 Microsoft Access showing key elements of interface design
Source: Screenshot frame reprinted by permission from Microsoft Corporation
WEB SITE DESIGN FOR B2C E-COMMERCEFOCUS ON…
M11_BOCI6455_05_SE_C11.indd 424 30/09/14 7:12 AM
425ChaPter 11 SYSTEMS DESIgN
should be able to make purchases quickly with minimum pages in the checkout process; communication and feedback – in essence, the user needs to be advised what is happening inside the system in response to their interaction (e.g. confirming order details, or informing the user of a mistake by writing the information in red next to the relevant box or area); in addition, the use of graphics should be such that web page loads are not slowed down (not all users have broadband!) and that animations should not distract users from the content of the page and the information they are looking for; search – search mechanisms to navigate a web site are one of the first strategies used by customers to a web site, often before they use links and menus; therefore search tools should cover the whole site and return the search findings in order of relevance; fill-in forms – the layout of forms for personal detail entry (e.g. for site registration and ordering) should be self-explanatory and relevant to the nationality of the customers using the web site.
■ Accessibility and speed – this refers to the ability for customers to access and navigate an organisation’s web site; factors here include the speed of the home-page download, the accessibility of the web site 24 hours a day, 7 days a week, 365 days of the year and the availability of sufficient bandwidth to cope with customer demand at peak periods.
■ Content – this refers to the information that an organisation is actually offering through its web site; important factors here include selection (the range of products and services on offer and the ease with which they can be found by the customer; product/ service information and availability including a clear picture with all the necessary information on brand, size, colour, capabilities and price so that the customer is not misled together with a clear statement of stock availability so that the customer knows before ordering whether an item is in stock; delivery information – this should be made accessible from the home page or with the product information so that customers are aware of the prices; in addition, customers should also be made aware of probable delivery times and any delays that may occur (e.g. during peak periods); policies, charges, terms and conditions – customers should be aware of all the company terms and conditions before committing to a purchase; security and reliability – lack of security is one of the main barriers to customers shopping online and so it is crucial that a B2C e-commerce web site offers a secure payment method online (either directly or through a third party).
■ Customer service – customer service plays an important part in delivering service quality to the customer and since face-to-face interaction is non-existent in e-commerce transactions, services such as ‘call-u-back’ during office hours and e-mailing queries are needed (contact details should be on every page of the web site and not just on the home page and during the transaction process); frequently asked questions (FAQ) arranged by topic can also help to guide the customer.
■ Customer relationships – the key to success for B2C e-commerce is to attract and retain customers that use the site and keep returning to make purchases: recognition – by asking customers to fill in a user ID (research suggests that it is simpler for customers if they are asked to use their e-mail address as their ID) an organisation can tailor the web sites to a particular customer; it also means that customer information such as the billing and shipping addresses do not have to be filled in again; customer feedback platforms – features such as product reviews (ebuyer.com is a good illustration of this) helps to create a community for customers and is more likely to lead to enhanced customer loyalty; frequent buyer incentives – these can include discounts, free delivery or benefits of promotions; extra services – examples here include a currency conversion rate mechanism on those sites engaging in international B2C e-commerce, extra or related information on the products being sold, links to other partner sites, and those that aid the customer in buying or finding the right product.
M11_BOCI6455_05_SE_C11.indd 425 30/09/14 7:12 AM
Part 2 BUSINESS INFORMATION SYSTEMS DEVELOPMENT426
Huang et al. (2006) in an analysis of web features and functions identify a number of factors that can impact positively on the customer experience. These clearly overlap with a number of those given above and include:
■ speeding up online tasks ■ establishing multiple communication channels ■ providing suitable access to contacts ■ making the web site personal ■ provision of company information and advertising online ■ facilitation of customer feedback ■ the ability of customers to control information detail.
Cao et al. (2005) also point out that the features that go to make for a good customer experience also have implications for web interface design. For example, in addition to the software considerations, the capabilities of the hardware (both the organisation’s and the customer’s) need to be taken account of (e.g. page loading times).
INPUT DESIGN
User interface design can also be subdivided into input design and output design, but these terms are used more generally to refer to all methods of data entry and display, so they warrant a separate section.
Input design includes the design of user input through on-screen forms, but also other methods of data entry such as import by file, transfer from another system or specialised data capture methods such as bar-code scanning and optical or voice recognition techniques.
Data input design involves capturing data that have been identified in the user requirements analysis via a variety of mechanisms. These have been described earlier (in Chapter 3) and include:
■ keyboard – the most commonly used method; ■ optical character recognition and scanning; ■ voice input; ■ directly from a monitoring system such as a manufacturing process, or from a phone
system when a caller line ID is used to identify the customer phoning and automatically bring their details on screen;
■ input from a data file that is used to store data; ■ import of data from another system via a batch process (for example a data warehouse
will require import of data from an operational system).
Input design
Input design includes the design of user input through on-screen forms, but also other methods of data entry such as import by file, transfer from another system or specialised data capture methods such as bar-code scanning and optical or voice recognition techniques.
One of the key elements in input by all these methods is ensuring the quality of data. This is achieved through data validation. This is a process to ensure the quality of data by checking they have been entered correctly; it prompts the user to inform them of incorrect data entry.
Validation is important in database systems and databases usually supply built-in input validation as follows:
■ Data type checking. When tables have been designed, field types will be defined such as text (alphanumeric), number, currency or date. Text characters will not be permitted in
Data validation
Data validation
Data validation is a process that ensures the quality of data by checking they have been entered correctly.
M11_BOCI6455_05_SE_C11.indd 426 30/09/14 7:12 AM
427ChaPter 11 SYSTEMS DESIgN
a number field and when a user enters a date, for example, the software will prompt the user if it is not a valid date.
■ Data range checking. Since storage needs to be pre-allocated in databases, designers will specify the number of digits required for each field. For example, a field for holding the quantity of an item ordered would typically only need the range 1–999. So three digits are required. If the user made an error and entered four digits, then they would be warned that this was not possible.
■ Restricted value checking. This usually occurs for text values that are used to describe particular attributes of an entity. For example, in a database for estate agents, the type of house would have to be stored. This would be a restricted choice of flat, bungalow, semi-detached, etc. Once the restricted choices have been specified, the software will ensure that only one of these choices is permitted, usually by prompting the user with a list of the available alternatives.
Some additional validation checks may need to be specified at the design phase which will later be programmed into the system. These include:
■ Input limits. This is another form of range checking when the input range cannot be specified through the number of digits alone. For example, if the maximum number of an item that could be ordered is 5, perhaps because of a special offer, this would be specified as a limit of 1–5. Note that the user would not be permitted to enter 0.
■ Multiple field validation. If there are business rules that mean that allowable input is governed by more than one field, then these rules must be programmed in. For example, in the estate agent database, there could be a separate field for commission shown as a percentage of house price, such as 1.5 per cent, and a separate field showing the amount, such as £500. In this situation the programmer would have to write code that would automatically calculate the commission amount depending on the percentage entered.
■ Checksum digits. A checksum involves the use of an extra digit for ensuring the validity of long code numbers. The checksum digit is calculated from an algorithm involving the numbers in the code and their modulus (by convention modulus 11). These can be used to ensure that errors are not made in entering long codes such as a customer account number (although these would normally be generated automatically by the computer). They are often used in bar codes.
Checksum digits
A checksum involves the use of an extra digit for ensuring the validity of long code numbers. The checksum digit is calculated from an algorithm involving the numbers in the code and their modulus (by convention modulus 11).
The checksum digit is calculated using the modulus of the weighted products of the number, as follows:
1. Code number without check digit 5 293643.
2. Calculate the sum of weighted products by multiplying the least significant digit by 2, the next by 3 and so on. For this example:
(7 3 2) 1 (6 3 9) 1 (5 3 3) 1 (4 3 6) 1 (3 3 4) 1 (2 3 3) 5 14 1 54 1 15 1 24 1 12 1 6 5 125
3. Remainder when sum divided by 11 (modulus 11) 5 125/11 5 11 remainder 4.
4. Subtract remainder from 11 to find check digit (1124) 5 7. (If the remainder is 0, check digit is 0; if 1, check digit is X.)
5. New code number with check digit 5 2936437.
Checksum digits exampleActivity 11.3
M11_BOCI6455_05_SE_C11.indd 427 30/09/14 7:12 AM
Part 2 BUSINESS INFORMATION SYSTEMS DEVELOPMENT428
Output design specifies how production of on-screen reports and paper-based reports will occur. Output may occur to database or file for storing information entered or also for use by other systems.
Output data are displayed by three methods:
1. They may be directly displayed from input data. 2. They may be displayed from previously stored data. 3. They may be derived data that are produced by calculation.
Design involves specifying the source of data (which database tables and fields map to a point on the report), what processing needs to occur to display data such as aggregation, sorting or calculations, and the form in which the information will be displayed – graph, table or summary form.
Output design is important for decision support software to ensure that relevant information can be chosen, retrieved and interpreted as easily as possible. Given that output design involves these three factors, it will also relate to input design (to select the report needed) and database design (to retrieve the information quickly).
OUTPUT DESIGN
Output design
Output design involves specifying how production of on-screen reports and paper-based reports will occur. Output may occur to database or file for storing information entered or also for use by other systems.
DESIGNING INTERFACES BETWEEN SYSTEMS
A major challenge for the designer of today’s systems is systems integration. Systems integration includes both linking the different modules of a new system together and linking the new system with existing systems often known as ‘legacy systems’. For applications that span a whole organisation this challenge is referred to as enterprise application integration (EAI). Designing how the systems interoperate involves consideration of how data are exchanged between applications and how one application controls another. A special class of software, middleware or messaging software, is used to achieve this control and data transfer. In a banking system, middleware is used to transfer data between an online banking service and a legacy account system. For example, if a user wishes to transfer money from one account to another using a web-based interface this web application must instruct the legacy system to make the transfer. The web-based interface will also need to access data from the legacy system on the amount of money available in the accounts. This illustrates the role of middleware in control messaging and data transfer messaging.
XML (eXtensible Markup Language) is a standard that has been widely adopted for the transfer of information between e-business systems. XML is increasingly used to share data between partners. For example, Chem eStandards, is an XML standard for the chemical industry, which covers 700 data elements and 47 transactions and is sponsored by the Chemical Industry Data Exchange (CIDX, www.cidx.org). A more widely applicable application of XML is ebXML (www.ebxml.org). One application developed using ebXML is to enable different accounting packages to communicate with online order processing systems. For designers to ensure future flexibility of their systems it is important to ensure that interfaces with external systems can support different XML data exchange standards.
Enterprise application integration (EAI)
The process of designing software to facilitate communications between business applications including data transfer and control.
Middleware
Software used to facilitate communications between business applications including data transfer and control.
XML (eXtensible Markup Language)
A standard for transferring structured data, unlike HTML which is purely presentational.
DEFINING THE STRUCTURE OF PROGRAM MODULES
The detailed design may include a definition for programmers, indicating how to structure the code of the module. The extent to which this is necessary will depend on the complexity of the module, how experienced the programmer is and how important it is to document
M11_BOCI6455_05_SE_C11.indd 428 30/09/14 7:12 AM
429ChaPter 11 SYSTEMS DESIgN
the method of programming. A safety-critical system (Chapter 4) will always be designed in this detail before coding commences. Structured English is one of the most commonly used methods of defining pro-gram structure. Standard flow charts can be used, but these tend to take longer to produce.
Structured English
A technique for producing a design specification for programmers which indicates the way individual modules or groups of modules should be implemented.
Structured English is a technique for producing a design specification for programmers which indicates the way individual modules or groups of modules should be implemented. It is more specific than a flow chart. It uses keywords to describe the structure of the program, as shown in the example box. Structured English is sometimes known as ‘pseudocode’ or ‘program design language’. Data action diagrams use a similar notation.
Structured English has the disadvantage that it is very time-consuming to produce a detailed design. But it has the advantage that to move from here to coding is very straightforward and the likelihood of errors is reduced.
Structured English
Example: Structured English This example moves through each record of a database table totalling all employees’ salaries. (Note that this could be accomplished more quickly using an SQL statement.)
DO WHILE NOT end of table
IF hoursrworked> basicrhours
SET pay = (hours*basicrrate) + (overtimerhours*overtimerrate)
ELSE
SET pay = (hours*basicrrate)
END if
SET totalrpay = totalrpay + pay
move to next record
ENDDO
SECURITY DESIGN
Data security is, of course, a key design issue, particularly for information systems that contain confidential company information which is accessed across a wide-area network or the Internet. The four main attributes of security which must be achieved through design are:
1. Authentication ensures that the sender of the message, or the person trying to access the system, is who they claim to be. Passwords are one way of providing authentication, but are open to abuse – users often tend to swap them. Digital certificates and digital signatures offer a higher level of security. These are available in some groupware products such as Lotus Notes.
2. Authorisation checks that the user has the right permissions to access the information that they are seeking. This ensures that only senior personnel managers can access salary figures, for example.
M11_BOCI6455_05_SE_C11.indd 429 30/09/14 7:12 AM
Part 2 BUSINESS INFORMATION SYSTEMS DEVELOPMENT430
3. Privacy – in a security context, privacy equates to scrambling or encryption of messages so that they cannot easily be decrypted if they are intercepted during transmission. Credit card numbers sent over the Internet are encrypted in this way.
4. Data integrity – security is also necessary to ensure that the message sent is the same as the one received and that corruption has not occurred. A security system can use a checksum digit to ensure that this is the case and the data packet has not been modified.
Data must also be secure in the sense of not being subject to deletion, or available to people who don’t have the ‘need to know’. Methods of safeguarding data are covered in more detail in later (in Chapter 15).
DESIGN TOOLS: CASE (COMPUTER-AIDED SOFTWARE ENGINEERING) TOOLS
CASE (computer-aided software engineering) tools are software that helps the systems analyst and designer in the analysis, design and build phases of a software project. They provide tools for drawing diagrams such as entity relationship diagrams (ERDs) and storing information about processes, entities and attributes.
CASE tools are primarily used by professional IS developers and are intended to assist in managing the process of capturing requirements, and converting these into design and program code. They also act as a repository for storing information about the design of the program and help make the software easy to maintain.
CASE (computer-aided software engineering) tools
Software that helps the systems analyst and designer in the analysis, design and build phases of a software project. They provide tools for drawing diagrams such as ERDs and storing information about processes, entities and attributes.
ERROR HANDLING AND EXCEPTIONS
The design will include a strategy for dealing with bugs in the system or problems resulting from changes to the operating environment, such as a network failure. When an error is encountered the design will specify that:
■ users should be prompted with a clear but not alarming message explaining the problem;
■ the message should contain sufficient diagnostics that developers will be able to identify and solve the problem.
HELP AND DOCUMENTATION
It is straightforward using tools to construct a Windows help file based on a word-processed document. The method of generating help messages for users will also be specified in the design. Help is usually available as:
■ an online help application similar to reading a manual, but with links between pages and a built-in index;
■ context-sensitive help, where pressing the help button of a dialogue will take the user straight to the relevant page of the online user guide;
■ ToolTip help, where the user places the mouse over a menu option or icon and further guidance is displayed in the status area;
■ help associated with error messages; this is also context-sensitive.
M11_BOCI6455_05_SE_C11.indd 430 30/09/14 7:12 AM
431ChaPter 11 SYSTEMS DESIgN
Object-oriented design is a popular design technique which involves basing the design of software on real-world objects that consist of both data and the procedures that process them, rather than traditional design where procedures operate on separate data. Many software products are labelled ‘object-oriented’ in a bid to boost sales, but relatively few are actually designed using object-oriented techniques. What makes the object approach completely different?
■ Traditional development methods are procedural, dealing with separate data that are transformed by abstract, hierarchical programming code.
■ OOD is a relatively new technique involving objects (which mirror real-world objects consisting of integrated data and code).
Examples of objects that are commonly used in business information systems include customer, supplier, employee and product. You may notice that these are similar to the entities referred to earlier (in Chapter 10), but a key difference is that an object will not only consist of different attributes such as name and address, but will also comprise procedures that process them. For example, a customer object may have a procedure (known as a ‘method’) to print these personal details.
The main benefits of using object orientation are said to be more rapid development and lower costs which can be achieved through greater reuse of code. Reuse in object-oriented systems is a consequence of the ease with which generic objects can be incorporated into code. This is a consequence of inheritance, where a new object can be derived from an existing object and its behaviour modified (polymorphism).
Some further advantages of the object-oriented approach are:
■ easier to explain object concepts to end-users since they are based on real-world objects; ■ more reuse of code – standard, tested business objects; ■ faster, cheaper development of more robust code.
Object-oriented design is closely linked to the growth in use of software components for producing systems. Developers writing programs for Microsoft Windows on a PC will now commonly buy pre-built objects with functionality such as displaying a diary, a project schedule or different types of graph. Such object components are referred to as Visual Basic controls and object controls (OCX). Through using these, developers can implement features without having to reinvent the wheel of writing graphical routines.
An example of a class hierarchy is shown in Figure 11.21. The base class is a person who attends the college. All other classes are derived from this person.
OBJECT-ORIENTED DESIGN (OOD)FOCUS ON…
Object-oriented design
This is a design technique which involves basing the design of software on real-world objects which consist of both data and the procedures that process them rather than traditional design where procedures operate on separate data.
Figure 11.21 A class hierarchy for different types of people at a university
Sta�
Admin Academic
Student
Undergrad Postgrad
Person
M11_BOCI6455_05_SE_C11.indd 431 30/09/14 7:12 AM
Part 2 BUSINESS INFORMATION SYSTEMS DEVELOPMENT432
How widely is the object-oriented approach used?
There was a rapid growth in the use of object-oriented techniques in the 1990s, although original research using the Simula language dates back to the late 1960s. This growth in interest is reflected by the increase in the number of jobs advertised by companies looking to develop software using object-oriented methods, such as Smalltalk, C++ and Java which is now one of the main methods for developing interactive web sites. Specialised methodologies exist for designing object-oriented systems. One of the most commonly used is the object modelling technique (OMT) (see Blaha and Rumbaugh, 2005). This shares some elements with DFD and ERD, but differs in that a hierarchical class breakdown is an additional perspective on designing the system.
What are the main characteristics of an object-oriented system? 1. An object consists of data and methods that act on them. A customer object would contain data such as
their personal details and methods that act on them such as ‘print customer details’.
2. Objects communicate using messages which request a particular service from another object, such as a ‘print current balance’ service. These services are known as ‘methods’ and are equivalent to functions in traditional programming.
3. Objects are created and destroyed as the program is running. For example, if a new customer opens an account, we would create a new instance of the object. If a customer closes an account, the object is destroyed.
4. Objects provide encapsulation – an object can have private elements that are not evident to other objects. This hides complex details and gives a simple public object interface for external use by other objects. A real-world analogy is that it is possible to use a limited number of functions on a television without knowing its inner workings. In object-oriented parlance the television controls are providing different public methods which can be used by other objects. ‘Abstraction’ refers to the simplified public interface of the object.
5. Objects can be grouped into classes which share characteristics. For example, an organisation might contain an employee class. The classes can be subdivided using a hierarchy to create subclasses such as ‘manager’ or ‘administrator’. Classes can share characteristics with other classes in the hierarchy, which is known as inheritance. This refers to the situation when an object inherits the behaviour of other objects. A specialised part-time staff class could inherit personal details data items from the employee class. If the method for calculating salary were different, then the part-time staff could override its inherited behaviour to define its own method ‘calculate salary’. This is known as polymorphism, where an object can modify its inherited behaviour.
Despite the growth of OOD, non-object or procedural systems vastly outnumber object systems. So if OOD is nirvana, why doesn’t everyone use it? The following are all practical barriers to growth:
■ Millions of lines of procedural legacy computer code exist in languages such as COBOL. ■ Many programmers’ skills are procedural – OOD requires retraining to a different way
of thinking. ■ Methodologies, languages and tools are developing rapidly, requiring constant retraining
and making reuse different when using different tools and languages, for example the most popular object-oriented method has changed from Small-talk to C++ to Java in just 10 years.
■ Limited libraries are available for reuse. ■ When initially designing projects, it is often slower and more costly – the benefits of
OOD take several years to materialise.
The experience of early adopters has shown that the benefits do not come until later releases of a product and that initial object-oriented design and development may be more expensive than traditional methods.
M11_BOCI6455_05_SE_C11.indd 432 30/09/14 7:12 AM
433ChaPter 11 SYSTEMS DESIgN
Stage summary: systems design
Purpose: Defines how the system will work
Key activities: Systems design, detailed design, database design, user interface design
Input: Requirements specification
Output: System design specification, detailed design specification, test specification
1. The design phase of the systems development lifecycle involves the specification of how the system should work.
2. The input to the design phase is the requirements specification from the analysis phase. The output from the design phase is a design specification that is used by programmers in the build phase.
3. Systems design is usually conducted using a top-down approach in which the overall architecture of the system is designed first. This is referred to as the systems or outline design. The individual modules are then designed in the de-tailed design phase.
4. Many modern information systems are designed using the client/server architecture. Processing is shared between the end-user’s clients and the server, which is used to store data and process queries.
5. Systems design and detailed design will specify how the following aspects of the system will work:
■ its user interface; ■ method of data input and output (input and output design); ■ design of security to ensure the integrity of confidential data; ■ error handling; ■ help system.
6. For systems based on a relational database and a file-based system, the design stage will involve determining the best method of physically storing the data. For a database system, the technique for optimising the storage is known as ‘normalisation’.
7. Object-oriented design is a relatively new approach to design. It has been adopted by some companies attracted by the possibility of cheaper development costs and fewer errors, which are made possible through reuse of code and a different design model that involves data and process integration.
SUMMARY
1. Define systems design.
2. What distinguishes systems design from systems analysis?
3. Describe the purpose of validation and verification.
4. What are process modelling and data modelling? Which diagrams used to summarise requirements at the analysis phase are useful in each of these types of modelling?
5. Explain the client/server model of computing.
6. What parts of the system need to be designed at the detailed design stage?
7. Describe the purpose of normalisation.
EXERCISES
Self-assessment exercises
M11_BOCI6455_05_SE_C11.indd 433 30/09/14 7:12 AM
8. Explain insertion, update and deletion anomalies.
9. What are the differences between the sequential and direct (random) file access methods? In which business applications might they be used? What is the purpose of a file index?
10. Explain the difference between a batch and a real-time system. Which would be the more appropriate design for each of the following situations:
■ periodic updating of a data warehouse from an operational database; ■ capturing information on customer sales transactions?
11. What are the different types of input validation that must be considered in the design of a user input form?
12. Describe the main differences between the analysis and design phases within the systems development lifecycle.
Essay questions
Part 2 BUSINESS INFORMATION SYSTEMS DEVELOPMENT434
Discussion questions
1. ‘The client/server model of computing has many disadvantages, but these do not outweigh the advantages.’ Discuss.
2. ‘The distinction between system design and detailed design is an artificial one since a bottom-up approach to design is inevitable.’ Discuss.
1. Explain, using an example from a human resources management database, the norm- alisation process from unnormalised data to third normal form (3NF).
2. Table 11.7, from a relational database, contains a number of rows and columns. When data are entered into the table, all columns must have data entered. Information about product descriptions, prices, product groups and rack locations is not held elsewhere. Explain how, because of its design, the table contains data duplicated in fields and contains the potential for insertion, update and deletion anomalies. What is meant by these anomalies and what could be done to prevent them?
3. A business-to-consumer company (B2C), a kitchenware retailer, wants to set up an e-commerce site, but first wants to produce a prototype in Microsoft Access. The data analysis has been performed and is shown in the expanded entity relationship diagram in Figure 11.22. Produce this database in Access based on the ERD. Include 4 or 5 sample records for each table.
Table 11.7 Table from a relational database
Product code
Product description
Product group
Group description
Cost retail price
rack location
Quantity
0942 Small Green KD Kiddy Doh 0.19 1.29 A201 16
0439 Large Red KD Kiddy Doh 0.31 1.89 W106 35
0942 Small Green KD Kiddy Doh 0.19 1.29 E102 0
0902 Small Green KD Kiddy Doh 0.19 1.29 J320 56
1193 Spinning Top PS Pre-School 1.23 12.49 X215 3
2199 Burger Kit KD Kiddy Doh 3.25 17.75 D111 0
M11_BOCI6455_05_SE_C11.indd 434 30/09/14 7:12 AM
1. Explain the difference between validation and verification. Why are they important elements of systems design?
2. What benefits does three-tier client/server offer over two-tier client/server?
3. What are the main elements of system design?
4. Explain normalisation and how it can help remove different types of anomaly when modifying a database.
5. Which criteria are important in deciding whether to use a batch or real-time system?
6. What are the important aspects of user interface design?
7. Which different types of validation need to occur on data input to a system to ensure information quality?
8. What are the four main attributes of information security which need to be attained in an information system?
9. What is meant by the terms ‘input design’, ‘output design’ and ‘database design’? Illustrate each of them with an example.
435ChaPter 11 SYSTEMS DESIgN
Customer
• Customer id * • Title
• First name • Last name
• Address line 1 • Address line 2
• City • Post / Zip code
• County • Password
• User id • E-mail
• Registration date
Primary key
Secondary key
one-to-many relationship
Key
*
+ 1 M
Order hdr
• Order id * • Order date
• Dispatch date • Total amount • Shipping cost • Order credit card number
• Customer id +
places
contains
co nt
ai ns
Order line
• Line id * • Order id + • Quantity
• Price • Product id +
Product
• Product id * • Short description • Long description
• Picture • Size
• Category • Manufacturer id +
• Standard price • Number in stock
• Reorder level • Next available date
Figure 11.22 The expanded ERD for a kitchenware retailer
Examination questions
References
Blaha, M.R. and Rumbaugh, J. (2005) Object Oriented Modeling and Design with UML, 2nd edition, Prentice-Hall, Englewood Cliffs, NJ
Cao, M., Zhang, Q. and Seydel, J. (2005) ‘B2C e-commerce web site quality: an empirical examination’, Industrial Management and Data Systems, 105, 5, 645–61
Cox, J. and Dale, B.G. (2002) ‘key quality factors in Web site design and use: an examination’, International Journal of Quality and Reliability Management, 19, 7, 862–88
M11_BOCI6455_05_SE_C11.indd 435 30/09/14 7:12 AM
Part 2 BUSINESS INFORMATION SYSTEMS DEVELOPMENT436
Curtis, G. and Cobham, D. (2008) Business Information Systems: Analysis, Design and Practice, 6th edition, Addison-Wesley, Harlow
Hoffer, J.A., George, J. and Valacich, J. (2010) Modern Systems Analysis and Design, 6th edition, Prentice-Hall, Upper Saddle River, NJ
Hoffer, J.A., Ramesh, V. and Topi, H. (2013) Modern Database Management, 11th edition, Prentice-Hall, Upper Saddle River, NJ
Huang, W., Le, T., Li, X. and Gandha, S. (2006) ‘Categorizing web features and functions to evaluate commercial web sites: an assessment framework and an empirical investigation of Australian companies’, Industrial Management and Data Systems, 106, 4, 523–39
Jackson, M.A. (1983) System Development, Prentice Hall, London
Rogers, Y., Sharp, H. and Preece, J. (2011) Interaction Design: Beyond Human-Computer Interaction, 3rd edition, Addison-Wesley, Wokingham
Whitten, J.L. and Bentley, L.D. (2006) Systems Analysis and Design Methods, 7th edition, Mcgraw-Hill Irwin, Boston, MA
Yeates, D. and Wakefield, T. (2003) Systems Analysis and Design, 2nd edition, Financial Times Pitman Publishing, London
Further reading
Booch, G. (2011) Object Oriented Analysis and Design with Applications, 2nd edition, Addison-Wesley, Upper Saddle River, NJ
Hasselbring, W. (2000) ‘Information system integration’, Communications of the ACM, June, 43, 6, 33–8
Hoffer, J.A., Ramesh, V. and Topi, H. (2013) Modern Database Management, 11th edition, Prentice-Hall, Upper Saddle River, NJ. A comprehensive text on the process of database design and normalisation together with applications such as data warehousing.
Hoffer, J.A., George, J. and Valacich, J. (2010) Modern Systems Analysis and Design, 6th edition, Prentice-Hall, Upper Saddle River, NJ. A complementary text to Modern Database Management, this has specific chapters on issues involved with designing user interfaces and Internet systems.
Kendall, K. and Kendall, J. (2013) Systems Analysis and Design, 9th edition, Prentice-Hall, Upper Saddle River, NJ. A longer text than the other two partly due to the extensive case on designing a student record system that runs through the book.
Rosenfeld, L. and Morville, P. (2007) Information Architecture for the World Wide Web, 3rd edition, O’Reilly & Associates, Sebastopol, CA. An excellent guide to analysis and design approaches to defining structured storage and access to information using web-based information systems.
Web links
www.cio.com CIO.com for chief information officers and IS staff has many articles related to analysis and design topics.
http://database.ittoolbox.com Channel of IT Toolbox giving topical news and whitepapers on database design, for example data quality, security design, data warehousing. Also has a series of useful introductory articles.
Usability and accessibility
www.uie.com/articles This site focuses on usability, but offers a counterpoint with different views based on research of user behaviour.
www.rnib.org.uk/accessibility Royal National Institute for the Blind web accessibility guidelines.
www.w3.org/WaI World Wide Web Consortium web accessibility guidelines.
M11_BOCI6455_05_SE_C11.indd 436 30/09/14 7:12 AM