Modelling Business Information: Entity relationship and class modelling for Business Analysts

By Keith Gordon

It is almost universally accepted that requirements documents for new or enhanced IT systems by business analysts should include a ‘data model’ to represent the information that has to be handled by the system.

Starting from first principles, this book will help business analysts to develop the skills required to construct data models through comprehensive coverage of entity relationship and class modelling, in line with the BCS Data Analysis syllabus. In addition to covering the topics in the syllabus, the book also includes extra information of interest including data model quality and taking a requirement model into database design.

• Language: English
• Publisher: BCS, The Chartered Institute for IT
• Release date: Sep 5, 2017
• ISBN: 9781780173559

Book preview

As the roles of Data and Business Analysts become more intertwined, this book is timely in its publication. Businesses often fail to recognise information is a key resource and are confused by how it is presented or overwhelmed its complexity during use. Keith brings to the forefront of the readers mind the importance of communicating and analysing the relationship between Business, Information, Systems and Data, and the value in developing models cooperatively, gaining ‘consensus, not perfection’ from stakeholders. Simple everyday examples and analogies to support the readers understanding and make the subject more relatable are used.

I enjoyed reading the book and completing the exercises. An excellent learning aid for Analysts who are new to modelling or need reminding of good practice.

Katie Walsh, Business Analyst and Mentor

Anyone interested in a thoughtful, well-done text on how to do high-quality business analytical data modelling should definitely proceed with this book.

David Hay, Essential Strategies International, CEO

Modelling Business Information provides an introduction to data modelling, to the nomenclature used by common modelling techniques, and to techniques for representing common patterns. This is a useful book for business analysts who are creating the information model as well as for business and IT users who need to understand a data model.

Keith W. Hare, JCC Consulting, Inc., Senior Consultant

Keith Gordon’s wonderfully compact yet thorough introduction to business-friendly information modelling is a terrific contribution to the field. Globally, there’s a surge of interest in data modelling as a powerful tool for improving communication, especially with professionals who used to think business-oriented entity relationship modelling didn’t need to be in their tool kits. Business analysts, Agile developers, data scientists, big data specialists, and other professionals will all benefit from Keith’s work.

Alec Sharp, Senior Consultant, Clariteq

Modelling Business Information by Keith Gordon, is aimed at those who are new to business analysis or information modelling. Keith draws on a wealth of experience in information management, both as a practitioner, and as a lecturer with the Open University in his writing.

The first six chapters provide an accessible and clear foundation in the topic covering the reasons for developing information models, the basic elements of entity-relationship diagrams, how to develop an information model from basic information requirements, and finally how to normalise existing data. I particularly like that it uses two graphical notations, the Barker-Ellis notation, noted for its readability, and the ubiquitous Unified Modelling Language notation, which helps to demonstrate that there are different notations that entity-relationship models can be developed in. This first part of the book also takes care to cover the syllabus for the Data Analysis certificate that is part of the scheme for the BCS Advanced International Diploma in Business Analysis.

The second part of the book covers a range of more advanced topics from naming conventions and yet more entity-relationship model notations, to considerations of quality in information models, corporate data models, modelling for business intelligence applications, and finally goes on to look at data and database topics including an overview of SQL, and moving to database design and optimisation.

Overall, the book provides an excellent grounding in the full range of topics related to information modelling.

Matthew West, Director, Information Junction

BCS, THE CHARTERED INSTITUTE FOR IT

BCS, The Chartered Institute for IT, champions the global IT profession and the interests of individuals engaged in that profession for the benefit of all. We promote wider social and economic progress through the advancement of information technology science and practice. We bring together industry, academics, practitioners and government to share knowledge, promote new thinking, inform the design of new curricula, shape public policy and inform the public.

Our vision is to be a world-class organisation for IT. Our 75,000-strong membership includes practitioners, businesses, academics and students in the UK and internationally. We deliver a range of professional development tools for practitioners and employees. A leading IT qualification body, we offer a range of widely recognised qualifications.

Further Information

BCS, The Chartered Institute for IT,

First Floor, Block D,

North Star House, North Star Avenue,

Swindon, SN2 1FA, UK.

T [phone]

F [phone]

www.bcs.org/contact

http://shop.bcs.org/

© 2017 BCS Learning & Development Ltd

