Event Details

This workshop introduces Concept Modelling and then Logical Modelling from a non-technical perspective, thoroughly covering the components of any data model - entities, relationships, attributes, and rules.  It includes clear, practical guidelines, as well as many tips, quality checklists, and warnings of common pitfalls.  Just as important, it contains far more advice on the process of developing a data model than other courses, including specific methods for getting subject matter experts involved and maintaining their commitment.

Training Outline

Essentials of Data Modelling  

  • What really is a data model or concept model?  
  • Essential components – entities, relationships, attributes, and rules  
  • Hands-on case study – how data modelling resolved business issues, and supported other business analysis techniques  
  • Guidelines for comprehension – how to lay out Entity-Relationship Diagrams (“ERDs”)  
  • The narrative parts of a data model – definitions and assertions  
  • Group exercise – getting started on a data model, then refining it  
  • Common misconceptions about data models and data modelling  
  • The real purpose of a data model  
  • Contextual, Conceptual, and Logical Data Models – purpose, audience, definition, and examples  
  • Overview of a three-phase methodology for developing a data model

Establishing the Initial Conceptual Data Model  

  • Top-down vs. bottom-up approaches to beginning a data model – when is each appropriate?  
  • A bottom-up approach focusing on collecting and analyzing terminology  
  • A structure for sorting terms and discovering entities  
  • Exercise – developing an initial conceptual data model  
  • Entities – what they are and are not  
  • Guidelines for naming and defining entities  
  • Three questions to help you quickly develop clear, useful entity definitions  
  • Exercise – identifying flawed entities  
  • Six criteria that entities must satisfy, and four common errors in identifying entities  
  • Identifying relationships
  • Fundamental vs. irrelevant or transitive relationships  
  • Good and bad relationship names  
  • Multiplicity or cardinality – 1:1, 1:M, and M:M relationships, and useful facts about each  
  • Common errors and special cases – recursive, multiple, and supertype-subtype relationships  
  • Attributes – guidelines and types  
  • Attributes in conceptual models vs. logical models 

Developing the Initial Logical Data Model by Adding Rigor, Structure and Detail  

  • Transition to the logical model – shifting the focus from entities to attributes  
  • Multi-valued, redundant, and constrained attributes, with simple patterns for dealing with each  
  • An understandable guide to normalisation – first, second, and third normal forms  
  • Higher order (fourth and fifth) and Boyce-Codd normal forms  
  • Exercise – developing the initial logical data model  
  • Four types of entities – kernel, characteristic, associative, and reference  
  • Guidelines and patterns for dealing with each type of entity  
  • How to draw your E-R Diagram for maximum readability and correctness  
  • Optional and mandatory relationships  
  • Considering time and history when looking at relationships  
  • Typical attribute documentation  
  • A common source of confusion and disagreement – primary keys  
  • What primary keys are, what they’re really for, and three essential criteria  
  • The four Ds of data modelling – definition, dependency, detail, and demonstration  
  • E-R Diagramming – symbol sets and their problems, rules for readability and comprehension 

Correctly Handling Attributes  

  • Granularity – dealing with non-atomic and semantically overloaded attributes  
  • Dealing with reference data and the “types vs. instances” problem  
  • Three attributes that always need a qualifier  
  • Vector modelling – entity or attribute? 

Interesting Structures – Generalisation, Recursion and the Two Together  

  • Generalisation (subtyping) – when to use it, and when not to  
  • Generalisation with and without specification  
  • Guidelines for using recursive relationships  
  • Generalisation and recursion working hand-in-hand as a cure for literalism  
  • Recognizing lists, trees, and networks, and modelling them with recursive relationships  
  • Modelling difficult rules by combining generalisation (subtyping) and recursion  
  • Staying clear on generalisation vs. roles, states, and aggregation

Modelling Time, History and Time- Dependent Business Rules  

  • Historical vs. audit data, and when to show them on a data model  
  • Thanks, Sarbanes-Oxley! Why we need “as-of reporting” and how to model data corrections  
  • “Do you need history?” – how to tell when your client is misleading you  
  • Modelling time – special considerations for recording past, present, and future values
  • Four variations on capturing history in a data model  
  • Seven questions you should always ask when a date range appears 

Modelling Rules on Relationships and Associations  

  • Using multi-way associations to handle complex rules  
  • “Use your words” – how assertions, scenarios, and other techniques will improve your modelling  
  • Associative entities – circular relationships, shared parentage, and other issues  
  • Alternatives for modelling constraints across relationships  
  • Advanced normal forms – how to quickly recognize potential 4NF and 5NF issues  
  • A simpler view – why the five normal forms could be reduced to three 

Preparing and Delivering a Data Model Review Presentation  

  • Context – your audience, and why the model matters to them  
  • It’s a story, not a data model! Building a storyboard  
  • Five key techniques for presenting data models or other technical subjects  
  • The mechanics of the data model review presentation  
  • A demonstration

Bridging the “E-R vs. Dimensional” Divide – the World’s Shortest Course on Dimensional Modelling  

  • The perils of dimensional modelling without understanding the underlying E-R model  
  • Spotting facts and dimensions – the relationship between dimensional models and E-R models  
  • Saving time – building a first-cut dimensional model from an ER model  
Who Is It For?

  • Specialist data modellers, data architects, data analysts, and DBAs who wish to hone their skills.
  • Business analysts, business architects, enterprise architects, and application architects
  • Application / solution developers (especially on Agile teams)
  • Business professionals, Subject Matter Experts, and Project /Programme Managers involved in the analysis, design, and development (or selection and configuration) of a system.

  • BI (Business Intelligence) professionals, DW (Data Warehouse) professionals, big data specialists, data scientists, analytics specialists, and data lake implementers

Alec Sharp
Senior Consultant
Clariteq Systems Consulting Ltd
40 years of international consulting experience helping clients achieve organisational change, improve or transform their business processes, improve their data initiatives through effective, business-friendly concept modelling (conceptual data modelling,) and specify requirements for purchased or custom-built systems to support those processes. Particular strengths in group facilitation, and helping parties with conflicting priorities and objectives reach alignment on major initiatives. Globally recognized as an expert in practical techniques for business process change, process modelling and analysis, and data modelling (or concept modelling, if you prefer.) Also widely recognized for expertise in requirements definition (use cases, scenarios, service specifications) and group facilitation. Deliver top-rated workshops globally on topics such as Working With Business Processes, Advanced Business Process Management, introductory and advanced Data Modelling, and Requirements Modelling with Use Cases and Service Specs. Author of "Workflow Modeling, Second Edition" (Artech, 2009) a top-selling title on process modeling, analysis, and redesign. Widely used in industry, by consulting firms, and as an MBA text. The second edition was a complete rewrite, and has received many (unsolicited!) five star ratings on Amazon.com. Specialties: All major industry sectors, but currently specialising in Higher Education Business Process Redesign, Process Scope and Workflow Modelling, Business-oriented Data Modelling, Requirements Modelling, Use Cases, Service Specification (requirements modelling for SOA environment), Group techniques - Session facilitation + presentations + education + seminars