Course Outline of Record

Item	Value
Curriculum Committee Approval Date	10/20/2021
Top Code	070720 - Database Design and Administration
Units	3 Total Units
Hours	72 Total Hours (Lecture Hours 45; Lab Hours 27)
Total Outside of Class Hours	0
Course Credit Status	Credit: Degree Applicable (D)
Material Fee	No
Basic Skills	Not Basic Skills (N)
Repeatable	No
Grading Policy	Standard Letter (S),  Pass/No Pass (B)

Course Description

Covers the terminology, technology, and software used to design and implement a Microsoft SQL Server database. Students will learn to design, create and maintain a database to handle LOB (Line of Business) and DW (Data Warehousing) applications. Transfer Credit: CSU. C-ID: ITIS 180.C-ID: ITIS 180.

Course Level Student Learning Outcome(s)

1. Demonstrate the ability to design and implement a database structure to satisfy LOB (line of Business) and DW (Data Warehouse) applications.
2. Demonstrate the ability to define additional components to aid in the retrieval and manipulation of data in a timely manner.

Course Objectives

• 1. Perform a basic installation of SQL Server and describe the additional modules and their functions.
• 2. Describe the rules of normalization used to define a database and its tables.
• 3. Define tables for storing data and define relationships between various pieces of information.
• 4. Load and unload data from the database using the import and export functions of SQL Server.
• 5. Define the structure of indexes and define indexes for optimal performance.
• 6. Query database tables, joining information together to satisify business requirements.
• 7. Define other database objects (Views, Procedures, Triggers etc.) to enhance data maintenance and support LOB applications.
• 8. Update data using various commands including insert, update, delete, merge and others.
• 9. Analyze queries and other objects to maintain a high level of performance.

---

Lecture Content

Installing SQL Server Overview of SQL Server (SQL, SSIS, SSAS, SSRS) Install SQL Server Install the Sample Databases SQL Logins and Authentication Building Tables Building a Database Files and Filegroups Transaction Logs Database snapshots Normalization Rules Table Design and Relationships Star and Snowflake Schemas Slowly Changing Dimensions Building a Table and Field Types Computed Columns Using the Identity Field Full Text and XML (Not Covered) Defining Constraints and Defaults Data Compression Partitioning a Table Defining and Structure of an Index Table Statistics Importing and Exporting Data BCP and Bulk Insert Bookshop Database Querying a Database The sequence a Select is processed The Select and From clause Select Functions (DatePart, SubString, Case etc) Filtering Data (Where clause) Sorting Data (Order By clause) Selecting records (Top and Offset_Fetch) Combining Sets (Joins) Sub Queries and CTE Union, Intersect and Except Grouping Pivot and Un Pivot Windowing Data (Over Partition) Working with SQL Profiler Viewing the Execution Plan Other Database Objects Views Inline Functions Synonyms Stored Procedures Cursors and Temp Tables Triggers User Defined functions Updating Data Insert statement Select Into Update statement Delete statement Truncate statement Merge statement Output statement Transaction Processing Locking Isolation Levels Error Handeling (RaiseError and Try/Catch) Dynamic SQL Performance Query Optimizer SQL Profiler Query Analyzer (Execution Plans) Access Methods (Scan vs Seek) Join Algorithms (Loop, Merge and Hash) Optimizer Hints

Lab Content

Practical Application of Installing SQL Server. Practical Application of Building Tables.Practical Application of Querying a Database.Practical Application of Other Database Objects.Practical Application of Updating Data. Practical Application of Performance Analysis.

Method(s) of Instruction

• Lecture (02)
• DE Live Online Lecture (02S)
• DE Online Lecture (02X)
• Lab (04)
• DE Live Online Lab (04S)
• DE Online Lab (04X)

Instructional Techniques

Students will be presented material from several different sources, including, but not limited to:   Microsoft study guides.   Web-based curriculum.   Lectures of systems integration and personal experiences of industry professionals.Students will need to complete individual exercises to:   Perform many of the tasks of a database administrator.   Build functional databases to satisfy user requirements.

Reading Assignments

45 hours (3hrs/wk).Read from the Assigned Textbook. Minimum of  hours per week reading from textbook material.

Writing Assignments

Student performance on exams and assignments will be used to determine proficiency. This course will be oriented toward a practical and hands-on approach to the subject. Lab assignments will consist of performing and documenting common functions performed by a database administrator. Minimum of 3 hours per week creating and editing class and software projects.

Out-of-class Assignments

45 hours (3hrs/wk). Student performance on quizzes, tests, including short essays, and laboratory assignments will be used to determine proficiency

Demonstration of Critical Thinking

.

Required Writing, Problem Solving, Skills Demonstration

Student performance on exams and assignments will be used to determine proficiency. This course will be oriented toward a practical and hands-on approach to the subject. Lab assignments will consist of performing and documenting common functions performed by a database administrator.

Eligible Disciplines

Computer information systems (computer network installation, microcomputer ...: Any bachelors degree and two years of professional experience, or any associate degree and six years of professional experience. Computer service technology: Any bachelors degree and two years of professional experience, or any associate degree and six years of professional experience.

Textbooks Resources

1. Required Radivojevic, M., Sarka, D. et al.. Mastering SQL Server 2017, ed. Microsoft Corporation, 2019