Course Description

This two-day Data Modelling training course shows participants how to apply data modelling to a variety of business areas and software applications. The course is based on standard Entity-Relationship (E-R) diagramming techniques.

Participants are first introduced to the concept of data as an enterprise resource that should be properly managed in the same manner as other resources. The course then moves on to a detailed discussion of data modelling techniques using Entity-Relationship (E-R) diagrams.The course concludes with a discussion of data analysis, database design techniques and normalisation as a mechanism for optimising database designs.

After completing this course, participants will be able to:

  • understand and describe the fundamental concepts on which data modelling is based and the techniques and sequence used in deriving a data model.
  • perform a data modelling process from initiation through to the mapping of a physical database design.
  • take a corporate, rather than a system-specific view of the organisation’s data resource.

 

Pre-requisites

Assumes no prior knowledge of data modelling concepts and techniques. Some familiarity with business data and business systems is useful.

 

Who is this course for?

Data Modelling is suitable for anyone acting (or planning to act) in the role of Data Architect, Data Analyst, Business Systems Analyst, Systems Analyst, Business Analyst or Business Consultant. It is also suitable for other IT professionals who need to understand what Data Architects do and don’t do. It is also relevant for experienced Data Architects who need to update their skills, attend a “refresher”, or simply get some new ideas.

 

Course content

Data Modelling Concepts

Principles of Data Modelling
Classification
Abstraction
Reification
Entities & Attributes
Tables
Primary Keys
Natural Keys
Surrogate Keys
Business Surrogate Keys
Artificial Surrogate Keys
Relationships
Cardinality
Relationships & Foreign Keys

Data Model Diagrams
Entity-Relationship (E-R) Diagrams
Identifying Entities
Classes of Entity
Where to Look
What to Challenge
Choosing Relationships
Relationships between different Entities
Multiple Relationships between Entities
Relationships involving a single Entity
Data Modelling Techniques
Removing Redundant Relationships
Effect of Time on Relationships
Entity Definitions & Relationships
Simplifying one-to-one Relationships
Expanding Many-to-Many Relationships
Relationships between different Entities
Multiple Relationships between Entities
Relationships involving a single Entity
Modelling Roles Correctly
Modelling Types Correctly
Partitioning Data Models into Subject Areas
Recursive one-to-many Relationships
Role of Glossaries & Data Dictionaries
Data Modelling Tools
Data Architectures
Overview of Enterprise Architecture
The Data Architecture
Types of Data Model
Conceptual Data Models
Logical Data Models
Physical Data Models
Role of the Data Architect
Transactional vs Business Intelligence Databases
Transactional Databases
Relational Database concepts
Data Analysis
Data Modelling vs Data Analysis
Functional Dependency
Dependency Diagrams
Repeating Groups of Attributes
Partial & Transitive Dependencies
Grouping Attributes into Tables
Many-to-many Associations between Attributes
User Views of the DB & Dependency Diagrams
Synthesising Dependency Diagrams into DB Design
Normalisation
First, Second, Third Normal Forms
Boyce/Codd Normal Form
Fourth & Fifth Normal Forms
Attribute Definitions
Joining Tables with SQL
High Cost of Error Correction in DB Designs
Business Intelligence Databases
Business Intelligence Concepts
Representing Facts as Data Cubes
Dimensional Modelling
Star Schema
Snowflake Schema
Data Marts
Data Warehouses
Data Modelling vs Dimensional Modelling
The Dimensional Model
Facts, Attributes & Keys
Dimensions
Granularity
‘Rolling Up’ & ‘Drilling Down’
Dimension Tables
Special Dimensions
Large & ‘Mini’-dimensions
Slowly Changing
Degenerate Dimensions
Fact Tables
Additive, Semi-additive & non-additive Facts
‘Fact-less’ Fact Tables
Fact Table Families
Building Dimensional Models
Extract, Transform & Load (ETL) Process