MCSE training kit : Microsoft SQL Server 2000 database design and implementation

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!

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

Available:*

On Order

Summary

Summary

Table of Contents