Standard

Posted: 09/10/07

R State Standard

£ Institutionally Developed

College: n/a

CIS 2163 – Designing and Implementing Databases with Microsoft® SQL Server™

Course Description:

This course provides instruction on how to design and implement a database solution by using Microsoft SQL Server. Topics include: developing a logical data model, deriving the physical design, creating data services, creating a physical database, and maintaining a database.

Competency Areas:	Hours
Developing a Logical Data Model	Class	4
Deriving the Physical Design	D. Lab	4
Creating Data Services	P. Lab/O.B.I.	0
Creating a Physical Database	Credit	6
Maintaining a Database

Prerequisite: CIS 2149 and CIS 2150

Corequisite:

Course Guide

Competency	After completing this section, the student will:	Hours
		Class	D. Lab	P. Lab/ O.B.I.
DEVELOPING A LOGICAL DATA MODEL		5	0	0
	Group data into entities by applying normalization rules
	Identify primary keys
	Choose a foreign key that will enforce a relationship between entities and that will ensure referential integrity
	Identify the business rules that relate to data integrity
	Incorporate business rules and constraints into the data model
	In a given situation, decide whether denormalization is appropriate
DERIVING THE PHYSICAL DESIGN		5	0	0
	Assess the potential impact of the logical design on performance, maintainability, extensibility, scalability, availability, and security
CREATING DATA SERVICES		20	20	0
	Access data by using the dynamic SQL model
	Access data by using the stored procedure model
Manipulate data by using Transact-SQL cursors.	Choose the appropriate type of cursor
	Define the appropriate level of sensitivity to change
	Choose the appropriate navigation
	Choose the scope of the cursor, specifically global or local
Create and manage explicit, implicit, and distributed transactions to ensure data consistency and recoverability.	Define the transaction isolation level
	Design transactions of appropriate length
	Avoid or handle deadlocks
	Use optimistic locking appropriately
	Implement error handling by using @@trancount
	Write INSERT, DELETE, UPDATE, and SELECT statements that retrieve and modify data
	Write Transact-SQL statements that use joins or subqueries to combine data from multiple tables
	Create scripts by using Transact-SQL. Programming elements include control-of-flow techniques, local and global variables, functions, and error handling techniques
Design, create, use, and alter views	Modify data through a view
	Query data through a view
Create and execute stored procedures to enforce business rules, to modify data in multiple tables, to perform calculations, and to use input and output parameters.	Implement error handling by using return codes and the RAISERROR statement
	Choose appropriate recompile options
Create triggers that implement rules that enforce data integrity, and that perform cascading updates and cascading deleted.	Implement transactional error handling
	Create result sets that provide summary data. Query types include TOP n PERCENT and GROUP BY, specifically HAVING, CUBE, and ROLLUP
	Configure session-level setting
Access data from static or dynamic sources by using remote stored procedures linked servers and OPENROWSET.	Evaluate where processing occurs when using OPENQUERY
CREATING A PHYSICAL DATABASE		5	10	0
	Create and manage files, file groups, and transaction logs that define a database
Create tables that enforce data integrity and referential integrity.	Choose the appropriate data types
	Create user-defined data types
	Define columns and NULL or NOT NULL
	Define columns to generate values by using the IDENTITY property, the uniqueidentifier data type, and the NEWID function
	Implement constraints
Create and maintain indexes.	Choose an indexing strategy that will optimize performance
	Given a situation, choose the appropriate type of index to create
	Choose the column or columns to index
	Choose the appropriate index characteristics, specifically FILLFACTOR, DROP_EXISTING, and PAD_INDEX
	Populate the database with data from an external data source. Methods include the bulk copy program and Data Transformation Services (DTS)
	Implement full-text search
MAINTAINING A DATABASE		5	10	0
	Evaluate and optimize the performance of an execution plan by using DBCC SHOWCONTIG, SHOWPLAN_TEXT, SHOWPLAN_ALL, and UPDATE STATISTICS
	Evaluate and optimize the performance of query execution plans
	Diagnose and resolve locking problems
	Identify SQL Server events and performance problems by using SQL Server Profiler

Suggested Resources

Books:

Microsoft SQL Server, Microsoft Press, ISBN: 0735621985