The right of Keith Gordon to be identified as author of this work has been asserted by him in accordance with sections 77 and 78 of the Copyright, Designs and Patents Act 1988.

All rights reserved. Apart from any fair dealing for the purposes of research or private study, or criticism or review, as permitted by the Copyright Designs and Patents Act 1988, no part of this publication may be reproduced, stored or transmitted in any form or by any means, except with the prior permission in writing of the publisher, or in the case of reprographic reproduction, in accordance with the terms of the licences issued by the Copyright Licensing Agency. Enquiries for permission to reproduce material outside those terms should be directed to the publisher.

All trademarks, registered names etc. acknowledged in this publication are the property of their respective owners.

BCS and the BCS logo are the registered trademarks of the British Computer Society, charity number 292786 (BCS).

Published by BCS Learning & Development Ltd, a wholly owned subsidiary of BCS, The Chartered Institute for IT, First Floor, Block D, North Star House, North Star Avenue, Swindon, SN2 1FA, UK.

www.bcs.org

Paperback ISBN: 9781780173535

PDF ISBN-13: 9781780173542

EPUB ISBN-13: 9781780173559

Kindle ISBN-13: 9781780173566

British Cataloguing in Publication Data.

A CIP catalogue record for this book is available at the British Library.

Disclaimer:

The views expressed in this book are those of the authors and do not necessarily reflect the views of the Institute or BCS Learning & Development Ltd except where explicitly stated as such. Although every care has been taken by the authors and BCS Learning & Development Ltd in the preparation of the publication, no warranty is given by the authors or BCS Learning & Development Ltd as publisher as to the accuracy or completeness of the information contained within it and neither the authors nor BCS Learning & Development Ltd shall be responsible or liable for any loss or damage whatsoever arising by virtue of such information or any instructions or advice contained within this publication or by any of the aforementioned.

Typeset by Lapiz Digital Services, Chennai, India.

CONTENTS

List of figures and tables

About the Author

Foreword

Acknowledgements

Glossary

Introduction

PART 1

THE BASICS

1.

WHY BUSINESS ANALYSTS SHOULD MODEL INFORMATION

What is business analysis?

Information and data

The importance for a business analyst of understanding information needs

The role of models in business analysis

Data models and data

Entity relationship modelling

Class modelling

Use of data models in business analysis

What makes a good data model?

Introducing data analysis

2.

MODELLING THE THINGS OF INTEREST TO THE BUSINESS AND THE RELATIONSHIPS BETWEEN THEM

Entities and objects

Naming of entity types and object classes

Introduction to relationships and associations

Relationship notation in entity relationship models

Association notation in UML class models

Degrees of cardinality and optionality

Multiple relationships and associations

Recursive relationships and reflexive associations

Exercises for Chapter 2

3.

MODELLING MORE COMPLEX RELATIONSHIPS

The problems with many-to-many relationships and associations

Resolving entity relationship model many-to-many relationships

Resolving class model many-to-many associations

The ‘bill of materials’ structure

Mutually exclusive relationships and associations

Generalisation and specialisation in entity relationship models

Generalisation and specialisation in class models

Aggregation and composition

Exercises for Chapter 3

4.

DRAWING AND VALIDATING INFORMATION MODEL DIAGRAMS

The model drawing process

Identifying the entity types or the object classes

Identifying the relationships or associations

Drawing the initial diagram

Validating the diagram

Exercises for Chapter 4

5.

RECORDING INFORMATION ABOUT THINGS

Revisiting entity types, object classes, relationships and associations

Introduction to attributes

The naming of attributes

Entity type, object class or attribute?

Unique identifiers

Domains

The UML extended attribute notation

Showing operations on class models

Exercises for Chapter 5

6.

RATIONALISING DATA USING NORMALISATION

What is normalisation?

The relational model of data

The rules of normalisation

Starting the normalisation process

First normal form

Second normal form

Third normal form

The third normal form data model

Candidate keys, primary keys and alternate keys

The relationship of normalisation to modelling

Exercises for Chapter 6

PART 2

SUPPLEMENTARY MATERIAL

7.

OTHER MODELLING NOTATIONS

The IDEF1X notation

The Information Engineering notation

The Chen notation

Comparison of the notations

8.

