SQL Server 2005 > MOC 2782: Designing SQL Server 2005 Databases

MOC 2782: Designing SQL Server 2005 Databases


MOC 2782: Designing SQL Server 2005 Databases

PACKAGE INCLUDES

  • 6 CD-ROMs featuring live instructor-led classroom sessions with full audio, video and demonstration components
  • Printable courseware
  • Sample Code
  • Step-by-step hands-on-labs
  • Comprehensive, understandable, and reusable as reference material


    • Single User CD Course
    £ 198
    Single User Online Course
    £ 158
    Multi User CD Course
    £ 396

    COURSE INTRODUCTION

    The Technology Specialist certifications enable professionals to target specific technologies and distinguish themselves by demonstrating in-depth knowledge and expertise in their specialized technologies.

    This video-based course provides students with the knowledge and skills to design databases for Microsoft SQL ServerT 2005 using business requirements to guide their decisions (beyond structured third normal form [3NF] modeling techniques). Students will also learn to incorporate security requirements throughout their design.

    Course Outline


    Module 1 - Steps to Designing Databases for SQL Server 2005

    Steps to Designing Databases for SQL Server 2005
    Steps to Designing Stable Databases
    Find a Plan and Stick To It
    Know the Core Scope
    Seek Knowledge
    Create a Contract
    Consider History..
    Upgrade and Integrate
    Demo - Requirements Document
    A Picture Is Worth…
    ORM
    ER
    UML
    Decision Making
    Demo - Modeling
    Review

    Module 2 - Logical Levels of Modeling

    Logical Levels of Modeling
    Move from Conceptual to Logical
    Focus On Real World Dependencies
    Primary Key: Substitutes or Real Attributes
    Move Toward Logical ER
    Model Theorys Functional Dependencies
    Demo - Generate a Logical ER Form
    Why Normalize?
    Normalize How?
    Demo - Normalization Examples
    OLAP Designs
    Creating the Fact Table
    Creating Dimensions
    Refactor a Logical Model
    Review

    Module 3 - Physical Levels of Modeling

    Physical Levels of Modeling
    Naming Conventions for Identifiers
    Additional Naming Conventions for Identifiers
    Demo - Showing Identifiers at Work
    RDMS Specific Data Type Decisions
    CLR Thoughts
    XML Thoughts
    How to Store – Character Type vs. New XML Type
    Computed Columns Thoughts
    Thoughts on Column Constraints
    Thoughts on Table Constraints
    New Data Definition Triggers
    Demo - Creating DDL Triggers
    “Who Are You? What Do You Want?”
    Protect the Data
    Audits
    Configuration Settings
    Improve I/O Performance
    Database Options
    Sample Your Design
    Extract-Transform-Load
    Review

    		 

    Module 4 - Performance Considerations to Make During Design

    Performance Considerations to Make During Design
    Clustered Index Thoughts
    Non-Clustered Index Thoughts
    Thoughts on XML Data Indexes
    Demo - Defining XML Indexes
    Thoughts on Computed Column Indexing
    Why Use Views
    Thoughts on Indexed Views
    Demo - Balancing Indexes with Cost
    Benefit with Partition Tables
    Thoughts on Summary Tables
    You Might Denormalize Now
    Thoughts on Snapshot Isolation
    Tempdb Does a Lot of Work
    Demo - Evaluating Tempdb Resources
    Validate Database Solution
    Review

    Module 5 - Data Access Designs

    Data Access Designs
    SQL 2005 Provides Great Granularity of Permissions
    Define Access Policies
    User Defined Functions
    Special User Defined Functions Thoughts
    New User Defined Aggregates
    Demo - User Defined Aggregates
    Stored Procedures
    Demo - EXECUTE AS
    Review

    Module 6 - Dependencies Count In Designs

    Dependencies Count In Designs
    If You Must Cross-DB…
    Old School Procedures
    Demo - Creation of Least Privilege Accounts
    Reach Out To COM
    Demo -Wrap an OLE Automation Or Do in CLR
    Reach Beyond the Instance
    Spread Data Around
    High Availability
    Review
    Course Closure
    Page Execution time is 4.703 seconds.