IT 6733 Database Administration course covers data administration and management, backup/recovery, security, access control, performance monitoring and tuning, data warehousing, data mining, online analytical processing, centralized versus distributed environments, client server and world-wide-web database integration.

Students who complete this course successfully will be able to

• Describe tasks commonly performed by DBAs.

• Analyze performance characteristics of a DB system and justify necessary changes.

• Write programs to perform maintenance and user support operations.

• Explain the concept of database security and backup/recovery

In this course, we will use Oracle 18c and SQL server 2019. We will start with SQL review, and then learn how to write triggers, administer users, and make configuration changes, you will also learn about database security, performance tuning, data mining and analytical processing.

Table of Contents

Module 1: Review

Module 2: Introduction to SQL Procedures and Functions

Module 3: SQL Procedures & Functions Part II

Module 4: Triggers

Module 5: Overview of Database Administration

Module 6: Database and Application Performance

Module 7: Security

Module 8: Data Warehousing and ETL

Module 9: Database Backup and Recovery

Module 10: MongoDB

Module 11: Data Dictionaries

Module 12: Implementing ETL with SQL Server Integration Services

Module 1: Review

Introduction and Module Summary

In this module, you will review database design and Structured Query Language (SQL). SQL is the standard language for relational database management systems. SQL knowledge is the prerequisite to this course. Depending on when you took an introduction to databases course, this module will take you anywhere from 4 to 12 hours of work. Spending enough time on this review will help you to complete other modules in this course.

Bookmark two resources that you will use throughout this course

1. Oracle 18c

2. MS SQL 2019

Objectives and Outcomes

This module directly supports highlighted course outcome(s)

Students who complete this course successfully will be able to

1. Describe tasks commonly performed by DBAs.

2. Analyze performance characteristics of a DB system and justify necessary changes.

3. Write programs to perform maintenance and user support operations.

4. Explain the concept of database security and backup/recovery

Module Outcomes and Activities

Assigned Reading

1. Environment to exercise (create an account, login, use Live SQL)

   

2. SQL

3. Readings linked throughout the module

4. Intro to SQL 1-10

5. Intro 2 to SQL 1-10

Optional Reading

1. Oracle Relational Data Structures

Module 2: Introduction to SQL Procedures and Functions

Introduction and Module Summary

In this module, you will learn the benefits of using procedural SQL and how to write, execute, and test SQL procedures and functions.

SQL has limitations: it can execute one statement at a time. PL/SQL is executed as a block of code. Moreover, you can repeat the execution of any named black as many times as you wish.

PL/SQL is used to write triggers, functions, procedures, and packages. You can call PL/SQL functions from SQL statements.

Why use PL/SQL?

By Bryn.Llewellyn

"Large software systems must be built from modules. A module hides its implementation behind an interface that exposes its functionality. This is computer science's most famous principle. For applications that use an Oracle Database, the database is, of course, one of the modules. The implementation details are the tables and the SQL statements that manipulate them. These are hidden behind a PL/SQL interface. This is the Thick Database paradigm: select, insert, update, delete, merge, commit, and rollback are issued only from database PL/SQL. Developers and end-users of applications built this way are happy with their correctness, maintainability, security, and performance."

Objectives and Outcomes

This module directly supports highlighted course outcome(s)

Students who complete this course successfully will be able to

1. Describe tasks commonly performed by DBAs.

2. Analyze the performance characteristics of a DB system and justify necessary changes.

3. Write programs to perform maintenance and user support operations.

4. Explain the concept of database security and backup/recovery

Module Outcomes and Activities

Assigned Reading

1. Introduction to PL/SQL part I and II (except cursors)

Optional Reading

1. Date functions examples

2. PL/SQL Language Fundamentals

3. Variables and Types

4. Oracle Procedures

5. Oracle Functions

6. PL/SQL FAQ

Module 3: SQL Procedures & Functions Part II

Introduction and Module Summary

In this module, you will learn how to use IF, CASE, COMMIT, and LOOPs. SQL does not support all of the traditional language constructs, so achieve branching and looping the procedural SQL will be used. Transact SQL and its difference with Procedural SQL will be discussed.

Objectives and Outcomes

This module directly supports highlighted course outcome(s)

Students who complete this course successfully will be able to

1. Describe tasks commonly performed by DBAs.

