|
PACKAGE INCLUDES6 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
COURSE INTRODUCTIONThe 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
|