Designing and Implementing Level 1

• 1.0 Relational Databases
• 1.1 What is a Database?
• 1.2 Relational Algebra
• 1.3 Relational Database Terms
• 2.0 Database Normalization
• 2.1 How to Normalize a Database
• 2.2 Case study: Employee Time Tracking System
• 3.0 An Overview of SQL Server
• 3.1 Features & Capacities
• 4.0 Creating Databases
• 4.1 SQL Server Databases & Logs
• 4.2 Files & FileGroups
• 4.3 Creating a Database
• 4.4 Database Size Planning
• 4.5 Using T-SQL to create a database

• 1.0 Creating Tables
• 1.1 SQL Server Data Types
• 1.2 Creating Tables
• 1.3 Adding Data to a Table
• 1.4 Creating Tables Using T-SQL
• 2.0 Constraints & Indexes
• 2.1 Data Integrity
• 2.2 Indexes
• 2.3 Creating CHECKS & Rules
• 2.4 Using T-SQL to Create Constraints
• 3.0 Retrieving Data
• 3.1 Select Statement
• 3.2 WHERE Clause
• 3.3 DISTINCT & Calculated Columns
• 3.4 ORDER BY Clause
• 3.5 Using SLQ Query Analyzer

• 1.0 Advanced Queries
• 1.1 Aggregated Functions
• 1.2 Grouping Data
• 1.3 Advanced Queries
• 1.4 Distributed Queries
• 2.0 Joining Tables
• 2.1 Inner Joins
• 2.2 Outer Joins
• 2.3 Cross Join & Self Join
• 2.4 Subqueries
• 2.5 Unions
• 3.0 Data Modification
• 3.1 INSERT Statements
• 3.2 Creating Temporary Tables
• 3.3 UPDATE Statements
• 3.4 DELETE Statement
• 4.0 System Functions
• 4.1 Date Functions
• 4.2 Mathematical Functions
• 4.3 String Functions
• 4.4 System Functions
• 4.5 Security Functions
• 4.6 Conversion Functions
• 4.7 User-Defined Functions
• 4.8 System Variables

• 1.0 Stored Procedures
• 1.1 Intro to Stored Procedures
• 1.2 Passing Parameters
• 1.3 Returning information from Stored Procedures
• 1.4 OUTPUT Parameters
• 1.5 Dynamic SQL
• 1.6 Cursors
• 2.0 Triggers & Views
• 2.1 Update Triggers
• 2.2 Insert Triggers
• 2.3 Delete Triggers
• 2.4 INSTEAD OF Triggers
• 2.5 Views
• 3.0 Locking Issues
• 3.1 Locking Scenarios
• 3.2 Isolation Levels
• 3.3 Resource Lock Types
• 3.4 Lock Modes
• 3.5 Lock Escalation
• 3.6 Viewing Locks
• 3.7 Setting the Isolation Level
• 3.8 Locking Hints

• 1.0 Transaction Processing
• 1.1 What is a Transaction?
• 1.2 Nesting Transactions
• 1.3 Transactions in Triggers
• 1.4 Trying out Transactions
• 1.5 Deadlocks
• 2.0 Data Transfer
• 2.1 Services Available
• 2.2 BCP
• 2.3 BULK INSERT
• 2.4 DTS
• 3.0 Full Text Search
• 3.1 What is Full Text Search?
• 3.2 Setting up Full Text Search
• 3.3 Performing a Full Text Search
• 3.4 Full Text System Procedures
• 3.5 FTS Enhancements in SQL 2000
• 4.0 Optimization
• 4.1 Optimizing your Server
• 4.2 Database design
• 4.3 Query Optimization
• 4.4 Index Usage
• 4.5 Statistics & Index Analyzer
• 4.6 SQL Server Profiler
• 4.7 Performance Monitor
• 5.0 System Databases & Tables
• 5.1 System Databases
• 5.2 System Tables

• 1.0 Using XML Extensions
• 1.1 Creating a Virtual Root
• 1.2 Using a Virtual Root
• 1.3 Returning Element Based XML
• 1.4 Passing Parameters
• 1.5 Multiple Result Sets
• 1.6 Using Stored Procedures
• 1.7 Inserting Data using XML
• 1.8 Joining Tables
• 1.9 Retrieving Data from VB
• 2.0 Application Development
• 2.1 OLE DB, ODBC & ADO
• 2.2 Using ADO

