Official Course Description: MCCCD Approval: 05/28/02
CIS275CP 20024-20035	L+L	3 Credit(s)	5 Period(s)
Programming a Microsoft Structured Query Language Server 2000 Database
Programming a Microsoft Structured Query Language (SQL) Server 2000 database. Includes server integration, database security, programming tools, data types and tables, data integrity, stored procedures, triggers and user-defined functions. Also covers Transact-SQL programming language and elements as well as index planning, creation and maintenance. Prerequisites: Experience using the Microsoft Windows 2000 operating system.

Go to Competencies    Go to Outline
 

MCCCD Official Course Competencies:
CIS275CP   20024-20035	Programming a Microsoft Structured Query Language Server 2000 Database

1.	Explain Structured Query Language (SQL) Server integration. (I)
2.	Explain SQL Server databases and security. (I)
3.	Examine enterprise application architecture. (II)
4.	Identify and describe the Transact-SQL elements. (II)
5.	Create and manage a database. (III)
6.	Create user-defined data types and user tables. (IV)
7.	Differentiate between the various types of data integrity. (V)
8.	Summarize and determine methods to enforce data integrity. (V)
9.	Describe how indexes improve database performance. (VI)
10.	Differentiate how SQL Server uses clustered and nonclustered indexes. (VI)
11.	Create and maintain indexes. (VII)
12.	Use the Index Tuning Wizard. (VII)
13.	Name advantages of views. (VIII)
14.	Create, execute, modify and drop a stored procedure. (IX)
15.	Differentiate between the types of user-defined functions. (X)
16.	Create, drop and alter a trigger. (XI)
17.	Explain distributed queries and distributed transactions. (XII)
18.	Write queries that access data from a remote data source and from linked server. (XII)
19.	Describe the function of the query optimizer. (XIII)
20.	Identify various index strategies. (XIII)
21.	Analyze various queries. (XIV)
22.	Demonstrate ability to execute a transaction. (XV)
23.	Describe the types of locks. (XV)
24.	Practice locking options and displaying locking information. (XV)

Go to Description    Go to top of Competencies
 

MCCCD Official Course Outline:
CIS275CP   20024-20035	Programming a Microsoft Structured Query Language Server 2000 Database

I. Structured Query Language (SQL) Server A. Overview B. Integration C. Databases D. Security E. Working With SQL Server 1. Administration of Databases 2. Implementation of Databases 3. Application Architecture Selection 4. Application Design II. Programming SQL Server A. Overview B. Enterprise Application Architecture Design 1. Logical Layers 2. Physical Layers 3. Data Access C. Programming Tools 1. SQL Query Analyzer 2. OSQL Utility D. Transact-SQL Programming Language E. Transact-SQL Elements 1. Data Control Language Statements 2. Data Definition Language Statements 3. Data Manipulation Language Statements 4. Server Object Names 5. Naming Guidelines F. Language Elements 1. Local Variables 2. Operators 3. Functions 4. Function Examples 5. Control of Flow Language Elements G. Execute Transact-SQL Statements 1. Constructing Statements 2. Batches 3. Scripts 4. Transactions 5. Extensible Markup Language (XML) III. Database Creation and Management A. Creation of Databases B. Creation of Filegroups C. Database Management D. Data Structures IV. Creation of Data Types and Tables A. Data Types B. Tables C. Column Values D. Scripts V. Data Integrity Implementation A. Types of Data Integrity 1. Domain 2. Entity 3. Referential B. Enforcing Data Integrity 1. Declarative 2. Procedural C. Constraints 1. Defining 2. Types 3. Disabling D. Using Defaults and Rules E. Enforcement Method VI. Index Planning A. Overview B. Architecture 1. Heaps 2. Clustered Indexes 3. Nonclustered Indexes C. Retrieve Stored Data D. Maintains Index and Heap Structures E. Columns to Index Process 1. Understanding Data 2. Guidelines 3. Choosing Clustered Index 4. Determining Selectivity 5. Determining Density 6. Determining Distribution of Data VII. Index Creation and Maintenance A. Overview B. Creation 1. Creating and Dropping 2. Unique Indexes 3. Composite Indexes 4. Indexes on Computed Columns 5. Existing Index Information C. Options D. Maintenance E. Statistics Overview 1. Gathering 2. Storing 3. Creating 4. Updating 5. Viewing F. Querying the Sysindexes Table G. Index Tuning Wizard H. Performance Considerations VIII. View Implementation A. Overview B. Advantages C. Defining D. Modifying Data E. Optimizing Performance IX. Stored Procedures A. Overview 1. Defining Stored Procedures 2. Initial Processing 3. Subsequent Processing 4. Advantages B. Creating C. Executing D. Modifying E. Dropping F. Using Parameters G. Extended H. Error Messages X. User-Defined Functions A. Overview 1. Scalar Functions 2. Multi-Statement Table-Valued Functions 3. In-Line Table-Valued Functions B. Defining 1. Creating 2. Creating Function with Schema Binding 3. Permission Setting 4. Altering and Dropping C. Examples XI. Triggers A. Overview 1. Definition of Trigger 2. Uses 3. Usage Considerations B. Defining 1. Creating 2. Altering and Dropping C. How Triggers Work D. Examples E. Performance Considerations XII. Programming Across Multiple Servers A. Distributed Queries B. Ad Hoc Query on Remote Data Source C. Linked Server Environment D. Query on Linked Servers E. Stored Procedure on Linked Server F. Distributed Transactions G. Data on Linked Server H. Partitioned Views XIII. Optimizing Query Performance A. Query Optimizer 1. Function 2. Usage of Cost-Based Optimization 3. How Optimizer Works 4. Phases 5. Caching Execution Plan 6. Setting Cost Limit B. Execution Plan C. Using an Index to Cover a Query D. Index Strategies E. Overriding Query Optimizer XIV. Analyzing Queries A. AND Operator B. OR Operator C. Join Operations XV. Managing Transactions and Locks A. Overview B. Managing Transactions 1. Recovery and Checkpoints 2. Considerations 3. Implicit Transactions Option 4. Restrictions C. SQL Server Locks 1. Concurrency Problems Prevented by Locks 2. Lockable Resources 3. Types of Locks 4. Lock Compatibility D. Managing Locks 1. Session-Level Locking Options 2. Dynamic Locking Architecture 3. Table-Level Locking Options 4. Deadlocks 5. Displaying Locking Information

Go to Description    Go to top of Competencies    Go to top of Outline