LOGICAL AND PHYSICAL DATA MODELING

This popular skill-building seminar begins where Conceptual Data Modeling: The Entity/Relationship Model ends, completing the student's skills in the three-phase process of Conceptual, then Logical, then Physical Data Modeling to build properly organized, shared, stable, well-defined, easy to use, and easily maintained databases which will markedly improve business performance and profitability. The three-step Conceptual, Logical and Physical Data Modeling process ensures that each particular concern is addressed in its proper time and sequence to completely eliminate error and rework. This is not only the fastest way to design databases, it is also the cheapest and the best -- a threefer!

The Logical Data Modeling (LDM) portion of this seminar teaches step-by-step techniques, skills, and rules necessary to form, define, standardize, and normalize the data elements (individual data facts) into the entities (conceived and defined in Conceptual Data Modeling) so that they can be shared for all business purposes. The principles of data normalization (first, second and third normal form) are taught in a natural and practical manner to ensure that the student can properly organize data in the LDM so it is non-redundant, easy to find, and sharable for all uses.

The Physical Data Modeling portion of the seminar teaches a very sophisticated step-by-step method for qualifying (and if possible, quantifying) the physical constraints imposed by the business (performance speeds of transactions, data volume limitations, physical distribution of the data at different locations, security protection of selected data, etc.), and for creating a best-balanced physical design of the database which meets those (sometimes conflicting) constraints without sacrificing the natural subject organization of the data. Extensive workshops in both Logical and Physical Data Modeling ensure that the student acquires usable and practical skills.

The seminar material is completely independent of any vendor's CASE tool or database management system (DBMS).

TOPICAL OUTLINE

• Background Concepts
  • IRM Environment Defined
  • Traditional "Systems" Approach
  • Orthogonality of Process and Data
  • Information Resource Categories
  • IRM View
  • The DAta Resource (Supermarket Analogy)
  • IRM Requires...
  • IRM Critical Success Factors
  • Conceptual/Logical/Physical Data Modeling in Context
  • Data Model Continuity
  • Time and Data
  • Data Modeling Sessions - Logistics and Critical Success Factors