• 1.0 An Overview of SQL Server
• 1.1 Why use SQL Server?
• 1.2 Graphical Tools
• 1.3 Transact SQL
• 1.4 Database Objects
• 1.5 Security
• 1.6 Services
• 1.7 Capacities
• 1.8 Connectivity
• 1.9 Other Features
• 2.0 Installing SQL Server 2000
• 2.1 Operating Systems
• 2.2 Versions & Configurations
• 2.3 Multiple Instances
• 2.4 Licensing
• 2.5 File Locations
• 2.6 Sort Orders
• 2.7 Types of Installations
• 2.8 Installation Checklist
• 2.9 Performing an Installation
• 2.10 Upgrading
• 2.11 Troubleshooting an Upgrade
• 2.12 Unattended Installations
• 3.0 Database Setup & Maintenance
• 3.1 Database Objects
• 3.2 System Stored Procedures & Functions
• 3.3 Database Layout
• 3.4 Database Files & File Groups
• 3.5 Transaction Log
• 3.6 Database Growth
• 3.7 Creating Databases using Enterprise Manager
• 3.8 Creating Databases using T-SQL
• 3.9 Sp-helpdb
• 3.10 Expanding a Database
• 3.11 Shrinking a Database
• 3.12 Database Options
• 3.13 Recovery Models
• 3.14 Database Space Planning
• 3.15 Multiple Disk Usage

• 1.0 Security
• 1.1 Introduction to Security
• 1.2 Authentication
• 1.3 Creating Logins
• 1.4 Server Roles
• 1.5 Assigning Server Roles
• 1.6 Database Roles
• 1.7 Managing Database Roles
• 1.8 Permissions
• 1.9 Managing Permissions
• 1.10 Views & Security
• 1.11 Stored Procedures & Security
• 1.12 Application Roles
• 2.0 Operators, Jobs, Alerts & Mail
• 2.1 Definitions
• 2.2 SQL Server Agent Configuration
• 2.3 Operators
• 2.4 Jobs
• 2.5 Alerts
• 2.6 SQL Mail
• 2.7 Troubleshooting

• 1.0 DBCC
• 1.1 Maintenance Statements
• 1.2 Pin Statements
• 1.3 Trace Flags
• 1.4 Informational Statements
• 1.5 Check Statements
• 1.6 Old Statements
• 2.0 Backup & Restore
• 2.1 Why do a Backup?
• 2.2 Considerations of the backup process
• 2.3 Managing the Backup Devices
• 2.4 System database backup
• 2.5 Multiple Backup Devices
• 2.6 Backup Planning
• 2.7 Media Choices
• 2.8 Backup Types
• 2.9 Backup Scenarios
• 2.10 Copying/Moving
• 2.11 Stand-by servers& Clustering
• 2.12 Performing a backup
• 2.13 Restore a Backup
• 3.0 Database Maintenance Plan Wizard
• 3.1 What is the DMPW?
• 3.2 Running the Wizard
• 3.3 Jobs created by the wizard
• 4.0 Web Assistant
• 4.1 What is the Web Assistant?
• 4.2 Running the Web Assistant
• 4.3 Using a Template file
• 4.4 Triggers generated by Web Assistant
• 5.0 SQL Server Configuration Options
• 5.1 SQL Server Registration Properties
• 5.2 SQL Server Properties Dialog

• 1.0 Transferring Objects & Scripting
• 1.1 Methods of Data Transfer
• 1.2 DTS Designer
• 1.3 Copy Database Wizard
• 1.4 sp_attach_db & sp_detach_db
• 1.5 Scripting Objects
• 2.0 Error Logs
• 2.1 Windows Event Logs
• 2.2 SQL Server Error Logs
• 2.3 Demo of Windows Logs
• 2.4 Demo of SQL Server Logs
• 3.0 Distributed Management Objects
• 3.1 DMO Object Hierarchy
• 3.2 Logging into SQL Server
• 3.3 Loading a list of Databases
• 3.4 Loading Tables
• 3.5 Users & Table Scripts
• 3.6 Indexes & Columns
• 4.0 Tips & Tricks
• 4.1 General Tips & Tricks

