Business-Oriented Data Modelling Masterclass: Balancing Engagement, Agility, and Complexity – In-House Training

Course 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 courseon 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