Assuming no prior knowledge of data modelling, we introduce our first case study which illustrates four important gaps filled by data models. Next, we will explain data modelling concepts and terminology, and provide you with a set of questions you can ask to quickly and precisely build a data model. We will also explore each component on a data model and practice reading business rules. We will complete several exercises, including one on enhancing an existing data model. You will be able to answer the following questions by the end of this section:
• What is a data model and what characteristic makes the data model an essential wayfinding tool?
• How does the 80/20 rule apply to data modelling?
• What six questions must be asked to translate ambiguity into precision?
• What two situations can ruin a data model’s credibility?
• What are five key skills every data modeller should possess?
• Why are there at least 108 ways to model any situation?
• What do a data model and a camera have in common?
• What are entities, data elements, and relationships?
• Why subtype and what are the differences between exclusive and non-exclusive subtypes?
• How do different modelling notations represent subtypes?
• What are candidate, primary, natural, secondary, and foreign keys?
• What are the perceived and actual benefits of surrogate keys?
• What is cardinality and referential integrity and how do they lead to better data quality?
• How do you “read” a data model?
• What are the different ways to model hierarchies and networks?
• What is recursion and why is it such an emotional topic?
Conceptual, logical, and physical data models
The conceptual data model captures a business need within a well-defined scope, the logical data model captures the business solution, and the physical data model captures the technical solution. Relational and dimensional techniques will be described at each of these three levels. We will also practice building several data models and you will be able to answer the following questions by the end of this section:
• How do relational and dimensional models differ?
• What are the seven different types of data models?
• Why are conceptual and logical data models so important?
• What are five different ways of communicating the conceptual?
• What are six conceptual data modelling challenges?
• What is the difference between grain, base, and atomic on a dimensional data model?
• What are the three different paths for navigation on a dimensional data model?
• What are the differences between transaction, snapshot and accumulating measures?
• What are the three different variations of conformed dimensions?
• What are some dimensional modelling do’s and don’ts?
• What is the difference between a star schema and a snowflake?
• What is normalization and how do you apply the Normalization Hike?
• Where should denormalization be performed on your models?
• What are the five denormalization techniques?
• What is the difference between aggregation and summarization?
• What are the four different types of Slowly Changing Dimensions?