IT6733 Database Administration

Summer 2020

Dr. Svetlana Peltsverger

IT Department

Kennesaw State University

IT6733 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.

Contents

Module 1 Review.. 1

Module 2 Introduction to SQL Procedures and Functions. 3

Module 3 SQL Procedures & Functions Part II 5

Module 4 Triggers. 6

Module 5 Overview of Database Administration. 7

Module 6 Database and Application Performance. 8

Module 7 Security. 9

Module 8 Data Warehousing and ETL. 11

Module 9 Database Backup and Recovery. 12

Module 10 MongoDB. 13

Module 11 Data Dictionaries. 14

Module 12 Implementing ETL with SQL Server Integration Services. 14

 

 

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 https://docs.oracle.com/en/database/oracle/oracle-database/18/books.html

2.       MS SQL 2019 https://docs.microsoft.com/en-us/sql/sql-server/sql-server-technical-documentation?toc=..%2Ftoc%2Ftoc.json&view=sql-server-ver15

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:

After completing this module, students will be able:

to develop conceptual, logical and physical data models

to use SQL for data manipulation and data extraction

Readings

introduced

introduced

Practice exercise

reinforced

reinforced

Lab 0

 

reinforced

SQL quiz

reinforced

reinforced

Lab 1

mastered

mastered

 

 

 

 

Assigned Reading

1.       Environment to exercise https://livesql.oracle.com/ (create an account, login, use Live SQL
Live SQL

2.       SQL https://docs.oracle.com/en/database/oracle/oracle-database/18/cncpt/sql.html#GUID-DA48618A-A6BB-421A-A10A-02859D8ED9AD

3.       Readings linked through the module

4.       Intro to SQL 1-10 http://sqlcourse.com/intro.html

5.       Intro 2 to SQL 1-10 http://sqlcourse2.com/

Optional Reading

1.       Oracle Relational Data Structures https://docs.oracle.com/en/database/oracle/oracle-database/18/cncpt/oracle-relational-structures.html#GUID-54C48ABE-E589-428A-849F-B8746C9D276A

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 https://blogs.oracle.com/plsql-and-ebr/entry/why_use_pl_sql

"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:

After completing this module, students will be able:

to list benefits of procedural SQL

differentiate when to use function and when to use procedures

develop procedural SQL code

test and execute procedural SQL code

Read PL/SQL Language Fundamentals (2 hours)

introduced

introduced

introduced

 

Read PL/SQL Subprograms (1 hour)

reinforced

reinforced

introduced

introduced

Module Discussion preparation and posting (1 hour)

mastered

mastered

 

 

Complete Module Lab (1 hour 40 min)

 

 

mastered

mastered

Self-assessment quiz (20 min)

 

 

reinforced

reinforced

 

Assigned Reading

1.       Introduction to PL/SQL part I and II (except cursors) http://w2.syronex.com/jmr/edu/db/introduction-to-plsql/

Optional Reading

1.       Date functions examples http://psoug.org/reference/date_func.html

2.       PL/SQL Language Fundamentals https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/overview.html

3.       Variables and Types http://infolab.stanford.edu/~ullman/fcdb/oracle/or-plsql.html#variables%20and%20types

4.       Oracle Procedures http://www.psoug.org/reference/procedures.html

5.       Oracle Functions http://www.psoug.org/reference/functions.html

6.       PL/SQL FAQ http://www.orafaq.com/wiki/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:

After completing this module, students will be able:

explain the use of transactions

use looping and branching control structures

declare variables

develop Transact SQL code

test and execute Transact SQL code

Read assigned materials

introduced

introduced

introduced

introduced

Read and execute code from the module

reinforced

reinforced

reinforced

reinforced

Complete the lab

 

mastered

mastered

mastered

 

Assigned Reading

1.       PL/SQL Control Structures https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/plsql-control-statements.html

2.       What is a Stored Procedures in SQL server with examples https://www.youtube.com/watch?v=Qu3E-oncF3g

3.       Creating and executing stored procedures with output parameters https://www.youtube.com/watch?v=bldBshxuhMk

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 https://www.youtube.com/watch?v=st8RnNg_LLA

5.       Transact SQL http://www.tsql.info/stored-procedures/stored-procedures.php

6.       A Comparison of PL/SQL and Transact SQL http://psoug.org/reference/sqlserver.html

Optional Materials

1.       Oracle control structures http://psoug.org/reference/control_struct.html

