MySQL Database Design

Delivery Options

• Live eLearning (2 days): $1,050.00

» Free Demo

» Watch Presentation

» Contact Education Consultant

• Customized Onsite Training (2 days): Request a Proposal

Upcoming Live eLearning Classes

There are currently no upcoming Live eLearning classes. Please contact us if you would like us to schedule a class.

Class Description

Class Overview

Class Goals

• Install MySQL
• Understand various MySQL programs
• Use databases effectively
• Learn database concepts and fundamentals.
• Understand efficient design.
• Learn application analysis and E-R modeling.
• Learn normalization principles.
• Use the INFORMATION_SCHEMA database to access metadata.
• Install and Upgrade MySQL 5.0 for the most common operating systems.
• Start and shutdown MySQL.
• Understand MySQL storage engines, transactions and features of the common engines.

Class Outline

1. Installing MySQL
   1. Installing MySQL
   2. Operating System Selection
   3. Downloading and Installing MySQL On Windows
      1. Running the MySQL Configuration Wizard
      2. Starting and Stopping the MySQL Server
   4. Installing MySQL On Linux and Unix
      1. Using RPM (Linux only)
      2. MySQL Packaged RPMs
      3. Using a Tar File to Install MySQL
      4. Starting the Server Automatically
   5. Installing MySQL On Mac
   6. Verifying a Downloaded File
   7. Verifying a MySQL Installation
      1. mysqladmin: Obtain Server Status
      2. mysql: Is Server Running?
      3. Query on Base Tables
   8. Launching the MySQL Server
   9. Installing From Source Files
2. Setting up MySQL
   1. Versions of the MySQL Server
      1. The mysqld_safe Wrapper
   2. MySQL Directory Structure
   3. Securing the MySQL Server: Set root Password
   4. Delete Anonymous Accounts
   5. Creating an Account for Basic Use
   6. General Information using MySQL Monitor
   7. Configuring MySQL
3. Designing Databases
   1. Relational Database Concepts
      1. Brief History of SQL
      2. Relational Database Management System
      3. Popular Databases
      4. Tables
      5. Rows
      6. Columns
      7. Relationships
      8. Datatypes
      9. Primary Keys
      10. Foreign Keys
      11. Valid Object References
      12. SQL Statements
   2. Database Design Principles
   3. Film Rentals - sakila
   4. Basic Modeling Process: Creating a Data Model
      1. Logical versus Physical Model
      2. The Entity-Relationship Model
      3. Entities And Entity Sets
      4. Attributes
      5. Relationships
      6. Relationship Types
      7. Graphical Representation of a Model
      8. Physical Model: Mapping The ER Model To A Relational Database
   5. Database Normalization
      1. First Normal Form
      2. Warning: Abnormal Normal Form!
      3. Second Normal Form
      4. Third Normal From
4. Creating Databases and Components
   1. Creating Databases
      1. Deleting Databases
   2. Creating Tables
      1. Dropping Tables
      2. Defining Table Types
      3. Altering Existing Table Structures
      4. Add a column
      5. Renaming Tables
      6. Temporary Tables
   3. Creating Column Definitions
   4. Data Types in MySQL
      1. Numeric Data Types
      2. String Data Types
      3. Character Set Fundamentals
      4. Set Types
      5. Date/Time Data Types
      6. Options and Attributes
   5. Constraints
      1. NOT NULL Constraint: Defining a Column's Nullability
      2. UNIQUE Constraint
      3. PRIMARY KEY Constraint: Defining a Master Key
      4. Defining Auto-Increment Columns
      5. Referential Integrity Constraints: Defining Foreign Keys
      6. Constraint Checking on a Transaction
      7. Deferred Checking
      8. Defining Default Values
   6. Managing Indexes
      1. Defining Indexes when Creating Tables
      2. CREATE INDEX Statement
      3. Full-Text Indexes
      4. Deleting Indexes
5. Identifiers in MySQL
   1. Identifiers in MySQL
      1. Identifier Names
   2. Schema Object Names
      1. Using Double Quotes
      2. Using Quote Within Names
      3. Using Digits
   3. Some Restrictions
   4. Identifier Qualifiers
   5. Mapping of Identifiers to Filenames
   6. Identifier Case Sensitivity
      1. lower_case_table_names Setting
      2. Avoiding Data Transfer Problems
6. Show Database Information
   1. SHOW and DESCRIBE Statements
      1. Using Table-Related SHOW Statements
      2. Using DESCRIBE Statement
   2. INFORMATION_SCHEMA Tables
      1. Showing Databases
7. MySQL Table Types
   1. MySQL Table Types (Storage Engines)
   2. Setting/Changing Engine
   3. MyISAM Tables
      1. Full-Text Search on MyISAM Tables
      2. Creating a Full-Text Index
      3. Using Full-Text Search
   4. InnoDB Tables
      1. InnoDB - Limitations and Drawbacks
      2. InnoDB Disk Management
      3. Preventing Problems
      4. Moving Files
   5. FOREIGN KEY Constraints in InnoDB
      1. Checking Foreign Keys
   6. Choosing - MyISAM or InnoDB?
   7. HEAP or MEMORY Tables
   8. Temporary Tables
   9. MERGE Tables
   10. Other Table Types
       1. ARCHIVE or Compressed Tables (since MySQL 4.1)
       2. CSV Type Tables(since MySQL 4.1)
       3. NDB or Cluster Tables (since MySQL 4.1)
       4. FEDERATED or External Tables (since MySQL 5.0)
       5. BDB Tables
   11. Table Files Storage

Class Materials

All students receive a course manual or book and all the class examples.

Students in private onsite classes will also receive:

• Technical Reference Library

Class Prerequisites

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.

Technical Requirements

Our computer technical requirements and setup process is easy, with support just a click away.

• Course technical requirements and setup instructions.

• MySQL ^TM and all MySQL-based marks are trademarks or registered trademarks of Sun Microsystems, Inc. or its subsidiaries in the U.S. and other countries.