Course DB583
MySQL: An Intensive Overview
MySQL: An Intensive Overview
Duration: 5 Days
Intended Audience
The course is aimed at those who already have some experience of working with relational databases and who need to get up to speed with MySQL quickly. MySQL is a powerful open source relational database that can be extended by adding functions to it, supports triggers and stored procedures. It is especially useful for Web designers who already have some experience with e.g. Access or MySQL and who wish to incorporate MySQL into their web applications.
Course Overview
This is an intensive course for MySQL application developers. It covers the fundamentals of relational database theory and design and the use of the Data Manipulation Language (DML) features of SQL to retrieve, delete and update information, and the Data Definition Language (DDL) features of SQL create databases, tables and indexes.
The course includes an introductory discussion (with demonstrations) of how MySQL is used in Web applications, and how it can be accessed from programming languages such as Perl, Java, PHP and Python and C.
Key Skills
- Revision of the relational model , relational database terminology and SQL
- Implement basic SQL queries
- Implement complex SQL queries
- Understand transactions
- Implement constraints
- Specify and deploy triggers and stored procedures
Practical Work
About 50% of the course time will be allocated to practical exercises. The exercises will be built around a specific case study ... and so will cover the entire process from design, to database implementation, to application development.
Attendees will be given a CD containing the compiled and source code for the most recent stable release of MySQL, as well as a collection of applications that enhance the usefulness of MySQL. The course exercises and solutions, plus further exercises (without solutions -- though these can be requested via email .... on presentation of evidence that a serious attempt to tackle them has been made) will also be on the CD.
- Identifying the entities, attributes and relationships for a given business application
- Carry out the normalisation to third normal form of a database that is (partly) in second normal form
- Interact with a pre-built database using SQL - both retrieving and modifying the data
- Adding new tables to the database and populating them with data
- Writing PL/PGSQL code to enforce referential data integrity constraints
- Implementing simple reporting applications
Course Contents
Introduction to MySQL
- The uses of databases
- The advantages of databases over files
- The history of MySQL
- Overview of obtaining and installing MySQL on Linux/Unix
Connecting to MySQL
- TCP/IP - Client/Server
- Command line utilities
- Graphical clients
- ODBC and JDBC
- Web interfaces
The Relational Model
- Relational Algebra
- Tables, Rows and Columns
- Keys
- Relationships
- Relational Operations
- SQL-92 features not supported by MySQL
Structured Query Language - SQL
- Data definition
- Inserting rows
- Updating rows
- Deleting rows
- Queries
- Joins
- Ordering
- Grouping
- Limiting
- Altering a table
Design and Normalisation
- Requirements and Design specifications
- Entity Relation(ER) diagrams (and their UML equivalents)
- Reasons for Normalising
- First, Second and Third Normal Forms
- Boyce-Codd Normal Form
- Fourth Normal Form
- Denormalisation
Advanced MySQL Features
- Constraints - NOT NULL, UNIQUE, PRIMARY KEY, Foreign Key/REFERENCES, CHECK
- Table management - ALTER, GRANT, REVOKE
- Table management - LISTEN, NOTIFY
- SQL Functions
- Triggers
Overview of Ways of implementing MySQL Clients
- C programs and the MySQL C API (libpq)
- Java programs and JDBC
- PHP, Perl, Python
- C++ and the MySQL C++ API
- ODBC
- Tcl/TK