Fundamental Relational Concepts
very data-modeling technique has its own set of terms, definitions, and techniques. This vernacular permits us to understand complex and difficult concepts and to use them to design complex databases. This book applies relational data-modeling techniques for developing the data warehouse data model. To that end, thischapter introduces the terms and terminology of relational data modeling. It then continues with an overview of normalization techniques and the rules for the different normalization levels (for example, first, second, and third normal form) and the purpose for each. Sample data models will be given, showing the progression of normalization. The chapter ends with a discussion of normalization of thedata model and the associated benefits. Before we get into the various types of data models we use in creating a data warehouse, it is necessary to first understand why a data model is important and the various types of data models you will create in developing your BI environment.
Why Do You Need a Data Model?
A model is an abstraction or representation of a subject that looks or behaveslike all or part of the original. Examples include a concept car and a model of a
building. All models have a common set of objectives. They are designed to help people envision how the parts fit together, help people understand how to use or apply the final product, reduce the development risk, and ensure that the people building the product and those requesting it have the same expectations.Let’s look more closely at these benefits:
A model reduces overall risk by ensuring that the requirements of the final product will be satisfactorily met. By examining a “mock-up” of the ultimate product, the intended users can make a reasonable determination of whether the product will indeed fulfill their needs and objectives. A model helps the developers envision how the final product willinterface with other systems or functions. The level of effort needed to create the interfaces and their feasibility can be reasonably estimated if a detailed model is created. (In the case of a data warehouse, these interfaces include the data acquisition and the data delivery programs, where and when to perform data cleansing, audits, data maintenance processes, and so on.) A model helps all thepeople involved understand how to relate to the ultimate product and how it will pertain to their work function. The model also helps the developers understand the skills needed by the ultimate audience and what training needs to occur to ensure proper usage of the product. Finally a model ensures that the people building the product and those requesting it have the same expectations about theultimate outcome of the effort. By examining the model, the potential for a missed opportunity is greatly reduced, and the belief and trust by all parties that the ultimate product will be satisfactory is greatly enhanced.
We feel that a model is so important, especially when undertaking a set of projects as complex as building a business intelligence (BI) environment, that we recommend aproject be halted or delayed until the justification for a solid set of models is made, signed off on, and funded.
Relational Data-Modeling Objects
Now that we understand the need for a model, let’s turn our attention to a specific type of model—the data model. Before describing the various levels of models, we need to come up with a common set of terms for use in describing these models.Fundamental Relational Concepts
NOTE book is not intended to replace the many significant and authoritative books This
written on generic data modeling; rather this section should only serve as a refresher on some of the more significant terms we will use throughout the book. If more detail is needed, please refer to the wealth of data-modeling books at your disposal and listed in the...