FOOTHILL COLLEGE
COMPUTERS, TECHNOLOGY AND INFORMATION SYSTEMS

DEGREE APPLICABLE

COMPUTER INFORMATION
SYSTEMS 54C
COURSE OUTLINE
FALL 2005

1. Catalog Description
CIS 54C SQL Server Database Design 5 UNITS

Advisory: CIS 52A, CNET 54A or equivalent
Two hours lecture, two hours lecture in the lab four hours terminal time
Plan, design and implement database systems using the latest version of Microsoft SQL-Server. The course includes training in the creation and maintenance of database objects, implementation of data integrity, Transact-SQL to query a SQL Server database. Database security and optimization techniques are covered. The course is designed to prepare students for Microsoft MCAD/MCSE/MCSD Exam 70-229

2. Expected Outcomes
The student will be able to:
A. Describe SQL Server, SQL Server terminology, its components and architecture
B. Use SQL Servers database language Transact-SQL to administer data base functions
C. Design SQL Server databases to include identifying system requirements and developing logical data models
D. Implement physical SQL Server databases and tables from a logical database design
E. Implement and enforce data integrity and describe the types of data integrity found on SQL Server databases
F. Access, modify and delete data using SQL Query Analyzer and other methods
G. Demonstrate advanced data management including importing, exporting and extracting data in XML format
H. Implement stored procedures to improve database performance and security
I. Implement Triggers to Improve Data Integrity
J. Describe and implement views and use views to manipulate data.
K. Implement Indexes to Improve System Performance
L. Manage transactions and locks
M. Design and administer database security
N. Optimize database performance

3. Special Facilities and/or Equipment Needed
Classroom and/or laboratory equipped with MS Windows based computers, MS SQL Server 2000 Enterprise Edition or later, instructor computer with projector. For Internet class - a PC with SQL Server 2000 or later Enterprise Edition or higher (120 day free evaluation edition OK), connection to an Internet provider that supports Internet Explorer?, zipping software such as WinZip?

4. Expanded Description of Course Content

A. Overview
1. Databases and database terminology
2. SQL Server
a. Versions
b. Features
c. Components
d. Architecture
3. Data Warehousing and OLAP
4. XML

B. Interact with a SQL Server Database
1. Database programming
2. Database Queries
3. Transact-SQL
a. Statements
b. Debugging
c. Data Definition Language
4. SQL Query Analyzer (GUI)
5. Command-Prompt Utilities

C. Database design
1. Overview
2. Database objects
3. Planning and creating a SQL Server database
a. Mapping a database
b. Physical database design
c. Installation
4. System requirements
5. Logical data models

D. Logical to physical design
1. Implement a physical database
a. Database objects
b. Table objects
2. Data types
3. Creating and modifying tables

E. Data Integrity
1. Table properties
2. Data Types and Data Integrity
a. System-supplied
b. User defined
3. Types of Data Integrity
4. Integrity Constraints

F. Access, Modify and Delete Data
1. Using Query Analyzer
2. Keywords and Clauses
3. Advanced Queries
a. Joins
b. Subqueries
c. Summarizing data
4. Database API’s and cursors

G. Advanced Data Management
1. Importing Data from an External Source
2. Exporting Data to an External Source
3. Using the bcp Command Prompt Utility
4. SQL Server DTS Tools
5. Using Distributed Queries
6. Using Cursors to Retrieve Data
a. Transact-SQL cursors
b. API Server cursors
c. Cursor locking
7. Retrieving XML Data

H. Stored Procedures
1. Purpose of Stored Procedures
2. When to use them
3. Creating, Executing and Manipulating Stored Procedures
4. Programming Stored Procedures

I. Triggers and Data Integrity
1. Trigger Types
2. Referential Integrity
3. Trigger Management
a. Create
b. Modify
c. View
d. Drop
e. Disable
4. Programming Triggers
a. Syntax
b. Inserted and deleted pseudo tables
c. Common trigger system functions

J. Views
1. Views vs. Real Tables
2. When to Use Views
3. Creating and Modifying Views
4. Accessing Data through Views

K. Indexes
1. Indexes and Database Performance
2. Index Architecture
3. Create and Manage Indexes

L. Transactions and Locks
1. Transactions, Locking and Database Integrity
2. Transaction and Locking Architecture
3. Managing Transactions and Locks

M. SQL Server Database Security Management
1. Database Security Overview
2. Designing a Database Security Plan using SQL Server
3. Implementing and Maintaining Database Security

N. Performance Tuning
1. SQL Profiler and Database Monitoring
2. Index Tuning
3. Implications for Hardware Upgrading
4. Database Partitioning

5. Repeatability Criteria
Not Repeatable.

6. Methods of Evaluation
A. Point system for all tests and assigned projects to include:
1. Observation of student project assignments
2. Performance examinations
3. Objective examinations based on Exam 70-229
4. Guided final project of the instructor/student’s design

7. Text(s)
Woolly, Valerie (ed.). Microsoft SQL Server 2000 Database Design and Implementation. (2003). Redmond, WA: Microsoft Press.
When course is taught on-line: Additional information, notes, handouts, syllabus, assignments, tests, and other relevant course material will be delivered by e-mail and on the World Wide Web, and discussion may be delivered in chat rooms or moderated listservs, and in the ETUDES environment.

8. Authorized Discipline(s)
Computer Information Systems
Computer Science