Enterprise Database Systems
MySQL Database Development
MySQL Database Development: Database Design Fundamentals
MySQL Database Development: DDL Statements
MySQL Database Development: GIS, Cloud, and Connectors for MySQL
MySQL Database Development: Introduction
MySQL Database Development: Manipulating Data
MySQL Database Development: Query and Performance Optimization
MySQL Database Development: SELECT Statement and Operators
MySQL Database Development: Stored Routines, Triggers, and the InnoDB memcached Plugin
MySQL Database Development: Working with Functions

MySQL Database Development: Database Design Fundamentals

Course Number:
it_dfmydd_02_enus
Lesson Objectives

MySQL Database Development: Database Design Fundamentals

  • describe the key components of a RDBMS, including table structure, keys, and relationships
  • define the standards used in tables for data storage, such as removing duplicates, breaking down fields, and the use of primary keys
  • define the standards used in column design and properties, such as auto increment columns, default and null values, and modifying size and format
  • use MySQL Workbench to determine table and column properties
  • define table relationships in a database, including one-to-many, one-to-one, and many-to-many
  • describe database normalization and identify the various levels of normalization
  • describe the normalization process through each level of the normal forms
  • identify the benefits achieved through the normalization process
  • describe what Full Stack Development is and the various tasks a developer would perform in each area
  • describe the components of a full stack environment and how each component interacts
  • describe the most common software stacks available that employ MySQL

Overview/Description

A database is only as good as its initial design. Explores RDBMS design fundamentals, such as table design, normalization, and relationships. Full Stack Development and how MySQL fits into the FDS landscape is also covered.



Target

Prerequisites: none

MySQL Database Development: DDL Statements

Course Number:
it_dfmydd_03_enus
Lesson Objectives

MySQL Database Development: DDL Statements

  • describe the use of the CREATE, ALTER, and DROP DDL statements, and recognize their basic syntax
  • use the CREATE DATABASE DDL statement to create a new database with specific syntax and parameters
  • use the ALTER and DROP DDL statements to change and drop an existing database
  • use the CREATE TABLE DDL statement to create new tables and define their properties
  • use the ALTER and DROP DDL statements to change and drop an existing table
  • use the CREATE and ALTER VIEW DDL statements to create and alter views in MySQL databases
  • use views to retrieve records from tables and other views
  • create nested views
  • create advanced views using join statements and use aggregate functions

Overview/Description

Data Definition Language (DDL) statements are used to manipulate MySQL database schema objects. Examine the use of the CREATE, ALTER, and DROP statements to create, modify, and maintain MySQL databases, tables, and views.



Target

Prerequisites: none

MySQL Database Development: GIS, Cloud, and Connectors for MySQL

Course Number:
it_dfmydd_09_enus
Lesson Objectives

MySQL Database Development: GIS, Cloud, and Connectors for MySQL

  • create and use geospatial tables, fields, and indexes to manage GIS based data in MySQL
  • work with the basic Point, Polygon, and Geometry spatial data types in MySQL
  • use the MySQL collection data types like Multipoint and Multilinestring to manage collections of values
  • describe a MySQL deployment using the Microsoft Azure cloud service
  • describe MySQL setup and migration using the Amazon Relational Database Service (RDS) and the Amazon Database Migration Service (DMS)
  • describe provisioning and integrating MySQL on the Oracle Cloud using Oracle's Cloud Services
  • use the Java Database Connectivity (JDBC) connector to access MySQL from within a Java app
  • work with the ADO.NET MySQL connector within Visual Studio
  • use the Python connector to access MySQL from within a Python App

Overview/Description

GIS data types, cloud integration, and low level access for database performance give MySQL the power necessary to handle many data storage tasks. Explore these features in MySQL.



Target

Prerequisites: none

MySQL Database Development: Introduction

Course Number:
it_dfmydd_01_enus
Lesson Objectives

MySQL Database Development: Introduction

  • describe the MySQL database platform, why you would want to use it, and the various editions available
  • locate and select the download options for the correct edition of MySQL
  • perform the installation and configuration of MySQL
  • properly configure the MySQL Server
  • describe what MySQL Workbench is and recognize the various functions it can perform
  • configure the Options File to change the behavior of the MySQL server
  • use MySQL Workbench to create and maintain database connections
  • use the Navigator Window to perform common server management tasks
  • use MySQL Workbench to define and configure both system variables and status variables
  • use MySQL Workbench to create and manage new user accounts
  • manage the permissions each user has within your MySQL environment

Overview/Description

MySQL is fast, reliable, easy to use, free to download, and supports standard SQL. Explore MySQL, including how to download and configure MySQL, and how to use the MySQL Workbench.



Target

Prerequisites: none

MySQL Database Development: Manipulating Data

Course Number:
it_dfmydd_04_enus
Lesson Objectives

