SQL Server 2000 Implementing and Designing Databases
SQL Server 2000 Implementing and Designing Databases
| This course prepares you to design and implement database solutions by using Microsoft SQL Server 2000. This course covers subjects such as: Developing a Logical Data Model, Implementing the Physical Database, Retrieving and Modifying Data, Programming Business Logic, Designing a Database Security Plan.
|
|
Upon Successful completion of this course student should be able to take and pass the Microsoft MCDBA exam 70-229 Designing and Implementing Databases with Microsoft SQL Server 2000. |
|
This course provides assesments in the form of quizzes and pre and post course tests. This course also comes with a practice exam CD designed from the general question pool used to certify under exam 70-229.
|
Course Information (see above or below lessons, outlines, activities, etc.)
Instructor: Paul D. Sheriff has over nine years of experience programming for various businesses in the aerospace, real estate, medical & hotel areas. Paul is a designer of courseware for Application Developers Training Company & tours with the Visual Basic Master Seminar Series in both the USA & Canada. He has published over 25 articles in major publications.
Course Outline:
Lesson 1
Relational Databases
- What is a Database?
- Relational Algebra
- Relational Database Terms
Database Normalization
- How to Normalize a Database
- Case Study: Employee Time Tracking System
An Overview of SQL Server
Creating Databases
- SQL Server Databases & Logs
- Files & File Groups
- Creating a Database
- Database Size Planning
- Using T-SQL to create a database
Lesson 2
Creating Tables
- SQL Server Data Types
- Creating Tables
- Adding Data to a Table
- Creating Tables Using T-SQL
Constraints & Indexes
- Data Integrity
- Indexes
- Creating CHECKS & Rules
- Using T-SQL to Create Constraints
Retrieving Data
- Select Statement
- WHERE Clause
- DISTINCT & Calculated Columns
- ORDER BY Clause
- Using SQL Query Analyzer
Lesson 3
Advanced Queries
- Aggregated Functions
- Grouping Data
- Advanced Queries
- Distributed Queries
Joining Tables
- Inner Joins
- Outer Joins
- Cross Join & Self Join
- Sub-Queries
- Unions
Data Modification
- INSERT Statements
- Creating Temporary Tables
- UPDATE Statements
- DELETE Statement
System Functions
- Date Function
- Mathematical Functions
- String Functions
- System Functions
- Security Functions
- Conversion Functions
- User-Defined Functions
- System Variables
Lesson 4
Stored Procedures
- Intro to Stored Procedures
- Passing Parameters
- Returning Information from Stored Procedures
- OUTPUT Parameters
- Dynamic SQL
- Cursors
Triggers & Views
- Update Triggers
- Insert Triggers
- Delete Triggers
- INSTEAD OF Triggers
- Views
Locking Issues
- Locking Scenarios
- Isolation Levels
- Resource Lock Types
- Lock Modes
- Lock Escalation
- Viewing Locks
- Setting the Isolation Level
- Locking Hints
Lesson 5
Transaction Processing
- What is a Transaction?
- Nesting Transactions
- Trying Out Transactions
- Deadlocks
Data Transfer
- Services Available
- BCP
- BULK INSERT
- DTS
Full Text Search
- What is Full Text Search?
- Settting Up Full Text Search
- Performing a Full Text Search
- Full Text System Procedures
- FTS Enhancements in SQL 2000
Optimization
- Optimizing Your Server
- Database Design
- Query Optimization
- Index Usage
- Statistics & Index Analyzer
- SQL Server Profiler
- Performance Monitor
System Databases & Tables
- System Databases
- System Tables
Lesson 6
Using XML Extensions
- Creating a Virtual Root
- Using a Virtual Root
- Returning Elements Based XML
- Passing Parameters
- Multiple Result Sets
- Using Stored Procedures
- Inserting Data Using XML
- Joining Tables
- Retrieving Data from VB
Application Development
- OLE DB, ODBC & ADO
- USING ADO
|
Contact Hours: 24
