uCertify

ISM 4210 - Introduction to Database Management

(FU-ISM4210.AJB1)
Lessons
Lab
TestPrep
AI Tutor (Add-on)
Get A Free Trial

Skills You’ll Get

Download Course Outline

1

Background Concepts

  • Introducing databases
  • Exploring MySQL
  • Exercise 1.01: Organizing data in a relational format
  • Exploring MySQL architecture
  • Storage engines (InnoDB and MyRocks)
  • Data modeling
  • Normalization
  • Activity 1.01: Creating an optimized table for an employee project
  • Summary
2

Creating a Database

  • Developing databases
  • The MySQL Workbench GUI
  • Accessing MySQL through the command-line interface
  • Creating a database
  • Using Workbench to add a table
  • MySQL table indexes and foreign keys
  • Reverse engineering a database
  • Activity 2.01 – modifying the EER diagram, the model, and the database
  • Summary
3

Using SQL to Work with a Database

  • An introduction to working with databases using SQL
  • Working with data
  • Backing up databases
  • Restoring databases
  • Working with SQL code to maintain a database
  • Creating a new database
  • Creating and modifying tables
  • SQL queries to create indexes and foreign keys
  • Activity 3.1 – creating a table with indexes and foreign keys
  • Altering table queries
  • Adding data to a table
  • Updating data in a record
  • Deleting data from tables
  • Blobs, files, and file paths
  • Activity 3.2 – adding image file paths to the database
  • Summary
4

Selecting, Aggregating, and Applying Functions

  • An introduction to querying data
  • Querying tables in MySQL
  • Exercise 4.01 – working with simple queries
  • Filtering results
  • Exercise 4.02 – filtering results
  • Using functions on data
  • Exercise 4.03 – using functions
  • Aggregating data
  • Exercise 4.04 – aggregating data
  • Case statements
  • Exercise 4.05 – writing case statements
  • Activity 4.01 – collecting information for a travel article
  • Summary
5

Correlating Data across Tables

  • Introduction to processing data across tables
  • Joining two tables
  • Analyzing subqueries
  • Common table expressions
  • Analyzing query performance with EXPLAIN
  • Activity 5.01: The Sakila video store
  • Activity 5.02: Generating a list of years
  • Summary
6

Stored Procedures and Other Objects

  • Introduction to database objects
  • Exploring various database objects
  • Working with views
  • Activity 6.01 – updating the data in a view
  • Working with user-defined functions
  • Working with stored procedures
  • Working with IN, OUT, and INOUT
  • Exploring triggers
  • Using transactions
  • Summary
7

Creating Database Clients in Node.js

  • Introduction to database management with Node.js
  • Best practices for SQL client development
  • JavaScript using Node.js
  • Connecting to MySQL
  • Activity 7.01 – building a database application with Node.js
  • Summary
8

Working with Data Using Node.js

  • Interacting with databases
  • Inserting records in Node.js
  • Updating the records of a table
  • Activity 8.01 – multiple updates
  • Displaying data in browsers
  • ODBC connections
  • Activity 8.02 – designing a customer database
  • Summary
9

Microsoft Access – Part 1

  • Introduction to MS Access
  • MS Access database application configurations
  • Upsizing an MS Access database to MySQL
  • Manually exporting MS Access tables
  • Adjusting field properties
  • Migrating with wizards
  • Linking to your tables and views
  • Refreshing linked MySQL tables
  • Activity 9.01 – linking the remaining MySQL tables to your MS Access database
  • Summary
10

Microsoft Access – Part 2

  • Introduction to MS Access
  • Migrating an MS Access application to MySQL
  • Activity 10.01 – Converting gender and job statistics
  • Calling MySQL functions
  • Activity 10.02 – Creating a function and calling it
  • Calling MySQL stored procedures
  • Activity 10.03 – Creating MySQL stored procedures and using them in VBA
  • Using parameters
  • Activity 10.04 – Parameterized stored procedure (series list)
  • Activity 10.05 – Multiple parameters stored procedure (date list)
  • The Bad Bits form
  • Summary