THE NAMING OF ARTEFACTS ON INFORMATION MODELS

The naming of entity types or object classes

The naming of domains

The naming of attributes

The naming of relationships in Ellis-Barker entity relationship models

The naming of associations on UML class models

9.

INFORMATION MODEL QUALITY

Genericity and specificity in models

The nine characteristics of a good data model

The six principles of high quality data models

The five dimensions of data model quality

The layout of models

10.

CORPORATE INFORMATION AND DATA MODELS

The problems

Principles for the development of a corporate model

11.

DATA AND DATABASES

The data landscape

Databases

12.

BUSINESS INTELLIGENCE

The data warehouse

The multidimensional model of data

Dimensional modelling

13.

ADVANCES IN SQL (OR WHY BUSINESS ANALYSTS SHOULD NOT BE IN THE WEEDS)

The basics of SQL

New SQL data types

The future

Implications for business analysts and information modellers

14.

TAKING A REQUIREMENTS INFORMATION MODEL INTO DATABASE DESIGN

First-cut database design stage

Optimised database design stage

APPENDICES

Appendix A: Table of equivalences

Appendix B: Bibliography

Appendix C: Solutions to the exercises

Index

LIST OF FIGURES AND TABLES

Figure 1.1

Three levels of system

Figure 1.2

The relationship between data and information

Figure 1.3

A rich picture

Figure 1.4

A business activity model

Figure 1.5

A business process model

Figure 1.6

A use case diagram

Figure 1.7

Requirements engineering in context

Figure 1.8

An example entity relationship model using the Ellis-Barker notation

Figure 1.9

An example of a UML class model

Figure 2.1

The vehicle hire company using Ellis-Barker notation

Figure 2.2

The vehicle hire company using UML class model notation

Figure 2.3

A relationship in an entity relationship model

Figure 2.4

An association in a UML class model

Figure 2.5

The use of role names

Figure 2.6

One-to-many (1:n) optional–mandatory relationship and association

Figure 2.7

One-to-many (1:n) mandatory–optional relationship and association

Figure 2.8

One-to-one (1:1) optional–mandatory relationship and association

Figure 2.9

Many-to-many (m:n) optional–optional relationship and association

Figure 2.10

Modelling the ‘one-way’ hire situation

Figure 2.11

Employee supervision

Figure 3.1

Employees and branches

Figure 3.2

Introducing the ASSIGNMENT entity type

Figure 3.3

Introducing the ASSIGNMENT object class

Figure 3.4

Introducing the ASSIGNMENT association class

Figure 3.5

Introducing products within products

Figure 3.6

The bill of materials structure in Ellis-Barker notation

Figure 3.7

The bill of materials structure in UML class model notation

Figure 3.8

Employee supervision in a matrix organisation

Figure 3.9

Employee supervision in a matrix organisation resolved

Figure 3.10

The vehicle hire company as shown in Figure 2.1

Figure 3.11

The introduction of an exclusive arc

Figure 3.12

The introduction of the {xor} constraint

Figure 3.13

An example of a supertype–subtype hierarchy

Figure 3.14

Alternative depiction of a supertype–subtype hierarchy

Figure 3.15

A UML superclass–subclass hierarchy

Figure 3.16

Alternative notation for a UML superclass–subclass hierarchy

Figure 3.17

A UML class model with multiple superclass–subclass hierarchies

Figure 3.18

Aggregation using Ellis-Barker notation

Figure 3.19

An example of the use of the aggregation symbol in a UML class model

Figure 3.20

An example of the use of the composition symbol in a UML class model

Figure 3.21

Composition using Ellis-Barker notation

Figure 4.1

The model drawing process

Figure 4.2

A ‘relationship matrix’

Figure 4.3

The initial Ellis-Barker entity relationship model

Figure 4.4

The initial UML class model

Figure 4.5

The first data navigation path

Figure 4.6

The second data navigation path

Figure 4.7

The revised Ellis-Barker entity relationship model

Figure 4.8

The revised UML class model

Figure 4.9

Partial high-level process map

Figure 4.10

Completed CRUD matrix

Figure 4.11

The final Ellis-Barker entity relationship model

Figure 4.12

The final UML class model

Figure 5.1

The previous models

Figure 5.2

Attribute types shown on an Ellis-Barker entity relationship model

