Overview:

This 3-day interactive workshop combines the core content from two popular data modelling offerings by Alec Sharp – “Business Oriented Data Modelling” and “Advanced Data Modelling.”  The first day of the workshop gets both new and experienced modellers to the same baseline on terminology, conventions, and the unique, business-engaging approaches this course provides. The next two days provide intense, hands-on practice with more advanced situations, such as the enforcement of complex business rules, handling recurring patterns, satisfying regulatory requirements to model time and history, capturing complex changes and corrections, dealing with existing databases or packaged applications, and integrating with dimensional modelling. In all cases, the underlying philosophy is that a data model is a description of a business, not of a database.

Three main themes are explored in a very practical way:

1. The foundations of data modelling – what a data model really is, and maximizing its relevance

2. The human side of data modelling - improving communication skills and engaging the business

3. The complex side of data modelling - getting better at modelling difficult situations

Learning Objectives:

On workshop completion, participants will be able to:

  • Apply a variety of techniques that support the active participation and engagement of business professionals and subject matter experts;

  • Use entity-relationship modelling to depict facts and rules about business entities at different levels of detail, including conceptual (overview) and logical (detailed) models;

  • Learn an easy, language-oriented approach to initiating development of a data model;

  • Recognize the four basic patterns in data modelling, and when to use them;

  • Effectively use definitions and assertions (“rules”) as part of data modelling;

  • Use an intuitive approach to data normalisation within an entity-relationship model;

  • Apply various techniques for discovering and meeting additional requirements ;

  • Read a data model, and communicate with specialists using the appropriate terminology.

  • Understand “the four Ds of data modelling” – definition, dependency, demonstration, and detail;

  • Be able to implement lists, trees, and networks with recursive relationships;

  • Know how and when to use supertypes/subtypes (generalisation/specialisation) vs. roles vs. both;

  • Combine sub-typing and recursion, as appropriate, to model difficult rules;

  • Recognize the “category vs. types vs. instances” problem, and model reference data properly;

  • Model “vectors” (attributes that repeat a fixed number of times) properly – entity or attribute?;

  • Use multi-way associations, associations of associations, and relationship constraints to handle complex rules;

  • Handle circular relationships and cyclic dependencies properly with advanced normal forms;

  • Model history, corrections, and time-dependent business rules with “temporal data models”;

  • Understand the connection between analytic data structures (star schema or dimensional models) and ER models;

  • Rapidly develop a first-cut dimensional model from a well-structured ER model;

  • Prepare and deliver a data model review presentation.

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