Advanced SQL Database Administration with MySQL Workbench

Advanced SQL Database Administration with MySQL Workbench

English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 8 Hours | 2.52 GB

Master advanced SQL skills for building, optimizing, and administering relational databases using MySQL Workbench MySQL is a powerful tool for managing and analyzing relational databases, and a skill that every analyst and BI professional should have in the arsenal. In this course, you’ll take your database administration skills to the next level, expanding into advanced concepts like replication, automation, and database security. We’ll start by introducing the course structure, installing MySQL Community and Workbench, and reviewing key database administration (DBA) tasks; creating and editing schemas & tables, inserting and deleting records, and reviewing the key principles of normalized databases. From there we’ll work through more advanced concepts like replication, backup and recovery, and learn how to leverage automation via stored procedures, triggers, and scheduled events. Throughout the course, you’ll be playing the role of Lead Database Administrator for an e-commerce startup, and learn how to build and populate an entire database from the ground up using real-world projects and assignments. SQL Intro & MySQL Setup For students who haven’t completed one of our other SQL courses, we’ll demonstrate exactly how to download and install the tools that we’ll need to access MySQL, including MySQL Community Server & MySQL Workbench. We’ll share download guides for both Mac & PC, and run our first piece of SQL code to generate the project database that we’ll analyze throughout the course. Importing Data to the Database We’ll quickly review some basic concepts like creating, inserting, and deleting records. Then we’ll learn how to tackle large data imports with ease, using the table data import wizard. We’ll be using this handy tool throughout the course. You will construct and populate a database from the ground up as the business grows, just like a real DBA as part of the course project. Altering, Updating & Mapping Keys We’ll briefly review critical concepts like altering tables and updating records, discuss the ins and outs of primary and foreign keys, and from there you’ll jump right into your assignments and quizzes for this section. Replication, Backup & Recovery Here we’ll introduce the critical DBA concepts of replication, backup and recovery. We’ll discuss the various benefits of replication, different methods of backing up your databases, and you’ll learn how to restore your data should the need arise. This is getting into some pretty serious stuff! MID-COURSE PROJECT You will need all of the skills you’ve learned so far to complete this hands on mid-course project. You’ll help your CEO by enhancing the data structure to accommodate changing business needs, and insert new data into various places within your database. Trigger Automation Here we’ll dive into one of the most powerful automation tools you’ll use as a DBA…triggers! You’ll learn how you can create triggers to look for changes in the database via create, update, and delete statements, and then automatically enact changes on one of more of your tables. Triggers will be your best friend as a DBA, and can be extremely handy for Analysts to understand as well. Creating Views Sometimes you don’t need to create a table, and a view (aka “a virtual table) is a better option. We’ll walk through what views are, how you create them, and some of the major benefits of using views in certain situations. The EER Diagram Editor One of the best tools at your disposal for understanding your database and communicating its structure to others in your organization is leveraging EER Diagrams. We’ll review some of the basics that we discussed in the Beginner course, and we’ll also learn how to use the EER Diagram Editor to reverse engineer your schema, which is an extremely handy tool! Stored Procedures & Scheduled Events Next, we’ll dive deeper into automation by learning how to create stored procedures and scheduled events. If you liked triggers, you’re going to love these! You can use stored procedures to simplify your processes, make your code more modular and easier to understand, and centralize certain parts of complex queries that you can leverage throughout your organization. Scheduled events will allow you to run one-time or recurring processes on your database, which you can schedule on whatever cadence you want. These are really fun! Automating Data from Website Activity Here we’ll be talking about the different types of data businesses typically track from their website activity, including traffic source data, on-site behavior, product purchases, and more. We’ll discuss the various methods used to capture the various data types, and discuss the DBA’s role in partnering with Engineering to make sure this is all working as it should. Database Security In the last section before the final project, we’ll discuss some of the most common types of database security threats you should be aware of as a DBA, as well as security best practices to mitigate your risk. Again, we’ll discuss the DBA’s role in the organization, how a DBA will partner with Engineering here, and who typically owns what. FINAL COURSE PROJECT This hands-on project will test all of your DBA skills. You will need to alter tables, import lots of data, design an optimal structure for an entirely new set of tables for a new business initiative, create an EER Diagram describing your new tables, and then you’ll actually create them. After that, you’ll need to create a stored procedure and a view, which will both used for reporting on the new data the company will be collecting. Finally, you’ll need to come up with a comprehensive security plan to make sure your organization is aware of threats and following best practices. I know this sounds like a lot, but at this point you’ll be ready for it! What you’ll learn
  • Write SQL queries and use MySQL Workbench to create new databases from scratch
  • Explore database security threats and practice techniques like replication, backup and recovery
  • Leverage advanced automation tools like triggers, stored procedures, and scheduled events
  • Create and modify EER diagrams to understand table structures and relationships
  • Work through practical, real-world assignments and projects you won’t find anywhere else
  • Get LIFETIME access to resource files, quizzes, assignments, and expert support