• Conceptual Data Modeling Overview
  • Purpose, Form and Content
  • Conceptual Data Modeling Definitions
  • Conceptual Data Modeling Steps - Overview
  • Entity Rules
  • Relationship Rules
  • E/R Diagram Symbols
  • Special E/R Constructs
    • N-ary Relationship
    • Recursive Relationship
    • Subtype/Supertype Construct
    • Characteristic Entity
    • Associative Entity
    • Relationship Roles
    • Role Entity
  • Analysis of E/R States
    • Example State/Transition Diagram
    • State Variations
    • Capturing State Information in the Model
  • Entity Definition Example
    • Rules for Primary Keys
  • Relationship Definition Example
  • The Metal-E/R Model (Repository to Support Conceptual Modeling
• Logical Data Modeling
  • Purpose, Form and Content
  • Logical Data Modeling Definitions
  • Alternative Approaches
  • Working Around Your CASE Tool
  • Typical Logical Data Model Diagrams
  • Logical Data Modeling Steps
    • 1. Identifying Pertinent Transactions/Dataviews for Analysis
    • 2. Analyzing the Transaction/Dataview
    • 3. Standardizing and Defining Required Data Elements
      • 3.1 Detecting and Understanding Need and Meaning
      • 3.2 Determining Entity or Relationship of Residence
      • 3.3 Choosing Best Representation
        • Rules for Data Elements
        • Data Element Types
        • Handling Derived Data Elements
        • Cohesive Data Elements
        • Data Elements to Represent States
      • 3.4 Fully Defining the Data Element
        • Data Element Definition Pro Forma
        • Example Data Element Definition
        • Data Element Domains and Synonyms
      • 3.5 Naming the Data Element
        • Example Data Element Naming Standards
        • Use of Keyword Glossaries
      • 3.6 Checking the Dictionary/Repository for Redundancy
        • Setting Up the Dictionary/Repository for Semantic Search
      • 3.7 Documenting New Data Elements in the Dictionary/Repository
    • Example Logical Modeler's Questions for Data Elements
    • 4. Diagramming and Normalizing the Dataview
      • Bubblecharting Symbols
      • Bubblechart/Logical Structure Rules
      • Starting the Bubblechart
      • Appending Attributes to the Correct Primary Key
      • The Normal Forms
        • First Normal Form Examples
        • Second Normal Form Examples
        • Third Normal Form Examples
      • Bubblecharting Notes
        • Recursive Relationship in Logical Data Model
        • Multiple Relationships Between Same Entities
        • Subtype/Supertype in Logical Data Model
        • Characteristic Entity vs. Subordinate Data Group
        • Relationship Roles in Logical Data Model
      • Logical Modeler's Questions for Normalizing Data Elements
    • 5. Fully Defining New LDG's, Associations, Entities, Relationships
      • Logical Data Group Naming convention
      • Logical Data Group Definition Pro Forma
      • Example Logical Data Group Definition
      • Key-to-Key Association Definition Pro Forma
      • Example Association Definition
    • 6. Verifying the Bubblechart
      • Logical Modeler's Questions for Verifying
    • 7. Synthesizing Bubblechart into Composite Logical Model
      • Synthesis Example
      • Meta-E/R Model After Synthesis
    • 8. Reviewing and Stabilizing the Logical Data Model
      • Synchronizing the Logical and E/R Models
      • Synchronizing the Logical Data and Transaction Models
      • Stability Review at Logical Model Level of Detail
      • Logical Modeler's Questions for Reviewing
• Physical Data Modeling
  • Purpose, Form and Content
  • Physical Data Modeling Definitions
  • Physical Modeling Steps
    • 1. Formalizing and Weighting Design Objectives
    • 2. Defining Physical and Technological Environment
    • 3. Laying Out First Cut Physical Design(s)
      • General Logical to Physical Transform (Relational)
        • Physical Data Model Schematic Diagram Symbols
        • Logical to Physical Transform
      • General Foreign Key Rules
      • Example Logical to Physical Transforms
      • Special Foreign Key Situations
      • Short Detour into the World of Distributed Data
        • Distribution Definitions
        • Qualitative Analysis Steps
        • Data Distribution Modes
        • Example Distributed Design
        • Quantitative Analysis
    • 4. Deciding Stored vs. Virtual Derived Data
    • 5. Analyzing and Adjusting for Large Data Volume and Growth
    • 6. Analyzing and Adjusting for Security Requirements
    • 7. Analyzing and Adjusting for Transaction Performance
      • Modeling Transaction Data Usage Patterns
      • Performance Prototypes
      • Possible Adjustments to Improve Performance
        • Clustering/Separating
        • Denormalizing and Side Effects
        • Secondary Indices
    • 8. Analyzing and Adjusting for Ease of Use
    • 9. Assessing Design Objectives
    • 10. Finalizing the Physical Model
    • 11. Specifying the Design in DBMS DDL
  • Meta-E/R Model (Repository) for Physical Data Modeling
• Workshops
  • Data Element Standardization and Bubblecharting
  • Bubblecharting Various Dataviews
  • Bubblechart Merging/Synthesis
  • First Cut Physical Database Design/Distribution Analysis
  • Deciding Stored Derived Data
  • Analyzing Volume and Growth
  • Analyzing Security and Integrity Requirements
  • Analyzing Transaction Performance Requirements
  • Analyzing Ad Hoc Usage Requirements

DURATION: 5 days

TARGETED AUDIENCES: (recommended maximum number of attendees - 25)

• IS/IRM Management
• Data Administrators/Data Base Administrators
• Conceptual/Logical Data Modelers
• Physical Database Designers
• Process/Transaction Modelers
• Development Project Managers
• Business persons participating in development projects

PREREQUISITE: Conceptual Data Modeling: the Entity/Relationship Model

RETURN TO SEMINARS PAGE