70-029 Implementing a Database Design on Microsoft SQL Server 7.0
General Info
| Exam #: |
70-029 |
| Status: |
Active |
| Time Limit: |
150 min. |
| Passing Score: |
693 |
| # of Questions: |
49 |
| Format: |
Adaptive with Simulations |
| Certifications: |
MCP, MCSE, MCSE+I, MCSD, MCDBA |
| Cost: |
$100.00 USD |
| Available Languages: |
|
Objectives
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.
|