+ Table of Contents

Getting Started
1 Course Structure & Outline
2 READ ME Important Notes for New Students
3 DOWNLOAD Course Resources
4 Introducing The Course Project
5 Setting Expectations

Prerequisite Skills
6 Prerequisite Skills Review

MySQL Intro & Setup
7 MySQL Installation Overview
8 MAC Download Community Server
9 MAC Download MySQL Workbench
10 PC Download Community Server & Workbench
11 Connecting Workbench to the Server
12 MySQL Workbench Interface
13 Preparing Workbench

Importing Data Files to the Database
14 Overview of Create, Insert & Delete
15 REVIEW Creating a Schema
16 REVIEW Creating a Table
17 REVIEW Inserting Records into a Table
18 REVIEW Deleting Records
19 Importing Data from a File
20 ASSIGNMENT Importing Order Item Data
21 SOLUTION Importing Order Item Data
22 ASSIGNMENT Importing Refund Data
23 SOLUTION Importing Refund Data
24 ASSIGNMENT Deleting Records
25 SOLUTION Deleting Records
26 ASSIGNMENT Importing Remaining 2012 Data
27 SOLUTION Importing Remaining 2012 Data
28 BONUS Load Data

Altering, Updating & Mapping Keys
29 Overview of Alter, Update & Key Relationships
30 REVIEW Altering Tables
31 REVIEW Updating Records
32 REVIEW Table Relationship & Cardinality
33 ASSIGNMENT Introducing a New Product
34 SOLUTION Introducing a New Product
35 ASSIGNMENT Adding Product to Order Items
36 SOLUTION Adding Product to Order Items
37 ASSIGNMENT Updating Product Data in Order Items
38 SOLUTION Updating Product Data in Order Items
39 ASSIGNMENT Primary Key to Foreign Key Mapping
40 SOLUTION Primary Key to Foreign Key Mapping
41 ASSIGNMENT Importing 2013 Q1 Data
42 SOLUTION Importing 2013 Q1 Data

Replication, Backup & Recovery
43 Replication
44 Backup & Recovery
45 ASSIGNMENT Backup & Recovery
46 SOLUTION Backup & Recovery

Mid-Course Project
47 ASSIGNMENT Mid-Course Project
48 SOLUTION Mid-Course Project

Trigger Automation
49 REVIEW Triggers Basics
50 Triggers Deep Dive
51 ASSIGNMENT Creating & Updating Orders
52 SOLUTION Creating & Updating Orders
53 ASSIGNMENT Triggers
54 SOLUTION Triggers
55 ASSIGNMENT Testing Your Trigger System
56 SOLUTION Testing Your Trigger System

Creating Views
57 Views
58 ASSIGNMENT Creating Website Sessions
59 SOLUTION Creating Website Sessions
60 ASSIGNMENT Creating Views
61 SOLUTION Creating Views

The EER Diagram Editor
62 REVIEW The EER Diagram Editor
63 Reverse Engineering EER Diagrams
64 ASSIGNMENT The EER Diagram Editor
65 SOLUTION The EER Diagram Editor

Stored Procedures & Scheduled Events
66 Stored Procedures
67 Scheduled Events
68 ASSIGNMENT Stored Procedures
69 SOLUTION Stored Procedures

Automating Data from Website Activity
70 Writing Data from Website Activity
71 ASSIGNMENT Writing Data from Website Activity
72 SOLUTION Writing Data from Website Activity
73 ASSIGNMENT Importing Website Pageview Data
74 SOLUTION Importing Website Pageview Data

Security
75 Security Threats
76 Security Best Practices
77 ASSIGNMENT Database Security
78 SOLUTION Database Security

Final Course Project
79 ASSIGNMENT Final Course Project
80 SOLUTION Final Course Project

Wrapping Up
81 BONUS LECTURE