Data Modeling Made Simple with Embarcadero ER/Studio Data Architect: Adapting to Agile Data Modeling in a Big Data World

Description

Build a working knowledge of data modeling concepts and best practices, along with how to apply these principles with ER/Studio. This second edition includes numerous updates and new sections including an overview of ER/Studio's support for agile development, as well as a description of some of ER/Studio's newer features for NoSQL, such as MongoDB's containment structure. You will build many ER/Studio data models along the way, applying best practices to master these ten objectives: Know why a data model is needed and which ER/Studio models are the most appropriate for each situation Understand each component on the data model and how to represent and create them in ER/Studio Know how to leverage ER/Studio's latest features including those assisting agile teams and forward and reverse engineering of NoSQL databases Know how to apply all the foundational features of ER/Studio Be able to build relational and dimensional conceptual, logical, and physical data models in ER/Studio Be able to apply techniques such as indexing, transforms, and forward engineering to turn a logical data model into an efficient physical design Improve data model quality and impact analysis results by leveraging ER/Studio's lineage functionality and compare/merge utility Be able to apply ER/Studio's data dictionary features Learn ways of sharing the data model through reporting and through exporting the model in a variety of formats Leverage ER/Studio's naming functionality to improve naming consistency, including the new Automatic Naming Translation feature. This book contains four sections: Section I introduces data modeling and the ER/Studio landscape. Learn why data modeling is so critical to software development and even more importantly, why data modeling is so critical to understanding the business. You will learn about the newest features in ER/Studio (including features on big data and agile), and the ER/Studio environment. By the end of this section, you will have created and saved your first data model in ER/Studio and be ready to start modeling in Section II! Section II explains all of the symbols and text on a data model, including entities, attributes, relationships, domains, and keys. By the time you finish this section, you will be able to 'read' a data model of any size or complexity, and create a complete data model in ER/Studio. Section III explores the three different levels of models: conceptual, logical, and physical. A conceptual data model (CDM) represents a business need within a defined scope. The logical data model (LDM) represents a detailed business solution, capturing the business requirements without complicating the model with implementation concerns such as software and hardware. The physical data model (PDM) represents a detailed technical solution. The PDM is the logical data model compromised often to improve performance or usability. The PDM makes up for deficiencies in our technology. By the end of this section you will be able to create conceptual, logical, and physical data models in ER/Studio. Section IV discusses additional features of ER/Studio. These features include data dictionary, data lineage, automating tasks, repository and portal, exporting and reporting, naming standards, and compare and merge functionality. Read me first! Conventions Used in This Book What’s Different in the 2nd Edition? How to Get the Most out of This Book SECTION I Foundation CHAPTER 1 Data Model Overview Finding Your Way Representing an Information Landscape Leveraging the Data Model Embarking on Our Publishing Adventure EXERCISE 1.1: Educating Your Neighbor CHAPTER 2 ER/Studio Functionality EXERCISE 2.1: Learning More About the ER/Studio Family New Features in ER/Studio My “Top 10” Favorite Features of ER/Studio EXERCISE 2.2: Installing and Starting ER/Studio CHAPTER 3 ER/Studio Landscape Using the Windows Data Model Explorer Data Model Window Overview Window Zoom Window Using the Menus Application Menus Shortcut Menus Using the Toolbars Using Keyboard Commands Using the Status Bar EXERCISE 3.1: Creating a New Data Model Creating Title Blocks Editing Title Blocks Deleting Title Blocks EXERCISE 3.2: Saving Your Data Model EXERCISE 3.3: Closing and Opening Existing Data Models EXERCISE 3.4: Getting Comfortable with ER/Studio SECTION II Data Model Objects CHAPTER 4 Entities Entity Explanation Entity Types Entities in ER/Studio Creating Entities Creating Text Blocks Editing Entities Changing the Name of an Entity Moving Entities Resizing Entities Changing the Appearance of Entities Copying Entities Finding Entities Find Entity/View Universal Naming Utility Deleting Entities EXERCISE 4.1: Creating Entities CHAPTER 5 Submodels Submodel Explanation Submodels in ER/Studio Creating Submodels Editing Submodels Moving Submodels Deleting Submodels EXERCISE 5.1: Changing Settings in Submodels EXERCISE 5.2: Creating Three More Submodels EXERCISE 5.3: Creating Title Blocks for Each Submodel CHAPTER 6 Attributes and Domains Attribute Explanation Attribute Types Attributes in ER/Studio Creating Attributes Editing Attributes Moving Attributes Defining Attributes Changing the Appearance of Attributes Copying Attributes Finding Attributes Deleting Attributes EXERCISE 6.1: Creating Attributes Key Explanation Candidate Key Primary and Alternate Keys Surrogate Key Foreign Key Inversion Entry EXERCISE 6.2: Clarifying Customer ID Keys in ER/Studio Creating a Key Editing a Key EXERCISE 6.3: Creating Keys Domain Explanation CHAPTER 7 Relationships Relationship Explanation Relationship Types Cardinality Independent vs. Dependent Entities Recursion Containment Subtyping EXERCISE 7.1: Reading a Model Data Modeling Notations Relationships in ER/Studio Creating Relationships Creating Subtypes Creating Recursive Relationships Duplicate Relationships Duplicate Attributes Creating Containment Relationships Editing Relationships Moving Relationships Changing the Appearance of Relationships Finding Relationships Deleting Relationships EXERCISE 7.2: Creating Relationships SECTION III Conceptual, Logical, and Physical Data Models CHAPTER 8 Conceptual Data Models Conceptual Data Model Explanation Relational and Dimensional Conceptual Data Models Relational CDM Example Dimensional CDM Example Creating a Conceptual Data Model Step 1: Ask the Five Strategic Questions Step 2: Identify and Define the Concepts For Relational For Dimensional Step 3: Capture the Relationships For Relational For Dimensional Step 4: Determine the Most Useful Form For Relational For Dimensional Step 5: Review and Confirm EXERCISE 8.1: Creating a Conceptual Data Model EXERCISE 8.3: Segmenting the Publisher CDM into Submodels EXERCISE 8.4: Creating a Conceptual Data Model for Your Organization CHAPTER 9 Logical Data Models Logical Data Model Explanation Relational and Dimensional Logical Data Models Relational LDM Example Dimensional LDM Example Creating a Relational Logical Data Model Normalization Initial Chaos First Normal Form (1NF) Second Normal Form (2NF) Third Normal Form (3NF) Abstraction Creating a Dimensional Logical Data Model EXERCISE 9.1: Creating a Dimensional Logical Data Model CHAPTER 10 Physical Data Models Physical Data Model Explanation Relational and Dimensional Physical Data Models Relational PDM Example Dimensional PDM Example Creating a Physical Data Model in ER/Studio Generating a Physical Data Model from a Logical Data Model Generating a Physical Data Model from a Database (Reverse Engineering) Reverse Engineering from Microsoft Access® Reverse Engineering from MongoDB® Reverse Engineering from Hive® Generating a Physical Data Model from a SQL File Generating a Database Using ER/Studio Editing Tables Customizing Datatype Mapping Denormalization Rolldown Denormalization Rollup Denormalization Star Schema Denormalizing in ER/Studio EXERCISE 10.1: Denormalizing Views Views in ER/Studio EXERCISE 10.2: Creating Views Indexing Indexing in ER/Studio EXERCISE 10.3: Indexing Partitioning Partitioning in ER/Studio EXERCISE 10.4: Partitioning EXERCISE 10.5: Tracing from Physical Back to Logical SECTION IV Additional ER/Studio Features CHAPTER 11 Data Dictionary Importing a Data Dictionary Types of Objects Imported from a Data Dictionary Domains Domains in ER/Studio EXERCISE 11.1: Creating Domains User-Defined Datatypes User-Defined Datatypes in ER/Studio EXERCISE 11.2: Creating User-Defined Datatypes Reference Values Reference Values in ER/Studio EXERCISE 11.3: Creating Reference Values Attachments Attachments in ER/Studio Exercise 11.4: Creating Attachments CHAPTER 12 Data Lineage Using the Data Lineage Tab Defining Source Systems in ER/Studio Creating Data Movement Rules in ER/Studio Creating a Data Flow in ER/Studio Using the Table Editor to Further Document Lineage Using the Column Editor to Further Document Lineage Exercise 12.1: Creating a Data Lineage A Less Formal Way to Connect Things: User-Defined Mappings CHAPTER 13 Importing, Exporting, Printing, and Reporting Importing into ER/Studio Importing from External Metadata Importing from an ERX File Importing from a SQL File Exporting out of ER/Studio Exporting to External Metadata Exporting to XML Exporting to a SQL file Exporting an Image Printing in ER/Studio Reporting in ER/Studio Exercise 13.1: Importing, Exporting, Printing, and Reporting CHAPTER 14 Naming Standards Creating a Naming Standards Template Applying the Naming Standards Utility Assigning Naming Standards to Objects Automatic Naming Translation Exercise 14.1: Creating a Naming Standard Template CHAPTER 15 Compare/Merge Utility Comparing Models and Submodels in ER/Studio Exercise 15.1: Running the Compare and Merge Utility CHAPTER 16 Features for Agile Teams and Continuous Improvement Macros Running Macros Top 5 Favorite Macros Creating Macros Editing Macros Deleting Macros Renaming Macros Exercise 16.1: Creating and Running Macros Reusable Procedure Logic Reusable Triggers Reusable Procedures Libraries Change Management Named Releases Model Validation APPENDIX A References APPENDIX B Answers to Exercises EXERCISE 1.1: Educating Your Neighbor EXERCISE 5.1: Changing Settings in Submodels EXERCISE 6.2: Clarifying Customer ID Document Uniqueness Properties Document the Characteristics of the Identifier Define the Customer EXERCISE 7.1: Reading a Model EXERCISE 8.1: Creating a Conceptual Data Model APPENDIX C Glossary APPENDIX D ER/Studio Commands Quick Reference Most Frequently Used Commands Model Level Commands Entity and Table Level Commands Sublevel Commands Attribute and Key Commands Relationship Commands Macro Commands Index