Activities
Activity 11.1
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.
The unnormalised data for the activity is shown in the table on the next page.
1. If a new product is added to the range, it will be necessary to have associated order information for that record. When the new product is first added, there will not be any order information such as order number or customer name to add to the database. This insertion anomaly would not occur if the product-related fields such as product code and description were stored in a separate table.
2. If the bottom record was deleted, the product information on the ‘Diplomat’ product would be lost. The details of this customer would also be lost.
3. The relevant records of order number 10001 and 13001 would be in error and would not indicate the product that was originally ordered.
Bocij, Greasley, Hickie, Business Information Systems, Fourth edition, Instructor’s Manual 187 © Pearson Education Limited 2008
Customer number Customer name Customer address Order number Product code Product description Quantity ordered Price per item Total cost Order date Salesperson number 100 Fred’s Toys 7 High Street 10001 324 Action Man 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
4. Suggested solution is as given below:
Customer table
• Customer number (primary key)
• Customer name
• Customer address.
Order table
• Order number (primary key)
• Order date
• Customer number (foreign key linked to customer table)
• Product number (foreign key linked to product table)
• Quantity ordered
• Salesperson number
• Price per item (this is placed in this table rather than the product table as it varies according to the price that has been negotiated for each order
• Total cost of order (this is inefficient as this is a duplicating field that could result in insertion anomalies).
Product table
• Product code (primary key)
• Product description
• Quantity ordered.
For a more fully normalised table, the order table could be broken down further as follows:
Order header table
• Order number (primary key)
• Order date
• Customer number (foreign key linked to customer table)
• Salesperson number
• Total cost of order (this is inefficient as this is a duplicating field that could result in insertion
Order line item table
• Order line number (primary key – not used in other tables)
• Order number (foreign key linked to order table)
• Product number (Foreign key linked to product table)
• Quantity ordered
• Price per item (this is placed in this table rather than the product table as it varies according to what has been negotiated for each order).
Activity 11.2
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.).
(a) It is not possible to provide a specimen answer for this question as students might identify and name a group of entities differently to those used by the authors. Readers should refer to (b) for a specific example that uses a number of fixed or named entities.
The answer to part (b) defines the third normal form tables for the ABC database. The answer could be extended by including these tables, but this tends to make the scale of the exercise too large.
• stock details;
• supplier details;
• invoice;
• purchase order;
• manufacturing order.
2. The tables below show the suggested answers.
Customer table:
Field name Field type Field size Validation rule Key?
Customer number
(or code or ID) Number 6 (allows for
999,999 customers Mandatory, >0 Primary key
Customer title Text 10
Customer first name Text 30
Customer last name Text 30 Mandatory
Customer address
line 1 Text 50 Mandatory
Customer address line 2
Text 50 Customer address
line 3 Text 50
Customer address
line 4 Text 50
Post code Text 10 Mandatory
Phone number Text (to allow spaces
and brackets) 20 Mandatory
Fax number Text 20
E-mail address Text 50
Credit limit Number 8.2 (up to
999,999.99)
Mandatory Sales person
number Number 6 Mandatory, >0 Foreign key
in sales person table
Region number Number 2 Restricted list
from 1,2,3 etc.
Salesperson table:
Field name Field type Field size Validation rule Key?
Salesperson number Number 6 Mandatory, >0 Primary key
Salesperson title Text 10
Salesperson first name Text 30
Salesperson last name Text 30 Mandatory
Phone number Text (to allow spaces
and brackets) 20 Mandatory
Fax number Text 20
E-mail address Text 50
Region number
responsible for Number 2 Restricted list
from 1,2,3 etc.
Sales order header table:
Field name Field type Field size Validation rule Key?
Sales order number Number 6 Mandatory, >0 Primary key
Date placed Date 10 Mandatory
Customer number Number 6 Mandatory Foreign key
in customer table Total order value Currency 8.2 (up to
999,999.99) Mandatory
Payment type Text 10 Restricted choice
of cash, cheque or credit
Sales order line table:
Field name Field type Field size Validation rule Key?
Line item number Number 6 Mandatory, >0 Primary key
(Not used as foreign key in other table)
Sales order number Number 6 Mandatory, >0 Foreign key in order header table
Product number Number 6 Mandatory, >0 Foreign key in product table
Item cost Currency 6.2 (up to
9999.99) Mandatory
Quantity ordered Number 3 (up to 999) Mandatory
Product table:
Field name Field type Field size Validation rule Key?
Product number Number 6 Mandatory, >0 Primary key
Product name Text 30 Mandatory
Product description Text 100
Standard product cost Currency 6.2 (up to 9999.99)
As well as the validation shown, there will be an automatic check that the field is of the correct type, e.g. text fields do not contain characters, and the data entered is less than the size shown.
Activity 11.3
There is no solution for this activity – the worked example is shown in the text.
Case Studies
Case Study 11.1: Beaverbrooks the Jewellers
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.
1. The key design features that students should mention include the following:
• The need for ‘a central repository for documentation and a framework to enable company information and knowledge to be exchanged’.
• The ability to use a ‘central data system extensively for stock enquires, placing special orders, sharing company information and making sure the merchandising in each branch conforms to the current company branding and directives’.
• Collaboration facilitated through ‘KnowledgeWorker collaboration, search and work-flow tools’ that ‘sit over the top of the data, which is accessed locally or remotely through a web browser interface’.
So these design aspects relate to ‘accessibility’, ‘data/database design’, and ‘user interface design’.
2. The business benefits arising from the design features mentioned include the following:
• Saving of staff time in progressing orders as a result of the centralised database system.
• Merchandising in each branch conforms to the current company branding and directives as a consequence of staff using the centralised system.
• Improved productivity as a result of improved methods of storing and sharing information.
• Developing more of a competitive edge as a result of ‘finding more activities the system can help us with’.
It is unclear whether the system is bespoke or packaged in nature. However, the last point demonstrates that a well designed system is capable of delivering benefits beyond what mat have been envisaged at the systems initiation or feasibility stages. What is perhaps interesting here is if the investment decision had been based purely on the ‘intended benefits’, then it is possible that a decision not to proceed with the investment may be made, thus denying the business of the wider benefits that might be obtained.
Exercises (pp. 460–462)
Self-assessment exercises
1. Define systems design.
The design phase of the life cycle defines how the finished information system will operate. This is defined in a design specification that sets out 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.
2. What distinguishes systems design from systems analysis?
Systems analysis occurs before system design and involves in finding out what the requirements of the users are. Design involves in specifying how these requirements will be implemented through the structure of the system and detailed programming.
3. Describe the purpose of validation and verification.
Validation is a test of the design to check that the design fulfils the requirements of the business users that are defined in the requirements specification.
Verification is a test of the design to ensure that the design chosen is the best solution and that it is error free.
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?
Process modelling is involved with the design of the different modules of the system, each of which is a process with clearly defined inputs, outputs and a transformation process. Dataflow diagrams and flow charts are often used to define system processes. The ultimate result of process modelling is the program code in the different user modules of a system.
Data modelling is involved with considering 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 ultimate result of data modelling is a database and the module routines that are used to update and extract this data.
5. Explain the client/server model of computing.
This describes a system architecture in which two separate types of computers are used to provide the service to the end-user. The first are end-user machines such as PCs (clients) that run applications while accessing data and possibly programs from a server. The second are the servers, which are more powerful computers used for storing and sharing programs and data.
Dividing the work in this way results in an overall efficient system.
6. What parts of the system need to be designed at the detailed design stage?
• Detailed database design including multi-user access.
• Detailed user interface design.
• Detailed input design (field validation and security design) and output design (how data will be displayed).
• Detailed module design – the ways in which the programme codes of each module works will be discussed.
7. Describe the purpose of normalisation.
Normalisation is intended to simplify the structure of a database to avoid duplication of data in order that anomalies do not occur when new information is added or old information is removed. It involves in splitting up a database into different tables.
8. Explain insertion, update and deletion anomalies.
These anomalies occur in poorly defined database tables. When information in the tables is changed, important information may be lost or it may be necessary to duplicate information. The different types of anomalies are as follows:
• Insertion anomaly – it is not possible to insert a new record into a table without having to insert other information that is unavailable.
• Update anomaly – it is not possible to change a single occurrence of a field in a 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 table without also losing some other information that might still be required.
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?
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. File index is an additional file that is used to ‘point’ to records in a direct access file for more rapid access.
10. Explain the difference between a batch and real-time system which would be the most appropriate design for each of the following situations:
• periodic updating of a data warehouse from operational database;
• capturing information on customer sales transactions.
A batch system involves processing of many transactions in sequence. This will typically occur some time after the transactions have occurred. In a real-time system, processing occurs immediately, data is collected or processing follows each transaction.
(a) A batch method is most appropriate since this is a periodic update that needs to occur at a time which minimises disruption to the end users of the system.
11. What are the different types of input validation which must be considered in the design of a user input form?
• Data type checking.
• Data range checking.
• Restricted value checking.
• Input limits.
• Multiple field validation.
• Checksum digits.
12. Describe the main differences between the analysis and design phases within the systems development life cycle.
The differences are as follows:
• the order in which they occur – analysis first, then design;
• user involvement is high in the analysis stage and minimal in the design stage;
• technical details such as database structure and system architecture are involved in the design phase, while analysis focuses on business requirements.
Discussion questions
1. ‘The client/server model of computing has many disadvantages, but these do not outweigh the advantages.’ Discuss.
A similar question is also asked in Chapter 5 – see relevant section for more details.
This question will need a brief definition of client/server (C/S) and can then list the main disadvantages that there will be compared to the more traditional mainframe arrangement. These disadvantages potentially include
• Cost – the total cost of ownership (TCO) of maintaining systems with PC-based clients is known to be higher than systems with simpler clients (see Chapter 16).
• Performance and scalability – the speed of client/server can be poor since with downsizing the processor power may be limited. There may also be a GUI which has a large programme that does not execute rapidly on a PC client.
• Stability and reliability – client/server systems tend to be less reliable than mainframe systems since they are designed using components from a range of manufacturers, some of which may not be well tested. This contrasts with well-established mainframe systems from a more limited range of suppliers.
• Security – client/server usually have more entry points and use operating systems on the client such as Windows that make them less secure.
C H A P T E R 1 2