The course starts at 09.30 am and ends at 5 pm. Registration commences at 08.30 am.
[IF ONLINE FORMAT] We will send the course materials and meeting instructions well in advance as well as the invitation with hyperlink to join us online. The workshop will start at 13:30 and lasts until 17:00 CET. The online meeting will be available at least one half hour earlier so please log in timely in order to check your sound and video settings beforehand.
Course Topics
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