Figure 5.3

Attributes shown on a UML class model

Figure 5.4

EMPLOYEE expanded (shown in Ellis-Barker entity relationship notation)

Figure 5.5

EMPLOYEE expanded (shown in UML class modelling notation)

Figure 5.6

Unique identifiers on an Ellis-Barker entity relationship model

Figure 5.7

The UML <> class

Figure 5.8

The UML extended attribute notation

Figure 5.9

The UML operations notation

Figure 6.1

Relational tables

Figure 6.2

The staff record form

Figure 6.3

Normalisation form completed to UNF

Figure 6.4

Normalisation form completed to 1NF

Figure 6.5

Normalisation form completed to 2NF

Figure 6.6

Normalisation form completed to 3NF

Figure 6.7

The third normal form data model

Figure 7.1

The model of the business scenario in Ellis-Barker notation

Figure 7.2

The model of the business scenario in UML class model notation

Figure 7.3

The model of the business scenario in IDEF1X notation

Figure 7.4

The model of the business scenario in Information Engineering notation

Figure 7.5

The model of the business scenario using Chen’s notation

Figure 7.6

Comparison of the relationship notations

Figure 9.1

An example of the replacement of roles by entity types

Figure 9.2

The generic to specific continuum

Figure 9.3

The cost-balance of flexible design

Figure 9.4

The five dimensions of data model quality

Figure 11.1

The data landscape

Figure 11.2

Example data arranged in tables and columns

Figure 11.3

The database chronology

Figure 11.4

Hierarchical database schema

Figure 11.5

Hierarchical database occurrences

Figure 11.6

Network database schema

Figure 11.7

Network database occurrences

Figure 12.1

A multidimensional data model

Figure 12.2

A typical ‘star’ schema for a data warehouse

Figure 12.3

A ‘snowflake’ schema

Figure 12.4

A ‘galaxy’ schema

Figure 13.1

The original ‘workshop’ model

Figure 13.2

The third normal form model

Figure 13.3

The final model

Table 4.1

Identified entity types or object classes

Table 8.1

Examples of formal attribute names

Table A.1

Table of equivalences

ABOUT THE AUTHOR

Keith Gordon was a professional soldier for 38 years, joining the Army straight from school at 16 and retiring on his 55th birthday. During his service with the Royal Armoured Corps, the Royal Corps of Signals and the Royal Army Educational Corps (now the Educational and Training Services Branch of the Adjutant General’s Corps) he gained a Higher National Certificate in Electrical, Electronic and Telecommunications Engineering, a Certificate in Education from the Institute of Education of the University of London, a Bachelor of Arts from the Open University and a Master of Science in Design of Information Systems from Cranfield Institute of Technology.

The Master of Science course, held at the Royal Military College of Science, was unclear about what sort of information system the students were supposed to be designing. Was it a business system to be used in the non-operational world of the military? Was it a command and control information system to be used on the battlefield? Was it a real-time system to be used in areas such as weapon control? Or was it a management information system?

The course did, however, cover some really useful stuff. On the technical side this included programming in Ada and Coral-66, which are languages designed for embedded and real-time systems. We also studied Soft Systems Methodology (the academic lead for the course had researched for his doctorate at Lancaster University under the supervision of Professor Peter Checkland) and we looked, in particular, at the work of Professor Brian Wilson specialising in the application of Soft Systems Methodology to the development of information systems. The Structured Systems Analysis and Design Method (SSADM) (now called ‘Business System Development’ and the impetus for the Business System Development scheme of BCS which includes the Business Analysis and Solution Development diplomas and used to include a Data Management diploma) also formed a substantial part of the course.

Following the Design of Information Systems course, Keith spent three years as a consultant in the Army School of Training Support, where he looked into and procured computer systems for use in education and training – computer-based training (CBT). This role was part researcher and part business analyst. The next two years were spent as the Senior Education Officer in the Army’s apprentice college for the training of apprentice soldier chefs.

In 1992, he was posted to the Ministry of Defence and joined a new team of four officers and a civil servant ‘doing data management’ for the Army. In 1995, he was promoted to Lieutenant Colonel and became the head of that team until he retired from the Army in 1998.

He is now an independent consultant and lecturer specialising in data management and business analysis. As well as