Exam 70-027
Implementing a Database Design on Microsoft^® SQL Server™ 6.5

This certification exam measures your ability to implement, administer, and troubleshoot information systems that incorporate Microsoft SQL Server version 6.5. This exam goes beyond testing your knowledge of SQL Server 6.5. You should have a comprehensive understanding of SQL Server concepts and procedures. You should also be able to analyze what you know about SQL Server 6.5 to synthesize configuration, optimization, and troubleshooting decisions and solutions. Before taking the exam, you should be proficient in the following job skills.

Data Modeling

• Define entity, relationship, and attribute.
• Identify and apply the different types of relationships used in designing a database.
• Identify the relationship between a primary key and a foreign key.
• Identify how key data-modeling components, such as no nulls, no duplicates, and no changes are implemented.

System Databases and Tables

• Identify the role of the master database.
• Identify the role of the tempdb database.
• Identify the role of system tables.
• Estimate space requirements.

Data Definition

• Increase transaction log size.
• Create and modify tables.
• Apply an identity property to a table definition.
• Identify the appropriate uses for the different types of constraints.
• Apply REFERENCE constraints to enforce referential integrity.

Retrieving Data

• Write SELECT statements.
• Select specific rows based on specific operators.
• Manipulate character, numeric, and date data by using TSQL functions.
• Use wildcards to retrieve rows.
• Write a SELECT statement by using system functions.
• Format and sort query results.

Retrieving Data (Advanced Topics)

• Generate summary data by using aggregate functions.
• Generate summary data by using the COMPUTE clause and COMPUTE BY clause.
• Generate summary data by using the GROUP BY clause.
• Generate summary data by using the HAVING clause.
• Correlate data by using outer joins.
• Correlate data by using self-joins.
• Recognize and apply subqueries that use nested SELECT statements.

Modifying Data

• Manipulate data by using INSERT statements.
• Manipulate data by using UPDATE statements.
• Manipulate data by using DELETE statements.
• Import and export data.

Indexes

• Identify appropriate uses of indexing.
• Differentiate between clustered and nonclustered indexes.
• Create unique and composite indexes.
• Identify performance considerations when using indexes.
• Use index management options.

Using Views, Defaults, and Rules

• Create views.
• Recognize the benefits of using views.
• Create, bind, unbind, and drop defaults.
• Create, bind, unbind, and drop rules.

Programmability

• Identify control-of-flow statements.
• Implement cursors.
• Recognize the benefits of using stored procedures.
• Identify capabilities of SQL Server and MAPI.
• Implement string and variable extensions for the EXECUTE statement.
• Manage user-defined error messages.

Triggers

• Create triggers.
• Enforce referential integrity.
• Enforce data integrity.
• Apply the inserted and deleted tables.

Replication

• Apply replication appropriately.
• Apply the appropriate replication model.
• Recognize the roles of the publishing server.
• Recognize the role of the distribution server.
• Recognize the role of the subscription server.
• Trace the replication process.

Application Development and Open Data Services (ODS)

• Recognize and apply open architecture.
• Recognize and apply Open Database Connectivity (ODBC).
• Identify the benefits of integrating OLE architecture with SQL Server.
• Recognize how SQL Server takes advantages of the OLE architecture.