2.       T-SQL Reference https://docs.microsoft.com/en-us/sql/t-sql/language-reference?view=sql-server-ver15

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:

After completing this module, students will be able:

to develop, test and debug Oracle PL/SQL triggers

 

to develop, test and debug MS SQL Server T-SQL triggers

Read assigned materials

introduced

introduced

Read and execute code from the module

reinforced

reinforced

Complete Module Lab

mastered

mastered

 

Assigned Reading

1.       Oracle Triggers https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/plsql-triggers.html

2.       T-SQL Triggers https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-ver15

Optional Materials

1.       Oracle triggers reference http://psoug.org/reference/table_trigger.html

2.       Triggers in PL/SQL includes: definition, trigger event, main parts of a trigger, types of trigger, syntax with example of creating triggers https://www.youtube.com/watch?v=aCOkOkfhmA0

3.       DML triggers in SQL server https://www.youtube.com/watch?v=JNb54seLzZY

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:

After completing this module, students will be able:

to distinguish between data and database administration

 

list responsibilities of a database administrator

 

Read the module and watch recorded lecture

introduced

introduced

Read assigned materials

reinforced

reinforced

Module Discussion preparation and posting

mastered

mastered

 

Assigned Reading

1.       DBA Roles and Responsibilities https://docs.oracle.com/en/database/oracle/oracle-database/18/admin/getting-started-with-database-administration.html (1.1 & 1.2)

Optional Materials

1.       Indeed DBA job description https://www.indeed.com/hire/job-description/database-administrator

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:

After completing this module, students will be able:

explain how to achieve good database performance

list activities performed during database performance tuning

to optimize performance of MS SQL databases

to optimize performance of Oracle databases

Assigned reading

introduced

introduced

introduced

introduced

Lab

Reinforced and mastered

Reinforced and mastered

Reinforced and mastered

Reinforced and mastered

 

Assigned Reading

1.       Execution Plan Basics https://www.simple-talk.com/sql/performance/execution-plan-basics/

2.       Database Indexes https://docs.microsoft.com/en-us/sql/2014-toc/sql-server-index-design-guide?view=sql-server-2014&viewFallbackFrom=sql-server-ver15 and https://docs.oracle.com/en/database/oracle/oracle-database/18/cncpt/indexes-and-index-organized-tables.html (if you need more details than provided in the module)

3.       Indexing in Oracle (25 min) https://www.youtube.com/watch?v=0X9bbtwTnuE

4.       Query optimization techniques in SQL Server: Database Design and Architecture https://www.sqlshack.com/query-optimization-techniques-in-sql-server-database-design-and-architecture/

Optional Materials

1.       Isolation http://en.wikipedia.org/wiki/Isolation_%28database_systems%29

2.       Understanding how SQL Server executes a query https://docs.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide?view=sql-server-ver15

3.       Database Performance Analyzer http://www.solarwinds.com/database-performance-analyzer-oracle.aspx (Interactive Demo)

4.       Oracle Database High Availability https://docs.oracle.com/en/database/oracle/oracle-database/18/high-availability.html

5.       MS SQL Server High Availability https://docs.microsoft.com/en-us/sql/database-engine/sql-server-business-continuity-dr?view=sql-server-ver15

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:

 

         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:

After completing this module, students will be able:

use Virtual Private Database feature (Oracle)

create and remove database user

 

create Profiles (Oracle) and Roles

 

GRANT, REVOKE , DENY Permissions to users and roles

 

Use Views and Stored Procedures for Security

 

Read the module and run all code

introduced

introduced

introduced

 

introduced

Read assigned materials

reinforced

reinforced

introduced

introduced

 

Complete Module Lab 1

 

mastered

 

reinforced

reinforced

Complete Module Lab 2

 

 

mastered

mastered

mastered

 

Assigned Reading

1.       Keeping Your Oracle Database Secure https://docs.oracle.com/en/database/oracle/oracle-database/18/dbseg/keeping-your-oracle-database-secure.html

2.       Database application security
https://github.com/OWASP/CheatSheetSeries/blob/master/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.md

3.       SQL Server

a.       USERS https://docs.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql?view=sql-server-ver15

b.       LOGINS https://docs.microsoft.com/en-us/sql/t-sql/statements/create-login-transact-sql?view=sql-server-ver15

c.       Permissions https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/getting-started-with-database-engine-permissions?view=sql-server-ver15

4.       Difference Between SQL Server Logins and Database Users https://www.top-password.com/blog/difference-between-sql-server-logins-and-database-users/

