The Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition exam (70-229) covers job skills in the following areas:
Before taking the exam, you should be proficient in the job skills represented by the following units, objectives, and subobjectives. Developing a Logical Data ModelDefine entities. Considerations include entity composition and normalization.
Design entity keys. Considerations include Foreign Key constraints, Primary Key constraints, and UNIQUE constraints.
Design attribute domain integrity. Considerations include CHECK constraints, data types, and nullability.
Implementing the Physical DatabaseCreate and alter databases. Considerations include file groups, file placement, growth strategy, and space requirements.
Create and alter database objects. Objects include constraints, indexes, stored procedures, tables, triggers, user -defined functions, and views.
Alter database objects to support replication and partitioned views.
Troubleshoot failed object creation. Retrieving and Modifying DataImport and export data. Methods include the bulk copy program, the Bulk Insert task, and Data Transformation Services (DTS). Manipulate heterogeneous data. Methods include linked servers, OPENQUERY , OPENROWSET , and OPENXML . Retrieve, filter, group, summarize, and modify data by using Transact -SQL. Manage resultsets by using cursors and Transact-SQL. Considerations include locking models and appropriate usage. Extract data in XML format. Considerations include output format and XML schema structure. Programming Business LogicManage data manipulation by using stored procedures, transactions, triggers, user-defined functions, and views.
Enforce procedural business logic by using stored procedures, transactions, triggers, user-defined functions, and views.
Troubleshoot and optimize programming objects. Objects include stored procedures, transactions, triggers, user-defined functions, and views. Tuning and Optimizing Data AccessAnalyze the query execution plan. Considerations include query processor operations and steps. Capture, analyze, and replay SQL Profiler traces. Considerations include lock detection, performance tuning, and trace flags. Create and implement indexing strategies. Considerations include clustered index, covering index, indexed views, nonclustered index, placement, and statistics. Improve index use by using the Index Tuning wizard. Monitor and troubleshoot database activity by using SQL Profiler. Designing a Database Security PlanControl data access by using stored procedures, triggers, user-defined functions, and views.
Define object-level security including column-level permissions by using GRANT , REVOKE , and DENY . Create and manage application roles. |