MySQL Database Design

This MySQL class is delivered for private groups onsite at your offices or a location of your choice. It can also be delivered via the Internet for geographically distributed staff.

Experience in the following areas is required:

-Basic computer literacy with previous experience with command-line programs..
- Some knowledge of database concepts.
Experience in the following areas would be beneficial.

Some knowledge of database modeling.

Installing MySQL
Operating System Selection
Downloading and Installing MySQL On Windows
- Running the MySQL Configuration Wizard
- Starting and stopping the MySQL server
Installing MySQL On Linux and Unix
- Using RPM (Linux only)
- MySQL Packaged RPMs
- Using a Tar File to Install MySQL
- Starting the Server Automatically
Installing MySQL On Mac
Verifying a Downloaded File
Verifying a MySQL Installation
- mysqladmin: Obtain Server Status
Launching the MySQL Server
Installing From Source Files

Versions of the MySQL Server
- The mysqld_safe Wrapper
Mysql Directory Structure
Securing the MySQL Server: Set root Password
Delete Anonymous Accounts
Creating an Account for Basic Use
General Information using MySQL Monitor
Configuring MySQL

Relational Database Concepts
- Brief History of SQL
- Relational Database Management System
- Popular Databases
- Tables
- Rows
- Columns
- Relationships
- Datatypes
- Primary Keys
- Foreign Keys
- Valid Object References
- SQL Statements
Database Design Principles
Film Rentals - sakila
Basic Modeling Process: Creating a Data Model
- Logical versus Physical Model
- The Entity-Relationship Model
- Entities And Entity Sets
- Attributes
- Relationships
- Relationship Types
- Graphical Representation of a Model
- Physical Model: Mapping The ER Model To A Relational Database
Database Normalization
- First Normal Form
- Warning: Abnormal Normal Form!
- Second Normal Form
- Third Normal From

Creating Databases
- Deleting Databases
Creating Tables
- Dropping Tables
- Defining Table Types
- Altering Existing Table Structures
- Add a column
- Renaming Tables
- Temporary Tables
Creating Column Definitions
Data Types in MySQL
- Numeric Data Types
- String Data Types
- Character Set Fundamentals
- Set Types
- Date/Time Data Types
- Options and Attributes
Constraints
- NOT NULL Constraint: Defining a Column`s Nullability
- UNIQUE Constraint
- PRIMARY KEY Constraint: Defining a Master Key
- Defining Auto-Increment Columns
- Referential Integrity Constraints: Defining Foreign Keys
- Constraint Checking on a Transaction
- Deferred Checking
- Defining Default Values
Managing Indexes
- Defining Indexes when Creating Tables
- CREATE INDEX Statement
- Full-Text Indexes
- Deleting Indexes

Identifiers in MySQL
- Identifier Names
Schema Object Names
- Using Double Quotes
- Using Quote Within Names
- Using Digits
Some Restrictions
Identifier Qualifiers
Mapping of Identifiers to Filenames
Identifier Case Sensitivity
- lower_case_table_names Setting
- Avoiding Data Transfer Problems

SHOW and DESCRIBE Statements
- Using Table-Related SHOW Statements
- Using DESCRIBE Statement
INFORMATION_SCHEMA Tables
- Showing Databases

MySQL Table Types
MySQL Table Types (Storage Engines)
Setting/Changing Engine
MyISAM Tables
- Full-Text Search on MyISAM Tables
- Creating a Full-Text Index
- Using Full-Text Search
InnoDB Tables
- InnoDB - Limitations and Drawbacks
- InnoDB Disk Management
- Preventing Problems
- Moving Files
FOREIGN KEY Constraints in InnoDB
- Checking Foreign Keys
Choosing - MyISAM or InnoDB?
HEAP or MEMORY Tables
Temporary Tables
MERGE Tables
Other Table Types
- ARCHIVE or Compressed Tables (since MySQL 4.1)
- CSV Type Tables(since MySQL 4.1)
- NDB or Cluster Tables (since MySQL 4.1)
- FEDERATED or External Tables (since MySQL 5.0)
- BDB Tables
Table Files Storage