2. Analyze performance characteristics of a DB system and justify necessary changes.

3. Write programs to perform maintenance and user support operations.

4. Explain the concept of database security and backup/recovery

Module Outcomes and Activities

Assigned Reading

1. PL/SQL Control Structures

2. What is a Stored Procedures in SQL server with examples

3. Creating and executing stored procedures with output parameters

4. What are return values for stored procedures, difference between stored procedure return values and output parameters, when to use output parameters over return values

5. Transact SQL

6. A Comparison of PL/SQL and Transact SQL

Optional Materials

1. Oracle control structures

2. T-SQL Reference

Module 4: Triggers

Introduction and Module Summary

In this module, you will learn how to write PL/SQL and T-SQL triggers. A trigger is a named structural SQL block (PL/SQL or T-SQL) that is stored in the database and executed (fired) in response to a specified event that occurs in the database.

Objectives and Outcomes

This module directly supports highlighted course outcome(s)

Students who complete this course successfully will be able to

1. Describe tasks commonly performed by DBAs.

2. Analyze performance characteristics of a DB system and justify necessary changes.

3. Write programs to perform maintenance and user support operations.

4. Explain the concept of database security and backup/recovery

Module Outcomes and Activities

Assigned Reading

1. Oracle Triggers

2. T-SQL Triggers

Optional Materials

1. Oracle triggers reference

2. Triggers in PL/SQL includes: definition, trigger event, main parts of a trigger, types of trigger, syntax with example of creating triggers

3. DML triggers in SQL server

Module 5: Overview of Database Administration

Introduction and Module Summary

In this module, you will become familiar with database administration tasks and learn how to ensure the ongoing operational functionality and efficiency of an organizations database.

Objectives and Outcomes

This module directly supports highlighted course outcome(s)

Students who complete this course successfully will be able to

1. Describe tasks commonly performed by DBAs.

2. Analyze performance characteristics of a DB system and justify necessary changes.

3. Write programs to perform maintenance and user support operations.

4. Explain the concept of database security and backup/recovery

Module Outcomes and Activities

Assigned Reading

1. DBA Roles and Responsibilities (1.1 & 1.2)

Optional Materials

1. Indeed DBA job description

Module 6: Database and Application Performance

Introduction and Module Summary

In this module, you will learn how to choose DBMS based on data availability requirements and how to design the database and configure the database instance to achieve the best performance by designing indexes for an application, using denormalization and views, avoiding deadlocks

Objectives and Outcomes

This module directly supports highlighted course outcome(s)

Students who complete this course successfully will be able to

1. Describe tasks commonly performed by DBAs.

2. Analyze performance characteristics of a DB system and justify necessary changes.

3. Write programs to perform maintenance and user support operations.

4. Explain the concept of database security and backup/recovery

Module Outcomes and Activities

Assigned Reading

1. Execution Plan Basics

2. Database Indexes 1 (Microsoft) and 2 (Oracle) (if you need more details than provided in the module)

3. Indexing in Oracle (25 min)

4. Query optimization techniques in SQL Server: Database Design and Architecture

Optional Materials

1. Isolation

2. Understanding how SQL Server executes a query

3. Database Performance Analyzer (Interactive Demo)

4. Oracle Database High Availability

5. MS SQL Server High Availability

Module 7: Security

Introduction and Module Summary

In this module, you will learn about Operating System Security, DBMS Security and Database Application Security. Database Administrators responsibility is to implement and manage Database Security. These include defining and determining the following:

• Who is allowed access to the database

• How a user will be authenticated

• The authority level a user is granted

• The data that a user is allowed to read and/or modify

• The database objects a user is allowed to create, alter, and/or drop

Objectives and Outcomes

This module directly supports highlighted course outcome(s)

Students who complete this course successfully will be able to

1. Describe tasks commonly performed by DBAs.

2. Analyze performance characteristics of a DB system and justify necessary changes.

3. Write programs to perform maintenance and user support operations.

4. Explain the concept of database security and backup/recovery

Module Outcomes and Activities

Assigned Reading

1. Keeping Your Oracle Database Secure

2. Database application security

3. SQL Server

   1. USERS

   2. LOGINS

   3. Permissions

4. Difference Between SQL Server Logins and Database Users

Optional Materials

1. Security check lists

