Microsoft SQL Server 2000 : Database Design

Microsoft SQL Server 2000 : Database Design

Course Specifications
Course number: 077462

Course length: 5 days
Software Version Number: 2000

Course Description

Overview: This course teaches you how to use the Transact-SQL language to query and program Microsoft SQL Server 2000 in a Windows 2000 Server environment. This course also assists you in preparing for the Microsoft Certified Systems Engineer and Microsoft Certified Database Administrator Exam #70-229, Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition.

Prerequisites: To ensure your success, we recommend you first take the following Element K courses or have equivalent knowledge:

Windows 2000: Installation and Administration , or the
Windows 2000 for Windows NT Administrators: Element K course, or have equivalent knowledge. You should also know the basics of querying a SQL server by using the SELECT, INSERT, and UPDATE SQL statements. If you aren't familiar with using these commands, you should first take the
Microsoft SQL Server 2000: Querying with Transact-SQLElement K course.

Performance-Based Objectives
Lesson objectives help students become comfortable with the course, and also provide a means to evaluate learning. Upon successful completion of this course, students will be able to:

Identify the features of Microsoft SQL Server 2000.
Use Transact-SQL to query a SQL server.
Design, create, and manage databases.
Create and manage tables.
Implement data integrity techniques.
Design and implement indexes.
Query multiple tables through the use of joins.
Design subqueries.
Use aggregate functions in queries and create user-defined functions.
Create and manage views.
Design and implement stored procedures.
Create triggers.
Manage transactions and locks.
Implement queries across multiple servers.
Optimize queries.
Analyze query performance.

Course Content

Lesson 1: An Overview of SQL Server
          Topic 1A: Exploring the Components of SQL Server
                   Task 1A-1: Exploring the Features of Microsoft SQL Server
                   Task 1A-2: Exploring the SQL Server Services
                   Task 1A-3: Identifying Administrative Utilities
                   Task 1A-4: Using SQL Server Enterprise Manager
                   Task 1A-5: Using SQL Query Analyzer
          Topic 1B: SQL Server Database Structure
                   Task 1B-1: Discussing the Components of SQL Server
                   Task 1B-2: Identifying the Default Databases and System Tables
                   Task 1B-3: Creating and Working with a Database Diagram
          Topic 1C:
                   Task 1C-1: Understanding the SQL Server Architecture
                   Task 1C-2: Exploring the Application Architecture
          Topic 1D: Identifying SQL Server Management Tasks
                   Task 1D-1: Determining SQL Server Management Tasks
                   Task 1D-2: Configuring Your Server’s Authentication Mode
                   Task 1D-3: Creating a SQL Login Account
                   Task 1D-4: Working With Server and Database Roles

Lesson 2: Exploring Transact-SQL
          Topic 2A: Working with Transact-SQL
                   Task 2A-1: Using SQL Query Analyzer
                   Task 2A-2: Using Osql
          Topic 2B: Transact-SQL Statements
                   Task 2B-1: Creating a Simple Table
                   Task 2B-2: Inserting Data
                   Task 2B-3: Selecting Data
                   Task 2B-4: Assigning Permissions to the Public Database Role
          Topic 2C: Programming in Transact-SQL
                   Task 2C-1: Using Variables
                   Task 2C-2: Saving a Script File
                   Task 2C-3: Choosing an XML Format

Lesson 3: Designing and Implementing Databases
          Topic 3A: Identifying Database Design Issues
                   Task 3A-1: Designing Databases
                   Task 3A-2: Identifying the Space Requirements for Databases
                   Task 3A-3: Optimizing Database Performance
          Topic 3B: Creating Databases
                   Task 3B-1: Setting Database Options
                   Task 3B-2: Using Stored Procedures to View Database Information
          Topic 3C: Managing Databases
                   Task 3C-1: Configuring SQL Server to Automatically Shrink a DatabaseFile
                   Task 3C-2: Deleting a Database

Lesson 4: Creating and Managing Tables
          Topic 4A: Design and Create Tables
                   Task 4A-1: Normalizing Table Designs
          Topic 4B: Maintaining Tables
                   Task 4B-1: Using SQL Server Enterprise Manager to Generate a Script

Lesson 5: Designing and Implementing Data Integrity
          Topic 5A: Understanding Data Integrity
          Topic 5B: Implementing Constraints
                   Task 5B-1: Adding a Primary Key Constraint
                   Task 5B-2: Adding a Foreign Key Constraint to the Movie Table
                   Task 5B-3: Adding a Default Constraint to the Movie Table
                   Task 5B-4: Adding a Check Constraint to the Movie Table
                   Task 5B-5: Disabling Constraint Checking

