Implementing a Database Design on Microsoft SQL Server 7.0 Curriculum CBT Training CDs

Rating:
Summary: Implementing a Database Design on Microsoft SQL Server 7.0 C
Review: Course Overview
The Implementing a Database Design on Microsoft SQL Server 7.0 Curriculum is designed for IT professionals responsible for implementing a database solution with Microsoft SQL Server 7.0. This curriculum will help students prepare for Microsoft certification exam 70-029, Designing and Implementing Databases with Microsoft SQL Server 7.0. Skills taught in this series include construct and manage databases; implement data integrity; create and maintain indexes; query multiple tables using Transact-SQL statements; define transactions and set transaction isolation levels; access data by using the Stored Procedure model; access data using triggers; and set up a distributed environment to access, modify and manage remote data.

# This curriculum consists of five Skill Builder courses: Microsoft SQL Server 7.0: Implementing a Database - Part 1
# Microsoft SQL Server 7.0: Implementing a Database - Part 2
# Microsoft SQL Server 7.0: Implementing a Database - Part 3
# Microsoft SQL Server 7.0: Implementing a Database - Part 4
# Microsoft SQL Server 7.0: Implementing a Database - Part 5

Certification Requirements: This curriculum contains content that will help prepare students for the MCSE Elective, MCSE+I Elective, MCSD Elective, MCDBA Core certifications and Exam 70-029.

Learn To
See individual course descriptions for specific course objectives and scope.

Audience
These courses are designed for IT professionals responsible for implementing a database solution with Microsoft SQL Server 7.0.

Deployment Options
e-Learning

Accreditation
NASBA credits: 4 CPE Credits Per Course
ITCAP credits: 2.5 Credit Hours
CEU credits: 3.50 CEUs

Language Options
US English

Total Learning Time
30 to 40 hours

Microsoft SQL Server 7.0: Implementing a Database - Part 1 Training CD
Course Overview
This is the first course in a five part series that will provide students with the knowledge to implement a database solution with Microsoft SQL Server 7.0.

Learn To
# Identify the components of SQL Server 7.0.
# Identify the characteristics of Transact-SQL and its processing.
# Perform the skills to construct and manage databases.


Audience
This course is designed for IT professionals responsible for implementing a database solution with Microsoft SQL Server 7.0. Participants should have completed Microsoft SQL Server 7.0: System Administration (Course Series 72470 - 72474).

Deployment Options
e-Learning

Accreditation
NASBA credits: 4 CPE Credits
CEU credits: 0.70 CEUs

Language Options
Japanese, US English

Total Learning Time
6 to 8 hours

Objectives
Unit 1: SQL Server Fundamentals 2 - 3 hours
# Select the operating systems that are used to create and execute applications using SQL Server.
# Identify the components of Windows NT that integrate with SQL Server 7.0.
# Match the Microsoft BackOffice applications with their descriptions.
# Select the features of SQL Server services.
# Match SQL Server client software with its corresponding function.
# Identify SQL Server communication architecture and its components.
# Identify the types of database interfaces supported by SQL Server.
# Select the SQL Server management tools used to automate routine administrative tasks.
# Match roles with their security permissions.
# Sequence the steps for validating permissions.
# Match SQL Server databases with their descriptions.
# Match the database components with their descriptions.
# Select the characteristics of SQL Server objects.
# Match commonly used system tables with their functions.
# Identify methods for retrieving metadata from system tables.
# Sequence the steps for implementing a SQL Server database.
# Identify administrative duties associated with a SQL Server database.


Unit 2: Working with Transact-SQL 2 - 3 hours
# Select features of the SQL Server Query Analyzer.
# Select the SQL Server programming tool that is used to enter Transact-SQL statements, system procedures and script files using ODBC.
# Select uses of Data Control Language statements.
# Select uses of Data Definition Language statements.
# Select SQL Server naming conventions for object identifiers.
# Select uses of Data Manipulation Language statements.
# Select when to use local variables.
# Select global variable uses.
# Sequence operators from their highest to lowest precedence.
# Select language elements that control the flow of logic within a statement.
# Perform the steps to create an in-line comment for a statement.
# Perform the steps to create a block comment for a statement.
# Perform the steps to create a dynamically constructed statement.
# Select the purposes used for submitting Transact-SQL statements in batches.
# Select the purpose for using scripts to submit Transact-SQL statements.
# Select the purpose for using transactions to submit Transact-SQL statements.
# Sequence the steps for processing cached queries.
# Select the mechanisms that save query definitions automatically.
# Select the mechanisms that save specified query definitions.


