MCAD/MCSE/MCDBA 70-229 Exam Cram 2: Designing & Implementing Databases w/SQL Server 2000 Enterprise Edition

by Moore, Thomas; Tittel, Ed

• ISBN13: 9780789731067
• ISBN10: 0789731061
• Format: Paperback w/CD
• Copyright: 2005-01-01
• Publisher: Que

Summary

TheMCAD/MCSE/MCDBA70-229 Exam Cram 2: Designing & Implementing Databases with SQL Server 2000 Enterprise Edition covers exactly what you need to know to pass the 70-229 exam. It is the ideal refresher that ensures readers cover the essentials and the ultimate complement guide for larger training guides, instructor-led classes or CBT training. This comprehensive reference guide also comes with the popular "Cram Sheet" tear card for your last minute cram sessions, and the accompanying CD includes a practice test engine powered by MeasureUp. You will also benefit from the community support at www.examcram2.com, which features industry news, study tips, practice exams, and discussion boards.

Table of Contents

Introduction	p. xx
Self-Assessment	p. xxvi
Database Development on SQL Server 2000	p. 1
Introduction	p. 2
The Server and Its Components	p. 2
The Exam	p. 3
Knowing the New Features Found in SQL Server 2000	p. 4
Other Relevant Exam Topics	p. 6
SQL Server Product Implementation	p. 6
Program Execution	p. 12
The Query Analyzer	p. 12
SQL Server Objects	p. 14
The Master Database	p. 15
The Msdb Database	p. 15
The Tempdb Database	p. 15
The Model Database	p. 15
The Distribution Database	p. 16
Objects Within a Database	p. 16
Other Objects Found on the Server	p. 17
Types of Data	p. 19
Object Definitions	p. 20
Metadata	p. 21
User Data	p. 21
Numeric Data Types	p. 22
Character Data Types	p. 22
Binary Data Types	p. 23
Specialty Data Types	p. 23
User-Defined Data Types	p. 24
Exam Prep Questions	p. 26
Creating a Logical Data Model	p. 29
Introduction	p. 30
Data Modeling and Logical Data Design	p. 30
Data Modeling	p. 32
Modeling with the ER Data Model	p. 32
Data Normalization	p. 41
Data Normalization with Normal Forms	p. 42
Denormalization	p. 44
Relationships Between Entities	p. 45
Identification of Relationships	p. 47
One-To-One Relationship	p. 47
One-To-Many Relationship	p. 48
Many-to-Many Relationship	p. 49
Exam Prep Questions	p. 52
Implementing the Physical Database	p. 61
Introduction	p. 62
A Hardware Dialogue	p. 62
Defining a SQL Server Database	p. 64
The File System	p. 66
Creating Files and Filegroups	p. 66
File Placement for Performance and Reliability	p. 68
Database Creation	p. 69
Using a Collation Sequence	p. 73
Altering Database Properties	p. 73
Inside the Database	p. 74
Tables	p. 75
Diagrams	p. 75
Views	p. 76
Stored Procedures	p. 76
Users and Roles	p. 77
Rules	p. 77
Defaults	p. 77
User-Defined Data Types and Functions	p. 78
Full-Text Catalogs	p. 78
What's on the Table	p. 78
Data Element Definition	p. 79
Keys to Success	p. 80
Using Cascade Action	p. 82
Maintaining Order with Indexes	p. 83
Room for Additions	p. 86
Data Entry Using Defaults	p. 87
Checks and Balances	p. 87
Other Bits and Pieces	p. 89
Trigger Utilization	p. 89
Troubleshooting Failed Object Creation	p. 90
Exam Prep Questions	p. 92
Advanced Physical Database Implementation	p. 101
Introduction	p. 102
The Need for a More Complex Design	p. 102
User Interaction Through Views	p. 103
Getting a Better View	p. 106
Utilizing Views	p. 108
Indexed Views	p. 109
Schema Binding	p. 110
Partitioned Views	p. 112
Operating-System Service Properties	p. 114
Server Configuration Settings	p. 115
Database Configuration Options	p. 121
Settings for Automatic Actions	p. 122
Moving the Cursor	p. 123
Settings for Recovery	p. 124
Set Coding Reaction with SQL Options	p. 125
The State of the Server	p. 126
Other Multiple Machine Database Implementations	p. 128
Standby Servers and Log Shipping	p. 128
Replicating Data	p. 129
Replication Strategies	p. 131
Types of Replication	p. 134
Exam Prep Questions	p. 139
Retrieving and Modifying Data	p. 145
Introduction	p. 146
Moving a Mountain of Data	p. 146
Mass Movements of Data via BCP	p. 147
Using Backup/Restore for More Than Recovery	p. 149
Data Movement with Manipulation	p. 150
Selecting Information from the Data	p. 150
Narrowing the Scope of Data Retrieval	p. 152
Conditional Filtering of Data	p. 155
Putting It All in ORDER	p. 160
Displaying Groups in Output	p. 161
More Advanced Query Groups	p. 163
Joining Multiple Sets of Data	p. 165
Return Output Only When It Matches-Inner	p. 166
Return Output Even When It Doesn't Match-Outer	p. 167
Joining Everything to Everything-Crossing	p. 169
Multiple Datasets-Unions	p. 169
Adding and Modifying Data	p. 171
Extensible Markup Language (XML)	p. 174
Simplifying XML Use	p. 175
Moving Data Using XML	p. 178
Exam Prep Questions	p. 181
Programming Business Logic	p. 187
Introduction	p. 188
Start with Data Analysis Prior to Coding	p. 188
Making Data Presentable	p. 189
Functions Make Data Work	p. 191
Aggregate Collective Functions	p. 191
Functions Used for Deviation and Variance	p. 192
Checksum Operations	p. 193
Of Calendars and Clocks	p. 194
Using Mathematical Functions in T-SQL	p. 196
String It Up	p. 197
Working with System-Level Functions	p. 198
Information Schema Views	p. 200
Providing the Orders to Be Followed	p. 201
Coding with Variables	p. 203
Setting and Using Variables	p. 203
Server Provided Global Values	p. 204
Document As You Go	p. 205
Statement Blocks with BEGIN...END	p. 206
Conditional Statements with IF...ELSE	p. 207
Multiple Conditions with CASE	p. 207
Loops in T-SQL	p. 209
Traditional Data Processing	p. 210
Converting Constructs to Code	p. 214
Using Stored Procedures	p. 215
Creating Stored Procedures	p. 216
Results from Procedure Execution	p. 217
True Customization with User-Defined Functions	p. 218
Alternative Coding Mechanisms	p. 219
Code Responses to Actions	p. 219
Appropriate Use of INSTEAD OF Triggers	p. 223
Exam Prep Questions	p. 226
Tuning and Optimizing Analysis	p. 231
Introduction	p. 232
Information Gathering	p. 233
Processes Operating in the Environment	p. 233
External Tools Provided by the Operating System	p. 235
Monitor Activity with the Profiler	p. 237
Using Profiler Traces to Diagnose Locking	p. 239
Trace Playback and Diagnosis	p. 241
Query Optimization	p. 241
The SQL Query Analyzer	p. 242
Query Execution Plans	p. 243
Reading Execution Plans	p. 244
Execution Plan Caching and Sharing	p. 246
Optimizer Hints	p. 247
Exam Prep Questions	p. 250
Designing for Optimized Data Access	p. 255
Introduction	p. 256
Concurrency and Locking	p. 257
Controlling Lock Behavior	p. 258
Controlling Business Logic Using Transactions	p. 260
Data Access Using Stored Procedures	p. 262
Optimization Through Design	p. 263
Pointing an Index in the Right Direction	p. 264
Clustered Indexes	p. 266
Nonclustered Indexes	p. 267
Index Maintenance over Time	p. 270
Using the Index Tuning Wizard	p. 272
Partitioned Views to Optimize Access	p. 273
Indexing Views for Optimization	p. 274
Maintaining the System to Get Back Performance	p. 275
Stored Procedures Used for Maintenance	p. 275
Problem Analysis Through Statistical Functions	p. 276
Database Console Command (DBCC)	p. 278
Exam Prep Questions	p. 283
Designing a Database Security Plan	p. 287
Introduction	p. 288
The SQL Server Login/User Model	p. 288
Using Security Roles	p. 289
Defining Your Own Roles	p. 290
Using Application Roles	p. 291
Encryption Can Secure Definitions	p. 291
Auditing User Activity	p. 293
Security Audits	p. 294
Statement Permissions	p. 294
Object Permissions	p. 294
Using Statement and Object Permissions	p. 296
Using Views to Enhance Security	p. 297
Stored Procedures Security Implementations	p. 298
Controlling, Filtering, and Enforcing Data Access	p. 299
Troubleshooting Security Issues	p. 300
Dealing with Broken Ownership Chains	p. 300
Exam Prep Questions	p. 302
Completing a Business System Design	p. 305
Introduction	p. 306
SQL Server Supporting Applications	p. 306
One- and Two-Tier Systems	p. 307
Three- or More Tier Systems	p. 308
Internet Application Architecture	p. 308
SQL Server 2000 Back Office Integration	p. 309
Third-Party Database Interactions	p. 310
Data Replication	p. 310
Very Large Database Applications	p. 312
Coding for Distributed Data	p. 313
A Brief Discussion on OLE-DB	p. 313
Using OPENROWSET	p. 314
Creating a Linked Server	p. 315
SQL Server Automation of Processes	p. 318
Job, Operator, Alert Integration	p. 319
Jobs Perform Recurring Tasks	p. 319
Operators Can Respond to Events	p. 320
Alerts Inform Based on Events	p. 320
Exam Prep Questions	p. 322
Practice Exam One	p. 325
Answers to Practice Exam One	p. 347
Practice Exam Two	p. 371
Answers to Practice Exam Two	p. 393
What's on the CD?	p. 415
Multiple Test Modes	p. 415
Study Mode	p. 415
Certification Mode	p. 415
Custom Mode	p. 415
Missed Question Mode	p. 416
Non-Duplicate Mode	p. 416
Question Types	p. 416
Random Questions and Order of Answers	p. 416
Detailed Explanations of Correct and Incorrect Answers	p. 417
Attention to Exam Objectives	p. 417
Installing the CD	p. 417
Creating a Shortcut to the MeasureUp Practice Tests	p. 418
Technical Support	p. 419
Need to Know More?	p. 421
Glossary	p. 427
Index	p. 445
Table of Contents provided by Ingram. All Rights Reserved.