Optional Materials

1.       Security check lists http://web.nvd.nist.gov/view/ncp/repository?keyword=oracle&startIndex=0

 

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:

After completing this module, students will be able:

list major reasons why most organizations today need data warehousing.

to design, create, and manage cubes from data warehouse

Read the module and watch lecture recording

introduced

introduced

Read assigned materials

reinforced

reinforced

Complete Module Lab

mastered

mastered

 

Assigned Reading

1.       Oracle Database/ Data Warehousing Guide/ 18c https://docs.oracle.com/en/database/oracle/oracle-database/18/dwhsg/index.html (Part I chapters 1 & 2)

2.       Design a multidimensional business intelligence (BI) semantic model https://www.microsoftpressstore.com/articles/article.aspx?p=2812063

3.       Data Warehouse Schema Design https://www.guru99.com/star-snowflake-data-warehousing.html

Optional Materials

1.       Big Data Trends https://financesonline.com/big-data-trends/

2.       Azure Synapse Analytics (formerly SQL Server Data Warehousing) https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-overview-what-is

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:

After completing this module, students will be able:

To create / schedule backups for the databases

 

Choose the correct type of database recovery

 

Read assigned materials, watch recorded lecture

introduced

introduced

Read assigned materials

reinforced

reinforced

Watch Oracle Recovery Management Tutorial

reinforced

 

Complete Module Lab

mastered

mastered

 

Assigned Reading

1.       Oracle Backup and Recovery https://docs.oracle.com/en/database/oracle/oracle-database/18/bradv/database-backup-and-recovery-users-guide.pdf ch. 1& 2 (34 pages)

2.       High Availability with SQL Server 2019 https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/always-on-availability-groups-sql-server?view=sql-server-ver15

3.       Required tutorial https://www.youtube.com/watch?v=tYwCzNiJD6w

Optional Materials

1.       How to: Restore a Database Backup (SQL Server Management Studio) https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-database-backup-using-ssms?view=sql-server-ver15

2.       Managing Backups (part IV) https://docs.oracle.com/en/database/oracle/oracle-database/18/bradv/database-backup-and-recovery-users-guide.pdf 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:

After completing this module, students will be able:

Write and run MongoDB shell commands

List major differences between RDBMS and NoSQL databases.

Read

introduced

introduced

Module Discussion preparation and posting

 

Reinforced & mastered

Complete Module Lab

Reinforced & mastered

 

 

Assigned Reading

1.       SQL vs NoSQL https://www.youtube.com/watch?v=ZS_kXvOeQ5Y 21 min

2.       The best NoSQL databases 2020 https://www.improgrammer.net/most-popular-nosql-database/

3.       NoSQL http://www.w3resource.com/mongodb/nosql.php

4.       MongoDB https://docs.mongodb.org/manual/introduction/

5.       MongoDB Tutorial https://www.youtube.com/watch?v=pWbMrx5rVBE 32 min

Optional Materials

1.       Mongo Shell Quick Reference Guide https://docs.mongodb.com/manual/reference/mongo-shell/#command-line-options

2.       NoSQL http://nosql-database.org/

3.       Sharding Concepts http://docs.mongodb.org/manual/core/sharded-clusters/

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:

After completing this module, students will be able:

to identify sources of metadata in Oracle and SQL Server

 

to query system catalog to extract metadata

 

Assigned reading

introduced

introduced

Module Discussion preparation and posting

reinforced

reinforced

Lab

mastered

mastered

 

Assigned Reading

  1. Data Dictionary https://docs.oracle.com/en/database/oracle/oracle-database/18/cncpt/data-dictionary-and-dynamic-performance-views.html
  2. SQL Server Catalog Views https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/catalog-views-transact-sql?view=sql-server-ver15

Optional Materials

1.       Oracle Catalog Views https://docs.oracle.com/en/database/oracle/oracle-database/18/rcmrf/summary-of-rman-recovery-catalog-views.html

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:

After completing this module, students will be able:

Set data sources and destinations for extracting and loading data

Use transformations for cleaning, aggregating, merging, and copying data

Read the module and watch lecture recording

introduced

introduced

Read assigned materials

reinforced

reinforced

Complete the Project

mastered

mastered

 

Assigned Reading

1.       N/A

Optional Materials

1.       Merge Two Data in ETL project of SSIS http://www.jitendrazaa.com/blog/sql/sqlserverintegrationservices/merge-two-data-in-etl-project-of-ssis/