Unit 3: Constructing Databases 2 hours
# Select the description of how SQL Server stores data.
# Sequence the steps of the transaction logging process.
# Perform the steps to create a database.
# Match frequently used database options with their descriptions.
# Perform the steps to expand the size of the log file.
# Select circumstances under which it is necessary to expand the transaction log.
# Perform the steps to shrink a database.
# Perform the steps to shrink a data file.
# Perform the action to drop a database.
# Perform the steps to create a filegroup.
# Match common types of data with SQL Server system-supplied data types.
# Perform the steps to create a user-defined data type.
# Perform the action to drop a user-defined data type.
# Perform the steps to create a table.
# Perform the action to drop a table.
# Perform the steps too add a column to a table.
# Perform the steps to drop a column in a table.
# Select the methods that are used to generate column values.
# Select common purposes for generating a schema as a Transact-SQL script.
# Select different types of schemas that can be made into scripts.


Microsoft SQL Server 7.0: Implementing a Database - Part 2 Training CD
Course Overview
This is the second course in a five part series that will provide students with the knowledge to implement a database solution with Microsoft SQL Server 7.0.

Learn To
# Implement data integrity.
# Create and maintain indexes.


Audience
This course is designed for IT professionals responsible for implementing a database solution with Microsoft SQL Server 7.0. Participants should have completed Microsoft SQL Server 7.0: System Administration (Course Series 72470 - 72474) and the first course in this series.

Deployment Options
e-Learning

Accreditation
NASBA credits: 4 CPE Credits
CEU credits: 0.70 CEUs

Language Options
Japanese, US English

Total Learning Time
6 to 8 hours

Objectives
Unit 1: Implementing Data Integrity 3 - 4 hours
# Select the appropriate method of denormalization.
# Match the type of data integrity with its correct definition.
# Identify the methods of enforcing data integrity.
# Match each type of data integrity with its constraint method.
# Identify the characteristics of the constraints.
# Select the considerations for using constraints.
# Perform the steps to enforce domain integrity.
# Perform the steps to enforce entity integrity.
# Select the guidelines for disabling complaints.
# Perform the steps to disable constraint checking.
# Perform the steps to create and bind a default.
# Perform the steps to enforce data integrity.
# Match data integrity enforcement methods to its impact.


Unit 2: Planning and Creating Indexes 3 - 4 hours
# Identify how data is stored by SQL Server 7.0.
# Select the methods SQL Server uses to access data.
# Identify when it is useful to create an index.
# Select the columns to index.
# Select the characteristics of a clustered index.
# Sequence the steps SQL Server goes through to access data.
# Select the architectural features of nonclustered indexes.
# Identify the key difference between accessing data with a clustered index and accessing data in a help using a nonclustered index.
# Sequence the steps SQL Server takes when accessing data using a nonclustered index on a clustered index.
# Sequence the steps SQL Server takes to retrieve a range of information contained in a heap.
# Identify how SQL Server accesses a range of data using clustering keys.
# Select the guidelines which apply when using the CREATE INDEX statement.
# Perform the steps to create a unique index.
# Perform the steps to create a composite index.
# Identify the methods used to view existing indexes.
# Perform the steps to drop indexes.
# Perform the steps to allocate a percentage of free space on leaf level index pages.
# Perform the steps to specify a percentage to fill non-leaf level pages.
# Identify the functions of the Index Tuning wizard.
# Select the methods of managing data fragmentation.
# Perform the steps to display the base table size and fragmentation.
# Select when to use the DBCC DBREINDEX statement.
# Select the methods available to manually update the index statistics.
# Identify the information the SHOWPLAN statements return when executed.
# Select the uses of the STATISTICS IO statement.
# Select the reasons to apply optimizer hints.