Lesson 6: Implementing Indexes
          Topic 6A: Designing Indexing
                   Task 6A-1: Understanding Index Architecture
          Topic 6B: Implementing Indexes
                   Task 6B-1: Creating a Clustered Index on the Movie Table
                   Task 6B-2: Viewing the Information in Sysindexes
          Topic 6C: Maintaining Indexes
                   Task 6C-1: Rebuilding an Index
                   Task 6C-2: Using DROP_EXISTING to Re-create an Index Based on a PrimaryKey
                   Task 6C-3: Generating a Workload File in SQL Profiler
                   Task 6C-4: Observing Index Statistics

Lesson 7: Joining Tables
          Topic 7A: Querying Multiple Tables
                   Task 7A-1: Working with Cross Joins
          Topic 7B: Implementing Advanced Table Joins
                   Task 7B-1: Joining Multiple Tables
                   Task 7B-2: Working with Self Joins
                   Task 7B-3: Combining SELECT Statements
                   Task 7B-4: Creating a New Table Based on a SELECT INTO Statement

Lesson 8: Designing Advanced Queries
          Topic 8A: Designing Subqueries
                   Task 8A-1: Designing Single Value Subqueries
          Topic 8B: Changing Data Through Queries
                   Task 8B-1: Inserting Data Based on a Query
                   Task 8B-2: Deleting Rows Based on a Query

Lesson 9: Designing Views
          Topic 9A: Creating and Managing Views
                   Task 9A-1: Creating a View
                   Task 9A-2: Displaying View Information
                   Task 9A-3: Dropping a View
                   Task 9A-4: Creating an Indexed View
                   Task 9A-5: Creating a Partitioned View

Lesson 10: Creating Stored Procedures
          Topic 10A: Designing Stored Procedures
                   Task 10A-1: Using Books Online to Research System Stored Procedures
                   Task 10A-2: Running Extended Stored Procedures
                   Task 10A-3: Understanding How SQL Server Processes Stored Procedures
          Topic 10B: Creating Stored Procedures
                   Task 10B-1: Creating a Stored Procedure
                   Task 10B-2: Executing a Stored Procedure
          Topic 10C: Using Parameters in Stored Procedures
                   Task 10C-1: Creating a Stored Procedure With an Input Parameter
                   Task 10C-2: Creating and Executing a Stored Procedure With Output Parameters
          Topic 10D: Managing Stored Procedures
                   Task 10D-1: Recompiling a Stored Procedure

Lesson 11: Using Functions
          Topic 11A: Working with Aggregate Functions
                   Task 11A-1: Using Aggregate Functions to Summarize Data
                   Task 11A-2: Designing GROUP BY Queries
                   Task 11A-3: Using TOP in a Query
          Topic 11B: Designing and Creating User-defined Functions
                   Task 11B-1: Dropping a User-defined Function

Lesson 12: Creating Triggers
          Topic 12A: Designing and Implementing Triggers
                   Task 12A-1: Designing Triggers
                   Task 12A-2: Creating an INSERT Trigger
                   Task 12A-3: Creating a DELETE Trigger
                   Task 12A-4: Creating an INSTEAD OF Trigger

Lesson 13: Understanding Transactions and Locks
          Topic 13A: Designing and Implementing Transactions
                   Task 13A-1: Working with Explicit Transactions
          Topic 13B: Managing Locks
                   Task 13B-1: Observing the Current Locks on Your Server
                   Task 13B-2: Implementing Session Locking
                   Task 13B-3: Implementing a Lock Timeout
                   Task 13B-4: Implementing Table-level Locking

Lesson 14: Implementing Distributed Queries
          Topic 14A: Establishing Linked Servers
                   Task 14A-1: Defining a Linked Server
                   Task 14A-2: Logging in to Linked Servers
          Topic 14B: Creating and Managing Distributed Queries
                   Task 14B-1: Configuring the MSDTC Service
                   Task 14B-2: Using Ad Hoc Queries to Retrieve Data From Remote Servers

Lesson 15: Optimizing Queries
          Topic 15A: Exploring the Query Optimizer
                   Task 15A-1: Configuring the Query Governor
                   Task 15A-2: Using SHOWPLAN to View the Query Execution Plan
                   Task 15A-3: Analyzing a Graphical Execution Plan
          Topic 15B: Using Indexes to Optimize Queries
                   Task 15B-1: Designing Indexing

Lesson 16: Analyzing Queries
          Topic 16A: Analyzing the Performance of Queries
Appendix A: The Movies Database Structure
    Table Design

Appendix B: Course Script Files
    Using the Course SQL Script Files