11

MS Excel VBA and MySQL – Part 1

  • Introduction to Excel
  • Exploring the ODBC connection
  • Exploring the Excel VBA structure
  • Learning about VBA libraries
  • Connecting to the MySQL database using VBA
  • Reading data from MySQL using VBA
  • Populating charts
  • Activity 11.01 – Creating a chart (artist track sales)
  • Summary
12

Working With Microsoft Excel VBA – Part 2

  • An introduction to MySQL connections
  • Connecting to the MySQL database using ODBC
  • Exploring generic data read functions
  • Creating connections to MySQL in Excel
  • Inserting data using MySQL for Excel
  • Updating data using MySQL for Excel
  • Pushing data from Excel
  • Pivot tables
  • Activity 12.01 – building a MySQL-based Excel document
  • Summary
13

Getting Data into MySQL

  • An introduction to data preparation
  • Working with the X DevAPI
  • Inserting documents
  • Loading data from a SQL file
  • Loading data from a CSV file
  • Loading data from a JSON file
  • Using the CSV storage engine to export data
  • Using the CSV storage engine to import data
  • Searching and filtering JSON documents
  • Using JSON functions and operators to query JSON columns
  • Using generated columns to query and index JSON data
  • Activity 13.01 – Exporting report data to CSV for Excel
  • Summary
14

Manipulating User Permissions

  • Introduction to user permissions
  • Exploring users and accounts
  • Exercise 14.01 – creating users and granting permissions
  • Changing users
  • Flush privileges
  • Changing permissions
  • Exercise 14.02 – modifying users and revoking permissions
  • Using roles
  • Exercise 14.03 – using roles to manage permissions
  • Troubleshooting access problems
  • Activity 14.01 – creating users for managing the world schema
  • Summary
15

Logical Backups

  • An introduction to backups
  • Understanding the basics of backups
  • Logical and physical backup
  • Types of restore
  • Scheduling backups
  • Using point-in-time recovery with binlog files
  • Activity 15.01 – backing up and restoring a single schema
  • Activity 15.02 – performing a point-in-time restore
  • Summary
16

What Is Relational?

  • Types of Databases
  • A Brief History of the Relational Model
  • Anatomy of a Relational Database
  • What’s in It for You?
  • Summary
17

Ensuring Your Database Structure Is Sound

  • Why Is this Lesson Here?
  • Why Worry about Sound Structures?
  • Fine-Tuning Columns
  • Fine-Tuning Tables
  • Establishing Solid Relationships
  • Is That All?
  • Summary
18

A Concise History of SQL

  • The Origins of SQL
  • Early Vendor Implementations
  • “… And Then There Was a Standard”
  • Evolution of the ANSI/ISO Standard
  • Commercial Implementations
  • What the Future Holds
  • Why Should You Learn SQL?
  • Which Version of SQL Does this Course Cover?
  • Summary
19

Creating a Simple Query

  • Introducing SELECT
  • The SELECT Statement
  • A Quick Aside: Data versus Information
  • Translating Your Request into SQL
  • Eliminating Duplicate Rows
  • Sorting Information
  • Saving Your Work
  • Sample Statements
  • Summary
  • Problems for You to Solve
20

Getting More Than Simple Columns

  • What Is an Expression?
  • What Type of Data Are You Trying to Express?
  • Changing Data Types: The CAST Function
  • Specifying Explicit Values
  • Types of Expressions
  • Using Expressions in a SELECT Clause
  • That “Nothing” Value: Null
  • Sample Statements
  • Summary
  • Problems for You to Solve
21

Filtering Your Data

  • Refining What You See Using WHERE
  • Defining Search Conditions
  • Using Multiple Conditions
  • Nulls Revisited: A Cautionary Note
  • Expressing Conditions in Different Ways
  • Sample Statements
  • Summary
  • Problems for You to Solve
22

