Course Information

INFO 3520 DATABASE ADMINISTRATION FOR PROFESSIONALS

COURSE CODE:         INFO 3520

COURSE TITLE:         DATABASE ADMINISTRATION FOR PROFESSIONALS

NO. OF CREDITS:      4

LEVEL:                          3

PRE-REQUISITE:       INFO 2415

COURSE DESCRIPTION:

This course introduces students to Database Administration. Students taking the course should have a basic understanding of how database concepts and SQL commands. The course provides practical experience in setting up and maintaining a MySQL/Oracle server, including backing up, recovery, configuration and optimization strategies.

This course is suitable for delegates intending to sit the ‘Certified MySQL DBA I’ and ‘Certified MySQL DBA 2’ examinations.

COURSE RATIONALE

More and more employers are expecting students to be industry ready. This course enhances the learning outcomes of INFO 2415 by giving the student the opportunity to tailor and extend their database skills in an industry specific database management system. For example, on completion of this course students will be able to take the MySQL DBA I and Certified MySQL DBA 2 examinations. Hence our students would now be in a better position to take advantage of high skilled job opportunities.

COURSE CONTENT:

Unit1     MySQL Administrator

  • Installation and Tools
  • Interpreting Error and Diagnostic Information

Unit 2    MySQL Architecture

  • Client/Server Overview
  • Communication Protocols
  • The SQL Parser and Storage Engine Tiers
  • How MySQL Uses Disk Space
  • How MYSQL Uses Memory

Unit 3    Installing, Configuring, Starting and Stopping

  • MySQL Distributions

1.1 Installing on Windows

1.2 Installing on Linux and UNIX

1.3 Some Security Issues

Unit 4    Table Management

  • Tables, Data Types and Character Set Support
  • Table Management
  • Locking Concepts
  • Storage Engines
  • Table Maintenance

Unit 5    Backup and Recovery

  • Planning and Implementing a Backup and Recovery Strategy
  • Defining a Disaster Recovery Plan

Unit 6    User Management

  • Managing Privileges

Unit 7    Stored Routines for Administration

  • Types of Stored Routines
  • Benefits of Stored Routines
  • Stored Routines Features
  • Stored Routine Maintenance
  • Stored Routine Privileges and Execution Security
  • Triggers

Unit 8    Securing the Server

  • Security Issues
  • Operating System Security
  • Filesystem Security
  • Log Files and Security
  • Network Security

Unit 9    Optimizing Queries and Processes

  • Optimization Overview
  • Optimization Process
  • Planning a Routine Monitoring Regime
  • Optimization and Indexes
  •  Indexes for Performance
  • Optimizing Schemas
  •  Normalisation
  • Optimizing the Server
  • Measuring Server Load
  • Optimizing the Environment
  • Choosing the Platform
  • Hardware Configurations

Unit10   The Event Scheduler

  • Event scheduler concepts
  • Event scheduler configuration
  • Creating, altering and dropping events
  • Event scheduler monitoring
  • Events and privilege

LEARNING OUTCOMES:

This course is designed to give administrators the knowledge and skills needed to maintain successful and efficient day-to-day operations of MySQL databases and services. Administrators will gain practical experience in setting up, administering and troubleshooting MySQL databases.

After completing the course, the students will be able to:

  • Start, Stop and Configure MySQL
  • Conduct Security-Related Configuration
  • Create Databases
  • Use Client Programs for DBA Work
  • Set up Character Set Support
  • Implement Locking
  • Use Storage Engines
  • Maintain user Tables
  • Query Data from the Information_Schema Database
  • Perform Backup and Recovery
  • Use Stored Procedures for Database Administration Tasks
  • Perform User Management
  • Secure the Server
  • Upgrade Security systems
  • Optimize Queries
  • Optimize Schemas
  • Optimize the Server
  • Configure the Event Scheduler
  • Implement Partitioned Tables
  • Interpret Error Messages
  • Optimize the Environment
  • Scale MySQL

COURSE ASSESSMENT:

Method of Evaluation

Percentage of Grade

Restriction

Coursework

   2 Assignments 20%

   (problem sets and practical implementation of MySQL)

 

   1 1-hr written  Exam 10%

 

   1 1-hr practical Exam 10%

40 %

 

Final Examination
     One 2-hours Multiple Choice

     Paper

 

60%

Fail Theory Regulation

Students must get a passing grade on final examination to pass the course.

TEACHING STRATEGIES:

Lectures will be used to introduce the main concepts of the course. Students will set up, configure, and manage MySQL server and databases in the lab

RESOURCES:

Lecture notes

Philip J. Pratt and Mary Z. Last. A Guide to MySQL.

Michael McLaughlin. MySQL Workbench Data Modeling and Development.

COURSE CALENDER:  52 hours (4 hours per week)

Week

Topic

Week 1

MySQL Administrator

  • Installation and Tools
  • Interpreting Error and Diagnostic Information

Week 2

MySQL Architecture

  • Client/Server Overview
  • Communication Protocols
  • The SQL Parser and Storage Engine Tiers
  • How MySQL Uses Disk Space
  • How MYSQL Uses Memory

Week 3

Installing, Configuring, Starting and Stopping

  • MySQL Distributions
    • 1.1 Installing on Windows
    • 1.2 Installing on Linux and UNIX
    • 1.3 Some Security Issues

 

Week 4

Table Management

  • Tables, Data Types and Character Set Support
  • Table Management
  • Locking Concepts
  • Storage Engines
  • Table Maintenance
  • Backup and Recovery
  • Planning and Implementing a Backup and Recovery Strategy
  • Defining a Disaster Recovery Plan

Week 4

Assignment 1

Week 5

User Management

  • Managing Privileges

 

Week 6

Stored Routines for Administration

  • Types of Stored Routines
  • Benefits of Stored Routines
  • Stored Routines Features
  • Stored Routine Maintenance
  • Stored Routine Privileges and Execution Security
  • Triggers

Week 7

Examination 1

Week 8

Securing the Server

  • Security Issues
  • Operating System Security
  • Filesystem Security
  • Log Files and Security
  • Network Security

Week 9

Optimizing Queries and Processes

  • Optimization Overview
  • Optimization Process
  • Planning a Routine Monitoring Regime
  • Optimization and Indexes
  •  Indexes for Performance

Week 9

Assignment 2

Week 10

  • Optimizing Schemas
  •  Normalisation
  • Optimizing the Server
  • Measuring Server Load
  • Optimizing the Environment
  • Choosing the Platform
  • Hardware Configurations

 

Week 10

Examination 2 – practical

Week 11

The Event Scheduler

  • Event scheduler concepts
  • Event scheduler configuration
  • Creating, altering and dropping events
  • Event scheduler monitoring
  • Events and privilege

 

Week 12

Revision/Review