did-you-know? rent-now

Amazon no longer offers textbook rentals. We do!

did-you-know? rent-now

Amazon no longer offers textbook rentals. We do!

We're the #1 textbook rental company. Let us show you why.

9780735612488

MCSE Training Kit (Exam 70-229): Microsoft SQL Server 2000 Database Design and Implementat

by
  • ISBN13:

    9780735612488

  • ISBN10:

    073561248X

  • Format: Trade Paper
  • Copyright: 2001-05-25
  • Publisher: Microsoft Pr
  • Purchase Benefits
List Price: $59.99

Summary

This official "MCSE Training Kit" teaches how to design and implement database solutions using Microsoft SQL Server 2000 Enterprise Edition, as well as providing in-depth preparation for MCP Exam 70-229 -- a core exam for MCDBA certification and an elective on the Microsoft Windows "RM" 2000 MCSE track. Topics map directly to the objectives measured by the MCP exam, including developing a logical data model, implementing the physical database, retrieving and modifying data, programming business logic, tuning and optimizing data access, and designing a database security plan. Students learn through an integrated system of skill-building tutorials, case study examples, and self-assessment exercises. An economical alternative to classroom instruction, this kit enables students to set their own pace and learn by doing!

Table of Contents

About This Book xiii
Intended Audience xiii
Prerequisites xiv
Reference Materials xiv
About the CD-ROM xiv
Features of this Book xv
The Microsoft Certified Professional Program xxii
Technical Support xxvii
Introduction to Microsoft SQL Server 2000
1(44)
About This Chapter
1(1)
Before You Begin
1(1)
Overview of SQL Server 2000
2(7)
What Is SQL Server 2000?
2(2)
SQL Server 2000 Features
4(3)
Editions of SQL Server 2000
7(1)
Lesson Summary
8(1)
Components of SQL Server 2000
9(11)
Overview of the SQL Server 2000 Components
9(1)
SQL Server 2000 Relational Database Engine
9(1)
SQL Server 2000 Replication
10(1)
SQL Server 2000 DTS
11(1)
SQL Server 2000 Analysis Services
12(1)
SQL Server 2000 English Query
12(1)
SQL Server Meta Data Services
13(1)
SQL Server Books Online
14(1)
SQL Server 2000 Tools
14(5)
Lesson Summary
19(1)
Overview of SQL Server 2000 Architecture
20(25)
Database Architecture
20(8)
Relational Database Engine Architecture
28(5)
Administration Architecture
33(5)
Data Warehousing and Online Analytical Processing (OLAP)
38(1)
Application Development Architecture
39(2)
Lesson Summary
41(1)
Review
42(3)
Using Transact-SQL on a SQL Server Database
45(54)
About This Chapter
45(1)
Before You Begin
45(1)
SQL Server Programming Tools
46(20)
SQL Query Analyzer
46(13)
isqlw Command-Prompt Utility
59(1)
isql Command Prompt Utility
60(1)
osql Command-Prompt Utility
60(5)
Lesson Summary
65(1)
Introduction to Transact-SQL
66(11)
Overview of Transact-SQL
66(1)
Transact-SQL Statements
66(9)
Lesson Summary
75(2)
Transact-SQL Syntax Elements
77(12)
Identifiers
77(1)
Variables
78(1)
Functions
79(3)
Data Types
82(1)
Expressions
82(1)
Control-of-Flow Language Elements
83(1)
Comments
84(4)
Lesson Summary
88(1)
Executing Transact-SQL Statements
89(10)
Single Transact-SQL Statements
89(2)
Batches
91(2)
Stored Procedures and Triggers
93(1)
Transact-SQL Scripts
94(1)
Lesson Summary
95(1)
Review
96(3)
Designing a SQL Server Database
99(44)
About This Chapter
99(1)
Before You Begin
99(1)
Introduction to Database Design
100(12)
Components of a SQL Server Database
100(1)
Normalizing a Database Design
101(4)
Entity Relationships
105(6)
Lesson Summary
111(1)
Planning a SQL Server Database
112(7)
Files and Filegroups
112(2)
Transactions Logs
114(1)
Environment
115(1)
SQL Server Installation
116(1)
Security
117(1)
Lesson Summary
118(1)
Identifying System Requirements
119(10)
The Process of Identifying System Requirements
119(1)
Identifying System Goals
120(3)
Identifying How the Data Will Be Used
123(1)
Identifying Business Rules of the System
123(4)
Lesson Summary
127(2)
Developing a Logical Data Model
129(14)
Identifying Entities and Their Attributes
129(2)
Identifying Relationships Between Entities
131(1)
Identifying Constraints on Data
132(7)
Lesson Summary
139(2)
Review
141(2)
Implementing SQL Server Databases and Tables
143(36)
About This Chapter
143(1)
Before You Begin
143(1)
Creating and Managing a SQL Server Database
144(11)
Creating a SQL Server Database
144(2)
Managing a SQL Server Database
146(4)
Creating and Managing a Database
150(4)
Lesson Summary
154(1)
Identifying Data Types
155(8)
System-Supplied Data Types
155(5)
User-Defined Data Types
160(1)
Identifying Column Data Types
160(2)
Lesson Summary
162(1)
Creating and Managing Tables
163(16)
Creating Tables in a SQL Server Database
163(7)
Managing Tables in a SQL Server Database
170(2)
Creating and Managing Tables in a SQL Server Database
172(3)
Lesson Summary
175(1)
Review
176(3)
Implementing Data Integrity
179(26)
About This Chapter
179(1)
Before You Begin
179(1)
Introduction to Data Integrity
180(7)
Enforcing Data Integrity
180(2)
Types of Data Integrity
182(2)
Identifying the Properties Used to Ensure Data Integrity
184(1)
Lesson Summary
185(2)
Implementing Integrity Constraints
187(18)
Introduction to Integrity Constraints
187(1)
PRIMARY KEY Constraints
188(2)
UNIQUE Constraints
190(1)
FOREIGN KEY Constraints
191(3)
CHECK Constraints
194(2)
Adding Constraints to Existing Tables
196(5)
Lesson Summary
201(1)
Review
202(3)
Accessing and Modifying Data
205(38)
About This Chapter
205(1)
Before You Begin
205(1)
Accessing Data in a SQL Server Database
206(9)
The Fundamentals of a SELECT Statement
206(1)
The Select Clause
207(2)
The into Clause
209(1)
The from Clause
209(1)
The Where, Group by, and Having Clauses
209(2)
The Order by Clause
211(1)
Using Select Statements to Access Data
211(3)
Lesson Summary
214(1)
Using Advanced Query Techniques to Access Data
215(14)
Using Joins to Retrieve Data
215(3)
Defining Subqueries inside SELECT Statements
218(3)
Summarizing Data
221(2)
Using Advanced Query Techniques to Retrieve Data
223(5)
Lesson Summary
228(1)
Modifying Data in a SQL Server Database
229(14)
Inserting Data into a SQL Server Database
229(3)
Modifying Data in a SQL Server Database
232(3)
Deleting Data from a SQL Server Database
235(1)
Modifying Data in a SQL Server Database
236(3)
Lesson Summary
239(1)
Review
240(3)
Managing and Manipulating Data
243(40)
About This Chapter
243(1)
Before You Begin
243(1)
Importing and Exporting Data
244(12)
Using the bcp Utility and the BULK INSERT Statement
244(4)
Using DTS
248(3)
Importing and Exporting Data
251(3)
Lesson Summary
254(2)
Using Distributed Queries to Access External Data
256(7)
Introduction to Distributed Queries
256(1)
Using Linked Server Names in Distributed Queries
257(2)
Using Ad Hoc Computer Names in Distributed Queries
259(1)
Using Distributed Queries to Access External Data
260(2)
Lesson Summary
262(1)
Using Cursors to Retrieve Data
263(9)
Introduction to Cursors
263(5)
Fetching and Scrolling
268(1)
Controlling Cursor Behavior
268(1)
Cursor Locking
269(1)
Creating a Cursor to Retrieve Data
269(2)
Lesson Summary
271(1)
Retrieving XML Data
272(11)
Introduction to XML
272(1)
Using the FOR XML Clause to Retrieve Data
273(2)
Using the OPENXML Function to Access XML Data
275(4)
Retrieving XML Data
279(1)
Lesson Summary
280(1)
Review
281(2)
Implementing Stored Procedures
283(46)
About This Chapter
283(1)
Before You Begin
283(1)
Introduction to Stored Procedures
284(8)
Purpose and Advantages of Stored Procedures
284(2)
Categories of Stored Procedures
286(5)
Lesson Summary
291(1)
Creating, Executing, Modifying, and Deleting Stored Procedures
292(18)
How a Procedure Is Stored
292(1)
Methods for Creating Stored Procedures
293(6)
Executing a Stored Procedures
299(3)
Modifying Stored Procedures
302(1)
Deleting Stored Procedures
303(6)
Lesson Summary
309(1)
Programming Stored Procedures
310(19)
Parameters and Variables
310(2)
The RETURN Statement and Error Handling
312(6)
Nesting Procedures
318(1)
Cursors
318(8)
Lesson Summary
326(1)
Review
327(2)
Implementing Triggers
329(34)
About This Chapter
329(1)
Before You Begin
330(1)
Introduction to Triggers
331(8)
Extending Data Integrity with Triggers
331(2)
Trigger Events
333(4)
Lesson Summary
337(2)
Creating and Managing Triggers
339(11)
Creating Triggers Using Transact-SQL
339(3)
Creating a Triggers Using Enterprise Manager
342(1)
Trigger Management
342(2)
Viewing, Dropping, and Disabling Triggers
344(5)
Lesson Summary
349(1)
Programming Triggers
350(13)
The Inserted and Deleted Pseudo Tables
350(1)
Trigger Syntax, System Commands, and Functions
351(2)
Common Trigger Programming Tasks
353(6)
Lesson Summary
359(2)
Review
361(2)
Implementing Views
363(28)
About This Chapter
363(1)
Before You Begin
363(1)
Introduction to Views
364(4)
Overview of Views
364(1)
Scenarios for Using Views
365(2)
Lesson Summary
367(1)
Creating, Modifying, and Deleting Views
368(11)
Creating Views
368(7)
Modifying Views
375(1)
Deleting Views
376(1)
Creating and Modifying a View
376(2)
Lesson Summary
378(1)
Accessing Data through Views
379(12)
Viewing Data through Views
379(1)
Modifying Data through Views
379(5)
Using the AuthorsBooks View to Access Data
384(3)
Lesson Summary
387(1)
Review
388(3)
Implementing Indexes
391(30)
About This Chapter
391(1)
Before You Begin
391(1)
Index Architecture
392(11)
Purpose and Structure
392(1)
Index Types
393(2)
Index Characteristics
395(2)
Index Information
397(2)
Full-Text Indexing
399(3)
Lesson Summary
402(1)
Index Creation and Administration
403(18)
Index Creation
403(5)
Index Administration
408(2)
Choosing to Index
410(2)
Index Performance
412(5)
Lesson Summary
417(1)
Review
418(3)
Managing SQL Server Transactions and Locks
421(40)
About This Chapter
421(1)
Before You Begin
421(1)
Transaction and Locking Architecture
422(10)
Transaction Log Architecture
422(5)
Concurrency Architecture
427(1)
Locking Architecture
427(2)
Distributed Transaction Architecture
429(1)
Accessing and Modifying the Transaction Log
429(2)
Lesson Summary
431(1)
Managing SQL Server Transactions
432(14)
Overview of SQL Server Transactions
432(3)
Types of Transactions
435(5)
Distributed Transactions
440(2)
Implementing Explicit Transactions
442(3)
Lesson Summary
445(1)
Managing SQL Server Locking
446(15)
Types of Concurrency Problems
446(2)
Optimistic and Pessimistic Concurrency
448(1)
Isolation Levels
448(1)
Customizing Locking
449(7)
Configuring Transaction Properties
456(1)
Lesson Summary
457(2)
Review
459(2)
Designing and Administering SQL Server 2000 Security
461(38)
About This Chapter
461(1)
Before You Begin
461(1)
Overview of SQL Server 2000 Security
462(11)
Physical Security
462(1)
Network Protocol Security
462(2)
Domain Security
464(1)
Local Computer Security
465(1)
SQL Server Security
465(6)
Application
471(1)
Lesson Summary
472(1)
Designing a Database Security Plan
473(6)
Requirements
473(2)
Nesting and Ownership Chains
475(1)
Security Design Recommendations
476(1)
Lesson Summary
477(2)
Database Security Implementation and Administration
479(20)
Administering Authentication
479(2)
Administering Authorization
481(2)
Administering Permissions
483(4)
Administering Roles
487(7)
Lesson Summary
494(2)
Review
496(3)
SQL Server Monitoring and Tuning
499(22)
About This Chapter
499(1)
Before You Begin
499(1)
Monitoring Databases with SQL Profiler
500(11)
SQL Server Monitoring
500(1)
SQL Profiler
501(9)
Lesson Summary
510(1)
Index Tuning and Database Partitioning
511(10)
SQL Server Tuning
511(1)
Index Tuning Wizard Overview
511(3)
Partitioning Overview
514(4)
Lesson Summary
518(1)
Review
519(2)
Appendix A Questions and Answers 521(54)
Glossary 575(48)
Index 623

Supplemental Materials

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.

Rewards Program