|
MCDBA Designing & Implementing Databases with SQL Server 2000 Enterpise Edition Exam Cram 2
The MCAD/MCSE/MCDBA 70-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.
CONTENTS:
Introduction.
Self-Assessment.
1. Database Development on SQL Server 2000.
Introduction
The Server and Its Components
The Exam
Knowing the New Features Found in SQL Server 2000
Other Relevant Exam Topics
SQL Server Product Implementation
Program Execution
The Query Analyzer
SQL Server Objects
The Master Database
The Msdb Database
The Tempdb Database
The Model Database
The Distribution Database
Objects Within a Database
Other Objects Found on the Server
Types of Data
Object Definitions
Metadata
User Data
Numeric Data Types
Character Data Types
Binary Data Types
Specialty Data Types
User-Defined Data Types
Exam Prep Questions
2. Creating a Logical Data Model.
Introduction
Data Modeling and Logical Data Design
Data Modeling
Modeling with the ER Data Model
Data Normalization
Data Normalization with Normal Forms
Denormalization
Relationships Between Entities
Identification of Relationships
One-To-One Relationship
One-To-Many Relationship
Many-to-Many Relationship
Exam Prep Questions
3. Implementing the Physical Database.
Introduction
A Hardware Dialogue
Defining a SQL Server Database
The File System
Creating Files and Filegroups
File Placement for Performance and Reliability
Database Creation
Using a Collation Sequence
Altering Database Properties
Inside the Database
Tables
Diagrams
Views
Stored Procedures
Users and Roles
Rules
Defaults
User-Defined Data Types and Functions
Full-Text Catalogs
What's on the Table
Data Element Definition
Keys to Success
Using Cascade Action
Maintaining Order with Indexes
Room for Additions
Data Entry Using Defaults
Checks and Balances
Other Bits and Pieces
Trigger Utilization
Troubleshooting Failed Object Creation
Exam Prep Questions
4. Advanced Physical Database Implementation.
Introduction
The Need for a More Complex Design
User Interaction Through Views
Getting a Better View
Utilizing Views
Indexed Views
Schema Binding
Partitioned Views
Operating-System Service Properties
Server Configuration Settings
Database Configuration Options
Settings for Automatic Actions
Moving the Cursor
Settings for Recovery
Set Coding Reaction with SQL Options
The State of the Server
Other Multiple Machine Database Implementations
Standby Servers and Log Shipping
Replicating Data
Replication Strategies
Types of Replication
Exam Prep Questions
5. Retrieving and Modifying Data.
Introduction
Moving a Mountain of Data
Mass Movements of Data via BCP
Using Backup/Restore for More Than Recovery
Data Movement with Manipulation
Selecting Information from the Data
Narrowing the Scope of Data Retrieval
Conditional Filtering of Data
Putting It All in ORDER
Displaying Groups in Output
More Advanced Query Groups
Joining Multiple Sets of Data
Return Output Only When It Matches-Inner
Return Output Even When It Doesn't Match-Outer
Joining Everything to Everything-Crossing
Multiple Datasets-Unions
Adding and Modifying Data
Extensible Markup Language (XML)
Simplifying XML Use
Moving Data Using XML
Exam Prep Questions
6. Programming Business Logic.
Introduction
Start with Data Analysis Prior to Coding
Making Data Presentable
Functions Make Data Work
Aggregate Collective Functions
Functions Used for Deviation and Variance
Checksum Operations
Of Calendars and Clocks
Using Mathematical Functions in T-SQL
String It Up
Working with System-Level Functions
Information Schema Views
Providing the Orders to Be Followed
Coding with Variables
Setting and Using Variables
Server Provided Global Values
Document As You Go
Statement Blocks with BEGIN...END
Conditional Statements with IF...ELSE
Multiple Conditions with CASE
Loops in T-SQL
Traditional Data Processing
Converting Constructs to Code
Using Stored Procedures
Creating Stored Procedures
Results from Procedure Execution
True Customization with User-Defined Functions
Alternative Coding Mechanisms
Code Responses to Actions
Appropriate Use of INSTEAD OF Triggers
Exam Prep Questions
7. Tuning and Optimizing Analysis.
Introduction
Information Gathering
Processes Operating in the Environment
External Tools Provided by the Operating System
Monitor Activity with the Profiler
Using Profiler Traces to Diagnose Locking
Trace Playback and Diagnosis
Query Optimization
The SQL Query Analyzer
Query Execution Plans
Reading Execution Plans
Execution Plan Caching and Sharing
Optimizer Hints
Exam Prep Questions
8. Designing for Optimized Data Access.
Introduction
Concurrency and Locking
Controlling Lock Behavior
Controlling Business Logic Using Transactions
Data Access Using Stored Procedures
Optimization Through Design
Pointing an Index in the Right Direction
Clustered Indexes
Nonclustered Indexes
Index Maintenance over Time
Using the Index Tuning Wizard
Partitioned Views to Optimize Access
Indexing Views for Optimization
Maintaining the System to Get Back Performance
Stored Procedures Used for Maintenance
Problem Analysis Through Statistical Functions
Database Console Command (DBCC)
Exam Prep Questions
9. Designing a Database Security Plan.
Introduction
The SQL Server Login/User Model
Using Security Roles
Defining Your Own Roles
Using Application Roles
Encryption Can Secure Definitions
Auditing User Activity
Security Audits
Statement Permissions
Object Permissions
Using Statement and Object Permissions
Using Views to Enhance Security
Stored Procedures Security Implementations
Controlling, Filtering, and Enforcing Data Access
Troubleshooting Security Issues
Dealing with Broken Ownership Chains
Exam Prep Questions
10. Completing a Business System Design.
Introduction
SQL Server Supporting Applications
One- and Two-Tier Systems
Three- or More Tier Systems
Internet Application Architecture
SQL Server 2000 Back Office Integration
Third-Party Database Interactions
Data Replication
Very Large Database Applications
Coding for Distributed Data
A Brief Discussion on OLE-DB
Using OPENROWSET
Creating a Linked Server
SQL Server Automation of Processes
Job, Operator, Alert Integration
Jobs Perform Recurring Tasks
Operators Can Respond to Events
Alerts Inform Based on Events
Exam Prep Questions
11. Practice Exam One.
12. Answers to Practice Exam One.
13. Practice Exam Two.
14. Answers to Practice Exam Two.
Appendix A. What's on the CD?
Multiple Test Modes
Study Mode
Certification Mode
Custom Mode
Missed Question Mode
Non-Duplicate Mode
Question Types
Random Questions and Order of Answers
Detailed Explanations of Correct and Incorrect Answers
Attention to Exam Objectives
Installing the CD
Creating a Shortcut to the MeasureUp Practice Tests
Technical Support
Appendix B. Need to Know More?
Glossary.
Index.
|