Microsoft SQL Server 7.0: Implementing a Database - Part 3 Training CD
Course Overview
This is the third course in a five part series that will provide students with the knowledge to implement a database solution with Microsoft SQL Server 7.0.

Learn To
# Query multiple tables using Transact-SQL statements.
# Retrieve and modify data using advanced query techniques.


Audience
This course is designed for IT professionals responsible for implementing a database solution with Microsoft SQL Server 7.0. Participants should have completed Microsoft SQL Server 7.0: System Administration (Course Series 72470 - 72474) and the first two courses in this series.

Deployment Options
e-Learning

Accreditation
NASBA credits: 4 CPE Credits
CEU credits: 0.70 CEUs

Language Options
Japanese, US English

Total Learning Time
6 to 8 hours

Objectives
Unit 1: Querying Multiple Tables 3 - 4 hours
# Select the primary properties of a SELECT statement.
# Select the uses for a join.
# Perform the steps to combine tables.
# Perform the steps to combine table data.
# Select the statement that combines table data.
# Perform the steps to combine data from more than two tables.
# Select the statement that joins a table to itself.
# Identify the characteristics of the UNION operator.
# Perform the steps to create a single result set from multiple queries.
# Identify the characteristics of the SELECT INTO statement.
# Perform the steps to create a temporary table.


Unit 2: Advanced Query Techniques 3 - 4 hours
# Select the guidelines for using subqueries.
# Perform the steps to return a single value of data.
# Perform the steps to return a list of values.
# Select the characteristics of a correlated subquery.
# Sequence the steps to process a correlated subquery.
# Sequence the steps to complete a correlated subquery.
# Sequence the steps in which the SQL Server processes EXISTS and NOT EXISTS subqueries.
# Perform the steps to add rows to a table.
# Perform the steps to delete rows from a table.
# Perform the steps to remove rows based on other tables.
# Perform the steps to update rows.
# Select the benefits of cursors.
# Select the methods for requesting a cursor.
# Select the uses of Transact-SQL cursors.
# Sequence the steps of the Transact-SQL cursor procedure.
# Match the types of cursors with their descriptions.
# Match the cursor characteristic with its description.
# Match the cursor syntax with its function.
# Identify the characteristics of a local cursor.
# Match the cursor system stored procedure with its description.
# Select the use of CURSOR_STATUS function.
# Select the most commonly used global variables.


Microsoft SQL Server 7.0: Implementing a Database - Part 4 Training CD
Course Overview
This is the fourth course in a five part series that will provide students with the knowledge to implement a database solution with Microsoft SQL Server 7.0.

Learn To
# Generate summary value and summary data as well as generate control break reports using aggregate functions.
# Define transactions and set transaction isolation levels.
# Identify the purpose and benefits of using views.


Audience
This course is designed for IT professionals responsible for implementing a database solution with Microsoft SQL Server 7.0. Participants should have completed Microsoft SQL Server 7.0: System Administration (Course Series 72470 - 72474) and the first three courses in this series.

Deployment Options
e-Learning

Accreditation
NASBA credits: 4 CPE Credits
CEU credits: 0.70 CEUs

Language Options
Japanese, US English

Total Learning Time
6 to 8 hours

Objectives
Unit 1: Summarizing Data 2 - 3 hours
# Match each aggregate function with its description.
# Select the ways aggregate functions handle null values.
# Select statements to use with the GROUP BY clause on columns.
# Select statements to use with the GROUP BY clause on expressions.
# Select when to use the HAVING clause.
# Perform the steps to summarize group values.
# Perform the steps to summarize combinations of groups.
# Select the characteristics of the GROUPING function.
# Select guidelines for using the COMPUTE clause.
# Select guidelines for using the COMPUTE BY clause.
# Select recommended practices for summarizing data.


