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.
April 28th 1996, Hannes Reisinger