Thinking in Sets

  • What Is a Set, Anyway?
  • Operations on Sets
  • Intersection
  • Difference
  • Union
  • SQL Set Operations
  • Summary
23

INNER JOINs

  • What Is a JOIN?
  • The INNER JOIN
  • Uses for INNER JOINs
  • Sample Statements
  • Summary
  • Problems for You to Solve
24

OUTER JOINs

  • What Is an OUTER JOIN?
  • The LEFT/RIGHT OUTER JOIN
  • The FULL OUTER JOIN
  • Uses for OUTER JOINs
  • Sample Statements
  • Summary
  • Problems for You to Solve
25

UNIONs

  • What Is a UNION?
  • Writing Requests with UNION
  • Uses for UNION
  • Sample Statements
  • Summary
  • Problems for You to Solve
26

Subqueries

  • What Is a Subquery?
  • Subqueries as Column Expressions
  • Subqueries as Filters
  • Uses for Subqueries
  • Sample Statements
  • Summary
  • Problems for You to Solve
27

Simple Totals

  • Aggregate Functions
  • Using Aggregate Functions in Filters
  • Sample Statements
  • Summary
  • Problems for You to Solve
28

Grouping Data

  • Why Group Data?
  • The GROUP BY Clause
  • “Some Restrictions Apply”
  • Uses for GROUP BY
  • Sample Statements
  • Summary
  • Problems for You to Solve
29

Filtering Grouped Data

  • A New Meaning for “Focus Groups”
  • Where You Filter Makes a Difference
  • Uses for HAVING
  • Sample Statements
  • Summary
  • Problems for You to Solve
30

Updating Sets of Data

  • What Is an UPDATE?
  • The UPDATE Statement
  • Some Database Systems Allow a JOIN in the UPDATE Clause
  • Uses for UPDATE
  • Sample Statements
  • Summary
  • Problems for You to Solve
31

Inserting Sets of Data

  • What Is an INSERT?
  • The INSERT Statement
  • Uses for INSERT
  • Sample Statements
  • Summary
  • Problems for You to Solve
32

Deleting Sets of Data

  • What Is a DELETE?
  • The DELETE Statement
  • Uses for DELETE
  • Sample Statements
  • Summary
  • Problems for You to Solve
33

“NOT” and “AND” Problems

  • A Short Review of Sets
  • Finding Out the “Not” Case
  • Finding Multiple Matches in the Same Table
  • Sample Statements
  • Summary
  • Problems for You to Solve
34

Condition Testing

  • Conditional Expressions (CASE)
  • Solving Problems with CASE
  • Sample Statements
  • Summary
  • Problems for You to Solve
35

Using Unlinked Data and “Driver” Tables

  • What Is Unlinked Data?
  • Solving Problems with Unlinked Data
  • Solving Problems Using “Driver” Tables
  • Sample Statements
  • Summary
  • Problems for You to Solve
36

Performing Complex Calculations on Groups

  • Grouping in Sub-Groups
  • Extending the GROUP BY Clause
  • Getting Totals in a Hierarchy Using Rollup
  • Calculating Totals on Combinations Using CUBE
  • Creating a Union of Totals with GROUPING SETS
  • Variations on Grouping Techniques
  • Sample Statements
  • Summary
  • Problems for You to Solve
37

Partitioning Data into Windows

  • What You Can Do With a “Window” into Your Data
  • Calculating a Row Number
  • Ranking Data
  • Splitting Data into Quintiles
  • Using Windows with Aggregate Functions
  • Sample Statements
  • Summary
  • Problems for You to Solve
A

Appendix A: SQL Standard Diagrams

B

Appendix B: Schema for the Sample Databases

  • Sales Orders Example Database
  • Sales Orders Modify Database
  • Entertainment Agency Example Database
  • Entertainment Agency Modify Database
  • School Scheduling Example Database
  • School Scheduling Modify Database
  • Bowling League Example Database
  • Bowling League Modify Database
  • Recipes Database
  • “Driver” Tables
C