Unit 2: Transactions and Locks Management 2 - 3 hours
# Select the characteristics of a transaction.
# Select the guidelines for setting implicit transactions.
# Select restrictions on user-defined transactions.
# Sequence the steps that SQL Server applies in its transaction processing.
# Sequence the steps that SQL Server uses to recover data with the checkpoint process.
# Select the guidelines to be followed when writing a transaction.
# Indicate the problems encountered when locks are not used.
# Match lock types with their definitions.
# Match each lockable resource with its description.
# Match locks with their compatible counterparts.
# Match session level locking options with their descriptions.
# Match table level locking options with their descriptions.
# Select the steps to reduce the risk of deadlocks.
# Select how to display locking information.


Unit 3: Implementing Views 2 hours
# Select the characteristics of a view.
# Identify the advantages of using views.
# Perform the steps to create views.
# Perform the steps to alter views.
# Perform the steps to drop views.
# Select the characteristics of a broken ownership chain.
# Match the information schema view with it display information text.
# Select the procedures for hiding view definitions.
# Identify the restrictions on data modification when using views.
# Select performance considerations for views created by joining several tables.


Microsoft SQL Server 7.0: Implementing a Database - Part 5 Training CD
Course Overview
This is the final course in a five part series that will provide students with the knowledge to implement a database solution with Microsoft SQL Server 7.0.

Learn To
# Access data by using the Stored Procedure model.
# Access data using triggers.
# Set up a distributed environment to access, modify and manage remote data.


Audience
This course is designed for IT professionals responsible for implementing a database solution with Microsoft SQL Server 7.0. Participants should have completed Microsoft SQL Server 7.0: System Administration (Course Series 72470 - 72474) and the first four courses in this series.

Deployment Options
e-Learning

Accreditation
NASBA credits: 4 CPE Credits
CEU credits: 0.70 CEUs

Language Options
Japanese, US English

Total Learning Time
6 to 8 hours

Objectives
Unit 1: Implementing Stored Procedures 2 - 3 hours
# Match the type of stored procedure with its definition.
# Sequence the steps of the initial processing of stored procedures.
# Select the characteristics of subsequent stored procedure processing.
# Select the advantages of stored procedures.
# Perform the steps for creating stored procedures.
# Select the characteristics of nested stored procedures.
# Match the system stored procedure with the information it produces.
# Select the guidelines for creating stored procedures.
# Perform the action to execute a stored procedure.
# Perform the steps to alter a stored procedure.
# Perform the action to drop a stored procedure.
# Select the guidelines for input parameters used in stored procedures.
# Perform the steps to pass parameters into a stored procedure.
# Perform the steps to return a value.
# Match the method for recompiling stored procedures with the corresponding function.
# Select the uses of extended stored procedures.
# Match each commonly used extended stored procedure name with its function.
# Match the error message types with its use.
# Select the functions of the RAISERROR statement.
# Perform the action to customize error messages.
# Select the statements used in transactional error-handling.
# Select the counters that can be monitored by SQL Server Performance Monitor.
# Perform the steps to view the event generated by the stored procedure.
# Select the recommended practices for effective stored procedures.


Unit 2: Implementing Triggers 2 - 3 hours
# Select the characteristics of triggers.
# Select the advantages of trigger usage.
# Select the guidelines for using triggers.
# Select the statements which are prohibited within a trigger creation.
# Select the characteristics of the ALTER TIRGGER statement.
# Perform the action to drop a trigger.
# Perform the steps to create and execute an INSERT trigger.
# Perform the steps to create and execute a DELETE trigger.
# Perform the steps to create and execute an UPDATE trigger.
# Select the characteristics of nested triggers.
# Select where triggers to enforce data integrity were used.
# Select where triggers to enforce business rules were used.
# Select the performance considerations for triggers.
# Select the recommended effective practices for triggers.


Unit 3: Distributed Data 2 hours
# Select the purposes for establishing linked servers.
# Perform the steps to connect to a remote SQL server.
# Match common OLE DB provider names with their data sources.
# Identify facts to consider when establishing linked server security.
# Match system stored procedures with the information they return about linked servers.
# Select the allowed Transact-SQL statements.
# Select the statement that executes a stored procedure on a linked server.
# Select the functions of the MS DTC tool.
# Identify the functions of SQL Server Replication.
# Identify the characteristics of designing and building a data warehouse.
# Select the functions of Import and Export wizards.