Microsoft SQL Server 2000 Design, Implement - Online Course

This series covers developing a logical data model, implementing the database and objects, retrieving and modifying data using Transact-SQL and XML, using indexes and views, programming business logic (stored procedures, functions, views, triggers), tuning and optimizing data access, and designing a database security plan. Some background knowledge and hands-on experience with enterprise systems and relational databases is assumed.

This online course contains 9 lessons and should take approximately 21 hours to complete.

Lesson 1: Data Modeling and Database Creation (4.0) hours

Data Modeling and Database Creation teaches you how to gather database requirements, build a logical data model, create and alter databases, files, and filegroups, and view and change database properties and options. This course provides an overview of data modeling with an emphasis on relational database systems, and shows users how to implement a logical design by creating a database in SQL Server 2000. Topics include Data modeling, Logical design, Components of the logical model, Entity relationships, Normalization, Data integrity and constraints, Physical design considerations, The SQL Server 2000 software, System files and tables, Database files and filegroups, Creating a database, Altering a database, Other database modifications, and Useful functions and stored procedures.

Lesson 2: Database Objects (2.0) hours

Database Objects teaches you how to create and alter database objects, create and alter tables, design and create constraints, and alter database objects to support replication. This course shows users how to create and alter tables, define data types, and ensure integrity by implementing constraints. Topics include Creating and altering database objects, Tables and data types, Creating tables, Modifying and deleting tables, System-supplied data types, User-defined data types, Designing attribute integrity, Cascading referential integrity/disabling constraints, Replication and data types, Generating column values, and Creating DEFAULT and RULE objects.

Lesson 3: Retrieving and Modifying Data (2.0) hours

Retrieving and Modifying Data teaches you how to use the Transact-SQL Data Manipulation Language statements SELECT, INSERT, UPDATE, and DELETE to retrieve and modify data, use SQL Server built-in functions, including CASE, CONVERT, and CAST, define a collation at the server, database, or column level, and use the Transact-SQL BULK INSERT statement, the BCP utility, or Data Transformation Services (DTS) to import and export data. This course shows users how to retrieve, filter, and modify data using Transact-SQL, and explains techniques for importing and exporting data in SQL Server 2000. Topics include Referring to a SQL Server object, Transact-SQL, System functions, Collation precedence, Using the SELECT statement, Clauses for the SELECT statement, Subqueries, Using the DELETE statement, Using the UPDATE statement, Using the INSERT statement, Bulk copy operations, and Data Transformation Services.

Lesson 4: Indexes and Views (3.0) hours

Indexes and Views teaches you how to create various types of indexes, create and modify table views, modify table data through a view, and partition data using views. This course explains the types of indexes and views available in SQL Server 2000, and shows how they can be used to enhance and control access to data. Topics include Types of indexes, Creating indexes, Using the Create Index Wizard, Analyzing index performance, Altering indexes, Using views, Guidelines for views, Creating views, Analyzing CREATE VIEW statements, Modifying a view, Using views to modify data, Creating partitioned views, and Using indexed and XML views.

Lesson 5: Database Programming (4.0) hours

Database Programming teaches you how to create, modify, and execute stored procedures, create and modify triggers, create and execute user-defined functions, and work with transactions, cursors, and locks. This course shows users how to create and use stored procedures, triggers, functions, user-defined functions (UDFs), transactions, cursors, and locks in database application programming. Topics include Introduction to stored procedures, Guidelines for creating stored procedures, Creating stored procedures, Modifying stored procedures, Working with stored procedures, Error handling within stored procedures, Introduction to triggers, Creating triggers, Using INSTEAD OF triggers, Modifying triggers, Introduction to functions and UDFs, Scalar-valued and table-valued UDFs, System UDFs and functions, Obtaining information about UDFs, Transactions, Transaction isolation levels, Cursors, and Locks.

Lesson 6: Remote Data Access and XML (2.0) hours

Remote Data Access and XML teaches you how to create a linked server, create ad hoc and pass-through queries, set up the SQL Server to return XML results, and request, retrieve, and parse XML data. This course explains techniques for accessing heterogeneous data using linked servers, ad hoc queries, and pass-through queries, and shows users how to retrieve and write data using XML. Topics include Linked servers, Ad hoc queries, Pass-through queries, XML and Internet Information Services (IIS), The FOR XML clause, and The OPENXML statement.

Lesson 7: Optimization and Security (2.0) hours

Optimization and Security teaches you how to configure Windows 2000 Server and SQL Server 2000, use SQL Profiler, Query Analyzer, and other tools to monitor database activity, and design a database security plan. This course shows users the tools and techniques that can be used to monitor and optimize the performance of a SQL Server 2000 database, and how to design and implement a database security plan. Topics include Monitoring performance, Configuring Windows 2000 Server, Configuring SQL Server 2000, Using SQL Profiler, Analyzing performance, Login authentication, Permissions, Database user accounts, Roles, Authentication modes, and The database security plan.

Lesson 8: Exam Preparation 1 (1.0) hour

Exam Preparation 1 teaches you how to demonstrate familiarity with the concepts of developing a logical data model, demonstrate familiarity with the concepts of implementing the physical database, demonstrate familiarity with the concepts of retrieving and modifying data, demonstrate familiarity with the concepts of programming business logic, demonstrate familiarity with the concepts of tuning and optimizing data access, and demonstrate familiarity with the concepts of designing a database security plan. This course provides practice questions with explanatory feedback for Microsoft Exam 70-229: Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition. Topics include Developing a logical data model, Implementing the physical database, Retrieving and modifying data, Programming business logic, Tuning and optimizing data access, and Designing a database security plan.

Lesson 9: Exam Preparation 2 (1.0) hour

Exam Preparation 2 teaches you how to demonstrate familiarity with the concepts of developing a logical data model, demonstrate familiarity with the concepts of implementing the physical database, demonstrate familiarity with the concepts of retrieving and modifying data, demonstrate familiarity with the concepts of programming business logic, demonstrate familiarity with the concepts of tuning and optimizing data access, and demonstrate familiarity with the concepts of designing a database security plan. This course provides practice questions with explanatory feedback for Microsoft Exam 70-229: Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition. Topics include Developing a logical data model, Implementing the physical database, Retrieving and modifying data, Programming business logic, Tuning and optimizing data access, and Designing a database security plan.