MySQL Database Development: Manipulating Data

  • describe the use of the INSERT, UPDATE, and DELETE DML statements, and recognize their basic syntax
  • work with the INSERT statement to add new data to your MySQL tables
  • work with the UPDATE statement to modify existing data in your MySQL tables
  • work with the DELETE statement to remove existing data from your MySQL tables
  • use additional INSERT options to add new data to your MySQL tables
  • use additional UPDATE options to modify existing data in your MySQL tables
  • use MySQL Workbench to insert and delete data in your tables
  • use MySQL Workbench to view and modify existing data in your tables
  • use MySQL Workbench to retrieve data from your tables using filters
  • use MySQL Workbench to export existing data in your tables to an external file
  • use MySQL Workbench to import existing data into your tables

Overview/Description

Data Manipulation Language (DML) statements are used to manipulate the actual data in MySQL databases. Explore the syntax and use of the most common DML statements, including INSERT, UPDATE, and DELETE.



Target

Prerequisites: none

MySQL Database Development: Query and Performance Optimization

Course Number:
it_dfmydd_08_enus
Lesson Objectives

MySQL Database Development: Query and Performance Optimization

  • identify the types of indexes and how they function in queries
  • recognize potential problems affecting query run time and how to eliminate these problems
  • describe the purpose of the EXPLAIN statement and the results it returns
  • work with clustered and secondary indexes on a table
  • recognize general methods for maintaining tables and indexes
  • use the Visual Explain feature to generate a visual representation of a MySQL query
  • use the EXPLAIN statement to analyze query execution in the MySQL client
  • use the query optimizer to control query plan evaluation
  • identify strategies available for optimizing InnoDB storage usage
  • recognize options for optimizing the configuration for the server running MySQL
  • configure MySQL values to fine-tune the instance for optimal performance
  • download and install the MONyog performance monitoring tool for MySQL
  • use MONyog to monitor and configure OS settings to optimize MySQL performance

Overview/Description

Indexing and understanding the configuration of MySQL can be key to performance. Explore index types and configuration options that can be set to fine-tune the performance of your MySQL databases to optimal levels



Target

Prerequisites: none

MySQL Database Development: SELECT Statement and Operators

Course Number:
it_dfmydd_05_enus
Lesson Objectives

MySQL Database Development: SELECT Statement and Operators

  • recognize the basic syntax of the SELECT statement and how it is used to retrieve data
  • create SELECT statements using the WHERE and ORDER BY clauses to limit and order returned records
  • create INSERT INTO statements using SELECT to copy data between tables
  • create SELECT statements using the JOIN clause to return records from multiple tables
  • create SELECT statements using the UNION clause to combine the result of multiple SELECT statements into a single returned dataset
  • use comparison operators on both number and string values in SELECT statements
  • use logical operators to test values in SELECT statements
  • use assignment operators in SELECT statements to assign values to variables
  • use control flow operators to control the results returned by SELECT statements

Overview/Description

SELECT statements are used to retrieve data from MySQL tables or views. Explore the syntax and use of the most common SELECT statement elements, including SELECT, FROM, WHERE, and ORDER BY, as well as unions and joins.



Target

Prerequisites: none

MySQL Database Development: Stored Routines, Triggers, and the InnoDB memcached Plugin

Course Number:
it_dfmydd_07_enus
Lesson Objectives

MySQL Database Development: Stored Routines, Triggers, and the InnoDB memcached Plugin

  • create and execute stored programs in MySQL
  • create and execute stored routines in MySQL
  • use the CREATE_TRIGGER and DROP_TRIGGER statements to create a new trigger or delete an existing trigger in MySQL
  • create and execute triggers in MySQL
  • use events in MySQL to schedule and run repetitive tasks in your database
  • recognize how the InnoDB memcached plugin fits in the overall architecture of MySQL and the function it serves
  • install and configure the InnoDB memcached plugin for use in MySQL

Overview/Description

Stored programs (procedures) and routines (functions) in MySQL are used to create reusable, executable code. Explore the syntax and use of stored procedures and functions in MySQL



Target

Prerequisites: none

MySQL Database Development: Working with Functions

Course Number:
it_dfmydd_06_enus
Lesson Objectives

MySQL Database Development: Working with Functions

  • describe what functions are, what they are used for, and the different categories of functions available in MySQL
  • recognize the syntax of the GROUP BY clause in a SELECT statement and the basic aggregate functions that are available
  • work with the GROUP BY clause and aggregate functions in SELECT statements to filter result sets
  • work with numeric functions such as ROUND, POW, CEILING, and FLOOR
  • use the COUNT function to determine the number of rows returned by a query
  • use the MIN and MAX functions in queries to return the smallest and largest values
  • use the SUM and AVERAGE functions to calculate the total and average of the values returned by a query
  • work with various character functions in queries, including CONCAT, LEFT, RIGHT, and LENGTH
  • use the string comparison functions to compare one string to another
  • work with date and time values, including how to modify the way they are displayed

Overview/Description

Funtions in MySQL are used to produce values from other values, or to determine values that may be unknown. Explore the syntax and use of the most common MySQL functions, including aggregate, date/time and string functions.



Target

Prerequisites: none

Close Chat Live