Description

The Microsoft SQL 2000 Database Implementation course gives students the skills and knowledge necessary to design and implement enterprise database solutions using SQL ServerTM 2000. Using our self-paced KnowledgeNet Interactive training solution, students will work through a system of modular lessons and hands-on labs to understand product architecture, write Transact-SQL code, and master the procedures for planning, configuring, and deploying a database using SQL Server 2000. This course will prepare students for Microsoft Certified Professional Exam 70-229, a core credit for the new Microsoft Certified Database Administrator certification and an elective for becoming a Microsoft Certified Systems Engineer.

Objectives

The key topics covered in this class include:

• Relational Databases
• Using Transact-SQL
• Designing Databases
• Implementing Databases and Tools
• Implementing Data Integrity
• Accessing and Modifying Data
• Managing and Manipulating Data
• Stored Procedures
• Implementing Triggers
• Implementing Views
• Indexes, Monitoring, and Tuning
• Managing Transactions and Locks
• Designing and Administering Security
  

Audience

This course is designed for database professionals responsible for creating and managing large information repositories in a Windows SQL 2000 database environment. This course provides the foundational information required for successful completion of the 70-229 certification exam, a core requirement for MCDBA certification and an elective option in the new Windows 2000 MCSE program.

Prerequisites

Before taking this course, students should have an understanding of basic relational database concepts and terminology, and of the SQL database language.

Included Materials

Interactive Self-Paced courses provide:

• Unlimited 24 x 7 access to Campus for the entire program (all courses are launched from a personalized web site that organizes and manages your training experience).
• Hands on labs - perfectly simulated, real-world environment to test skills without risk. Based on availability.
• Engaging course demos and real-world examples
• Access to a variety of training resources and certification information.

Labs

• Operating SQL Query Analyzer
• Navigating SQL Query Analyzer
• Viewing Query Results
• Creating and Modifying Tables
• Granting Permissions
• Inserting and Modifying Data
• Basic Database Design Concepts
• Creating a Database Diagram
• Creating Databases
• Modifying Databases
• Creating Tables
• Creating a Table
• Using Inner Joins
• Using a ROLLUP operator
• Adding Data to a New Table
• Deleting Data and Tables
• Importing Data Using BCP
• Importing Data-BULK INSERT
• Setting Up a Linked Server
• Creating a Cursor
• Exploring Stored Procedures
• Applying Integrity Constraints
• Creating Triggers
• Creating and Modifying Triggers
• Updating Column Values
• Testing the Trigger for Column Updates
• Creating Views
• Modifying Views
• Composite Index Creation
• Creating and Managing Indexes
• Event Capture Part 1
• Event Capture Part 2
• Modifying Transaction Log

Outline

Microsoft SQL Server 2000 Database Design and Implementation

• Relational Databases
• Fundamentals
• Concepts
• Features
• Components
• Tools
• Architecture
• Logical Database Components
• Physical Architecture
• Relational Database Engine Architecture
• Administration Architecture
• Data Warehousing and OLAP
• Using Transact-SQL
• Programming Tools and Transact-SQL
• Programming Tools
• Transact-SQL Fundamentals
• Syntax Elements and Statements
• Transact-SQL Syntax Elements
• Script Creation
• Executing Transact-SQL Statements
• Designing Databases
• Database Design and Planning
• Database Design Fundamentals
• SQL Server Database Planning
• Requirements and Data Models
• Identifying System Requirements
• Developing a Logical Data Model
• Implementing Databases and Tables
• Creating and Managing Databases
• Database Creation
• Database Management
• Data Types
• Creating and Managing Tables
• Table Creation
• Table Management
• Implementing Data Integrity
• Data Integrity Fundamentals
• General Column Properties
• Advanced Techniques
• Integrity Constraints
• PRIMARY KEY and UNIQUE Constraints
• FOREIGN KEY Constraints
• CHECK Constraints
• Accessing and Modifying Data
• Data Access
• The SELECT Statement
• Locations and Conditions
• Grouping and Sorting
• Advanced Data Access
• Using Joins
• Defining Subqueries
• Summarizing Data
• Data Modification
• Inserting Data
• Modifying Data in a SQL Server Database
• Deleting Data
• Managing and Manipulating Data
• Data Import and Export
• Using bcp and BULK INSERT
• Using DTS
• Distributed Queries and Cursors
• Using Linked Server Names
• Using Ad Hoc Computer Names
• Using Cursors to Retrieve Data
• XML Data
• Using XML
• Using FOR XML
• Using OPENXML
• Implementing Stored Procedures
• Introduction to Stored Procedures
• Stored Procedure Advantages
• Categories of Stored Procedures
• Managing Stored Procedures
• Modifying Stored Procedures
• Administering Procedures
• Executing Stored Procedures
• Programming Stored Procedures
• Parameters and Error Handling
• Procedures and Data Retrieval
• Application
• Implementing Triggers
• Introduction to Triggers
• Trigger Basics
• Trigger Events
• Creating and Managing Triggers
• Creating Triggers
• Trigger Management
• Programming Triggers
• Pseudo Tables and Trigger Syntax
• Functions and System Commands
• Implementing Views
• Managing Views
• Fundamentals
• Creating Views and Indexes
• Modifying and Deleting Views
• Accessing Data Through Views
• Viewing and Modifying Data
• Using Views to Access Data
• Indexes, Monitoring, and Tuning
• Index Implementation
• Index Architecture
• Index Characteristics
• Index Information
• Index Creation
• Index Administration
• Performance Optimization
• Database Monitoring
• SQL Profiler
• Index Tuning
• Partitioning
• Managing Transactions and Locks
• Transaction and Locking Architecture
• Transaction Logs
• Concurrency and Locking
• Managing SQL Server Transactions
• SQL Server Transactions
• Transaction Types
• Managing SQL Server Locking
• Concurrency Problems
• Customizing Locking
• Designing and Administering Security
• Designing a Security Plan
• Security Fundamentals
• Security Configuration and Tools
• Database Security Plans
• Plan Fundamentals
• Authorization and Authentication
• Permission Administration
• Role Administration