Module 8: Data Warehousing and ETL

Introduction and Module Summary

In this module, you will learn about Data Warehousing. The term Data Warehouse was coined by Bill Inmon in 1990, which he defined in the following way: "A warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management's decision making process". ETL is short for extract, transform, load, three database functions that to pull data out of different data sources in on database.

Objectives and Outcomes

This module directly supports highlighted course outcome(s)

Students who complete this course successfully will be able to

1. Describe tasks commonly performed by DBAs.

2. Analyze performance characteristics of a DB system and justify necessary changes.

3. Write programs to perform maintenance and user support operations.

4. Explain the concept of database security and backup/recovery

Module Outcomes and Activities

Assigned Reading

1. Oracle Database/ Data Warehousing Guide/ 18c (Part I chapters 1 & 2)

2. Design a multidimensional business intelligence (BI) semantic model

3. Data Warehouse Schema Design

Optional Materials

1. Big Data Trends

2. Azure Synapse Analytics (formerly SQL Server Data Warehousing)

Module 9: Database Backup and Recovery

Introduction and Module Summary

In this module, you will learn structures and processes for backup and recovery. Introduce the different methods for backup and recovery Diagnose and troubleshoot database problems.

Objectives and Outcomes

This module directly supports highlighted course outcome(s)

Students who complete this course successfully will be able to

1. Describe tasks commonly performed by DBAs.

2. Analyze performance characteristics of a DB system and justify necessary changes.

3. Write programs to perform maintenance and user support operations.

4. Explain the concept of database security and backup/recovery

Module Outcomes and Activities

Assigned Reading

1. Oracle Backup and Recovery ch 1 & 2 (34 pages)

2. High Availability with SQL Server 2019

3. Required tutorial

Optional Materials

1. How to: Restore a Database Backup (SQL Server Management Studio)

2. Managing Backups (part IV) pp 33-37

Module 10: MongoDB

Introduction and Module Summary

In this module, you will learn about NoSQL (Not Only SQL) Databases and how real time web applications deal with massive amount of data. NoSQL databases use the concept of a key/value store, and it is designed for distributed data stores where very large scale of data storing needs. These type of data storing may not require fixed schema, avoid join operations and typically scale horizontally.

Objectives and Outcomes

This module directly supports highlighted course outcome(s)

Students who complete this course successfully will be able to

1. Describe tasks commonly performed by DBAs.

2. Analyze performance characteristics of a DB system and justify necessary changes.

3. Write programs to perform maintenance and user support operations.

4. Explain the concept of database security and backup/recovery

Module Outcomes and Activities

Assigned Reading

1. SQL vs NoSQL 21 min

2. The best NoSQL databases 2020

3. NoSQL

4. MongoDB

5. MongoDB Tutorial 32 min

Optional Materials

1. Mongo Shell Quick Reference Guide

2. NoSQL

3. Sharding Concepts

Module 11: Data Dictionaries

Introduction and Module Summary

In this module, you will learn about metadata and about metadata in both SQL Server and Oracle. Database metadata provides technical information about a database object, such as, field structures of a table, the owner of a table, the tablespace, constraints.

Objectives and Outcomes

This module directly supports highlighted course outcome(s)

Students who complete this course successfully will be able to

1. Describe tasks commonly performed by DBAs.

2. Analyze performance characteristics of a DB system and justify necessary changes.

3. Write programs to perform maintenance and user support operations.

4. Explain the concept of database security and backup/recovery

Module Outcomes and Activities

Assigned Reading

1. Data Dictionary

2. SQL Server Catalog Views

Optional Materials

1. Oracle Catalog Views

Module 12: Implementing ETL with SQL Server Integration Services

Introduction and Module Summary

In this module, you will use Microsoft SQL Server Integration Services (SSIS) features you'll extract data from many different data sources to provide a unified view of the data.

Objectives and Outcomes

This module directly supports highlighted course outcome(s)

Students who complete this course successfully will be able to

1. Describe tasks commonly performed by DBAs.

2. Analyze performance characteristics of a DB system and justify necessary changes.

3. Write programs to perform maintenance and user support operations.

4. Explain the concept of database security and backup/recovery

Module Outcomes and Activities

Assigned Reading

1. N/A

Optional Materials

1. Merge Two Data in ETL project of SSIS