Boot Camp Pricing
Windows 2000 MCSE | NT 4.0 MCSE | MCSE Classes | MCSE FAQs | MCSE Exam Info

Exam 70-029

Designing and Implementing Databases with Microsoft SQL Server 7.0

Exam 70-029 has been available since June 8, 1999.

This document includes information on:

• Microsoft Certification Path
• Skills Being Measured

Microsoft Certification Path

When you pass this exam, you achieve Microsoft Certified Professional status. You also earn core credit toward Microsoft Certified Database Administrator certification, elective credit toward Microsoft Certified Systems Engineer certification, elective credit toward Microsoft Certified Systems Engineer + Internet certification, and elective credit toward Microsoft Certified Solution Developer certification.

Skills Being Measured

This certification exam measures your ability to design and implement a database solution by using Microsoft SQL Server version 7.0. Before taking the exam, you should be proficient in the job skills listed below.

Developing a Logical Data Model

Group data into entities by applying normalization rules.

Identify primary keys.

Choose the foreign key that will enforce a relationship between entities and that will ensure referential integrity.

Identify the business rules that relate to data integrity.

Incorporate business rules and constraints into the data model.

In a given situation, decide whether denormalization is appropriate.

Deriving the Physical Design

Assess the potential impact of the logical design on performance, maintainability, extensibility, scalability, availability, and security.

Creating Data Services

Access data by using the dynamic SQL model.

Access data by using the stored procedure model.

Manipulate data by using Transact-SQL cursors.

• Choose the appropriate type of cursor.
• Define the appropriate level of sensitivity to change.
• Choose the appropriate navigation.
• Choose the scope of the cursor, specifically global or local.

Create and manage explicit, implicit, and distributed transactions to ensure data consistency and recoverability.

• Define the transaction isolation level.
• Design transactions of appropriate length.
• Avoid or handle deadlocks.
• Use optimistic locking appropriately.
• Implement error handling by using @@trancount.

Write INSERT, DELETE, UPDATE, and SELECT statements that retrieve and modify data.

Write Transact-SQL statements that use joins or subqueries to combine data from multiple tables.

Create scripts by using Transact-SQL. Programming elements include control-of-flow techniques, local and global variables, functions, and error handling techniques.

Design, create, use, and alter views.

• Modify data through a view.
• Query data through a view.

Create and execute stored procedures to enforce business rules, to modify data in multiple tables, to perform calculations, and to use input and output parameters.

• Implement error handling by using return codes and the RAISERROR statement.
• Choose appropriate recompile options.

Create triggers that implement rules, that enforce data integrity, and that perform cascading updates and cascading deletes.

• Implement transactional error handling.

Create result sets that provide summary data. Query types include TOP n PERCENT and GROUP BY, specifically HAVING, CUBE, and ROLLUP.

Configure session-level settings.

Access data from static or dynamic sources by using remote stored procedures, linked servers, and OPENROWSET.

• Evaluate where processing occurs when using OPENQUERY.

Creating a Physical Database

Create and manage files, filegroups, and transaction logs that define a database.

Create tables that enforce data integrity and referential integrity. Choose the appropriate data types.

• Create user-defined data types.
• Define columns as NULL or NOT NULL.
• Define columns to generate values by using the IDENTITY property, the uniqueidentifier data type, and the NEWID function.
• Implement constraints.

Create and maintain indexes.

• Choose an indexing strategy that will optimize performance.
• Given a situation, choose the appropriate type of index to create.
• Choose the column or columns to index.
• Choose the appropriate index characteristics, specifically FILLFACTOR, DROP_EXISTING, and PAD_INDEX.

Populate the database with data from an external data source. Methods include the bulk copy program and Data Transformation Services (DTS).

Implement full-text search.

Maintaining a Database

Evaluate and optimize the performance of an execution plan by using DBCC SHOWCONTIG, SHOWPLAN_text, SHOWPLAN_ALL, and UPDATE STATISTICS.

Evaluate and optimize the performance of query execution plans.

Diagnose and resolve locking problems.

Identify SQL Server events and performance problems by using SQL Server Profiler.

[ Back to top ]

Home | Technical Schedule | Application Classes | Class Outlines | MCSE, MCDBA, MCSD Training | Microsoft .NET Programming | Cisco Classes | Linux, Unix, AIX | CompTIA Certification | Webmaster Training | Pricing | Locations | Financing | E-mail Us