• 1.0 Introduction
• 1.1 Topic Overview
• 1.2 Transactional vs. Analytical Data Systems
• 1.3 Data Warehouse Defined
• 1.4 Data Warehouses vs. Data Marts/OLAP
• 2.0 Dimensional Modeling & Planning
• 2.1 Entity-Relationship Modeling vs. Dimensional Modeling
• 2.2 Elements of the Dimensional Model
• 2.3 The Role of User Requirements
• 2.4 Identifying Source Data
• 2.5 Implementing the Data Warehouse Bus
• 3.0 Planning Facts
• 3.1 What is a fact?
• 3.2 Measures
• 4.0 Planning Dimensions
• 4.1 Planning Dimension Tables
• 4.2 Planning a Hierarchy
• 4.3 Special dimensions

• 1.0 Building a Data Mart
• 1.1 Creating the Database
• 1.2 Creating the Fact Table
• 1.3 Creating Dimension Tables
• 1.4 Creating Constraints and Indexes
• 2.0 Loading the Data Mart
• 2.1 Using Staging Tables
• 2.2 Using Transact SQL for Data Migration
• 2.3 Loading Data using Data Transformation Services
• 2.4 Advanced DTS Issues
• 2.5 Evaluating a Typical Load Scenario
• 3.0 Maintaining the Data Mart
• 3.1 Backup and Restoration Considerations
• 3.2 Handling Changing Facts
• 3.3 Handling Changing Dimensions
• 3.4 Securing the Data Mart

• 1.0 Introduction to OLAP
• 1.1 OLAP Concepts
• 1.2 Analysis Services Architecture
• 2.0 Cube Basics
• 2.1 Cube Basics & Measures
• 2.2 Dimensions & Hierarchies
• 2.3 Aggregations
• 2.4 Using the Analysis Manager
• 3.0 Creating Dimensions
• 3.1 Defining Terms
• 3.2 Creating Dimensions with the Dimension Wizard
• 3.3 Creating Time Dimensions
• 3.4 Using the Dimension Editor
• 3.5 Basic Dimension Properties
• 3.6 Using Member Properties
• 3.7 Virtual Dimensions
• 3.8 Multiple Hierarchies
• 4.0 Creating Cubes
• 4.1 Selecting Facts and Measures
• 4.2 Choosing Dimensions
• 4.3 Creating Calculated Measures
• 4.4 Storage Models
• 4.5 Processing Dimensions
• 4.6 Processing Cubes
• 4.7 Using Cube Properties

Data Warehousing Level 4

• 1.0 Managing Cube Partitions
• 1.1 Partitions Concepts
• 1.2 Creating Cube Partitions
• 1.3 Merging Partitions
• 2.0 Optimizing Cubes
• 2.1 Optimizing Aggregations with Storage Models
• 2.2 Usage-Based Optimizations
• 2.3 Tuning the Analysis Server
• 3.0 Special Features
• 3.1 Using Drillthrough
• 3.2 Creating and Using Actions (Demo: Custom actions)
• 4.0 Connecting Clients Using OLE DB
• 4.1 Analysis Application Architecture
• 4.2 Using Excel as an Analysis Server Client
• 4.3 Overview of Client Interfaces
• 5.0 Introduction to Data Mining
• 5.1 Data Mining Basics
• 5.2 Creating Data Mining Models

• 1.0 MDX Concepts Overview
• 1.1 What is MDX?
• 1.2 Role of MDX in Application Architecture
• 1.3 Creating Calculated Members Within Cubes
• 1.4 Using MDX in Client
• 1.5 MDX Examples
• 2.0 Creating Simple MDX Expressions
• 2.1 Understanding the Hierarchy
• 2.2 Using Brackets
• 2.3 Identifying Members
• 2.4 Qualifying Sets of Members
• 2.5 Creating Calculated Members (Demo: Creating a Calculated Member)
• 3.0 Interacting with Cubes
• 3.1 Anatomy of an MDX Query
• 3.2 Basic MDX Queries
• 3.3 Tuple Expressions (Demo: _____)
• 4.0 Performing Aggregations
• 4.1 Calculating data in an MDX Query
• 4.2 Using Aggregate Functions
• 4.3 Using Time Set Functions
• 5.0 Using MDX with ADOMD
• 5.1 Connecting to an Analysis Server
• 5.2 Executing an MDX Query