Appendix C: Date and Time Types, Operations, and Functions

  • IBM DB2
  • Microsoft Access
  • Microsoft SQL Server
  • MySQL
  • Oracle
  • PostgreSQL

1

Background Concepts

  • Creating a Relational Database
  • Creating a New Database Using MS Access
  • Normalizing Data from 2NF to 3NF
  • Normalizing Data from 1NF to 2NF
2

Creating a Database

  • Creating a Connection With the MySQL Workbench GUI
  • Creating a Database using MySQL Workbench
  • Creating a Table Using MySQL Workbench
  • Creating an Index
  • Creating a Foreign Key
  • Creating an EER Diagram
3

Using SQL to Work with a Database

  • Modifying an Existing Table
  • Adding a Record
  • Updating a Record
  • Deleting Data from a Table
4

Selecting, Aggregating, and Applying Functions

  • Filtering Results
  • Using Functions
  • Aggregating Data
  • Using CASE Statements
5

Correlating Data across Tables

  • Joining Two Tables
  • Using Common Table Expressions (CTEs)
  • Using the EXPLAIN Command
6

Stored Procedures and Other Objects

  • Creating a View
  • Creating a Stored Procedure
  • Using Triggers
  • Implementing a Transaction
7

Creating Database Clients in Node.js

  • Testing the Output of a Node.js Script
  • Writing to a Disk File
8

Working with Data Using Node.js

  • Creating a LAN DSN/ODBC Connection
9

Microsoft Access – Part 1

  • Creating an ODBC for a MySQL Database Schema
  • Upsizing a Table from Microsoft Access to MySQL
10

Microsoft Access – Part 2

  • Calling a MySQL Function
  • Creating a Parameterized Stored Procedure
11

MS Excel VBA and MySQL – Part 1

  • Activating the Developer tab and the VBA IDE
  • Creating a Code Module
12

Getting Data into MySQL

  • Importing a SQL File using MySQL Workbench
13

Creating a Simple Query

  • Using the SELECT Statement
  • Using the DISTINCT Keyword
  • Using the ORDER BY Clause
14

Getting More Than Simple Columns

  • Using the CAST Function
  • Using a Literal
  • Using the Concatenation Expression
  • Using the NULL Values
  • Naming an Expression
  • Finding Null Values in a Column
15

Filtering Your Data

  • Using the LIKE Predicate
  • Using the IN Predicate
  • Using the BETWEEN Predicate
  • Using Comparison Predicates
  • Using the WHERE Clause
  • Using the NOT Operator
  • Using the ESCAPE Option
  • Using the Order of Precedence
  • Using AND and OR Operators
  • Using the NOT IN Operator
16

Thinking in Sets

  • Using the UNION Operator
  • Using the EXCEPT Operator
  • Using the INTERSECT Operator
17

INNER JOINs

  • Using an INNER JOIN
  • Using a Subquery with the IN Predicate
18

OUTER JOINs

  • Using the FULL OUTER JOIN
  • Using the RIGHT OUTER JOIN
  • Using the LEFT OUTER JOIN
19

UNIONs

  • Sorting with UNION
20

Subqueries

  • Using Subqueries
  • Using the COUNT Function
  • Using the SOME Predicate
  • Using the ALL predicate
  • Using the ANY Predicate
21

Simple Totals

  • Using the MIN and MAX Functions
  • Using the SUM and AVG Functions
22

Grouping Data

  • Using the GROUP BY Clause
23

Filtering Grouped Data

  • Using the HAVING Clause
24

Updating Sets of Data

  • Using the UPDATE Statement
25

Inserting Sets of Data

  • Using the INSERT Statement
26

Deleting Sets of Data

  • Using the DELETE Statement
27

“NOT” and “AND” Problems

  • Using the NOT EXISTS Command
28

Condition Testing

  • Using the CASE Statement
29

Performing Complex Calculations on Groups

  • Using ROLLUP
  • Using the CUBE clause
30

Partitioning Data into Windows

  • Using the RANK Function

Related Courses

All Courses