Excerpts

= 0) {slash = '\\';} else {slash = '/';}openLoc = figLoc.substring(0, figLoc.lastIndexOf(slash) + 1);while (pPage.substring(0,3) == '../') {openLoc = openLoc.substring(0, openLoc.lastIndexOf(slash, openLoc.length - 2)+ 1);pPage = pPage.substring(3, pPage.length + 1);}popUpWin =window.open('','popWin','resizable=1,scrollbars=1,location=0,toolbar=0,width=525,height=394');figDoc = popUpWin.document;zhtm= ' ' + pPage + ' ';zhtm += ' ';zhtm += ' ';zhtm += ' ';zhtm += '' + pPage.substring(pPage.lastIndexOf('/') + 1, pPage.length) + '';zhtm += ' ';figDoc.write(zhtm);figDoc.close();}// modified 3.1.99 RWE v4.1 --> Designing and Implementing Databases with SQL Server 2000 Enterprise Edition Designing and Implementing Databases with SQL Server 2000 Enterprise Edition IntroductionSo into the world of database development we plunge. The selection you have made of thisExam Cram 2series book will help you along your way to passing the 70-229 exam. You will find information within this book that will help ensure your success as you pursue this Microsoft exam and MCDBA certification.This Introduction explains MCDBA certification in general and talks about how theExam Cram 2series can help you prepare for the 70-229 exam. This Introduction discusses the basics of the MCDBA certification, including test-taking strategies. Chapters 1 through 10 are designed to remind you of everything you need to know to take and pass the exam. The two sample tests at the end of the book should give you a reasonably accurate assessment of your knowledge and, yes, we've provided the answers and their explanations. Along with the explanations, you will find some particularly useful links to more information on the topic. For each answer there is a reference to the chapter of the book that covers the topic, as well as a link to more information on the Microsoft MSDN site and another to SQL Server books online.Read the book and understand the material, and you'll stand a very good chance of passing the test. Make use of the additional links to the other materials and points of reference, and that, along with actual product use, will put you in excellent shape to do well on the exam.Exam Cram 2books help you understand and appreciate the subjects and materials necessary to pass Microsoft certification exams.Exam Cram 2books are aimed strictly at test preparation and review. They do not teach you everything you need to know about a topic. Instead, we present and dissect the questions and problems we've found that you're likely to encounter on a test. We've worked to bring together as much information as possible about Microsoft certification exams.The MC